Thanks for the bug report.

Here is a description of the underlying cause of the problem, for those who are interested: SQLite has an optimization that tries to convert expressions of the form "x ISNULL" into "FALSE" if the column "x" has a NOT NULL constraint. That optimization has to be disabled if the column "x" is on the nullable size of an outer join and is not inside the ON clause. All of this works. But in this case, SQLite is also using another optimization that converts the LEFT JOIN into an INNER JOIN. And that second optimization makes the "t2.c1 ISNULL" constraint think that it is no longer on the nullable side of an outer join, even though it is still on the nullable side of a different RIGHT JOIN. Hence, the optimization of converting "t2.c1 ISNULL" into "FALSE" is incorrectly applied.

We can easily make SQLite get the correct answer here by just disabling the optimization that converts "t2.c1 ISNULL" into "FALSE". But that will cause performance regressions. We are working now to figure out how to disable the ISNULL-to-FALSE optimization only when it is truly necessary.

Problem now fixed on trunk - check-in b1be2259e2e08ec2

Richard, I notice designing and coding RIGHT JOIN is a nightmare for you. There seem to be endless paths where it can break queries. Watching from afar, I get the feeling that one can never be sure that RIGHT JOIN is entirely correctly implemented.

Perhaps, there is a smarter way to proceed: you use path analysis tools in testing to tell you whether every path is executed. Is there a way to "tag" paths related to RIGHT JOIN code & data such that the results of running your total test suite highlights paths which were affected/not affected by RIGHT JOIN code and data? And then use that information to generate new tests and/or examine the not-affected paths?

Just thinking outside the box a bit ...

Hi Doug.

Will you explain why do you think it's a nightmare? We've just seen a few fixes of very complicated SQL RIGHT JOINs. For a very new addition to SQLite, IMHO, it has very little problems. If we had seen 30+ reports, than I would have agreed with you.

josé

Apologies for the characterization of nightmare. From over here, it looks like a never-ending stream of "oops - missed that path". I thought if there were a way to increase the reliability of SQLite, that you might think about it.

I admit I was having similar thoughts at first. But then, I remembered that Richard has been at this for a very long time, far longer than I have ever pursued any single project, so he probably has a pretty good idea what he is doing. More to the point, I believe every line of code in SQLite is exercised in some test case or other. But truly comprehensive tests would have to exercise every code path in every conceivable circumstance, and that is just impossible to achieve.

I can only guess, but I imagine that what is happening here is that the knowledge that the only outer joins are LEFT joins has been used as a simplifying assumption many places in the code, and that the current task is to identify those places, remove the assumption, and adjust the code accordingly.

In addition to other replies, I think the thing not to lose focus of is that SQLIte is just fine, this is a very new bit of development that is being shared/published as-they-go due to the nature of the SQLite project, and people are reporting issues in very new code that probably doesn't even have test-cases yet, which by the way is very helpful I think, but should not be taken to reflect on the code-stability or code-goodness of production code of SQLite at all.

Apologies for the characterization of nightmare.

No apologies needed. :-) I been using SQLite since 2006, and I am a witness of how well design it is, and it has the best support of any software out there (IMHO). And to make it even better, It's free!

Perhaps, there is a smarter way to proceed: you use path analysis tools in testing to tell you whether every path is executed.

Perhaps I'm missing something here, but I am unable to see path analysis by whatever means as something that would lead to fewer problems for the new RIGHT/OUTER JOIN feature. The problems have generally been unforeseen interactions between the new joins and optimizations already in place for the older joins. The issues have not been "whether every path is executed". Rather, they have been "whether all of the old paths remain appropriate in the face of new inputs" and "whether new paths are needed to preserve preconditions upon which old join optimizations were predicated".

There may be some development refinements that would help, but coverage testing or analysis is something that does the most good once the code to implement various features is stable.

Thanks for you detailed explanation! It is very useful for me to understand the bug.