/ Check-in [871f2ddc]
Login

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

Overview
Comment:Do not skip over TK_IF_NULL_ROW operators when bypassing TK_COLLATE operators. Fix to check-in [ac31edd3eeafcef4] which was itself a fix for ticket [45f4bf4eb4ffd788].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 871f2ddcfbb9196dbd851a350e3471ee6d242d86bbd755201f7e2406fce3ac55
User & Date: drh 2020-08-19 23:32:06
Context
2020-09-16
16:38
Do not skip over TK_IF_NULL_ROW operators when bypassing TK_COLLATE operators. Fix to check-in [ac31edd3eeafcef4] which was itself a fix for ticket [45f4bf4eb4ffd788]. (check-in: 85041611 user: dan tags: branch-3.33)
2020-08-19
23:51
Try to make SQLite easier to compiler for Mac Catalyst. See forum post 803387a1c5. (check-in: e1595a20 user: drh tags: trunk)
23:32
Do not skip over TK_IF_NULL_ROW operators when bypassing TK_COLLATE operators. Fix to check-in [ac31edd3eeafcef4] which was itself a fix for ticket [45f4bf4eb4ffd788]. (check-in: 871f2ddc user: drh tags: trunk)
15:24
Update releasetest_data.tcl so that the "Fast-One" configuration runs bigmmap.test as part of release testing. (check-in: a0aa35aa user: dan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
** CREATE TABLE t1(a);
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(const Expr *pExpr){
  int op;
  while( ExprHasProperty(pExpr, EP_Skip) ){
    assert( pExpr->op==TK_COLLATE || pExpr->op==TK_IF_NULL_ROW );
    pExpr = pExpr->pLeft;
    assert( pExpr!=0 );
  }
  op = pExpr->op;
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
................................................................................
}

/*
** Skip over any TK_COLLATE operators.
*/
Expr *sqlite3ExprSkipCollate(Expr *pExpr){
  while( pExpr && ExprHasProperty(pExpr, EP_Skip) ){
    assert( pExpr->op==TK_COLLATE || pExpr->op==TK_IF_NULL_ROW );
    pExpr = pExpr->pLeft;
  }   
  return pExpr;
}

/*
** Skip over any TK_COLLATE operators and/or any unlikely()
................................................................................
  while( pExpr && ExprHasProperty(pExpr, EP_Skip|EP_Unlikely) ){
    if( ExprHasProperty(pExpr, EP_Unlikely) ){
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      assert( pExpr->x.pList->nExpr>0 );
      assert( pExpr->op==TK_FUNCTION );
      pExpr = pExpr->x.pList->a[0].pExpr;
    }else{
      assert( pExpr->op==TK_COLLATE || pExpr->op==TK_IF_NULL_ROW );
      pExpr = pExpr->pLeft;
    }
  }   
  return pExpr;
}

/*







|







 







|







 







|







40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
** CREATE TABLE t1(a);
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(const Expr *pExpr){
  int op;
  while( ExprHasProperty(pExpr, EP_Skip|EP_IfNullRow) ){
    assert( pExpr->op==TK_COLLATE || pExpr->op==TK_IF_NULL_ROW );
    pExpr = pExpr->pLeft;
    assert( pExpr!=0 );
  }
  op = pExpr->op;
  if( op==TK_SELECT ){
    assert( pExpr->flags&EP_xIsSelect );
................................................................................
}

/*
** Skip over any TK_COLLATE operators.
*/
Expr *sqlite3ExprSkipCollate(Expr *pExpr){
  while( pExpr && ExprHasProperty(pExpr, EP_Skip) ){
    assert( pExpr->op==TK_COLLATE );
    pExpr = pExpr->pLeft;
  }   
  return pExpr;
}

/*
** Skip over any TK_COLLATE operators and/or any unlikely()
................................................................................
  while( pExpr && ExprHasProperty(pExpr, EP_Skip|EP_Unlikely) ){
    if( ExprHasProperty(pExpr, EP_Unlikely) ){
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      assert( pExpr->x.pList->nExpr>0 );
      assert( pExpr->op==TK_FUNCTION );
      pExpr = pExpr->x.pList->a[0].pExpr;
    }else{
      assert( pExpr->op==TK_COLLATE );
      pExpr = pExpr->pLeft;
    }
  }   
  return pExpr;
}

/*

Changes to src/select.c.

3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
      }else{
        sqlite3 *db = pSubst->pParse->db;
        if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;
          ifNullRow.flags = EP_Skip;
          pCopy = &ifNullRow;
        }
        testcase( ExprHasProperty(pCopy, EP_Subquery) );
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( pNew && pSubst->isLeftJoin ){
          ExprSetProperty(pNew, EP_CanBeNull);
        }







|







3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
      }else{
        sqlite3 *db = pSubst->pParse->db;
        if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){
          memset(&ifNullRow, 0, sizeof(ifNullRow));
          ifNullRow.op = TK_IF_NULL_ROW;
          ifNullRow.pLeft = pCopy;
          ifNullRow.iTable = pSubst->iNewTable;
          ifNullRow.flags = EP_IfNullRow;
          pCopy = &ifNullRow;
        }
        testcase( ExprHasProperty(pCopy, EP_Subquery) );
        pNew = sqlite3ExprDup(db, pCopy, 0);
        if( pNew && pSubst->isLeftJoin ){
          ExprSetProperty(pNew, EP_CanBeNull);
        }

Changes to src/sqliteInt.h.

2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
#define EP_IntValue   0x000400 /* Integer value contained in u.iValue */
#define EP_xIsSelect  0x000800 /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Skip       0x001000 /* Operator does not contribute to affinity */
#define EP_Reduced    0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
#define EP_TokenOnly  0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
#define EP_Win        0x008000 /* Contains window functions */
#define EP_MemToken   0x010000 /* Need to sqlite3DbFree() Expr.zToken */
                  /*  0x020000 // available for reuse */
#define EP_Unlikely   0x040000 /* unlikely() or likelihood() function */
#define EP_ConstFunc  0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull  0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery   0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias      0x400000 /* Is an alias for a result set column */
#define EP_Leaf       0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
#define EP_WinFunc   0x1000000 /* TK_FUNCTION with Expr.y.pWin set */







|







2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
#define EP_IntValue   0x000400 /* Integer value contained in u.iValue */
#define EP_xIsSelect  0x000800 /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Skip       0x001000 /* Operator does not contribute to affinity */
#define EP_Reduced    0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
#define EP_TokenOnly  0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
#define EP_Win        0x008000 /* Contains window functions */
#define EP_MemToken   0x010000 /* Need to sqlite3DbFree() Expr.zToken */
#define EP_IfNullRow  0x020000 /* The TK_IF_NULL_ROW opcode */
#define EP_Unlikely   0x040000 /* unlikely() or likelihood() function */
#define EP_ConstFunc  0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull  0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery   0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias      0x400000 /* Is an alias for a result set column */
#define EP_Leaf       0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
#define EP_WinFunc   0x1000000 /* TK_FUNCTION with Expr.y.pWin set */

Changes to test/join2.test.

290
291
292
293
294
295
296
297



298
299
300
301
302
303
304
...
319
320
321
322
323
324
325












326
327
328

do_execsql_test 8.1 {
  SELECT * FROM t0 LEFT JOIN t1 
  WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
}

#-------------------------------------------------------------------------
# Ticket [45f4bf4eb].



#
reset_db
do_execsql_test 9.0 {
  CREATE TABLE t0(c0 INT);
  CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
  INSERT INTO t0(c0) VALUES (0);
}
................................................................................
  SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';
} {0 0}

