Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
b9ec72203c19c2b95e648ac1dfad74ee |
User & Date: | dan 2019-09-09 20:17:24 |
References
2019-09-09
| ||
20:18 | • Closed ticket [a7debbe0ad]: BETWEEN issue in view plus 6 other changes artifact: 2c6a62d634 user: dan | |
Context
2019-09-10
| ||
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 | |
19:49 | 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. Possible fix for [a7debbe0]. Closed-Leaf check-in: 1863b7bf12 user: dan tags: tkt-a7debbe0. | |
2019-09-04
| ||
06:56 | Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. check-in: cb3e2be674 user: dan tags: trunk | |
Changes
Changes to src/select.c.
3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 .... 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 .... 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 .... 4364 4365 4366 4367 4368 4369 4370 4371 4372 4373 4374 4375 4376 4377 |
*/ 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; } } }else{ if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){ pExpr->iTable = pSubst->iNewTable; } pExpr->pLeft = substExpr(pSubst, pExpr->pLeft); ................................................................................ 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; |
> > > > > > > > > > > > > > > |
3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 3425 3426 3427 3428 .... 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 .... 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); } } } }else{ if( pExpr->op==TK_IF_NULL_ROW && pExpr->iTable==pSubst->iTable ){ pExpr->iTable = pSubst->iNewTable; } pExpr->pLeft = substExpr(pSubst, pExpr->pLeft); ................................................................................ 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; |
Added test/tkt-a7debbe0.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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
# 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 a7debbe0ad1 have been fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix tkt-a7debbe0 foreach tn {1 2} { reset_db if {$tn==1} { # Disable the flattener optimization_control db query-flattener 0 } else { # Enable the flattener optimization_control db query-flattener 1 } do_execsql_test $tn.1.0 { CREATE TABLE t0(xyz INTEGER); INSERT INTO t0(xyz) VALUES(456); CREATE VIEW v2(a, B) AS SELECT 'a', 'B' COLLATE NOCASE FROM t0; CREATE TABLE t2(a, B COLLATE NOCASE); INSERT INTO t2 VALUES('a', 'B'); CREATE VIEW v3(a, B) AS SELECT 'a' COLLATE BINARY, 'B' COLLATE NOCASE FROM t0; CREATE VIEW v4(a, B) AS SELECT 'a', +CAST('B' COLLATE NOCASE AS TEXT) FROM t0; CREATE VIEW v5(a, B) AS SELECT 'a', ('B' COLLATE NOCASE) || '' FROM t0; } # Table t2 and views v2 through v5 should all be equivalent. do_execsql_test $tn.1.1.1 { SELECT a >= B FROM t2; } 1 do_execsql_test $tn.1.1.2 { SELECT 'a' >= 'B' COLLATE NOCASE } 0 do_execsql_test $tn.1.1.3 { SELECT a >= B FROM v2 } 1 do_execsql_test $tn.1.1.4 { SELECT a >= B FROM v3 } 1 do_execsql_test $tn.1.1.5 { SELECT a >= B FROM v4 } 1 do_execsql_test $tn.1.1.6 { SELECT a >= B FROM v5 } 1 do_execsql_test $tn.1.2.1 { SELECT B < a FROM t2 } 0 do_execsql_test $tn.1.2.2 { SELECT 'B' COLLATE NOCASE < 'a' } 0 do_execsql_test $tn.1.2.3 { SELECT B < a FROM v2 } 0 do_execsql_test $tn.1.2.4 { SELECT B < a FROM v3 } 0 do_execsql_test $tn.1.2.5 { SELECT a < B FROM v4 } 0 do_execsql_test $tn.1.2.6 { SELECT a < B FROM v5 } 0 #------------------------------------------------------------------------- do_execsql_test $tn.2.0 { CREATE TABLE t5(a, b COLLATE NOCASE); INSERT INTO t5 VALUES(1, 'XYZ'); } # Result should be 0, as column "xyz" from the sub-query has implicit # collation sequence BINARY. do_execsql_test $tn.2.1 { SELECT xyz==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5; } {0} # Result should be 1, as literal 'xyz' has no collation sequence, so # the comparison uses the implicit collation sequence of the RHS - NOCASE. do_execsql_test $tn.2.2 { SELECT 'xyz'==b FROM ( SELECT a, 'xyz' AS xyz FROM t5 ), t5; } {1} #----------------------------------------------------------------------- # The test case submitted with the ticket. # do_execsql_test $tn.3.0 { DROP TABLE t0; DROP VIEW v2; CREATE TABLE t0(c0); INSERT INTO t0(c0) VALUES(''); CREATE VIEW v2(c0, c1) AS SELECT 'B' COLLATE NOCASE, 'a' FROM t0 ORDER BY t0.c0; SELECT SUM(count) FROM ( SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2 ); } 1 # The result is 1, as the collation used is the implicit collation sequence # of v2.c1 - BINARY. do_execsql_test $tn.3.1 { SELECT v2.c1 BETWEEN v2.c0 AND v2.c1 as count FROM v2; } 1 } finish_test |