Different value about null from local and query scope

dev support
1

Hi all.

This is a bug?

Seems that Lucee evaluates a null value differently depending on whether it is in “local” or “query” scope.
I noticed this with a query in Postgres.

    var thisNull = nullValue();
    dump( var=thisNull ?: 'is_null', label="from local" );

This return ‘is_null’. Correct.

    var q = queryNew( "nullField", "varchar", [ [ nullValue() ] ] );
    dump( var=q.nullField ?: 'is_null', label="from db" );

This return ‘’.
Should return ‘is_null’ too. No?
(I try with several datatype, but nothing change).

image

IsNull( q.nullField )

This is false.
But shouldn’t it be true?

What do you think?
(I’m using Lucee 5)

2

Yes CFML handles null in queries like this unless you have full null support enabled.

The reason is very simple, when full null support is not enabled null is equal to not existing and that would result in a excption like “the variable secondname does not exist” in a simple query loop that outputs this #secondname# if the column “secondName” is null in some cases.

that is the compromise CFML added so you can interact with a interface providing null as vales in a language not supporting null as values, unless full null supportis eanled, of course.

4

Thanks @micstriit for your reply!
I understand uour explanation.

But now I’m playing with nullSupport=true (i always work with null enabled).
It seems to be a problem. I’ll try to show you:

cfapplication( action="update", nullSupport=true);

var thisNull = nullValue();
dump( var=thisNull ?: 'is_null', label="from local" );

This return “is_null”.
This is correct.

var q = queryNew( "nullField", "varchar", [ [ nullValue() ] ] );
dump( var=q.nullField ?: 'is_null', label="from db" );

But this should return “is_null” too.
But return null. Infact:

IsNull( q.nullField )

This is true.
But it seems like it doesn’t behave like null.

image

I mean, if with nullSupport=true, the q.nullField is Null (test with IsNull), it should return “is_null” in my dump. But it doesn’t.

Here the Gist of code:

I hope I was clear.
Thanks for your patience.

5

@Roberto_Marzialetti
In CFML there is no need to point to a specific row with a query, CFML does that for you automatically, in a loop it points to the current row (internal pointer) and outside a loop to row number 1 if it exists.

but in your example q.nullField is a column not a cell value, sure you can do things like #q.nullField# and Lucee will automatically yutput the cell value of the current row, but this is still pointing to the whole column. you can see that best when you do for example valueList(q.nullField), then you get a list of the values from all rows.

So CFML does a lot of adjustment to make the column feel like a cell value, but it is not, what you see i would still label as a bug, Lucee should be consistent here. i will open a ticket for this and fix in Lucee 6.

I have extened your examples, to better illustrate my point, hope it makes sense.

2 Likes
6

“fixed” in Lucee 5 and 6

https://luceeserver.atlassian.net/browse/LDEV-4765

1 Like
7

@micstriit many many thanks!
that’s why a love so much Lucee and this community :heart: