/ Check-in [e53403b6]
Login

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

Overview
Comment:If a unique index covers any prefix of the ORDER BY clause then let it satisfy the ORDER BY clause. (CVS 5301)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e53403b6d742be83c5b130ed6cef4035d081d613
User & Date: drh 2008-06-25 02:47:57
References
2012-04-20
17:41 Ticket [2a562920] Malfunctioning interaction between a multi-term ORDER BY clause and UNIQUE index containing NULL values status still Open with 1 other change artifact: be0c3a72 user: dan
Context
2008-06-25
08:02
Fixed typo to close #1731. (CVS 5302) check-in: 9902be8d user: mihailim tags: trunk
02:47
If a unique index covers any prefix of the ORDER BY clause then let it satisfy the ORDER BY clause. (CVS 5301) check-in: e53403b6 user: drh tags: trunk
02:22
Add new test script for compound select statements. (CVS 5300) check-in: a193b161 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1085
1086
1087
1088
1089
1090
1091



1092
1093
1094
1095
1096
1097
1098
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.309 2008/06/15 02:51:48 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
    if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){
      /* Term j of the ORDER BY clause does not match column i of the index */
      if( i<nEqCol ){
        /* If an index column that is constrained by == fails to match an
        ** ORDER BY term, that is OK.  Just ignore that column of the index
        */
        continue;



      }else{
        /* If an index column fails to match and is not constrained by ==
        ** then the index cannot satisfy the ORDER BY constraint.
        */
        return 0;
      }
    }







|







 







>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.310 2008/06/25 02:47:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
    if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){
      /* Term j of the ORDER BY clause does not match column i of the index */
      if( i<nEqCol ){
        /* If an index column that is constrained by == fails to match an
        ** ORDER BY term, that is OK.  Just ignore that column of the index
        */
        continue;
      }else if( i==pIdx->nColumn ){
        /* Index column i is the rowid.  All other terms match. */
        break;
      }else{
        /* If an index column fails to match and is not constrained by ==
        ** then the index cannot satisfy the ORDER BY constraint.
        */
        return 0;
      }
    }

Changes to test/misc3.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.18 2008/01/19 23:50:26 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.
................................................................................
      CREATE UNIQUE INDEX ex1i1 ON ex1(a);
      EXPLAIN REINDEX;
    }]
    regexp { IsUnique \d+ \d+ \d+ \d+ } $x
  } {1}
  do_test misc3-6.11 {
    set x [execsql {
      EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY a, b DESC
    }]
    set y [regexp { 123456789012 } $x]
    lappend y [regexp { 4.5678 } $x]
    lappend y [regexp { hello } $x]
    lappend y [regexp {,-BINARY} $x]
  } {1 1 1 1}








|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.19 2008/06/25 02:47:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.
................................................................................
      CREATE UNIQUE INDEX ex1i1 ON ex1(a);
      EXPLAIN REINDEX;
    }]
    regexp { IsUnique \d+ \d+ \d+ \d+ } $x
  } {1}
  do_test misc3-6.11 {
    set x [execsql {
      EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
    }]
    set y [regexp { 123456789012 } $x]
    lappend y [regexp { 4.5678 } $x]
    lappend y [regexp { hello } $x]
    lappend y [regexp {,-BINARY} $x]
  } {1 1 1 1}

Changes to test/selectA.test.

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# The focus of this file is testing the compound-SELECT merge
# optimization.  Or, in other words, making sure that all
# possible combinations of UNION, UNION ALL, EXCEPT, and
# INTERSECT work together with an ORDER BY clause (with or w/o
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.1 2008/06/25 02:22:32 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test selectA-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c COLLATE NOCASE);







|







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# The focus of this file is testing the compound-SELECT merge
# optimization.  Or, in other words, making sure that all
# possible combinations of UNION, UNION ALL, EXCEPT, and
# INTERSECT work together with an ORDER BY clause (with or w/o
# explicit sort order and explicit collating secquites) and
# with and without optional LIMIT and OFFSET clauses.
#
# $Id: selectA.test,v 1.2 2008/06/25 02:47:57 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test selectA-1.0 {
  execsql {
    CREATE TABLE t1(a,b,c COLLATE NOCASE);