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:

10fa79d00f8091e5748c245f4cae5b5f499a5f8db20da741c130e05a21ede443

User & Date:

drh on 2020-06-15 13:51:34

Other Links:

manifest | tags

Context

2021-07-12

15:00

Fix a defect in the query-flattener optimization identified by ticket [8f157e8010b22af0]. This fix is associated with CVE-2020-15358. (Leaf check-in: bcd014c4 user: dan tags: branch-3.32a)

14:38

Fix a defect in the query-flattener optimization identified by ticket [8f157e8010b22af0]. This fix is associated with CVE-2020-15358. (check-in: 9e001b63 user: dan tags: branch-3.28a)

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

Modified src/select.c from [1a791ad4] to [6ddd86a7].

︙

︙

2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714

    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);

< < |

2698 2699 2700 2701 2702 2703 2704

2705 2706 2707 2708 2709 2710 2711 2712

    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);

︙

︙

4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099 4100 4101

\*\*   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

|

4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 4098 4099

\*\*   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

︙

︙

5787 5788 5789 5790 5791 5792 5793

5794 5795 5796 5797 5798 5799 5800

       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

5785 5786 5787 5788 5789 5790 5791 5792 5793 5794 5795 5796 5797 5798 5799

       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

︙

︙

Modified src/sqliteInt.h from [fe320867] to [abf448e9].

︙

︙

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

︙

︙

Modified test/selectA.test from [b8a590f6] to [68de5240].

︙

︙

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