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 [link]
Thanks for the report. Sometimes [fixing one bug] creates a [new one]. This is especially true when the [query flattening optimization] tangles with a LEFT JOIN operator. Both problems should now be fixed on trunk. Please follow-up if you find otherwise. : https://www.sqlite.org/src/info/ac31edd3eeafcef4 : https://www.sqlite.org/src/info/871f2ddcfbb9196d : 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/>
(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.
(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
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.