/ Check-in [500c9152da]
Login

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

Overview
Comment:Ensure the functions that appear to be constant are not factored out of expression that originate on the right-hand side of a LEFT JOIN. Ticket [6710d2f7a13a2997]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 500c9152daaf11cf69d778aa8592175f6088337c6667c59af6df3a24cd81eb0e
User & Date: drh 2019-08-17 17:07:15
References
2019-08-17
17:11 Fixed ticket [6710d2f7a1]: LEFT JOIN fails to fetch row plus 7 other changes artifact: 12e735c034 user: drh
Context
2019-08-17
19:13
When populating an ephemeral b-tree for the RHS of an IN(...) clause, avoid applying an affinity to a value that may be used later on for some other purpose. Fix for [c7a117190]. check-in: 43e8b14314 user: dan tags: trunk
17:07
Ensure the functions that appear to be constant are not factored out of expression that originate on the right-hand side of a LEFT JOIN. Ticket [6710d2f7a13a2997] check-in: 500c9152da user: drh tags: trunk
15:27
Activatate introspection pragmas by default. The new option SQLITE_OMIT_INTROSPECTION_PRAGMAS must be provided to keep them out. check-in: 9c4bca64fb user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

3990
3991
3992
3993
3994
3995
3996






3997
3998

3999





4000

4001
4002
4003
4004
4005
4006
4007
    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "row value misused");
      break;
    }







    case TK_IF_NULL_ROW: {
      int addrINR;

      addrINR = sqlite3VdbeAddOp1(v, OP_IfNullRow, pExpr->iTable);





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

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

    /*
    ** Form A:







>
>
>
>
>
>


>

>
>
>
>
>

>







3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020
    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "row value misused");
      break;
    }

    /* TK_IF_NULL_ROW Expr nodes are inserted ahead of expressions
    ** that derive from the right-hand table of a LEFT JOIN.  The
    ** Expr.iTable value is the table number for the right-hand table.
    ** The expression is only evaluated if that table is not currently
    ** on a LEFT JOIN NULL row.
    */
    case TK_IF_NULL_ROW: {
      int addrINR;
      u8 okConstFactor = pParse->okConstFactor;
      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:

Changes to test/join.test.

859
860
861
862
863
864
865
866















867
  CREATE TABLE t1(a INT);
  INSERT INTO t1(a) VALUES(1);
  CREATE TABLE t2(b INT);
  SELECT a, b
    FROM t1 LEFT JOIN t2 ON 0
   WHERE (b IS NOT NULL)=0;
} {1 {}}
















finish_test








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

859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
  CREATE TABLE t1(a INT);
  INSERT INTO t1(a) VALUES(1);
  CREATE TABLE t2(b INT);
  SELECT a, b
    FROM t1 LEFT JOIN t2 ON 0
   WHERE (b IS NOT NULL)=0;
} {1 {}}

# 2019-08-17 ticket https://sqlite.org/src/tktview/6710d2f7a13a299728ab
# Ensure that constants that derive from the right-hand table of a LEFT JOIN
# are never factored out, since they are not really constant.
#
do_execsql_test join-17.100 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(0),(1);
  SELECT * FROM t1 LEFT JOIN (SELECT abs(1) AS y FROM t1) ON x WHERE NOT(y='a');
} {1 1 1 1}
do_execsql_test join-17.110 {
  SELECT * FROM t1 LEFT JOIN (SELECT abs(1)+2 AS y FROM t1) ON x
   WHERE NOT(y='a');
} {1 3 1 3}

finish_test