From 4ffcce88ad49b174ce5714e3c7a0b8e6462da7cd Mon Sep 17 00:00:00 2001
From: drh <>
Date: Fri, 2 Jun 2023 00:03:28 +0000
Subject: [PATCH] Fix the OUTER JOIN strength reduction theorem prover's
 handling of the CASE operator.

FossilOrigin-Name: 73d7b14b1713d93c4ae07995e9aa6485cbad90bd6125584dbaeccb0cc9410ea3
---
 manifest        | 14 +++++++-------
 manifest.uuid   |  2 +-
 src/expr.c      | 12 ++----------
 test/joinH.test | 12 ++++++++++++
 4 files changed, 22 insertions(+), 18 deletions(-)

diff --git a/manifest b/manifest
index 4ec12de380..061697b0a5 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Generalize\sthe\sLEFT\sJOIN\sstrength\sreduction\soptimization\sso\sthat\sit\sworks\sfor\nRIGHT\sand\sFULL\sJOIN\sas\swell.\s\sRename\sit\sto\sthe\s"OUTER\sJOIN\sstrength\sreduction"\noptimization.
-D 2023-06-01T20:38:22.975
+C Fix\sthe\sOUTER\sJOIN\sstrength\sreduction\stheorem\sprover's\shandling\sof\sthe\sCASE\noperator.
+D 2023-06-02T00:03:28.930
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -587,7 +587,7 @@ F src/date.c cb71f73d5df5303ec6630971b9c8b8c7df466fa3233dbdc643d613806e53e554
 F src/dbpage.c f3eea5f7ec47e09ee7da40f42b25092ecbe961fc59566b8e5f705f34335b2387
 F src/dbstat.c ec92074baa61d883de58c945162d9e666c13cd7cf3a23bc38b4d1c4d0b2c2bef
 F src/delete.c 05e27e3a55dcfeadf2f7ca95a5c5e0928f182c04640ec1954ffa42f3d5c19341
-F src/expr.c 7644055e99fef28b471b29d824850a5fd296333bcc22d524907a08b1608e877c
+F src/expr.c 93a8292352d7f6f0f10fbf601342b8a481bc79edb2686b60e0f3e9344f07f4b5
 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
 F src/fkey.c 03c134cc8bffe54835f742ddea0b72ebfc8f6b32773d175c71b8afeea6cb5c83
 F src/func.c 03e6b501f3056d0ba398bda17df938b2b566aa0b3ca7e1942a3cd1925d04ec36
@@ -1247,7 +1247,7 @@ F test/joinC.test 1f1a602c2127f55f136e2cbd3bf2d26546614bf8cffe5902ec1ac9c07f87f2
 F test/joinD.test 2ce62e7353a0702ca5e70008faf319c1d4686aa19fba34275c6d1da0e960be28
 F test/joinE.test d5d182f3812771e2c0d97c9dcf5dbe4c41c8e21c82560e59358731c4a3981d6b
 F test/joinF.test 53dd66158806823ea680dd7543b5406af151b5aafa5cd06a7f3231cd94938127
-F test/joinH.test 705157cf9b9b7c207caf960812a7d0e4dc1dd45aa5fb2b563f12df59088645f3
+F test/joinH.test c9550bb6a0257cf99668a28485bb309bac542081702e89261b95542ab5f676b1
 F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497
 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4
 F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e
@@ -2072,8 +2072,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P d01688554715eb4aaa1b1fd1a6b660b84e930edb0e062156ecf5228ee81ca754
-R 2af57c1a9d08a6d51463844f0d5b7329
+P d747afda5683ca5feb92866a14ccc4c5127d3378ece5cad2c3da819f2477b457
+R 2f1552edaf0903b8228566e8c1b54d0e
 U drh
-Z 578aeee2b373b356a1309c1c7c658e84
+Z e8ce4ce62d218cffe886d1f5ea89a7c4
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index f8f4e34b06..a220ba69b9 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-d747afda5683ca5feb92866a14ccc4c5127d3378ece5cad2c3da819f2477b457
\ No newline at end of file
+73d7b14b1713d93c4ae07995e9aa6485cbad90bd6125584dbaeccb0cc9410ea3
\ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 707a9ca6ba..92e96b0f12 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -6026,6 +6026,7 @@ static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
     case TK_VECTOR:
     case TK_FUNCTION:
     case TK_TRUTH:
+    case TK_CASE:
       testcase( pExpr->op==TK_ISNOT );
       testcase( pExpr->op==TK_ISNULL );
       testcase( pExpr->op==TK_NOTNULL );
@@ -6033,6 +6034,7 @@ static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
       testcase( pExpr->op==TK_VECTOR );
       testcase( pExpr->op==TK_FUNCTION );
       testcase( pExpr->op==TK_TRUTH );
+      testcase( pExpr->op==TK_CASE );
       return WRC_Prune;
 
     case TK_COLUMN:
@@ -6055,16 +6057,6 @@ static int impliesNotNullRow(Walker *pWalker, Expr *pExpr){
       testcase( pExpr->op==TK_AND );
       bothImplyNotNullRow(pWalker, pExpr->pLeft, pExpr->pRight);
       return WRC_Prune;
-
-    case TK_CASE:
-      /* In "CASE x WHEN y THEN ..." the overall expression is non-null-row
-      ** if either x or y is non-null-row.  If the neither x nor y is
-      ** non-null-row, assume the whole expression is not, to be safe. */
-      assert( ExprUseXList(pExpr) );
-      assert( pExpr->x.pList->nExpr>0 );
-      sqlite3WalkExpr(pWalker, pExpr->pLeft);
-      sqlite3WalkExpr(pWalker, pExpr->x.pList->a[0].pExpr);
-      return WRC_Prune;
         
     case TK_IN:
       /* Beware of "x NOT IN ()" and "x NOT IN (SELECT 1 WHERE false)",
diff --git a/test/joinH.test b/test/joinH.test
index 78d1556293..edba26de2d 100644
--- a/test/joinH.test
+++ b/test/joinH.test
@@ -120,4 +120,16 @@ do_execsql_test 5.5 {
   SELECT * FROM t0 RIGHT JOIN t1 INNER JOIN t2 ON (0)
 } {}
 
+
+reset_db
+db null NULL
+do_execsql_test 6.0 {
+  CREATE TABLE t1(a INT);
+  CREATE TABLE t2(b INT);
+  INSERT INTO t1 VALUES(3);
+  SELECT CASE WHEN t2.b THEN 0 ELSE 1 END FROM t1 LEFT JOIN t2 ON true;
+} {1}
+do_execsql_test 6.1 {
+  SELECT * FROM t1 LEFT JOIN t2 ON true WHERE CASE WHEN t2.b THEN 0 ELSE 1 END;
+} {3 NULL}
 finish_test
-- 
2.47.2