Table with null values return undefined (implement NULL values)

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

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?

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")');

👍 to silverhawk184

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.

Thank you.

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?

@silverhawk184

@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);

Is there any news about this?

Any news on the NULLing?

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"

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)

@mathiasrw

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?

Navigation Menu

Repository navigation

Table with null values return undefined (implement NULL values) #85

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Table with null values return undefined (implement NULL values) #85

Description

Activity

agershun commented on Mar 10, 2015

silverhawk184 commented on Mar 10, 2015

mathiasrw commented on Mar 24, 2015

agershun commented on Mar 24, 2015

silverhawk184 commented on Mar 24, 2015

agershun commented on Mar 25, 2015

agershun commented on May 18, 2015

mathiasrw commented on Jul 14, 2015

mathiasrw commented on Mar 21, 2016

BuserNetAdmin commented on Nov 18, 2020

mathiasrw commented on Nov 20, 2020

BuserNetAdmin commented on Nov 20, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions