Re: not in on sub query not working as expected.(calcite version 1.13.0)

[Nuwan Abeysiriwardana]

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 <jh...@apache.org> 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 <nuwan...@gmail.com>
> 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
>
>