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 [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]

(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.

(5) By anonymous on 2020-08-20 08:45:31 in reply to 2 [link] [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.