/ Check-in [ce853a75]
Login

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

Overview
Comment:Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Fix for ticket [6709574d2a8d8].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ce853a75068073d6acc8bec0819505a22c4c7e69
User & Date: drh 2013-05-08 16:57:48
Context
2013-05-08
17:06
Fix a couple of harmless compiler warnings in the fts3_tokenize virtual table. check-in: 1fa8c457 user: drh tags: trunk
16:57
Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Fix for ticket [6709574d2a8d8]. check-in: ce853a75 user: drh tags: trunk
14:20
Fix the wholenumber virtual table so that it returns higher costs for unconstrained usage. Cherrypick from [ceff8955020cd13]. check-in: 7227b615 user: drh tags: trunk
2013-05-07
17:49
Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Candidate fix for ticket [6709574d2a8d8]. Closed-Leaf check-in: fc3630cd user: drh tags: tkt-6709574
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3256   3256         pParse->checkSchema = 1;
  3257   3257         return SQLITE_ERROR;
  3258   3258       }
  3259   3259       pFrom->pIndex = pIdx;
  3260   3260     }
  3261   3261     return SQLITE_OK;
  3262   3262   }
         3263  +/*
         3264  +** Detect compound SELECT statements that use an ORDER BY clause with 
         3265  +** an alternative collating sequence.
         3266  +**
         3267  +**    SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ...
         3268  +**
         3269  +** These are rewritten as a subquery:
         3270  +**
         3271  +**    SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2)
         3272  +**     ORDER BY ... COLLATE ...
         3273  +**
         3274  +** This transformation is necessary because the multiSelectOrderBy() routine
         3275  +** above that generates the code for a compound SELECT with an ORDER BY clause
         3276  +** uses a merge algorithm that requires the same collating sequence on the
         3277  +** result columns as on the ORDER BY clause.  See ticket
         3278  +** http://www.sqlite.org/src/info/6709574d2a
         3279  +**
         3280  +** This transformation is only needed for EXCEPT, INTERSECT, and UNION.
         3281  +** The UNION ALL operator works fine with multiSelectOrderBy() even when
         3282  +** there are COLLATE terms in the ORDER BY.
         3283  +*/
         3284  +static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){
         3285  +  int i;
         3286  +  Select *pNew;
         3287  +  Select *pX;
         3288  +  sqlite3 *db;
         3289  +  struct ExprList_item *a;
         3290  +  SrcList *pNewSrc;
         3291  +  Parse *pParse;
         3292  +  Token dummy;
         3293  +
         3294  +  if( p->pPrior==0 ) return WRC_Continue;
         3295  +  if( p->pOrderBy==0 ) return WRC_Continue;
         3296  +  for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){}
         3297  +  if( pX==0 ) return WRC_Continue;
         3298  +  a = p->pOrderBy->a;
         3299  +  for(i=p->pOrderBy->nExpr-1; i>=0; i--){
         3300  +    if( a[i].pExpr->flags & EP_Collate ) break;
         3301  +  }
         3302  +  if( i<0 ) return WRC_Continue;
         3303  +
         3304  +  /* If we reach this point, that means the transformation is required. */
         3305  +
         3306  +  pParse = pWalker->pParse;
         3307  +  db = pParse->db;
         3308  +  pNew = sqlite3DbMallocZero(db, sizeof(*pNew) );
         3309  +  if( pNew==0 ) return WRC_Abort;
         3310  +  memset(&dummy, 0, sizeof(dummy));
         3311  +  pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0,0);
         3312  +  if( pNewSrc==0 ) return WRC_Abort;
         3313  +  *pNew = *p;
         3314  +  p->pSrc = pNewSrc;
         3315  +  p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ALL, 0));
         3316  +  p->op = TK_SELECT;
         3317  +  p->pWhere = 0;
         3318  +  pNew->pGroupBy = 0;
         3319  +  pNew->pHaving = 0;
         3320  +  pNew->pOrderBy = 0;
         3321  +  p->pPrior = 0;
         3322  +  pNew->pLimit = 0;
         3323  +  pNew->pOffset = 0;
         3324  +  return WRC_Continue;
         3325  +}
  3263   3326   
  3264   3327   /*
  3265   3328   ** This routine is a Walker callback for "expanding" a SELECT statement.
  3266   3329   ** "Expanding" means to do the following:
  3267   3330   **
  3268   3331   **    (1)  Make sure VDBE cursor numbers have been assigned to every
  3269   3332   **         element of the FROM clause.
................................................................................
  3573   3636   ** If anything goes wrong, an error message is written into pParse.
  3574   3637   ** The calling function can detect the problem by looking at pParse->nErr
  3575   3638   ** and/or pParse->db->mallocFailed.
  3576   3639   */
  3577   3640   static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){
  3578   3641     Walker w;
  3579   3642     memset(&w, 0, sizeof(w));
  3580         -  w.xSelectCallback = selectExpander;
         3643  +  w.xSelectCallback = convertCompoundSelectToSubquery;
  3581   3644     w.xExprCallback = exprWalkNoop;
  3582   3645     w.pParse = pParse;
         3646  +  sqlite3WalkSelect(&w, pSelect);
         3647  +  w.xSelectCallback = selectExpander;
  3583   3648     sqlite3WalkSelect(&w, pSelect);
  3584   3649   }
  3585   3650   
  3586   3651   
  3587   3652   #ifndef SQLITE_OMIT_SUBQUERY
  3588   3653   /*
  3589   3654   ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()

Changes to test/selectA.test.

   277    277       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   278    278       ORDER BY b,a,c
   279    279     }
   280    280   } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
   281    281   do_test selectA-2.35 {
   282    282     execsql {
   283    283       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   284         -    ORDER BY b COLLATE NOCASE,a,c
          284  +    ORDER BY y COLLATE NOCASE,x,z
   285    285     }
   286    286   } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
   287    287   do_test selectA-2.36 {
   288    288     execsql {
   289    289       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   290         -    ORDER BY b COLLATE NOCASE DESC,a,c
          290  +    ORDER BY y COLLATE NOCASE DESC,x,z
   291    291     }
   292    292   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
   293    293   do_test selectA-2.37 {
   294    294     execsql {
   295    295       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   296    296       ORDER BY c,b,a
   297    297     }
................................................................................
   307    307       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   308    308       ORDER BY c DESC,a,b
   309    309     }
   310    310   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
   311    311   do_test selectA-2.40 {
   312    312     execsql {
   313    313       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   314         -    ORDER BY c COLLATE BINARY DESC,a,b
          314  +    ORDER BY z COLLATE BINARY DESC,x,y
   315    315     }
   316    316   } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
   317    317   do_test selectA-2.41 {
   318    318     execsql {
   319    319       SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
   320    320       ORDER BY a,b,c
   321    321     }
................................................................................
   598    598       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
   599    599       ORDER BY b,a,c
   600    600     }
   601    601   } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
   602    602   do_test selectA-2.86 {
   603    603     execsql {
   604    604       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
   605         -    ORDER BY b COLLATE NOCASE,a,c
          605  +    ORDER BY y COLLATE NOCASE,x,z
   606    606     }
   607    607   } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
   608    608   do_test selectA-2.87 {
   609    609     execsql {
   610    610       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
   611    611       ORDER BY y COLLATE NOCASE DESC,x,z
   612    612     }
................................................................................
   628    628       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
   629    629       ORDER BY c DESC,a,b
   630    630     }
   631    631   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
   632    632   do_test selectA-2.91 {
   633    633     execsql {
   634    634       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
   635         -    ORDER BY c COLLATE BINARY DESC,a,b
          635  +    ORDER BY z COLLATE BINARY DESC,x,y
   636    636     }
   637    637   } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
   638    638   do_test selectA-2.92 {
   639    639     execsql {
   640    640       SELECT x,y,z FROM t2
   641    641       INTERSECT SELECT a,b,c FROM t3
   642    642       EXCEPT SELECT c,b,a FROM t1
................................................................................
   889    889       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   890    890       ORDER BY b,a,c
   891    891     }
   892    892   } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
   893    893   do_test selectA-3.35 {
   894    894     execsql {
   895    895       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   896         -    ORDER BY b COLLATE NOCASE,a,c
          896  +    ORDER BY y COLLATE NOCASE,x,z
   897    897     }
   898    898   } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
   899    899   do_test selectA-3.36 {
   900    900     execsql {
   901    901       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   902         -    ORDER BY b COLLATE NOCASE DESC,a,c
          902  +    ORDER BY y COLLATE NOCASE DESC,x,z
   903    903     }
   904    904   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
   905    905   do_test selectA-3.37 {
   906    906     execsql {
   907    907       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   908    908       ORDER BY c,b,a
   909    909     }
................................................................................
   919    919       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   920    920       ORDER BY c DESC,a,b
   921    921     }
   922    922   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
   923    923   do_test selectA-3.40 {
   924    924     execsql {
   925    925       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
   926         -    ORDER BY c COLLATE BINARY DESC,a,b
          926  +    ORDER BY z COLLATE BINARY DESC,x,y
   927    927     }
   928    928   } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
   929    929   do_test selectA-3.41 {
   930    930     execsql {
   931    931       SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
   932    932       ORDER BY a,b,c
   933    933     }
................................................................................
  1210   1210       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
  1211   1211       ORDER BY b,a,c
  1212   1212     }
  1213   1213   } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
  1214   1214   do_test selectA-3.86 {
  1215   1215     execsql {
  1216   1216       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
  1217         -    ORDER BY b COLLATE NOCASE,a,c
         1217  +    ORDER BY y COLLATE NOCASE,x,z
  1218   1218     }
  1219   1219   } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
  1220   1220   do_test selectA-3.87 {
  1221   1221     execsql {
  1222   1222       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
  1223   1223       ORDER BY y COLLATE NOCASE DESC,x,z
  1224   1224     }
................................................................................
  1240   1240       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
  1241   1241       ORDER BY c DESC,a,b
  1242   1242     }
  1243   1243   } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
  1244   1244   do_test selectA-3.91 {
  1245   1245     execsql {
  1246   1246       SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
  1247         -    ORDER BY c COLLATE BINARY DESC,a,b
         1247  +    ORDER BY z COLLATE BINARY DESC,x,y
  1248   1248     }
  1249   1249   } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
  1250   1250   do_test selectA-3.92 {
  1251   1251     execsql {
  1252   1252       SELECT x,y,z FROM t2
  1253   1253       INTERSECT SELECT a,b,c FROM t3
  1254   1254       EXCEPT SELECT c,b,a FROM t1

Added test/selectE.test.

            1  +# 2013-05-07
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for compound SELECT statements
           12  +# that have ORDER BY clauses with collating sequences that differ
           13  +# from the collating sequence used for comparison in the compound.
           14  +# 
           15  +# Ticket 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7:
           16  +# drh added on 2013-05-06 15:21:16:
           17  +#
           18  +# In the code shown below (which is intended to be run from the
           19  +# sqlite3.exe command-line tool) the three SELECT statements should all
           20  +# generate the same answer. But the third one does not. It is as if the
           21  +# COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT
           22  +# operator. Note that the ".print" commands are instructions to the
           23  +# sqlite3.exe shell program to output delimiter lines so that you can more
           24  +# easily tell where the output of one query ends and the next query
           25  +# begins. 
           26  +# 
           27  +#     CREATE TABLE t1(a);
           28  +#     INSERT INTO t1 VALUES('abc'),('def');
           29  +#     CREATE TABLE t2(a);
           30  +#     INSERT INTO t2 VALUES('DEF');
           31  +# 
           32  +#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a;
           33  +#     .print -----
           34  +#     SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2)
           35  +#      ORDER BY a COLLATE nocase;
           36  +#     .print -----
           37  +#     SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase;
           38  +# 
           39  +# Bisecting shows that this problem was introduced in SQLite version 3.6.0
           40  +# by check-in [8bbfa97837a74ef] on 2008-06-15. 
           41  +#
           42  +
           43  +set testdir [file dirname $argv0]
           44  +source $testdir/tester.tcl
           45  +
           46  +do_test selectE-1.0 {
           47  +  db eval {
           48  +    CREATE TABLE t1(a);
           49  +    INSERT INTO t1 VALUES('abc'),('def'),('ghi');
           50  +    CREATE TABLE t2(a);
           51  +    INSERT INTO t2 VALUES('DEF'),('abc');
           52  +    CREATE TABLE t3(a);
           53  +    INSERT INTO t3 VALUES('def'),('jkl');
           54  +
           55  +    SELECT a FROM t1 EXCEPT SELECT a FROM t2
           56  +     ORDER BY a COLLATE nocase;
           57  +  }
           58  +} {def ghi}
           59  +do_test selectE-1.1 {
           60  +  db eval {
           61  +    SELECT a FROM t2 EXCEPT SELECT a FROM t3
           62  +     ORDER BY a COLLATE nocase;
           63  +  }
           64  +} {abc DEF}
           65  +do_test selectE-1.2 {
           66  +  db eval {
           67  +    SELECT a FROM t2 EXCEPT SELECT a FROM t3
           68  +     ORDER BY a COLLATE binary;
           69  +  }
           70  +} {DEF abc}
           71  +do_test selectE-1.3 {
           72  +  db eval {
           73  +    SELECT a FROM t2 EXCEPT SELECT a FROM t3
           74  +     ORDER BY a;
           75  +  }
           76  +} {DEF abc}
           77  +
           78  +do_test selectE-2.1 {
           79  +  db eval {
           80  +    DELETE FROM t2;
           81  +    DELETE FROM t3;
           82  +    INSERT INTO t2 VALUES('ABC'),('def'),('GHI'),('jkl');
           83  +    INSERT INTO t3 SELECT lower(a) FROM t2;
           84  +    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
           85  +     ORDER BY 1
           86  +  }
           87  +} {}
           88  +do_test selectE-2.2 {
           89  +  db eval {
           90  +    SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3
           91  +     ORDER BY 1 COLLATE binary
           92  +  }
           93  +} {}
           94  +
           95  +finish_test