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] [source]
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.
I've also made available a zip file of the 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] [source]
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.
(3) By Erik S (easpsea) on 2020-08-20 01:55:42 in reply to 2 [link] [source]
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] [source]
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 [source]
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] [source]
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] [source]
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.