Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a defect in the query-flattener optimization identified by ticket [8f157e8010b22af0]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
10fa79d00f8091e5748c245f4cae5b5f |
User & Date: | drh 2020-06-15 13:51:34 |
Context
2020-06-17
| ||
12:37 | Merge miscellaneous fixes from trunk into the 3.32 branch. (check-in: d55b8e79 user: drh tags: branch-3.32) | |
2020-06-15
| ||
14:38 | Fix the --enable-update-limit option to ./configure. (check-in: d31fd57e user: drh tags: trunk) | |
13:51 | Fix a defect in the query-flattener optimization identified by ticket [8f157e8010b22af0]. (check-in: 10fa79d0 user: drh tags: trunk) | |
2020-06-14
| ||
13:40 | Check-in [1d4f86201dab9a22] changed a testcase() to an assert() because we didn't know how to reach that condition any more. But YongHeng's fuzzer found a way. So now we change it back. Ticket [9fb26d37cefaba40]. (check-in: 90b1169d user: drh tags: trunk) | |
Changes
Changes to src/select.c.
2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 .... 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101 .... 5787 5788 5789 5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 5800 |
pLimit = p->pLimit; p->pLimit = 0; uniondest.eDest = op; ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE", selectOpName(p->op))); rc = sqlite3Select(pParse, p, &uniondest); testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */ sqlite3ExprListDelete(db, p->pOrderBy); pDelete = p->pPrior; p->pPrior = pPrior; p->pOrderBy = 0; if( p->op==TK_UNION ){ p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow); } sqlite3ExprDelete(db, p->pLimit); ................................................................................ ** 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. ** ** Not doing this may cause an error if a subsequent call to this ................................................................................ pDest->eDest==SRT_Queue || pDest->eDest==SRT_DistFifo || pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo); /* If ORDER BY makes no difference in the output then neither does ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; p->selFlags &= ~SF_Distinct; } sqlite3SelectPrep(pParse, p, 0); if( pParse->nErr || db->mallocFailed ){ goto select_end; } assert( p->pEList!=0 ); #if SELECTTRACE_ENABLED |
< < | | > |
2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 .... 4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 .... 5785 5786 5787 5788 5789 5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 |
pLimit = p->pLimit; p->pLimit = 0; uniondest.eDest = op; ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE", selectOpName(p->op))); rc = sqlite3Select(pParse, p, &uniondest); testcase( rc!=SQLITE_OK ); assert( p->pOrderBy==0 ); pDelete = p->pPrior; p->pPrior = pPrior; p->pOrderBy = 0; if( p->op==TK_UNION ){ p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow); } sqlite3ExprDelete(db, p->pLimit); ................................................................................ ** 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 && (pParent->selFlags & SF_NoopOrderBy)==0 ){ /* 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. ** ** Not doing this may cause an error if a subsequent call to this ................................................................................ pDest->eDest==SRT_Queue || pDest->eDest==SRT_DistFifo || pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo); /* If ORDER BY makes no difference in the output then neither does ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; p->selFlags &= ~SF_Distinct; p->selFlags |= SF_NoopOrderBy; } sqlite3SelectPrep(pParse, p, 0); if( pParse->nErr || db->mallocFailed ){ goto select_end; } assert( p->pEList!=0 ); #if SELECTTRACE_ENABLED |
Changes to src/sqliteInt.h.
3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 |
#define SF_MaybeConvert 0x0008000 /* Need convertCompoundSelectToSubquery() */ #define SF_Converted 0x0010000 /* By convertCompoundSelectToSubquery() */ #define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */ #define SF_ComplexResult 0x0040000 /* Result contains subquery or function */ #define SF_WhereBegin 0x0080000 /* Really a WhereBegin() call. Debug Only */ #define SF_WinRewrite 0x0100000 /* Window function rewrite accomplished */ #define SF_View 0x0200000 /* SELECT statement is a view */ /* ** The results of a SELECT can be distributed in several ways, as defined ** by one of the following macros. The "SRT" prefix means "SELECT Result ** Type". ** ** SRT_Union Store results as a key in a temporary index |
> |
3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 |
#define SF_MaybeConvert 0x0008000 /* Need convertCompoundSelectToSubquery() */
#define SF_Converted 0x0010000 /* By convertCompoundSelectToSubquery() */
#define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */
#define SF_ComplexResult 0x0040000 /* Result contains subquery or function */
#define SF_WhereBegin 0x0080000 /* Really a WhereBegin() call. Debug Only */
#define SF_WinRewrite 0x0100000 /* Window function rewrite accomplished */
#define SF_View 0x0200000 /* SELECT statement is a view */
#define SF_NoopOrderBy 0x0400000 /* ORDER BY is ignored for this query */
/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros. The "SRT" prefix means "SELECT Result
** Type".
**
** SRT_Union Store results as a key in a temporary index
|
Changes to test/selectA.test.
1442 1443 1444 1445 1446 1447 1448 1449 1450 |
DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER); CREATE TABLE t2(b TEXT); INSERT INTO t2(b) VALUES('12345'); SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a; } {12345} finish_test |
> | > > > > > > > > > > > > > > > > > > > > |
1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 |
DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER); CREATE TABLE t2(b TEXT); INSERT INTO t2(b) VALUES('12345'); SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a; } {12345} # 2020-06-15 ticket 8f157e8010b22af0 # reset_db do_execsql_test 7.1 { CREATE TABLE t1(c1); INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc'); CREATE TABLE t2(c2); INSERT INTO t2 VALUES(44),(55),(123); CREATE TABLE t3(c3,c4); INSERT INTO t3 VALUES(66,1),(123,2),(77,3); CREATE VIEW t4 AS SELECT c3 FROM t3; CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4; } do_execsql_test 7.2 { SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123; } {123 123} do_execsql_test 7.3 { SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123; } {123 123} do_execsql_test 7.4 { CREATE TABLE a(b); CREATE VIEW c(d) AS SELECT b FROM a ORDER BY b; SELECT sum(d) OVER( PARTITION BY(SELECT 0 FROM c JOIN a WHERE b =(SELECT b INTERSECT SELECT d FROM c) AND b = 123)) FROM c; } {} finish_test |