The subtype of a value should not propagate across a subquery boundary.

Proposed fix for the problem reported by
[forum:/forumpost/3d9caa45cbe38c78|forum post 3d9caa45cbe38c78].
Additional works is needed as not all cases are covered.

FossilOrigin-Name: 08af1fe27ebd0edf6e0f1ac477deea033e7f7c813f1016b75196836daf02d2e4

5 files changed, 63 insertions, 10 deletions

diff --git a/manifest b/manifest
index 810121af5..ac167625a 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Move\san\s#ifdef\sin\sshell.c\sto\savoid\sa\sharmless\s"unused\sfunction"\s\ncompiler\swarning.
-D 2022-06-08T18:29:23.637
+C The\ssubtype\sof\sa\svalue\sshould\snot\spropagate\sacross\sa\ssubquery\sboundary.\nProposed\sfix\sfor\sthe\sproblem\sreported\sby\n[forum:/forumpost/3d9caa45cbe38c78|forum\spost\s3d9caa45cbe38c78].\nAdditional\sworks\sis\sneeded\sas\snot\sall\scases\sare\scovered.
+D 2022-06-09T16:19:01.223
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -521,7 +521,7 @@ F src/date.c 15082566229d4b1e5f24fdb490bf9bcc68824b911d70e3573ef075a1b9e2d26f
 F src/dbpage.c 90661a87e1db8bfbc8d2ebbdcd3749651ddb287c555c07a28fb17c7c591ffb68
 F src/dbstat.c 861e08690fcb0f2ee1165eff0060ea8d4f3e2ea10f80dab7d32ad70443a6ff2d
 F src/delete.c a8e844af211a48b13b5b358be77a12c860c6a557c21990ad51a548e2536500ce
-F src/expr.c 69c833a8faa081f1a97c17cc40db15fd5295647b4885fa5d3efe93a46fc21a3b
+F src/expr.c 4907afcb86d72b5525d8767515ce425ec53c7a2d3664441b46cef5b376ee0cba
 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
 F src/fkey.c d965ede15d8360c09ed59348940649ee647b192e784466837d7aefa836d1d91e
 F src/func.c 8f72e88cccdee22185133c10f96ccd61dc34c5ea4b1fa9a73c237ef59b2e64f1
@@ -642,7 +642,7 @@ F src/upsert.c 8789047a8f0a601ea42fa0256d1ba3190c13746b6ba940fe2d25643a7e991937
 F src/utf.c ee39565f0843775cc2c81135751ddd93eceb91a673ea2c57f61c76f288b041a0
 F src/util.c 602fe229f32a96ceccae4f40824129669582096f7c355f53dbac156c9fecef23
 F src/vacuum.c bb346170b0b54c6683bba4a5983aea40485597fdf605c87ec8bc2e199fe88cd8
-F src/vdbe.c d4c46579b471421bda8403fb0f1c7e7eaa6522653e4f06b2591a33c0ac8f77ac
+F src/vdbe.c 9959b20b1dbe72eca51cc611669ab1cc623274b4681c4bec7c4fee0c35c080d4
 F src/vdbe.h 07641758ca8b4f4c6d81ea667ea167c541e6ece21f5574da11e3d21ec37e2662
 F src/vdbeInt.h ef43f7fdc5fde29fc3fd29c506c12830f366178fdb4edbbf0cbc3dfbd1278b5f
 F src/vdbeapi.c 354c893f1500cf524cc45c32879b9c68893a28b77e3442c24668d6afe4236217
@@ -1479,7 +1479,7 @@ F test/subquery.test 3a1a5b600b8d4f504d2a2c61f33db820983dba94a0ef3e4aedca8f0165e
 F test/subquery2.test 90cf944b9de8204569cf656028391e4af1ccc8c0cc02d4ef38ee3be8de1ffb12
 F test/subselect.test 0966aa8e720224dbd6a5e769a3ec2a723e332303
 F test/substr.test a673e3763e247e9b5e497a6cacbaf3da2bd8ec8921c0677145c109f2e633f36b
