Table with null values return undefined (implement NULL values)

[silverhawk184]

alasql("CREATE TABLE AA (b VARCHAR(10))"); alasql("INSERT INTO TABLE AA (b) VALUES (null)"); alasql("SELECT * FROM AA");

returns

[{b:undefined}]

Where as

alasql("SELECT * FROM ?",[[{b:null}]])

returns

[{b:null}]

which is expected

[agershun]

Alasql by design uses JavaScript undefined value instead null, because undefined is really undefined, where null is an object.

So,

    alasql("SELECT * FROM ?",[[{b:undefined}]])

returns

    [{}] i.e. b is undefined

See jsFiddle.

Question: am I right? or it is better to have separate null value?

[silverhawk184]

I vote "null" to be left as "null" and "undefined" to be left as "undefined". In my current project, I am cloning a database from the server to run queries against it on the client side. Having "null" mean "null" directly is less confusing and allows data to be sent back to the server to be interpreted. For me the power is when trying to do SUM() and AVG() statements and excluding those that that are null. Both JS and SQL support this.

Such as this: (Which almost works perfectly)

alasql("SELECT AVG(a) AS avgA, AVG(b) AS avgB, SUM(c) as sumC, d FROM ? GROUP BY d",[[{a:1,b:1,c:1,d:'red'},{a:null,b:2,c:2,d:'red'},{a:3,b:3,c:null,d:'red'},{a:null,b:2,c:3,d:'green'}]]); => [{avgA: 1.33, avgB: 2, sumC: 3, d: "red"}, {avgA: null, avgB: 2, sumC: 3, d: "green"}]

should be

[{avgA: 2, avgB: 2, sumC: 3, d: "red"}, {avgA: null, avgB: 2, sumC: 3, d: "green"}]

Now this is not consistent: (bug)

alasql('CREATE TABLE DD'); alasql('INSERT INTO DD (a,b,c,d) VALUES (1,1,1,"red"),(null,2,2,"red"),(3,3,null,"red"),(null,2,3,"green")'); alasql("SELECT AVG(a) AS avgA, AVG(b) AS avgB, SUM(c) as sumC, d FROM DD GROUP BY d"); => [{avgA: NaN, avgB: 2, sumC: NaN, d: "red"}, {avgA: undefined, avgB: 2, sumC: 3, d: "green"}]

Also, Here is the same with undefined entries:

alasql("SELECT AVG(a) AS avgA, AVG(b) AS avgB, SUM(c) as sumC, d FROM ? GROUP BY d",[[{a:1,b:1,c:1,d:'red'},{b:2,c:2,d:'red'},{a:3,b:3,d:'red'},{b:2,c:3,d:'green'}]]) => [{avgA: NaN, avgB: 2, sumC: NaN, d: "red"}, {avgA: undefined, avgB: 2, sumC: 3, d: "green"}]

And forget about doing this:

alasql('INSERT INTO DD (a,b,c,d) VALUES (1,1,1,"red"),(,2,2,"red"),(3,3,,"red"),(,2,3,"green")'); or alasql('INSERT INTO DD (a,b,c,d) VALUES (1,1,1,"red"),(undefined,2,2,"red"),(3,3,undefined,"red"),(undefined,2,3,"green")');

[mathiasrw]

:+1: to silverhawk184

[agershun]

Ok. The only problem I decided to use undefined instead nulll that Alasql has to check all arifmetic operations on null, like: SUM(x) where x can be NULL gives a sum of all non-null items.

I will try to change NULL value the undefined to null.

[silverhawk184]

Thank you.

[agershun]

I tried to implement NULL value in SQK way with JavaScript null value (see branch feature/NULL tests 233 and 249):

The problem that if that NULL and null works different in JavaScript and SQL (I tested with Postgres):

   console.log(40 * null);    // gives 0

but in Postgres

    SELECT NULL * 40

returns NULL.

Of course, I can implement type checking for each arifmetic operator, but it will slow down the library.

What do you think how to solve this issue?

[agershun]

@silverhawk184 The same NULL problem occures with SLT tests (select2.test). I still thinking about convert NULL to undefined. There are some reasons for that:

• actually, in SQL implementations NULL means 'undefined' rather than special value
• 1+null returns NaN. It will slow down queries to check for NaN and null every math operation
• Each conversion cost a lot of time.

I still in the idea to keep only undefined and convert cases like 1+undefined from NaN to undefined with code like:

var y;
(y = 10, y === y ? y : undefined);
(y = NaN, y == y ? y : undefined);

[mathiasrw]

Is there any news about this?

[mathiasrw]

Any news on the NULLing?

[BuserNetAdmin]

Can anyone share example of how to test for undefined in a WHERE clause? For nulls I would use IS NULL. When I try typeof(foo)==='undefined' I get an an error "alasql.fn.typeof is not a function"

[mathiasrw]

Im not sure distinguishing them is a good path to travel, but if you must I suggest you add something along with:

alasql.fn.typeof = (val)=>typeof val;

of maybe better

alasq.fn.isUndef = (val)=>typeof val === 'undefined';

(code not testeed)

[BuserNetAdmin]

Thanks for your response @mathiasrw ... is there a "good path to travel" that you can suggest?

I'm hoping to be able to test for undefined values in a WHERE clause (since we can't use IS NULL), can you suggest the best way to do it?