Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch wrong-branch Excluding Merge-Ins
This is equivalent to a diff from 8682931f to 681dfe2d
2024-04-07
| ||
10:30 | Merge trunk enhancements into the pushdown-subquery branch. (check-in: 27865e31 user: drh tags: pushdown-subquery) | |
10:27 | Add comments to note the name abiguity between the MySQL push-down optimization and the WHERE-clause push-down optimization. (check-in: 3d5fb1ec user: drh tags: trunk) | |
2024-04-06
| ||
23:09 | Add comments to note the name abiguity between the MySQL push-down optimization and the WHERE-clause push-down optimization. (Leaf check-in: 681dfe2d user: drh tags: wrong-branch) | |
18:30 | Do not allow changes to sqlite3ExprIsTableConstant() that support pushdown of subqueries interfere with the hash-join logic. (check-in: 8682931f user: drh tags: pushdown-subquery) | |
17:42 | Merge testing logic fixes on trunk into the pushdown-subquery branch. (check-in: a4e1f03d user: drh tags: pushdown-subquery) | |
Changes to src/expr.c.
︙ | |||
5064 5065 5066 5067 5068 5069 5070 | 5064 5065 5066 5067 5068 5069 5070 5071 5072 5073 5074 5075 5076 5077 5078 5079 5080 | - - + + + | exprCodeBetween(pParse, pExpr, target, 0, 0); return target; } case TK_COLLATE: { if( !ExprHasProperty(pExpr, EP_Collate) ){ /* A TK_COLLATE Expr node without the EP_Collate tag is a so-called ** "SOFT-COLLATE" that is added to constraints that are pushed down |
︙ |
Changes to src/select.c.
︙ | |||
5055 5056 5057 5058 5059 5060 5061 5062 5063 5064 5065 5066 5067 5068 | 5055 5056 5057 5058 5059 5060 5061 5062 5063 5064 5065 5066 5067 5068 5069 5070 5071 5072 5073 5074 5075 5076 5077 5078 5079 5080 | + + + + + + + + + + + + | ** Transformed into: ** ** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10) ** WHERE x=5 AND y=10; ** ** The hope is that the terms added to the inner query will make it more ** efficient. ** ** NAME AMBIGUITY ** ** This optimization is called the "WHERE-clause push-down optimization". ** ** Do not confuse this optimization with another unrelated optimization ** with a similar name: The "MySQL push-down optimization" causes WHERE ** clause terms that can be evaluated using only the index and without ** reference to the table are run first, so that if they are false, ** unnecessary table seeks are avoided. ** ** RULES ** ** Do not attempt this optimization if: ** ** (1) (** This restriction was removed on 2017-09-29. We used to ** disallow this optimization for aggregate subqueries, but now ** it is allowed by putting the extra terms on the HAVING clause. ** The added HAVING clause is pointless if the subquery lacks |
︙ | |||
5121 5122 5123 5124 5125 5126 5127 | 5133 5134 5135 5136 5137 5138 5139 5140 5141 5142 5143 5144 5145 5146 5147 5148 5149 5150 | - - - - + + + + | ** of a join (either an INNER or an OUTER join), and ** ** (9b) The subquery is to the right of the ON/USING clause ** ** (9c) There is a RIGHT JOIN (or FULL JOIN) in between the ON/USING ** clause and the subquery. ** |
︙ | |||
7685 7686 7687 7688 7689 7690 7691 | 7697 7698 7699 7700 7701 7702 7703 7704 7705 7706 7707 7708 7709 7710 7711 | - + | TREETRACE(0x4000,pParse,p, ("After WHERE-clause push-down into subquery %d:\n", pSub->selId)); sqlite3TreeViewSelect(0, p, 0); } #endif assert( pItem->pSelect && (pItem->pSelect->selFlags & SF_PushDown)!=0 ); }else{ |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
1902 1903 1904 1905 1906 1907 1908 | 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 | - + | #define SQLITE_OrderByIdxJoin 0x00000040 /* ORDER BY of joins via index */ #define SQLITE_Transitive 0x00000080 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x00000100 /* Omit unused tables in joins */ #define SQLITE_CountOfView 0x00000200 /* The count-of-view optimization */ #define SQLITE_CursorHints 0x00000400 /* Add OP_CursorHint opcodes */ #define SQLITE_Stat4 0x00000800 /* Use STAT4 data */ /* TH3 expects this value ^^^^^^^^^^ to be 0x0000800. Don't change it */ |
︙ | |||
3576 3577 3578 3579 3580 3581 3582 | 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 | - + | #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 */ #define SF_UFSrcCheck 0x0800000 /* Check pSrc as required by UPDATE...FROM */ |
︙ |
Changes to src/wherecode.c.
︙ | |||
2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 | 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 | + + + + + + | ** ** iLoop==1: Code only expressions that are entirely covered by pIdx. ** iLoop==2: Code remaining expressions that do not contain correlated ** sub-queries. ** iLoop==3: Code all remaining expressions. ** ** An effort is made to skip unnecessary iterations of the loop. ** ** This optimization of causing simple query restrictions to occur before ** more complex one is call the "push-down" optimization in MySQL. Here ** in SQLite, the name is "MySQL push-down", since there is also another ** totally unrelated optimization called "WHERE-clause push-down". ** Sometimes the qualifier is omitted, resulting in an ambiguity, so beware. */ iLoop = (pIdx ? 1 : 2); do{ int iNext = 0; /* Next value for iLoop */ for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ Expr *pE; int skipLikeAddr = 0; |
︙ |
Changes to test/pushdown.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 | - + + + + + + + + + + + + + + + + + + + + + |
|
︙ | |||
83 84 85 86 87 88 89 | 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | - - + + | SELECT x FROM u2 WHERE x=a AND f('two') ) AND f('three')=123 } set L } {three} # 2022-11-25 dbsqlfuzz crash-3a548de406a50e896c1bf7142692d35d339d697f |
︙ | |||
181 182 183 184 185 186 187 | 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | - + | `--SCAN t2 } # ^^^^^^^^^^^^^^^^^^^^ # The query should be converted into: # SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2) # 2023-05-09 https://sqlite.org/forum/forumpost/a7d4be7fb6 |
︙ | |||
225 226 227 228 229 230 231 | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 | - + | FROM t1 JOIN t2 ON null RIGHT JOIN t3 ON true LEFT JOIN (t4 JOIN t5 ON d+1=e) ON d=4 WHERE e>0; } {- - 3 4 5} # 2024-04-05 |
︙ |