-F test/subtype1.test 7fe09496352f97053af1437150751be2d0a0cae8
+F test/subtype1.test 0dcd5daf00a1bdd4ba4b3410aed1c5336adb4d9695e2c93633588fe38b0b3404
 F test/superlock.test ec94f0556b6488d97f71c79f9061ae08d9ab8f12
 F test/swarmvtab.test 250231404fcac88f61a6c147bb0e3a118ed879278cd3ccb0ae2d3a729e1e8e26
 F test/swarmvtab2.test c948cb2fdfc5b01d85e8f6d6504854202dc1a0782ab2a0ed61538f27cbd0aa5c
@@ -1976,8 +1976,11 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 09c8f9f1970cd5b369d98a2b38f0b04d44ed095cb0bda80f7968bb6be4e0263b
-R 1dfc7c7d24e49c7701ee2b2304893786
+P 5abb5ef500f52c52dac33e54d824cf89481fec1643c27943f34f0ca4560a7e00
+R a4791de68ccc60dbd3fc486a3fbb1159
+T *branch * subtype-subquery
+T *sym-subtype-subquery *
+T -sym-trunk *
 U drh
-Z ac5a8d59f2f998b129bdd56d29189077
+Z f43ab1740e2db0ad3ebbcc397a4e347d
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 89d7cae01..4c2499a25 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-5abb5ef500f52c52dac33e54d824cf89481fec1643c27943f34f0ca4560a7e00
\ No newline at end of file
+08af1fe27ebd0edf6e0f1ac477deea033e7f7c813f1016b75196836daf02d2e4
\ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 09b56f489..c0b2bee94 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -4577,8 +4577,24 @@ expr_code_doover:
       exprCodeBetween(pParse, pExpr, target, 0, 0);
       return target;
     }
+    case TK_COLLATE: {
+      if( !ExprHasProperty(pExpr, EP_Collate)
+       && ALWAYS(pExpr->pLeft)
+       && pExpr->pLeft->op==TK_FUNCTION
+      ){
+        inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
+        if( inReg!=target ){
+          sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
+          inReg = target;
+        }
+        sqlite3VdbeAddOp1(v, OP_ClrSubtype, inReg);
+        return inReg;
+      }else{
+        pExpr = pExpr->pLeft;
+        goto expr_code_doover; /* 2018-04-28: Prevent deep recursion. */
+      }
+    }
     case TK_SPAN:
-    case TK_COLLATE: 
     case TK_UPLUS: {
       pExpr = pExpr->pLeft;
       goto expr_code_doover; /* 2018-04-28: Prevent deep recursion. OSSFuzz. */
diff --git a/src/vdbe.c b/src/vdbe.c
index c893e7763..e907b11f2 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -8306,6 +8306,17 @@ case OP_Function: {            /* group */
   break;
 }
 
+/* Opcode: ClrSubtype P1 * * * *
+** Synopsis:  r[P1].subtype = 0
+**
+** Clear the subtype from register P1.
+*/
+case OP_ClrSubtype: {   /* in1 */
+  pIn1 = &aMem[pOp->p1];
+  pIn1->flags &= ~MEM_Subtype;
+  break;
+}
+
 /* Opcode: FilterAdd P1 * P3 P4 *
 ** Synopsis: filter(P1) += key(P3@P4)
 **
diff --git a/test/subtype1.test b/test/subtype1.test
index 101961102..3ea326115 100644
--- a/test/subtype1.test
+++ b/test/subtype1.test
@@ -28,4 +28,27 @@ do_execsql_test subtype1-130 {
   SELECT test_setsubtype('hello',123);
 } {hello}
 
+# 2022-06-09
+# https://sqlite.org/forum/forumpost/3d9caa45cbe38c78
+#
+# Avoid carrying subtypes through into a subquery that has been flattened
+# or to which the outer WHERE clause has been pushed down.
+#
+reset_db
+do_execsql_test subtype1-200 {
+  CREATE TABLE t1(a); INSERT INTO t1 VALUES ('x');
+  CREATE VIEW t2(b) AS SELECT json(TRUE);
+  CREATE TABLE t3(b); INSERT INTO t3 VALUES(json(TRUE));
+}
+do_execsql_test subtype1-210 {
+  SELECT * FROM t3, t1 WHERE NOT json_quote(b);
+} {1 x}
+do_execsql_test subtype1-220 {
+  SELECT * FROM t2, t1 WHERE NOT json_quote(b);
+} {1 x}
+
+
+
+
+
 finish_test