/ Check-in [36997c4a]
Login

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

Overview
Comment:Ensure the columns of views and sub-queries maintain their implicit collation sequences when the "push-down" optimization is applied. Fix for [18458b1a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 36997c4ade2ef3a274cd1ac52b44118fb3a05325adb650b7b338ecb43d060575
User & Date: dan 2019-09-10 15:33:52
Context
2019-09-10
17:51
Enhance treeview to show SOFT-COLLATE for TK_COLLATE operators that omit the EP_Collate flag. (check-in: a9780462 user: drh tags: trunk)
15:33
Ensure the columns of views and sub-queries maintain their implicit collation sequences when the "push-down" optimization is applied. Fix for [18458b1a]. (check-in: 36997c4a user: dan tags: trunk)
2019-09-09
20:17
Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Fix for [a7debbe0]. (check-in: b9ec7220 user: dan tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

3414
3415
3416
3417
3418
3419
3420
3421
3422
3423
3424
3425
3426
3427
3428
*/
typedef struct SubstContext {
  Parse *pParse;            /* The parsing context */
  int iTable;               /* Replace references to this table */
  int iNewTable;            /* New table number */
  int isLeftJoin;           /* Add TK_IF_NULL_ROW opcodes on each replacement */
  ExprList *pEList;         /* Replacement expressions */
  int bFlattener;           /* True for query-flattener, false otherwise */
} SubstContext;

/* Forward Declarations */
static void substExprList(SubstContext*, ExprList*);
static void substSelect(SubstContext*, Select*, int);

/*







<







3414
3415
3416
3417
3418
3419
3420

3421
3422
3423
3424
3425
3426
3427
*/
typedef struct SubstContext {
  Parse *pParse;            /* The parsing context */
  int iTable;               /* Replace references to this table */
  int iNewTable;            /* New table number */
  int isLeftJoin;           /* Add TK_IF_NULL_ROW opcodes on each replacement */
  ExprList *pEList;         /* Replacement expressions */

} SubstContext;

/* Forward Declarations */
static void substExprList(SubstContext*, ExprList*);
static void substSelect(SubstContext*, Select*, int);

/*
3476
3477
3478
3479
3480
3481
3482
3483
3484

3485
3486
3487
3488
3489
3490
3491
3492
        if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          ExprSetProperty(pNew, EP_FromJoin);
        }
        sqlite3ExprDelete(db, pExpr);
        pExpr = pNew;

        /* If this call is part of query-flattening, ensure that the
        ** new expression has an implicit collation sequence. */

        if( pSubst->bFlattener && pExpr ){
          if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){
            CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr);
            pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, 
                (pColl ? pColl->zName : "BINARY")
            );
          }
          ExprClearProperty(pExpr, EP_Collate);







<
|
>
|







3475
3476
3477
3478
3479
3480
3481

3482
3483
3484
3485
3486
3487
3488
3489
3490
3491
        if( pNew && ExprHasProperty(pExpr,EP_FromJoin) ){
          pNew->iRightJoinTable = pExpr->iRightJoinTable;
          ExprSetProperty(pNew, EP_FromJoin);
        }
        sqlite3ExprDelete(db, pExpr);
        pExpr = pNew;


        /* Ensure that the expression now has an implicit collation sequence,
        ** just as it did when it was a column of a view or sub-query. */
        if( pExpr ){
          if( pExpr->op!=TK_COLUMN && pExpr->op!=TK_COLLATE ){
            CollSeq *pColl = sqlite3ExprCollSeq(pSubst->pParse, pExpr);
            pExpr = sqlite3ExprAddCollateString(pSubst->pParse, pExpr, 
                (pColl ? pColl->zName : "BINARY")
            );
          }
          ExprClearProperty(pExpr, EP_Collate);
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;
      x.iTable = iParent;
      x.iNewTable = iNewParent;
      x.isLeftJoin = isLeftJoin;
      x.pEList = pSub->pEList;
      x.bFlattener = 1;
      substSelect(&x, pParent, 0);
    }
  
    /* The flattened query is a compound if either the inner or the
    ** outer query is a compound. */
    pParent->selFlags |= pSub->selFlags & SF_Compound;
    assert( (pSub->selFlags & SF_Distinct)==0 ); /* restriction (17b) */







