SQLite

Check-in [e72661eb]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Follow-up to [bbaf1f2eb1e1637b]: Make sure subtypes do not cross a subquery boundary even if the function that returned the value with a subtype is buried down inside a larger expression. This fixes a problem identified by forum post 37dd14a538.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e72661eb680ea707a839cb3d5cf6c7ef03706e7b40af1b84760147e59cd61a50
User & Date: drh 2023-03-01 15:21:53
Context
2023-03-01
20:23
When flattening a view that is the right operand of a LEFT JOIN, using the optimization of check-in [41c27bc0ff1d3135], always insert the TK_IF_NULL_ROW expression nodes, even for TK_COLUMN expressions, as the TK_COLUMN might be a column from an outer query and hence still need to be NULLed out. This fixes the problem described by forum post 26387ea7ef. (check-in: 198b3e33 user: drh tags: trunk)
15:28
Make sure subtypes do not cross a subquery boundary even if the function that returned the value with a subtype is buried down inside a larger expression. (check-in: 9dc46031 user: drh tags: branch-3.41)
15:21
Follow-up to [bbaf1f2eb1e1637b]: Make sure subtypes do not cross a subquery boundary even if the function that returned the value with a subtype is buried down inside a larger expression. This fixes a problem identified by forum post 37dd14a538. (check-in: e72661eb user: drh tags: trunk)
13:54
Do not attempt to apply the count-of-view optimization to a CTE. dbsqlfuzz ef8623915d843b150c159166ee4548c78cc6895a (check-in: abc3a383 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

︙︙
4715
4716
4717
4718
4719
4720
4721
4722
4723




4724
4725
4726
4727
4728
4729
4730
4731
4732

    ** Z is stored in pExpr->pList->a[1].pExpr.
    */
    case TK_BETWEEN: {
      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;








|
|
>
>
>
>
|
<








4715
4716
4717
4718
4719
4720
4721
4722
4723
4724
4725
4726
4727
4728

4729
4730
4731
4732
4733
4734
4735

    ** Z is stored in pExpr->pList->a[1].pExpr.
    */
    case TK_BETWEEN: {
      exprCodeBetween(pParse, pExpr, target, 0, 0);
      return target;
    }
    case TK_COLLATE: {
      if( !ExprHasProperty(pExpr, EP_Collate) ){
        /* A TK_COLLATE Expr node without the EP_Collate tag is a so-called
        ** "SOFT-COLLATE" that is added to constraints that are pushed down
        ** from outer queries into sub-queries by the push-down optimization.
        ** Clear subtypes as subtypes may not cross a subquery boundary.
        */
        assert( pExpr->pLeft );

        inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
        if( inReg!=target ){
          sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
          inReg = target;
        }
        sqlite3VdbeAddOp1(v, OP_ClrSubtype, inReg);
        return inReg;
︙︙

Changes to test/subtype1.test.

︙︙
49
50
51
52
53
54
55


56
















57
58
59

do_execsql_test subtype1-230 {
  WITH t4(a) AS MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;
} {0}
do_execsql_test subtype1-231 {
  WITH t4(a) AS NOT MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;
} {0}






















finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

do_execsql_test subtype1-230 {
  WITH t4(a) AS MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;
} {0}
do_execsql_test subtype1-231 {
  WITH t4(a) AS NOT MATERIALIZED (SELECT json(1)) SELECT subtype(a) FROM t4;
} {0}

# 2023-03-01
# https://sqlite.org/forum/forumpost/37dd14a538
# 
# Additional tests to show that subtypes do not traverse subquery boundaries.
#
do_execsql_test subtype1-300 {
  CREATE TABLE t0(c0);
  INSERT INTO t0 VALUES ('1');
  CREATE VIEW v0(c0) AS SELECT CASE WHEN 1 THEN json_patch('1', '1') END 
                        FROM t0 GROUP BY t0.c0;
  SELECT * FROM v0 WHERE json_quote(v0.c0) != '1';
} {1}
do_execsql_test subtype1-310 {
  SELECT *, json_quote(y) FROM (SELECT +json('1') AS y);
} {1 {"1"}}
do_execsql_test subtype1-320 {
  SELECT *, json_quote(y) FROM (SELECT +json('1') AS y)
   WHERE json_quote(y)='"1"';
} {1 {"1"}}


finish_test