SQLite

Check-in [0d3aef97eb]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0d3aef97ebddf422b8bdcbc5878970c6129e3f54
User & Date: drh 2014-12-04 21:54:58.307
Context
2014-12-05
00:17
Fix the autoconf and MSVC makefiles, which have been broken for nearly a month. :-( (check-in: 520c2b838d 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: 463ad971d2 user: drh tags: apple-osx)
23:35
Incorporate the SQLITE_CHECKPOINT_TRUNCATE enhancement and a couple of obscure bug fixes from trunk. (check-in: 34ffa3b3c0 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: 0d3aef97eb 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: 1461d543ac user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
4835
4836
4837
4838
4839
4840
4841
4842
4843
4844
4845
4846
4847
4848
4849
4850
4851
4852

  ** if the select-list is the same as the ORDER BY list, then this query
  ** can be rewritten as a GROUP BY. In other words, this:
  **
  **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  **
  ** is transformed to:
  **
  **     SELECT xyz FROM ... GROUP BY xyz
  **
  ** The second form is preferred as a single index (or temp-table) may be 
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, p->pEList, -1)==0
  ){
    p->selFlags &= ~SF_Distinct;
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
    pGroupBy = p->pGroupBy;
    sSort.pOrderBy = 0;
    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
    ** original setting of the SF_Distinct flag, not the current setting */
    assert( sDistinct.isTnct );
  }

  /* If there is an ORDER BY clause, then this sorting








|












<








4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
4835
4836
4837
4838
4839
4840
4841
4842
4843
4844

4845
4846
4847
4848
4849
4850
4851

  ** if the select-list is the same as the ORDER BY list, then this query
  ** can be rewritten as a GROUP BY. In other words, this:
  **
  **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
  **
  ** is transformed to:
  **
  **     SELECT xyz FROM ... GROUP BY xyz ORDER BY xyz
  **
  ** The second form is preferred as a single index (or temp-table) may be 
  ** used for both the ORDER BY and DISTINCT processing. As originally 
  ** written the query must use a temp-table for at least one of the ORDER 
  ** BY and DISTINCT, and an index or separate temp-table for the other.
  */
  if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
   && sqlite3ExprListCompare(sSort.pOrderBy, p->pEList, -1)==0
  ){
    p->selFlags &= ~SF_Distinct;
    p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
    pGroupBy = p->pGroupBy;

    /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
    ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
    ** original setting of the SF_Distinct flag, not the current setting */
    assert( sDistinct.isTnct );
  }

  /* If there is an ORDER BY clause, then this sorting
︙︙
Changes to test/distinct.test.
︙︙
217
218
219
220
221
222
223






























224
225

    SELECT DISTINCT
      CASE a WHEN 1 THEN x'0000000000'
             WHEN 2 THEN zeroblob(5)
             ELSE 'xyzzy' END
      FROM t1;
  SELECT quote(x) FROM t2 ORDER BY 1;
} {'xyzzy' X'0000000000'}































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255

    SELECT DISTINCT
      CASE a WHEN 1 THEN x'0000000000'
             WHEN 2 THEN zeroblob(5)
             ELSE 'xyzzy' END
      FROM t1;
  SELECT quote(x) FROM t2 ORDER BY 1;
} {'xyzzy' X'0000000000'}

#----------------------------------------------------------------------------
# Ticket [c5ea805691bfc4204b1cb9e9aa0103bd48bc7d34] (2014-12-04)
# Make sure that DISTINCT works together with ORDER BY and descending
# indexes.
#
do_execsql_test 5.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1(x) VALUES(3),(1),(5),(2),(6),(4),(5),(1),(3);
  CREATE INDEX t1x ON t1(x DESC);
  SELECT DISTINCT x FROM t1 ORDER BY x ASC;
} {1 2 3 4 5 6}
do_execsql_test 5.2 {
  SELECT DISTINCT x FROM t1 ORDER BY x DESC;
} {6 5 4 3 2 1}
do_execsql_test 5.3 {
  SELECT DISTINCT x FROM t1 ORDER BY x;
} {1 2 3 4 5 6}
do_execsql_test 5.4 {
  DROP INDEX t1x;
  CREATE INDEX t1x ON t1(x ASC);
  SELECT DISTINCT x FROM t1 ORDER BY x ASC;
} {1 2 3 4 5 6}
do_execsql_test 5.5 {
  SELECT DISTINCT x FROM t1 ORDER BY x DESC;
} {6 5 4 3 2 1}
do_execsql_test 5.6 {
  SELECT DISTINCT x FROM t1 ORDER BY x;
} {1 2 3 4 5 6}

finish_test