SQL query generated with AdvancedCriteria uses a completely different value for field

[kmkale]

Version : v9.0p_2013-09-12

I have a datasource with following definition :

<DataSource 
	tableName="vlogbookmaster"
	ID="vlogbookmaster"
	serverType="sql"
>
	<fields>
		<field name="actiondetailid" type="integer"></field>
		<field name="assetname" length="35" type="text"></field>
		<field name="periodfrom" type="datetime" ></field>
		<field name="username" length="50" type="text"></field>
		<field name="remarks" length="200" type="text"></field>
		<field name="alerts" type="integer"></field>
		<field name="partybranchid" type="integer"></field>
	</fields>
	 <operationBindings>
    
    <operationBinding operationType="fetch">
      <customSQL>
    	SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause and partybranchid=$session.partybranchid ORDER BY $defaultOrderClause
  	  </customSQL>
    </operationBinding>
   
  </operationBindings>
</DataSource>

The field partybranchid is of type bigint in our postgresql database ( version 9.1)
I am using this datasource in a simple listgrid with showFilterEditor=true. When the listgrid is initially populated, it sends a simple criteria object which generates a query like :

=== 2013-11-27 16:26:08,253 [c-25] INFO  SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Performing fetch operation with
	criteria: {partybranchid:-2500000000}	values: {partybranchid:-2500000000}