do_execsql_test 9.5 {
  SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE 
  UNION SELECT 0,0 WHERE 0; 
} {0 0}














finish_test







|
>
>
>







 







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



290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
...
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

do_execsql_test 8.1 {
  SELECT * FROM t0 LEFT JOIN t1 
  WHERE (t1.c0 BETWEEN 0 AND 0) > ('' AND t0.c0);
}

#-------------------------------------------------------------------------
# Ticket [45f4bf4eb] reported by Manuel Rigger (2020-04-25)
#
# Follow up error reported by Eric Speckman on the SQLite forum
# https://sqlite.org/forum/info/c49496d24d35bd7c (2020-08-19)
#
reset_db
do_execsql_test 9.0 {
  CREATE TABLE t0(c0 INT);
  CREATE VIEW v0(c0) AS SELECT CAST(t0.c0 AS INTEGER) FROM t0;
  INSERT INTO t0(c0) VALUES (0);
}
................................................................................
  SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0';
} {0 0}

do_execsql_test 9.5 {
  SELECT * FROM t0 LEFT JOIN v0 ON v0.c0 >= '0' WHERE TRUE 
  UNION SELECT 0,0 WHERE 0; 
} {0 0}

do_execsql_test 9.10 {
  CREATE TABLE t1 (aaa);
  INSERT INTO t1 VALUES(23456);
  CREATE TABLE t2(bbb);
  CREATE VIEW v2(ccc) AS SELECT bbb IS 1234 FROM t2;
  SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
} {{} 1}
optimization_control db query-flattener 0
do_execsql_test 9.11 {
  SELECT ccc, ccc IS NULL AS ddd FROM t1 LEFT JOIN v2;
} {{} 1}


finish_test