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: |
36997c4ade2ef3a274cd1ac52b44118f |
| 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: a97804620a 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: 36997c4ade 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: b9ec72203c user: dan tags: trunk) | |
Changes
Changes to src/select.c.
3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 .... 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 .... 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 .... 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 4390 4391 4392 |
*/
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);
/*
................................................................................
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);
................................................................................
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) */
................................................................................
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;
|
< | | | < < |
3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 .... 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 .... 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 .... 4376 4377 4378 4379 4380 4381 4382 4383 4384 4385 4386 4387 4388 4389 |
*/
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);
/*
................................................................................
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);
................................................................................
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) */
................................................................................
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 |