Repository navigation
Comprison on NULL differes from SQL #1414
Description
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.
Activity
piotr-m-bielski commentedon Oct 14, 2022
Im on it
mathiasrw commentedon Oct 14, 2022
It is a very interesting areas where AlaSQL is stepping in the two worlds of NULL...
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 commentedon Oct 14, 2022
Okey, so let me know when you make decision how to do it.
mathiasrw commentedon Oct 17, 2022
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 commentedon Oct 26, 2022
@piotr-m-bielski Have you had a chance to dive into this? Any ideas?
piotr-m-bielski commentedon Oct 26, 2022
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 commentedon Oct 27, 2022
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
undefinedandnullas kind of the same.mathiasrw commentedon Oct 27, 2022
You can also have a look at #1415 (comment) for some inputs regarding diving into the code.
piotr-m-bielski commentedon Nov 3, 2022
I give up.
mathiasrw commentedon Nov 3, 2022
Thank you for trying.
Di you find out anything interesting? Any thoughts to share?
aotru commentedon Nov 11, 2022
My friend and I plan on working on this and issue 1415 (union operator not working). Any advice before we get started?
mathiasrw commentedon Nov 13, 2022
@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: #1415 (comment)
mathiasrw commentedon Dec 27, 2022
Any luck @aotru ?
Jnani-222 commentedon Aug 30, 2023
Try using the join concept, to get the expected output.