<







4051
4052
4053
4054
4055
4056
4057

4058
4059
4060
4061
4062
4063
4064
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;
      x.iTable = iParent;
      x.iNewTable = iNewParent;
      x.isLeftJoin = isLeftJoin;
      x.pEList = pSub->pEList;

      substSelect(&x, pParent, 0);
    }
  
    /* The flattened query is a compound if either the inner or the
    ** outer query is a compound. */
    pParent->selFlags |= pSub->selFlags & SF_Compound;
    assert( (pSub->selFlags & SF_Distinct)==0 ); /* restriction (17b) */
4378
4379
4380
4381
4382
4383
4384
4385
4386
4387
4388
4389
4390
4391
4392
      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
      unsetJoinExpr(pNew, -1);
      x.pParse = pParse;
      x.iTable = iCursor;
      x.iNewTable = iCursor;
      x.isLeftJoin = 0;
      x.pEList = pSubq->pEList;
      x.bFlattener = 0;
      pNew = substExpr(&x, pNew);
      if( pSubq->selFlags & SF_Aggregate ){
        pSubq->pHaving = sqlite3ExprAnd(pParse, pSubq->pHaving, pNew);
      }else{
        pSubq->pWhere = sqlite3ExprAnd(pParse, pSubq->pWhere, pNew);
      }
      pSubq = pSubq->pPrior;







<







4376
4377
4378
4379
4380
4381
4382

4383
4384
4385
4386
4387
4388
4389
      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
      unsetJoinExpr(pNew, -1);
      x.pParse = pParse;
      x.iTable = iCursor;
      x.iNewTable = iCursor;
      x.isLeftJoin = 0;
      x.pEList = pSubq->pEList;

      pNew = substExpr(&x, pNew);
      if( pSubq->selFlags & SF_Aggregate ){
        pSubq->pHaving = sqlite3ExprAnd(pParse, pSubq->pHaving, pNew);
      }else{
        pSubq->pWhere = sqlite3ExprAnd(pParse, pSubq->pWhere, pNew);
      }
      pSubq = pSubq->pPrior;

Changes to src/test1.c.

7196
7197
7198
7199
7200
7201
7202

7203
7204
7205
7206
7207
7208
7209
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
    { "stat4",               SQLITE_Stat4          },
    { "skip-scan",           SQLITE_SkipScan       },

  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;







>







7196
7197
7198
7199
7200
7201
7202
7203
7204
7205
7206
7207
7208
7209
7210
    { "distinct-opt",        SQLITE_DistinctOpt    },
    { "cover-idx-scan",      SQLITE_CoverIdxScan   },
    { "order-by-idx-join",   SQLITE_OrderByIdxJoin },
    { "transitive",          SQLITE_Transitive     },
    { "omit-noop-join",      SQLITE_OmitNoopJoin   },
    { "stat4",               SQLITE_Stat4          },
    { "skip-scan",           SQLITE_SkipScan       },
    { "push-down",           SQLITE_PushDown       },
  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Added test/tkt-18458b1a.test.











































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# 2019 September 10
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. In particular,
# that problems related to ticket [18458b1a] have been fixed.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix tkt-18458b1a

foreach tn {1 2} {
  reset_db
  if {$tn==1} {
    # Disable the flattener and push-down optimizations
    optimization_control db query-flattener 0
    optimization_control db push-down 0
  } else {
    # Enable them
    optimization_control db query-flattener 1
    optimization_control db push-down 1
  }

  db cache size 0

  do_execsql_test $tn.1.1 {
    CREATE TABLE t0(c0 COLLATE NOCASE);
    INSERT INTO t0(c0) VALUES ('B');
    CREATE VIEW v0(c0, c1) AS SELECT DISTINCT t0.c0, 'a' FROM t0;
  } 

  do_execsql_test $tn.1.2 {
    SELECT count(*) FROM v0 WHERE c1 >= c0;
  } 1

  do_execsql_test $tn.1.3 {
    SELECT count(*) FROM v0 WHERE NOT NOT (c1 >= c0);
  } 1

  do_execsql_test $tn.1.4 {
    SELECT count(*) FROM v0 WHERE ((c1 >= c0) OR 0+0);
  } 1
}

finish_test