/ Check-in [0d3aef97]
Login

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

Overview
Comment:Make sure that a DISTINCT query with an ORDER BY works correctly even if it uses a descending index. Fix for ticket [c5ea805691bfc4204b1cb9e].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0d3aef97ebddf422b8bdcbc5878970c6129e3f54
User & Date: drh 2014-12-04 21:54:58
Context
2014-12-05
00:17
Fix the autoconf and MSVC makefiles, which have been broken for nearly a month. :-( check-in: 520c2b83 user: drh tags: trunk
2014-12-04
23:42
Import from trunk support for SQLITE_CHECKPOINT_TRUNCATE and fixes for a couple of obscure bugs. check-in: 463ad971 user: drh tags: apple-osx
23:35
Incorporate the SQLITE_CHECKPOINT_TRUNCATE enhancement and a couple of obscure bug fixes from trunk. check-in: 34ffa3b3 user: drh tags: sessions
21:54
Make sure that a DISTINCT query with an ORDER BY works correctly even if it uses a descending index. Fix for ticket [c5ea805691bfc4204b1cb9e]. check-in: 0d3aef97 user: drh tags: trunk
20:24
Performance enhancement for single-table queries with many OR-connected WHERE clause terms and multiple indexes with the same left-most columns. check-in: 1461d543 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4825   4825     ** if the select-list is the same as the ORDER BY list, then this query
  4826   4826     ** can be rewritten as a GROUP BY. In other words, this:
  4827   4827     **
  4828   4828     **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  4829   4829     **
  4830   4830     ** is transformed to:
  4831   4831     **
  4832         -  **     SELECT xyz FROM ... GROUP BY xyz
         4832  +  **     SELECT xyz FROM ... GROUP BY xyz ORDER BY xyz
  4833   4833     **
  4834   4834     ** The second form is preferred as a single index (or temp-table) may be 
  4835   4835     ** used for both the ORDER BY and DISTINCT processing. As originally 
  4836   4836     ** written the query must use a temp-table for at least one of the ORDER 
  4837   4837     ** BY and DISTINCT, and an index or separate temp-table for the other.
  4838   4838     */
  4839   4839     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
  4840   4840      && sqlite3ExprListCompare(sSort.pOrderBy, p->pEList, -1)==0
  4841   4841     ){
  4842   4842       p->selFlags &= ~SF_Distinct;
  4843   4843       p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  4844   4844       pGroupBy = p->pGroupBy;
  4845         -    sSort.pOrderBy = 0;
  4846   4845       /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
  4847   4846       ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
  4848   4847       ** original setting of the SF_Distinct flag, not the current setting */
  4849   4848       assert( sDistinct.isTnct );
  4850   4849     }
  4851   4850   
  4852   4851     /* If there is an ORDER BY clause, then this sorting

Changes to test/distinct.test.

   217    217       SELECT DISTINCT
   218    218         CASE a WHEN 1 THEN x'0000000000'
   219    219                WHEN 2 THEN zeroblob(5)
   220    220                ELSE 'xyzzy' END
   221    221         FROM t1;
   222    222     SELECT quote(x) FROM t2 ORDER BY 1;
   223    223   } {'xyzzy' X'0000000000'}
          224  +
          225  +#----------------------------------------------------------------------------
          226  +# Ticket [c5ea805691bfc4204b1cb9e9aa0103bd48bc7d34] (2014-12-04)
          227  +# Make sure that DISTINCT works together with ORDER BY and descending
          228  +# indexes.
          229  +#
          230  +do_execsql_test 5.1 {
          231  +  DROP TABLE IF EXISTS t1;
          232  +  CREATE TABLE t1(x);
          233  +  INSERT INTO t1(x) VALUES(3),(1),(5),(2),(6),(4),(5),(1),(3);
          234  +  CREATE INDEX t1x ON t1(x DESC);
          235  +  SELECT DISTINCT x FROM t1 ORDER BY x ASC;
          236  +} {1 2 3 4 5 6}
          237  +do_execsql_test 5.2 {
          238  +  SELECT DISTINCT x FROM t1 ORDER BY x DESC;
          239  +} {6 5 4 3 2 1}
          240  +do_execsql_test 5.3 {
          241  +  SELECT DISTINCT x FROM t1 ORDER BY x;
          242  +} {1 2 3 4 5 6}
          243  +do_execsql_test 5.4 {
          244  +  DROP INDEX t1x;
          245  +  CREATE INDEX t1x ON t1(x ASC);
          246  +  SELECT DISTINCT x FROM t1 ORDER BY x ASC;
          247  +} {1 2 3 4 5 6}
          248  +do_execsql_test 5.5 {
          249  +  SELECT DISTINCT x FROM t1 ORDER BY x DESC;
          250  +} {6 5 4 3 2 1}
          251  +do_execsql_test 5.6 {
          252  +  SELECT DISTINCT x FROM t1 ORDER BY x;
          253  +} {1 2 3 4 5 6}
   224    254   
   225    255   finish_test