SQLite

Check-in [8fe13f7a]
Login

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

Overview
Comment:When flattening the right operand of a LEFT JOIN (check-in [41c27bc0ff1d3135]), ensure that the OP_IfNullRow opcode does not NULL-out a subquery result that was computed within OP_Once. This fixes the problem problem reported by forum post 402f05296d.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 8fe13f7a5e5eb798189acb25a608df7a94c2f5cc83463331a048b779c7890c82
User & Date: drh 2023-03-02 13:49:50
Context
2023-03-03
10:42
Remove unnecessary call to sqlite3_dbdata_init() from shell.c.in. (check-in: c4d083a3 user: dan tags: trunk)
2023-03-02
14:09
When flattening the right operand of a LEFT JOIN, ensure that the OP_IfNullRow opcode does not NULL-out a subquery result that was computed within OP_Once. (check-in: c80b262c user: drh tags: branch-3.41)
13:49
When flattening the right operand of a LEFT JOIN (check-in [41c27bc0ff1d3135]), ensure that the OP_IfNullRow opcode does not NULL-out a subquery result that was computed within OP_Once. This fixes the problem problem reported by forum post 402f05296d. (check-in: 8fe13f7a user: drh tags: trunk)
06:58
Resolve a parallel build timing issue when building sqlite3.c/h from ext/wasm. For the time being, do not add sqlite3_wasm_extra_init.c to fiddle.wasm because it can cause duplicate definitions of extensions which are already built into the shell (a better resolution for this conflict is pending). No longer add sqlite3_wasm_extra_init.c to speedtest1.wasm because it's useless there. (check-in: 75fdd5b8 user: stephan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

4829
4830
4831
4832
4833
4834
4835
4836



4837
4838
4839

4840

4841
4842
4843




4844
4845
4846
4847
4848
4849
4850
4851
4852
          sqlite3VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
                            pAggInfo->aCol[pExpr->iAgg].iSorterColumn,
                            target);
          inReg = target;
          break;
        }
      }
      addrINR = sqlite3VdbeAddOp1(v, OP_IfNullRow, pExpr->iTable);



      /* Temporarily disable factoring of constant expressions, since
      ** even though expressions may appear to be constant, they are not
      ** really constant because they originate from the right-hand side

      ** of a LEFT JOIN. */

      pParse->okConstFactor = 0;
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      pParse->okConstFactor = okConstFactor;




      sqlite3VdbeJumpHere(v, addrINR);
      sqlite3VdbeChangeP3(v, addrINR, inReg);
      break;
    }

    /*
    ** Form A:
    **   CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **







|
>
>
>
|
<
<
>
|
>
|


>
>
>
>

<







4829
4830
4831
4832
4833
4834
4835
4836
4837
4838
4839
4840


4841
4842
4843
4844
4845
4846
4847
4848
4849
4850
4851

4852
4853
4854
4855
4856
4857
4858
          sqlite3VdbeAddOp3(v, OP_Column, pAggInfo->sortingIdxPTab,
                            pAggInfo->aCol[pExpr->iAgg].iSorterColumn,
                            target);
          inReg = target;
          break;
        }
      }
      addrINR = sqlite3VdbeAddOp3(v, OP_IfNullRow, pExpr->iTable, 0, target);
      /* The OP_IfNullRow opcode above can overwrite the result register with
      ** NULL.  So we have to ensure that the result register is not a value
      ** that is suppose to be a constant.  Two defenses are needed:
      **   (1)  Temporarily disable factoring of constant expressions


      **   (2)  Make sure the computed value really is stored in register
      **        "target" and not someplace else.
      */
      pParse->okConstFactor = 0;   /* note (1) above */
      inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
      pParse->okConstFactor = okConstFactor;
      if( inReg!=target ){         /* note (2) above */
        sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
        inReg = target;
      }
      sqlite3VdbeJumpHere(v, addrINR);

      break;
    }

    /*
    ** Form A:
    **   CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END
    **

Changes to test/join2.test.

381
382
383
384
385
386
387


















388
389
} NULL
optimization_control db all 0
do_execsql_test 10.4 {
  SELECT (
    SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
  ) FROM t1;
} NULL



















finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
} NULL
optimization_control db all 0
do_execsql_test 10.4 {
  SELECT (
    SELECT 1 FROM t2 LEFT JOIN (SELECT x AS v FROM t3) ON 500=v WHERE (v OR FALSE)
  ) FROM t1;
} NULL

# 2023-03-02 https://sqlite.org/forum/forumpost/402f05296d
#
# The TK_IF_NULL_ROW expression node must ensure that it does not overwrite
# the result register of an OP_Once subroutine.
#
optimization_control db all 1
do_execsql_test 11.1 {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE t3;
  CREATE TABLE t1(x TEXT, y INTEGER);
  INSERT INTO t1(x,y) VALUES(NULL,-2),(NULL,1),('0',2);
  CREATE TABLE t2(z INTEGER);
  INSERT INTO t2(z) VALUES(2),(-2);
  CREATE VIEW t3 AS SELECT z, (SELECT count(*) FROM t1) AS w FROM t2;
  SELECT * FROM t1 LEFT JOIN t3 ON y=z;
} {NULL -2 -2 3 NULL 1 NULL NULL 0 2 2 3}

finish_test