SQLite Forum

Bug Report: Under some conditions NULL isn't NULL.
Login

Bug Report: Under some conditions NULL isn't NULL.

(1.1) By Erik S (easpsea) on 2020-08-19 19:53:05 edited from 1.0 [link]

I was working on a project for an online course I'm taking and ran into a problem where NULL values resulting from missing rows in a LEFT JOIN weren't being treated as NULL values. 

It took me a while to conclude the problem was with SQLite, rather than my code. But I compared the behavior I was seeing locally using 3.33.0 with a cloud-hosted version using 3.21.0 and found that I couldn't reproduce the behavior with the earlier version.

I've done my best to show the problem, but I'm also near the limits of my present knowledge and skill, so I hope it is enough for someone else to run with, or let me know that it behaves as it should.

I've shared a static rendering of [jupyter notebook illustrating the problem](https://thinkish.net/files/2020/08/SQLite-Bug-Test-Case.html).

I've also made available a zip file of the [notebook and database](https://www.icloud.com/iclouddrive/0E6mEptsVbIBzBCS3Y-vIwD8A#notebook_and_database) I was working with.

(2) By Richard Hipp (drh) on 2020-08-19 23:39:29 in reply to 1.1

Thanks for the report.

Sometimes [fixing one bug][1] creates a [new one][2].  This is especially true
when the [query flattening optimization][3] tangles with a LEFT JOIN operator.
Both problems should now be fixed on trunk.  Please follow-up if you find
otherwise.

[1]: https://www.sqlite.org/src/info/ac31edd3eeafcef4
[2]: https://www.sqlite.org/src/info/871f2ddcfbb9196d
[3]: https://www.sqlite.org/optoverview.html#subquery_flattening

(3) By Erik S (easpsea) on 2020-08-20 01:55:42 in reply to 2 [link]

Thanks! I'll have to wait until it works its way through releases and packaging. In the meantime I think I found another way to get at what I need.

(4) By sean (jungleboogie) on 2020-08-20 04:08:29 in reply to 3 [link]

See this post about compiling sqlite:
<https://jvns.ca/blog/2019/10/28/sqlite-is-really-easy-to-compile/>

(5) By anonymous on 2020-08-20 08:45:31 in reply to 2 [link]

FWIW, the SQL from that Query-Flatting doc you link to looks weird.  
I hesitate to write *incorrect*, since I'm no expert, but `WHERE="1"`  
and `AND="1"` look suspicious to me. Wasn't the LHS of the = omitted?

(6) By Richard Hipp (drh) on 2020-08-20 11:34:35 in reply to 5 [link]

Thanks.  The original input text was correct, except that I had failed
to escape the less-than and greater-than operators, which consequently
got interpreted as markup.  Fixed now.  Press reload.

(7) By Erik S (easpsea) on 2020-09-17 16:21:37 in reply to 4 [link]

Thanks. That's a useful link.

It's more a matter of time and attention. I already feel like my python environment is a house of cards, despite my efforts to make it otherwise. Adding a custom build of a library would be one more thing I don't feel on top of.