/ Check-in [edc1de2a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem with resolving ORDER BY clauses that feature COLLATE clauses attached to compound SELECT statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: edc1de2a588fd50c0049bb2be76d3f6783443165
User & Date: dan 2015-04-04 16:49:04
Context
2015-04-06
11:04
Fix a problem with fts3 prefix terms within phrase queries on "order=DESC" tables with a mix of negative and positive rowids. check-in: 3ad829e5 user: dan tags: trunk
09:05
Fix a problem with fts3 prefix terms within phrase queries on "order=DESC" tables with a mix of negative and positive rowids. Closed-Leaf check-in: 0cdf5028 user: dan tags: fts3-prefix-query-fix
2015-04-04
16:49
Fix a problem with resolving ORDER BY clauses that feature COLLATE clauses attached to compound SELECT statements. check-in: edc1de2a user: dan tags: trunk
16:43
Fix a problem with resolving ORDER BY clauses that feature COLLATE clauses attached to compound SELECT statements. Closed-Leaf check-in: 427b50fb user: dan tags: compound-order-by-fix
2015-04-03
20:33
Disable the SQLITE_FCNTL_WAL_BLOCK feature for now. It needs more work and is not yet ready for release. check-in: 4ae9a3ac user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

  1182   1182       */
  1183   1183       memset(&sNC, 0, sizeof(sNC));
  1184   1184       sNC.pParse = pParse;
  1185   1185       if( sqlite3ResolveExprNames(&sNC, p->pLimit) ||
  1186   1186           sqlite3ResolveExprNames(&sNC, p->pOffset) ){
  1187   1187         return WRC_Abort;
  1188   1188       }
         1189  +
         1190  +    /* If the SF_Converted flags is set, then this Select object was
         1191  +    ** was created by the convertCompoundSelectToSubquery() function.
         1192  +    ** In this case the ORDER BY clause (p->pOrderBy) should be resolved
         1193  +    ** as if it were part of the sub-query, not the parent. This block
         1194  +    ** moves the pOrderBy down to the sub-query. It will be moved back
         1195  +    ** after the names have been resolved.  */
         1196  +    if( p->selFlags & SF_Converted ){
         1197  +      Select *pSub = p->pSrc->a[0].pSelect;
         1198  +      assert( p->pSrc->nSrc==1 && isCompound==0 && p->pOrderBy );
         1199  +      assert( pSub->pPrior && pSub->pOrderBy==0 );
         1200  +      pSub->pOrderBy = p->pOrderBy;
         1201  +      p->pOrderBy = 0;
         1202  +    }
  1189   1203     
  1190   1204       /* Recursively resolve names in all subqueries
  1191   1205       */
  1192   1206       for(i=0; i<p->pSrc->nSrc; i++){
  1193   1207         struct SrcList_item *pItem = &p->pSrc->a[i];
  1194   1208         if( pItem->pSelect ){
  1195   1209           NameContext *pNC;         /* Used to iterate name contexts */
................................................................................
  1263   1277       if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort;
  1264   1278   
  1265   1279       /* The ORDER BY and GROUP BY clauses may not refer to terms in
  1266   1280       ** outer queries 
  1267   1281       */
  1268   1282       sNC.pNext = 0;
  1269   1283       sNC.ncFlags |= NC_AllowAgg;
         1284  +
         1285  +    /* If this is a converted compound query, move the ORDER BY clause from 
         1286  +    ** the sub-query back to the parent query. At this point each term
         1287  +    ** within the ORDER BY clause has been transformed to an integer value.
         1288  +    ** These integers will be replaced by copies of the corresponding result
         1289  +    ** set expressions by the call to resolveOrderGroupBy() below.  */
         1290  +    if( p->selFlags & SF_Converted ){
         1291  +      Select *pSub = p->pSrc->a[0].pSelect;
         1292  +      p->pOrderBy = pSub->pOrderBy;
         1293  +      pSub->pOrderBy = 0;
         1294  +    }
  1270   1295   
  1271   1296       /* Process the ORDER BY clause for singleton SELECT statements.
  1272   1297       ** The ORDER BY clause for compounds SELECT statements is handled
  1273   1298       ** below, after all of the result-sets for all of the elements of
  1274   1299       ** the compound have been resolved.
  1275   1300       */
  1276   1301       if( !isCompound && resolveOrderGroupBy(&sNC, p, p->pOrderBy, "ORDER") ){

Changes to src/select.c.

  3880   3880     p->pWhere = 0;
  3881   3881     pNew->pGroupBy = 0;
  3882   3882     pNew->pHaving = 0;
  3883   3883     pNew->pOrderBy = 0;
  3884   3884     p->pPrior = 0;
  3885   3885     p->pNext = 0;
  3886   3886     p->selFlags &= ~SF_Compound;
         3887  +  assert( (p->selFlags & SF_Converted)==0 );
         3888  +  p->selFlags |= SF_Converted;
  3887   3889     assert( pNew->pPrior!=0 );
  3888   3890     pNew->pPrior->pNext = pNew;
  3889   3891     pNew->pLimit = 0;
  3890   3892     pNew->pOffset = 0;
  3891   3893     return WRC_Continue;
  3892   3894   }
  3893   3895   

Changes to src/sqliteInt.h.

  2385   2385   #define SF_Compound        0x0040  /* Part of a compound query */
  2386   2386   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2387   2387   #define SF_AllValues       0x0100  /* All terms of compound are VALUES */
  2388   2388   #define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2389   2389   #define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
  2390   2390   #define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
  2391   2391   #define SF_MinMaxAgg       0x1000  /* Aggregate containing min() or max() */
         2392  +#define SF_Converted       0x2000  /* By convertCompoundSelectToSubquery() */
  2392   2393   
  2393   2394   
  2394   2395   /*
  2395   2396   ** The results of a SELECT can be distributed in several ways, as defined
  2396   2397   ** by one of the following macros.  The "SRT" prefix means "SELECT Result
  2397   2398   ** Type".
  2398   2399   **

Changes to test/selectA.test.

  1370   1370   do_execsql_test 4.2.2 {
  1371   1371     SELECT c, f(d,c,d,c,d) FROM t7
  1372   1372     UNION ALL
  1373   1373     SELECT a, b FROM t6 
  1374   1374     ORDER BY 1,2
  1375   1375   } {/2 . 3 . 4 . 5 . 6 . 7 ./}
  1376   1376   
         1377  +
         1378  +proc strip_rnd {explain} {
         1379  +  regexp -all {sqlite_sq_[0123456789ABCDEF]*} $explain sqlite_sq
         1380  +}
         1381  +
         1382  +proc do_same_test {tn q1 args} {
         1383  +  set r2 [strip_rnd [db eval "EXPLAIN $q1"]]
         1384  +  set i 1
         1385  +  foreach q $args {
         1386  +    set tst [subst -nocommands {strip_rnd [db eval "EXPLAIN $q"]}]
         1387  +    uplevel do_test $tn.$i [list $tst] [list $r2]
         1388  +    incr i
         1389  +  }
         1390  +}
         1391  +
         1392  +do_execsql_test 5.0 {
         1393  +  CREATE TABLE t8(a, b);
         1394  +  CREATE TABLE t9(c, d);
         1395  +} {}
         1396  +
         1397  +do_same_test 5.1 {
         1398  +  SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY a;
         1399  +} {
         1400  +  SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t8.a;
         1401  +} {
         1402  +  SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY 1;
         1403  +} {
         1404  +  SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY c;
         1405  +} {
         1406  +  SELECT a, b FROM t8 INTERSECT SELECT c, d FROM t9 ORDER BY t9.c;
         1407  +}
         1408  +
         1409  +do_same_test 5.2 {
         1410  +  SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY a COLLATE NOCASE
         1411  +} {
         1412  +  SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t8.a COLLATE NOCASE
         1413  +} {
         1414  +  SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY 1 COLLATE NOCASE
         1415  +} {
         1416  +  SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY c COLLATE NOCASE
         1417  +} {
         1418  +  SELECT a, b FROM t8 UNION SELECT c, d FROM t9 ORDER BY t9.c COLLATE NOCASE
         1419  +}
         1420  +
         1421  +do_same_test 5.3 {
         1422  +  SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY b, c COLLATE NOCASE
         1423  +} {
         1424  +  SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY 2, 1 COLLATE NOCASE
         1425  +} {
         1426  +  SELECT a, b FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, a COLLATE NOCASE
         1427  +} {
         1428  +  SELECT a, b FROM t8 EXCEPT SELECT * FROM t9 ORDER BY t9.d, c COLLATE NOCASE
         1429  +} {
         1430  +  SELECT * FROM t8 EXCEPT SELECT c, d FROM t9 ORDER BY d, t8.a COLLATE NOCASE
         1431  +}
         1432  +
         1433  +do_catchsql_test 5.4 {
         1434  +  SELECT * FROM t8 UNION SELECT * FROM t9 ORDER BY a+b COLLATE NOCASE
         1435  +} {1 {1st ORDER BY term does not match any column in the result set}}
         1436  +
  1377   1437   
  1378   1438   finish_test