It works fine for me: $ cat file/src/test/resources/sales/ORDERS.csv Q:string aaaa 3333 zzzz $ cat file/src/test/resources/sales/TRADES.csv A:string aaaa 1111 2222 $ ./sqlline -u "jdbc:calcite:schemaFactory=org.apache.calcite.adapter.file.FileSchemaFactory;schema.directory=/Users/jhyde/open1/calcite.2/file/src/test/resources/sales" sqlline version 1.3.0 0: jdbc:calcite:schemaFactory=org.apache.calc> select a, a not in (select q from "adhoc".orders) from "adhoc".trades; +---+--------+ | A | EXPR$1 | +---+--------+ | aaaa | false | | 1111 | true | | 2222 | true | +---+--------+ 3 rows selected (1.918 seconds)
Can you provide a test case that fails? Julian > On Sep 26, 2017, at 11:46 AM, Nuwan Abeysiriwardana <[email protected]> > wrote: > > Hi Julian, > > thanks for the quick reply.i have check the leading and trailing spaces,it > looks good. execution plan as follows. > > EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], > expr#7=[false], expr#8=[IS NOT NULL($t4)], expr#9=[true], expr#10=[IS > NULL($t0)], expr#11=[null], expr#12=[<($t2, $t1)], expr#13=[CASE($t6, $t7, > $t8, $t9, $t10, $t11, $t12, $t11, $t7)], expr#14=[NOT($t13)], Q=[$t0], > output=[$t14]) > EnumerableJoin(condition=[=($0, $3)], joinType=[left]) > EnumerableJoin(condition=[true], joinType=[inner]) > EnumerableCalc(expr#0..2=[{inputs}], Q=[$t0]) > EnumerableInterpreter > BindableTableScan(table=[[csv, execution]]) > EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)]) > EnumerableInterpreter > BindableTableScan(table=[[csv, trades]]) > EnumerableAggregate(group=[{0, 1}]) > EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], A=[$t0], i=[$t3]) > EnumerableInterpreter > BindableTableScan(table=[[csv, trades]]) > > thanks > nuwan > > On Wed, Sep 27, 2017 at 12:05 AM, Julian Hyde <[email protected]> wrote: > >> Sounds like it might be a bug. Can you do ‘explain plan’ and send the >> result? Also check for leading and trailing spaces in your data. >> >>> On Sep 26, 2017, at 11:11 AM, Nuwan Abeysiriwardana <[email protected]> >> wrote: >>> >>> Hi All, >>> i have following csv tables. >>> >>> TableA TableB >>> +------+ +-------+ >>> | Q | | A | >>> +------+ +-------+ >>> | aaaa | | aaaa | >>> | 1111 | | ssss | >>> | 555 | | 22222 | >>> +------+ +-------+ >>> >>> 'select Q,Q not in(select A from TableB) as output from TableA' produce >>> following result >>> >>> +------+--------+ >>> | Q | output | >>> +------+--------+ >>> | aaaa | true | >>> | 1111 | true | >>> | 555 | true | >>> +------+--------+ >>> >>> but I'm expecting false for value 'aaaa'. >>> >>> also i have tested the following query and it work as expected. >>> 'select Q,Q not in('aaaa','1111') as output from TableA' >>> >>> output : >>> +------+--------+ >>> | Q | output | >>> +------+--------+ >>> | aaaa | false | >>> | 1111 | false | >>> | 555 | true | >>> +------+--------+ >>> >>> i have tested similar query with PostgreSQL ,it works fine. >>> >>> thanks >>> nuwan >> >>
