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 > >> > >> > >
