Thanks for the test case. Run with -Dcalcite.debug, take a look at the generated code and see whether == and .equals are being used correctly. Also see what happens if each table has two columns rather than one. Some of our “optimizations” for single-column tables may be suspect.
Julian > On Sep 26, 2017, at 1:15 PM, Nuwan Abeysiriwardana <[email protected]> wrote: > > Hi Julian, > i'm sorry if I'm mislead with this CSV thing. this CSV table not the > calcite inbuilt csv table. this table i have tested, created by me by just > implementing ScannableTable . test case created. please check below link. > > <goog_1962026729> > https://github.com/nuwansa/CalcitePlayGround > > > thanks > nuwan > > > On Wed, Sep 27, 2017 at 12:41 AM, Julian Hyde <[email protected]> wrote: > >> 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 >>>> >>>> >> >>