=== 2013-11-27 16:26:08,253 [c-25] INFO  SQLDataSource - [builtinApplication.vlogbookmaster_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-11-27 16:26:08,254 [c-25] DEBUG SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-11-27 16:26:08,254 [c-25] DEBUG SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Eval'd row count query: SELECT COUNT(*) FROM vlogbookmaster WHERE (LOWER(CAST(vlogbookmaster.partybranchid AS varchar(50))) LIKE '%-2500000000%')
=== 2013-11-27 16:26:08,255 [c-25] DEBUG SQLDriver - [builtinApplication.vlogbookmaster_fetch] About to execute SQL query in 'USCProduction-localhost' using connection '1222743196'
=== 2013-11-27 16:26:08,255 [c-25] INFO  SQLDriver - [builtinApplication.vlogbookmaster_fetch] Executing SQL query on 'USCProduction-localhost': SELECT COUNT(*) FROM vlogbookmaster WHERE (LOWER(CAST(vlogbookmaster.partybranchid AS varchar(50))) LIKE '%-2500000000%')
=== 2013-11-27 16:26:08,328 [c-25] DEBUG SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Using SQL Limit query
=== 2013-11-27 16:26:08,328 [c-25] DEBUG SQLDataSource - [builtinApplication.vlogbookmaster_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT vlogbookmaster.actiondetailid, vlogbookmaster.alerts, vlogbookmaster.assetname, vlogbookmaster.partybranchid, vlogbookmaster.periodfrom, vlogbookmaster.remarks, vlogbookmaster.username FROM vlogbookmaster WHERE (LOWER(CAST(vlogbookmaster.partybranchid AS varchar(50))) LIKE '%-2500000000%') OFFSET 0 LIMIT 75
=== 2013-11-27 16:26:08,373 [c-25] INFO  DSResponse - [builtinApplication.vlogbookmaster_fetch] DSResponse: List with 75 items

but when I try to filter data using the date filter shown for the periodfrom field, an adnavcedcriteria is generated and sent to the server like :

=== 2013-11-27 16:05:33,650 [ec-9] INFO  SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Performing fetch operation with
	criteria: {criteria:[{value:-2500000000,fieldName:"partybranchid",operator:"equals"},{criteria:[{criteria:[{value:new Date(1380623640000),fieldName:"periodfrom",operator:"greaterOrEqual"},{value:new Date(1385576999000),fieldName:"periodfrom",operator:"lessOrEqual"}],operator:"and"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{value:-2500000000,fieldName:"partybranchid",operator:"equals"},{criteria:[{criteria:[{value:new Date(1380623640000),fieldName:"periodfrom",operator:"greaterOrEqual"},{value:new Date(1385576999000),fieldName:"periodfrom",operator:"lessOrEqual"}],operator:"and"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}

If you notice the value for partybranchid in the advanced criteria above is value:-2500000000,fieldName:"partybranchid".
But the actual query generated with this advanced criteria is :

=== 2013-11-27 16:05:33,651 [ec-9] DEBUG SQLDataSource - [builtinApplication.vlogbookmaster_fetch] Eval'd row count query: SELECT COUNT(*) FROM vlogbookmaster WHERE ((vlogbookmaster.partybranchid = 1794967296 AND vlogbookmaster.partybranchid IS NOT NULL) AND (((vlogbookmaster.periodfrom >= '2013-10-01 16:04:00' AND vlogbookmaster.periodfrom IS NOT NULL) AND (vlogbookmaster.periodfrom <= '2013-11-27 23:59:59' OR vlogbookmaster.periodfrom IS NULL))))

If you notice the value for partybranchid field in the generated query has changed to 1794967296. This is my problem.

Please advise?

[Isomorphic]

Please post the *complete* server log for the request. Never post just selected excerpts without posting the complete log - everything we need to see is in the parts you omitted.

[kmkale]

I am not allowed to post that log in the forum, so I have sent it to support@isomorphic.com by email attachment with a link to this forum thread.

Hope it helps you in identifying the problem. Please help..

[Isomorphic]

We can't see any plausible reason why this complete server log could not have been posted here, please do so in the future, or if part of the log has to be sanitized (to remove a password or whatever), just edit that part of the log, and clearly mark your edits.

As far as the issue you're having, you're claiming "partybranchid" is sent from the browser, but your logs clearly indicate this is not the case. You see to be modifying the request with your own server-side code, presumably in the DMI or Custom DataSource - troubleshoot this code, it is at fault.

Note that your .ds.xml indicates no such DMI or Custom DataSource is in use.. we hope you didn't edit it before posting?

[kmkale]

The reason we don't post code & logs in the public forum is very simple. We have a blanket ban on posting any production code, logs etc to anywhere outside the company. Whenever we post anything here we have to jump hoops to sanitize it & obtain internal approvals. We do it ONLY because when we send support emails, the answers are much delayed as compared to forum post answers. It is this insistence from your side to post everything on public forum thats making it difficult for us to obtain support, as we have to try and sanitize what we post, obtain internal approval etc.

Please review your policy with regards to email based support for paying customers.

Yes the ds has been edited to exclude some parts. Yes the partybranchid is supplied by serveside DMI. The relevant part of code in the DMI is :

dsRequest.setCriteriaValue("partybranchid",Long.parseLong(session.getAttribute("partybranchid").toString()));

Pertinently, as you can see clearly from the logs, the DMI is doing its job and supplying the value. This can be clearly seen from the criteria in the logs. ( Even in the small log excerpt I had posted in the first post) But when the criteria is converted to actual sql query, the value is something else. Again something you can clearly see in the logs. And that is the problem we wish you would address and reply to!

[Isomorphic]

The reason we recommend posting to the forums is because you are then posting from your SmartClient.com account, which is already associated with your support contract, previous support requests, etc. Your post triggers automated alerts as well, so you will receive quicker responses.

You also have the possibility of receiving replies from other forums members, which can often be useful.

While you are still free to use the slower method of email support, it is to your benefit to use this system, and there is nothing about the process which appears to need review.

In the meantime, please be extremely careful never to post incorrect or misleading information for Support to review. Posting a DataSource that contained no DMI declaration when there really was a DMI declaration led the investigation down the wrong path, wasting our time and yours. So again please be careful to avoid this.

We'll see if we can reproduce the claimed behavior - might be a Postgres JDBC bug.

[Isomorphic]

This was happening due to a down-cast from Long to Integer when generating SQL for certain AdvancedCriteria clauses. The bug has been fixed - please try with a nightly build of 9.0 or greater, as of tomorrow, December 4th

[kmkale]

Tested with SmartClient_v90p_2013-12-07_PowerEdition

Problem is solved. Now value of partybranchid in the generated sql statement is same as that shown in the criteria in log.

Thanks.