JSON + whereNull/whereNotNull is non-intuitive or broken

[LastDragon-ru]

• Laravel Version: 5.8 / 6.x
• PHP Version: 7.2.0
• Database Driver & Version: MySQL 5.7 (not sure about other dbs)

Description:

The whereNull('a->b') will generate json_unquote(json_extract(`a`, '$."b"')) is null that doesn't work as expected:

set @a='{"a":3,"b":null}';

select 
json_unquote(json_extract(@a, '$."b"')) IS NULL, -- = false (but value is null => should be true)
json_unquote(json_extract(@a, '$."c"')) IS NULL; -- = true

Desired behavior:

1. IMHO whereNull should cover both cases:
   1. key doesn't exist (fine now)
   2. key exists but the value is null (the JSON_TYPE(json_extract(@a, '$."b"')) = 'NULL' can be used)
2. (or) will be probably good to have a notice in docs about this non-intuitive whereNull behavior.

Also, this issue similar to #27696

[driesvints]

@staudenmeir since you sent in #27847 can you maybe weigh in here? Thanks.

[staudenmeir]

I'll look into it.

[Krunch]

If you want more information on this, there is a MySQL bug on this exact subject (JSON not returning SQL NULL in some cases). This is a distinct behavior in MySQL and I haven't seen this in other SQL engines.

https://bugs.mysql.com/bug.php?id=85755

Edit: This also applies to boolean values, JSON functions inside MySQL does not return a SQL boolean type for those either.

[driesvints]

Closing this since this is a MySQL bug.

[LastDragon-ru]

Closing this since this is a MySQL bug.

@driesvints, this is not a bug in general, please read mysql.com carefully - this is special behavior to distinguish when key exists and when not (same as isset and array_key_exists). And Laravel must handle this properly. Please reopen.

[driesvints]

@LastDragon-ru can you point to the parts where it says that?

[Krunch]

@driesvints https://bugs.mysql.com/bug.php?id=85755 (2nd reply)

[10 Apr 2017 7:09] Knut Anders Hatlen
Posted by developer:

Hi Dave,

I think what you are seeing is the intended behaviour.

JSON_OBJECT('a', NULL) evaluates to the JSON document { "a" : null }. Here, JSON_OBJECT has converted the SQL NULL value to the JSON null literal, which are different in SQL. When you call JSON_EXTRACT with path $.a on { "a" : null }, it extracts the JSON null literal from that document. If JSON_EXTRACT had not found anything on the specified path, it would have returned SQL NULL instead. So the difference between SQL NULL and the JSON null literal allows you do distinguish between the case where JSON_EXTRACT found a (JSON) null value and the case where it didn't find anything.

I didn't find any mentioning of how JSON_OBJECT handles NULL values in the reference manual, only in the design documents for WL#7909 which added the JSON_OBJECT function. Unless I have overlooked something in the manual, maybe what we should do is clarify how JSON_OBJECT handles NULL arguments in the manual.

[driesvints]

Gotcha thanks. Feel free to send in a PR for this.