Comprison on NULL differes from SQL

[mandel]

Hi,

We have noticed that the result of the following example doesn't return the expected result (based on https://doi.org/10.14778/3151113.3151116)

alasql('CREATE TABLE R (a number)');
alasql('CREATE TABLE S (b number)');
alasql.tables.R.data = [ {a: 1}, {a: null} ];
alasql.tables.S.data = [ {b: null} ];
var res = alasql('select a from R where a not in (select b from S)');
// expected: res = []
// alasql:   res = [ {"a": 1} ]

See https://jsfiddle.net/3ya794kr/.

This query compares each row of R with NULL which always returns unknown in SQL. Since not in unknown is unknown, the result should be the empty collection.

[piotr-m-bielski]

Im on it

[mathiasrw]

It is a very interesting areas where AlaSQL is stepping in the two worlds of NULL...

The programming language null is a value but database null is not a value.

I found great joy in reading this a while ago: https://arxiv.org/html/1606.00740

@piotr-m-bielski this will likely be a breaking change. I suggest we identify how to solve it, and then consider if its possible to make an option to use the old way or the SQL way.

[piotr-m-bielski]

Okey, so let me know when you make decision how to do it.

[mathiasrw]

Oh - we can work on a solution. So you can just start on this. But before the release we will discuss how to let the users configure this. How we do that depends on what the solution looks like. So I suggest you work on solving this, and then we talk about how the solution can be part of what Alasql offers.

[mathiasrw]

@piotr-m-bielski Have you had a chance to dive into this? Any ideas?

[piotr-m-bielski]

I tried to do that, but It is really hard to understand how it works. (parser) Variables like yy do not help. I will try again over this weekend

[mathiasrw]

In agree. Its a nasty beast to dive into.

In short: the compiler makes an AST. The AST is transformed into a string of the javascript doing all the things needed to execute the SQL. Then the string is run with data as parameters.

NULL has a sad story in AlaSQL with the original design not tapping into its power, but treating undefined and null as kind of the same.

[mathiasrw]

You can also have a look at https://github.com/AlaSQL/alasql/issues/1415#issuecomment-1293335079 for some inputs regarding diving into the code.

[piotr-m-bielski]

I give up.

[mathiasrw]

Thank you for trying.

Di you find out anything interesting? Any thoughts to share?

[aotru]

My friend and I plan on working on this and issue 1415 (union operator not working). Any advice before we get started?

[mathiasrw]

@aotru Thank you so much.

Advice: Make a test first that illustrates the problem.

You might also benefit from the suggestions in this this comment about one way of running alasql with breaks and steps: https://github.com/AlaSQL/alasql/issues/1415#issuecomment-1293335079

[mathiasrw]

Any luck @aotru ?

[Jnani-222]

Try using the join concept, to get the expected output.