Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch early-column-names Excluding Merge-Ins
This is equivalent to a diff from bcec155e to 0c38dde4
2017-07-31
| ||
20:06 | TclKit batch tool updates for MSVC. Cherrypick of [bcec155e0d6c6b17] and [0c77935cf9949099]. (check-in: 96ff1675 user: mistachkin tags: branch-3.20) | |
17:40 | More consistent column names. Cherry-pick of [09834279] and [0c38dde45] as a fix for ticket [de3403bf5ae]. (check-in: be0e24a0 user: drh tags: branch-3.20) | |
16:42 | Move the generation of output column names earlier, to right after name resolution and before query transformations such as flattening. This prevents the names from getting mangled by query transformations, and obviates hacks in the query flattener that attempt to work around the name mangling. The resulting code is smaller and faster and gives more consistent output. Fix to ticket [de3403bf5ae5f72ed]. (check-in: ade7ddf1 user: drh tags: trunk) | |
2017-07-30
| ||
18:40 | Correctly handle an "INTEGER PRIMARY KEY UNIQUE" column in a WITHOUT ROWID table. This is a fix for ticket [bc115541132dad136], a problem discovered by OSSFuzz. (check-in: 5216bfb7 user: drh tags: trunk) | |
2017-07-29
| ||
17:10 | Merge latest trunk changes with this branch. (check-in: b42c8779 user: dan tags: server-process-edition) | |
17:02 | New test cases for column name generation interacting with the query flattener. (Closed-Leaf check-in: 0c38dde4 user: drh tags: early-column-names) | |
16:01 | Move the generation of output column names earlier, to right after name resolution and before query transformations such as flattening. This prevents the names from getting mangled by query transformations, and obviates hacks in the query flattener that attempt to work around the name mangling. The resulting code is smaller and faster and gives more consistent output. This is an alternative fix to ticket [de3403bf5ae5f72ed]. (check-in: 09834279 user: drh tags: early-column-names) | |
03:33 | In the query flattener, only add AS clauses to output columns of the outer query that are copied directly from the inner query. Formerly, all columns of the outer query received an AS clause if they did not have one already. This is a proposed fix for ticket [de3403bf5ae5f72]. (check-in: 439cc5c5 user: drh tags: flattener-column-names) | |
2017-07-28
| ||
22:22 | Update Tcl version used by the TclKit batch tool for MSVC. (check-in: bcec155e user: mistachkin tags: trunk) | |
22:13 | Fix harmless compiler warning. (check-in: 3286e1a0 user: mistachkin tags: trunk) | |
Changes to src/select.c.
︙ | ︙ | |||
1434 1435 1436 1437 1438 1439 1440 | assert( pTab && pExpr->pTab==pTab ); if( pS ){ /* The "table" is actually a sub-select or a view in the FROM clause ** of the SELECT statement. Return the declaration type and origin ** data for the result-set column of the sub-select. */ | | < < < | 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 | assert( pTab && pExpr->pTab==pTab ); if( pS ){ /* The "table" is actually a sub-select or a view in the FROM clause ** of the SELECT statement. Return the declaration type and origin ** data for the result-set column of the sub-select. */ if( iCol>=0 && iCol<pS->pEList->nExpr ){ /* If iCol is less than zero, then the expression requests the ** rowid of the sub-select or view. This expression is legal (see ** test case misc2.2.2) - it always evaluates to NULL. */ NameContext sNC; Expr *p = pS->pEList->a[iCol].pExpr; sNC.pSrcList = pS->pSrc; sNC.pNext = pNC; sNC.pParse = pNC->pParse; zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol, &estWidth); |
︙ | ︙ | |||
1550 1551 1552 1553 1554 1555 1556 | zType = columnType(&sNC, p, 0, 0, 0, 0); #endif sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT); } #endif /* !defined(SQLITE_OMIT_DECLTYPE) */ } | < < < < < < < < < < < < | 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 | zType = columnType(&sNC, p, 0, 0, 0, 0); #endif sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, zType, SQLITE_TRANSIENT); } #endif /* !defined(SQLITE_OMIT_DECLTYPE) */ } /* ** Compute the column names for a SELECT statement. ** ** The only guarantee that SQLite makes about column names is that if the ** column has an AS clause assigning it a name, that will be the name used. ** That is the only documented guarantee. However, countless applications |
︙ | ︙ | |||
1595 1596 1597 1598 1599 1600 1601 | ** ** full=ON, short=ANY: If the result refers directly to a table column, ** then the result column name with the table name ** prefix, ex: TABLE.COLUMN. Otherwise use zSpan. */ static void generateColumnNames( Parse *pParse, /* Parser context */ | | < > > | | > > > > | < < < > > | 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 | ** ** full=ON, short=ANY: If the result refers directly to a table column, ** then the result column name with the table name ** prefix, ex: TABLE.COLUMN. Otherwise use zSpan. */ static void generateColumnNames( Parse *pParse, /* Parser context */ Select *pSelect /* Generate column names for this SELECT statement */ ){ Vdbe *v = pParse->pVdbe; int i; Table *pTab; SrcList *pTabList; ExprList *pEList; sqlite3 *db = pParse->db; int fullName; /* TABLE.COLUMN if no AS clause and is a direct table ref */ int srcName; /* COLUMN or TABLE.COLUMN if no AS clause and is direct */ #ifndef SQLITE_OMIT_EXPLAIN /* If this is an EXPLAIN, skip this step */ if( pParse->explain ){ return; } #endif if( pParse->colNamesSet || db->mallocFailed ) return; /* Column names are determined by the left-most term of a compound select */ while( pSelect->pPrior ) pSelect = pSelect->pPrior; pTabList = pSelect->pSrc; pEList = pSelect->pEList; assert( v!=0 ); assert( pTabList!=0 ); pParse->colNamesSet = 1; fullName = (db->flags & SQLITE_FullColNames)!=0; srcName = (db->flags & SQLITE_ShortColNames)!=0 || fullName; sqlite3VdbeSetNumCols(v, pEList->nExpr); for(i=0; i<pEList->nExpr; i++){ Expr *p = pEList->a[i].pExpr; assert( p!=0 ); if( pEList->a[i].zName ){ /* An AS clause always takes first priority */ char *zName = pEList->a[i].zName; sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_TRANSIENT); }else if( srcName && p->op==TK_COLUMN ){ char *zCol; int iCol = p->iColumn; pTab = p->pTab; assert( pTab!=0 ); if( iCol<0 ) iCol = pTab->iPKey; assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) ); if( iCol<0 ){ zCol = "rowid"; }else{ zCol = pTab->aCol[iCol].zName; } |
︙ | ︙ | |||
2464 2465 2466 2467 2468 2469 2470 | /* Convert the data in the temporary table into whatever form ** it is that we currently need. */ assert( unionTab==dest.iSDParm || dest.eDest!=priorOp ); if( dest.eDest!=priorOp ){ int iCont, iBreak, iStart; assert( p->pEList ); | < < < < < | 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 | /* Convert the data in the temporary table into whatever form ** it is that we currently need. */ assert( unionTab==dest.iSDParm || dest.eDest!=priorOp ); if( dest.eDest!=priorOp ){ int iCont, iBreak, iStart; assert( p->pEList ); iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp2(v, OP_Rewind, unionTab, iBreak); VdbeCoverage(v); iStart = sqlite3VdbeCurrentAddr(v); selectInnerLoop(pParse, p, p->pEList, unionTab, 0, 0, &dest, iCont, iBreak); |
︙ | ︙ | |||
2539 2540 2541 2542 2543 2544 2545 | p->pLimit = pLimit; p->pOffset = pOffset; /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); | < < < < < | 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 | p->pLimit = pLimit; p->pOffset = pOffset; /* Generate code to take the intersection of the two temporary ** tables. */ assert( p->pEList ); iBreak = sqlite3VdbeMakeLabel(v); iCont = sqlite3VdbeMakeLabel(v); computeLimitRegisters(pParse, p, iBreak); sqlite3VdbeAddOp2(v, OP_Rewind, tab1, iBreak); VdbeCoverage(v); r1 = sqlite3GetTempReg(pParse); iStart = sqlite3VdbeAddOp2(v, OP_RowData, tab1, r1); sqlite3VdbeAddOp4Int(v, OP_NotFound, tab2, iCont, r1, 0); VdbeCoverage(v); |
︙ | ︙ | |||
3151 3152 3153 3154 3155 3156 3157 | sqlite3VdbeChangeP5(v, OPFLAG_PERMUTE); sqlite3VdbeAddOp3(v, OP_Jump, addrAltB, addrAeqB, addrAgtB); VdbeCoverage(v); /* Jump to the this point in order to terminate the query. */ sqlite3VdbeResolveLabel(v, labelEnd); | < < < < < < < < | 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 | sqlite3VdbeChangeP5(v, OPFLAG_PERMUTE); sqlite3VdbeAddOp3(v, OP_Jump, addrAltB, addrAeqB, addrAgtB); VdbeCoverage(v); /* Jump to the this point in order to terminate the query. */ sqlite3VdbeResolveLabel(v, labelEnd); /* Reassembly the compound query so that it will be freed correctly ** by the calling function */ if( p->pPrior ){ sqlite3SelectDelete(db, p->pPrior); } p->pPrior = pPrior; pPrior->pNext = p; |
︙ | ︙ | |||
3453 3454 3455 3456 3457 3458 3459 | ){ const char *zSavedAuthContext = pParse->zAuthContext; Select *pParent; /* Current UNION ALL term of the other query */ Select *pSub; /* The inner query or "subquery" */ Select *pSub1; /* Pointer to the rightmost select in sub-query */ SrcList *pSrc; /* The FROM clause of the outer query */ SrcList *pSubSrc; /* The FROM clause of the subquery */ | < | 3424 3425 3426 3427 3428 3429 3430 3431 3432 3433 3434 3435 3436 3437 | ){ const char *zSavedAuthContext = pParse->zAuthContext; Select *pParent; /* Current UNION ALL term of the other query */ Select *pSub; /* The inner query or "subquery" */ Select *pSub1; /* Pointer to the rightmost select in sub-query */ SrcList *pSrc; /* The FROM clause of the outer query */ SrcList *pSubSrc; /* The FROM clause of the subquery */ int iParent; /* VDBE cursor number of the pSub result set temp table */ int iNewParent = -1;/* Replacement table for iParent */ int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */ int i; /* Loop counter */ Expr *pWhere; /* The WHERE clause */ struct SrcList_item *pSubitem; /* The subquery */ sqlite3 *db = pParse->db; |
︙ | ︙ | |||
3778 3779 3780 3781 3782 3783 3784 | ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b; ** \ \_____________ subquery __________/ / ** \_____________________ outer query ______________________________/ ** ** We look at every expression in the outer query and every place we see ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10". */ | < < < < < < < < | 3748 3749 3750 3751 3752 3753 3754 3755 3756 3757 3758 3759 3760 3761 | ** SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b; ** \ \_____________ subquery __________/ / ** \_____________________ outer query ______________________________/ ** ** We look at every expression in the outer query and every place we see ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10". */ if( pSub->pOrderBy ){ /* At this point, any non-zero iOrderByCol values indicate that the ** ORDER BY column expression is identical to the iOrderByCol'th ** expression returned by SELECT statement pSub. Since these values ** do not necessarily correspond to columns in SELECT statement pParent, ** zero them before transfering the ORDER BY clause. ** |
︙ | ︙ | |||
5214 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 | isAgg = (p->selFlags & SF_Aggregate)!=0; #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif /* Try to flatten subqueries in the FROM clause up into the main query */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) for(i=0; !p->pPrior && i<pTabList->nSrc; i++){ struct SrcList_item *pItem = &pTabList->a[i]; Select *pSub = pItem->pSelect; | > > > > > > > > | 5176 5177 5178 5179 5180 5181 5182 5183 5184 5185 5186 5187 5188 5189 5190 5191 5192 5193 5194 5195 5196 5197 | isAgg = (p->selFlags & SF_Aggregate)!=0; #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif /* Get a pointer the VDBE under construction, allocating a new VDBE if one ** does not already exist */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto select_end; if( pDest->eDest==SRT_Output ){ generateColumnNames(pParse, p); } /* Try to flatten subqueries in the FROM clause up into the main query */ #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) for(i=0; !p->pPrior && i<pTabList->nSrc; i++){ struct SrcList_item *pItem = &pTabList->a[i]; Select *pSub = pItem->pSelect; |
︙ | ︙ | |||
5250 5251 5252 5253 5254 5255 5256 | if( db->mallocFailed ) goto select_end; if( !IgnorableOrderby(pDest) ){ sSort.pOrderBy = p->pOrderBy; } } #endif | < < < < < | 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 | if( db->mallocFailed ) goto select_end; if( !IgnorableOrderby(pDest) ){ sSort.pOrderBy = p->pOrderBy; } } #endif #ifndef SQLITE_OMIT_COMPOUND_SELECT /* Handle compound SELECT statements using the separate multiSelect() ** procedure. */ if( p->pPrior ){ rc = multiSelect(pParse, p, pDest); explainSetInteger(pParse->iSelectId, iRestoreSelectId); |
︙ | ︙ | |||
6054 6055 6056 6057 6058 6059 6060 | /* Control jumps to here if an error is encountered above, or upon ** successful coding of the SELECT. */ select_end: explainSetInteger(pParse->iSelectId, iRestoreSelectId); | < < < < < < | 6019 6020 6021 6022 6023 6024 6025 6026 6027 6028 6029 6030 6031 6032 6033 | /* Control jumps to here if an error is encountered above, or upon ** successful coding of the SELECT. */ select_end: explainSetInteger(pParse->iSelectId, iRestoreSelectId); sqlite3DbFree(db, sAggInfo.aCol); sqlite3DbFree(db, sAggInfo.aFunc); #if SELECTTRACE_ENABLED SELECTTRACE(1,pParse,p,("end processing\n")); pParse->nSelectIndent--; #endif return rc; } |
Changes to test/colname.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # The focus of this file is testing how SQLite generates the names # of columns in a result set. # | < | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # The focus of this file is testing how SQLite generates the names # of columns in a result set. # set testdir [file dirname $argv0] source $testdir/tester.tcl # Rules (applied in order): # # (1) If there is an AS clause, use it. |
︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 | do_test colname-8.1 { db eval { CREATE TABLE "t3893"("x"); INSERT INTO t3893 VALUES(123); SELECT "y"."x" FROM (SELECT "x" FROM "t3893") AS "y"; } } {123} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 | do_test colname-8.1 { db eval { CREATE TABLE "t3893"("x"); INSERT INTO t3893 VALUES(123); SELECT "y"."x" FROM (SELECT "x" FROM "t3893") AS "y"; } } {123} # 2017-07-29: Interaction between column naming and query flattening. # For years now, the query flattener has inserted AS clauses on the # outer query that were the original SQL text of the column. This caused # column-name shifts when the query flattener was enhanced, breaking # legacy applications. See https://sqlite.org/src/info/41c27bc0ff1d3135 # for details. # # To fix this, the column naming logic was moved ahead of the query # flattener so that column names are assigned before the query flattener # runs. # db close sqlite3 db :memory: do_test colname-9.100 { db eval { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2); CREATE VIEW v1(x,y) AS SELECT a,b FROM t1; } execsql2 {SELECT v1.x, (Y) FROM v1} # Prior to the fix, this would return: "v1.x 1 (Y) 2" } {x 1 y 2} do_test colname-9.110 { execsql2 {SELECT * FROM v1} } {x 1 y 2} do_test colname-9.120 { db eval { CREATE VIEW v2(x,y) AS SELECT a,b FROM t1 LIMIT 10; } execsql2 {SELECT * FROM v2 WHERE 1} } {x 1 y 2} do_test colname-9.130 { execsql2 {SELECT v2.x, [v2].[y] FROM v2 WHERE 1} } {x 1 y 2} do_test colname-9.140 { execsql2 {SELECT +x, +y FROM v2 WHERE 1} } {+x 1 +y 2} do_test colname-9.200 { db eval { CREATE TABLE t2(c,d); INSERT INTO t2 VALUES(3,4); CREATE VIEW v3 AS SELECT c AS a, d AS b FROM t2; } execsql2 {SELECT t1.a, v3.a AS n FROM t1 LEFT JOIN v3} } {a 1 n 3} do_test colname-9.211 { execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3} } {n 1 a 3} do_test colname-9.210 { execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3} } {a 1 n 3} finish_test |