A rule of thumb I have used is that select … where x in (v1, v2)
should give the same result as select … where x = v1 or x = v2 even taking into account implicit conversions of x, v1, and v2. Maybe this rule of thumb is mistaken; I don’t know. It is interesting to see what Postgres does, but the definitive answer is to look into the SQL standard. What does the standard say on this matter? Julian > On Oct 25, 2015, at 7:17 PM, Yuri Au Yong <[email protected]> wrote: > > 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] > <mailto:[email protected]> | Desk: +60 3 7663 8372 > Persistent Systems Ltd. | Partners in Innovation | www.persistentsys.com > <http://www.persistentsys.com/> > <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. >
