Hi,
When using JDBC adapter with postgres db, I observed that the implicit DataType
conversion for IN predicate is only done on the in value list and not according
to a datatype precedence list. Is this expected?
Consider T2:
SQL: select * from "T2"
| CSINT(SMALLINT) | CINT(INTEGER) | CBINT(BIGINT) | CDBL(DOUBLE) | V1(VARCHAR)
| V2(VARCHAR) | CTS(TIMESTAMP) |
|_|0|_|0|_|0|_|0.0|_|zero|_|z|_|1970-01-01 00:00:00.0|_|
|_|0|_|null|_|0|_|0.5|_|half|_|h|_|1970-01-01 00:00:00.0|_|
|_|1|_|1|_|1|_|1.0|_|one|_|o|_|null|_|
|_|2|_|2|_|2|_|null|_|two|_|t|_|1970-01-01 00:00:00.002|_|
|_|3|_|3|_|3|_|3.0|_|three|_|null|_|1970-01-01 00:00:00.003|_|
|_|4|_|4|_|null|_|4.0|_|four|_|f|_|1970-01-01 00:00:00.004|_|
|_|5|_|5|_|5|_|5.0|_|null|_|n|_|1970-01-01 00:00:00.005|_|
I am able to get the same result when comparing "CBINT in (CDBL)" and "CDBL in
(CBINT)" in postgres db
When I apply the same query using JDBC Adaptor with postgres db, "CBINT in
(CDBL)" will give different results from "CDBL in (CBINT)"
SQL: explain plan for select * from T2 where CBINT in (CDBL)
| PLAN(VARCHAR) |
|_|EnumerableCalc(expr#0..6=[{inputs}], expr#7=[CAST($t3):JavaType(class
java.lang.Long)], expr#8=[=($t2, $t7)], proj#0..6=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[TEST, T2]])
|_|
SQL: explain plan for select * from T2 where CDBL in (CBINT)
| PLAN(VARCHAR) |
|_|EnumerableCalc(expr#0..6=[{inputs}], expr#7=[CAST($t2):JavaType(class
java.lang.Double)], expr#8=[=($t3, $t7)], proj#0..6=[{exprs}], $condition=[$t8])
EnumerableTableScan(table=[[TEST, T2]])
|_|
SQL: select * from T2 where CBINT in (CDBL)
| CSINT(SMALLINT) | CINT(INTEGER) | CBINT(BIGINT) | CDBL(DOUBLE) | V1(VARCHAR)
| V2(VARCHAR) | CTS(TIMESTAMP) |
|_|0|_|0|_|0|_|0.0|_|zero|_|z|_|1970-01-01 00:00:00.0|_|
|_|0|_|null|_|0|_|0.5|_|half|_|h|_|1970-01-01 00:00:00.0|_|
|_|1|_|1|_|1|_|1.0|_|one|_|o|_|null|_|
|_|3|_|3|_|3|_|3.0|_|three|_|null|_|1970-01-01 00:00:00.003|_|
|_|5|_|5|_|5|_|5.0|_|null|_|n|_|1970-01-01 00:00:00.005|_|
selectSomething - SQL: select * from T2 where CDBL in (CBINT)
| CSINT(SMALLINT) | CINT(INTEGER) | CBINT(BIGINT) | CDBL(DOUBLE) | V1(VARCHAR)
| V2(VARCHAR) | CTS(TIMESTAMP) |
|_|0|_|0|_|0|_|0.0|_|zero|_|z|_|1970-01-01 00:00:00.0|_|
|_|1|_|1|_|1|_|1.0|_|one|_|o|_|null|_|
|_|3|_|3|_|3|_|3.0|_|three|_|null|_|1970-01-01 00:00:00.003|_|
|_|5|_|5|_|5|_|5.0|_|null|_|n|_|1970-01-01 00:00:00.005|_|
Thanks & Regards,
Yuri Au Yong
Software Engineer - NPM | [email protected] | Desk: +60 3 7663 8372
Persistent Systems Ltd. | Partners in Innovation |
www.persistentsys.com<http://www.persistentsys.com/>
[sign1]<https://tnpmsupport.persistentsys.com/training>
DISCLAIMER
==========
This e-mail may contain privileged and confidential information which is the
property of Persistent Systems Ltd. It is intended only for the use of the
individual or entity to which it is addressed. If you are not the intended
recipient, you are not authorized to read, retain, copy, print, distribute or
use this message. If you have received this communication in error, please
notify the sender and delete all copies of this message. Persistent Systems
Ltd. does not accept any liability for virus infected mails.
