SQLite4
Check-in [57b8ae848c]
Not logged in

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

Overview
Comment:Fix a bug to do with ORDER BY and collation sequences.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-planner
Files: files | file ages | folders
SHA1: 57b8ae848ce492715a6b0dcba61beb0f5484167d
User & Date: dan 2013-07-19 19:20:47
Context
2013-07-19
20:03
Update a couple of test case files. check-in: 3b9ae7c6db user: dan tags: nextgen-query-planner
19:20
Fix a bug to do with ORDER BY and collation sequences. check-in: 57b8ae848c user: dan tags: nextgen-query-planner
18:33
Fix some problems to do with optimizing ORDER BY queries. check-in: cc7bc86da5 user: dan tags: nextgen-query-planner
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

5195
5196
5197
5198
5199
5200
5201
5202
5203
5204
5205
5206
5207
5208
5209
          if( pOBExpr->op!=TK_COLUMN ) continue;
          if( pOBExpr->iTable!=iCur ) continue;
          if( pOBExpr->iColumn!=iColumn ) continue;
          if( iColumn>=0 ){
            const char *zIdxColl;
            pColl = sqlite4ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            zIdxColl = idxColumnCollation(pPk, pIndex, j);
            if( sqlite4_stricmp(pColl->zName, zIdxColl)!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch ){
          obSat |= MASKBIT(i);







|







5195
5196
5197
5198
5199
5200
5201
5202
5203
5204
5205
5206
5207
5208
5209
          if( pOBExpr->op!=TK_COLUMN ) continue;
          if( pOBExpr->iTable!=iCur ) continue;
          if( pOBExpr->iColumn!=iColumn ) continue;
          if( iColumn>=0 ){
            const char *zIdxColl;
            pColl = sqlite4ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            zIdxColl = idxColumnCollation(pIndex, pPk, j);
            if( sqlite4_stricmp(pColl->zName, zIdxColl)!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch ){
          obSat |= MASKBIT(i);

Changes to test/analyze4.test.

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;
    ANALYZE;
  }

  # Should choose the t1a index since it is more specific than t1b.
  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}

# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;







|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;
    ANALYZE;
  }

  # Should choose the t1a index since it is more specific than t1b.
  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;

Changes to test/simple.test.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
....
1654
1655
1656
1657
1658
1659
1660










1661
1662
1663
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple


do_execsql_test 1.0 { 
  PRAGMA table_info = sqlite_master
} {
    0 type text        0 {} 0 
    1 name text        0 {} 0 
    2 tbl_name text    0 {} 0 
    3 rootpage integer 0 {} 0 
................................................................................
do_execsql_test 88.1 {
  CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT);
  CREATE UNIQUE INDEX t8i ON t8(b);
}
do_eqp_test 88.2 {
  SELECT * FROM t8 x ORDER BY x.b, x.a, x.b||x.a
} {0 0 0 {SCAN TABLE t8 AS x USING INDEX t8i}}











finish_test








<







 







>
>
>
>
>
>
>
>
>
>



10
11
12
13
14
15
16

17
18
19
20
21
22
23
....
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple


do_execsql_test 1.0 { 
  PRAGMA table_info = sqlite_master
} {
    0 type text        0 {} 0 
    1 name text        0 {} 0 
    2 tbl_name text    0 {} 0 
    3 rootpage integer 0 {} 0 
................................................................................
do_execsql_test 88.1 {
  CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT);
  CREATE UNIQUE INDEX t8i ON t8(b);
}
do_eqp_test 88.2 {
  SELECT * FROM t8 x ORDER BY x.b, x.a, x.b||x.a
} {0 0 0 {SCAN TABLE t8 AS x USING INDEX t8i}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 89.1 {
  CREATE TABLE t1(a COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
  SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}

finish_test

Changes to test/where7.test.

23328
23329
23330
23331
23332
23333
23334
23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
  );
  CREATE INDEX t302_c3 on t302(c3);
  CREATE INDEX t302_c8_c3 on t302(c8, c3);
  CREATE INDEX t302_c5 on t302(c5);
  
  EXPLAIN QUERY PLAN
  SELECT t302.c1 
    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING INDEX t301_c4 (c4=?) (~5 rows)} 
  0 0 1 {SEARCH TABLE t301 USING PRIMARY KEY (c8=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test







|






|
|
|




23328
23329
23330
23331
23332
23333
23334
23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
  );
  CREATE INDEX t302_c3 on t302(c3);
  CREATE INDEX t302_c8_c3 on t302(c8, c3);
  CREATE INDEX t302_c5 on t302(c5);
  
  EXPLAIN QUERY PLAN
  SELECT t302.c1 
    FROM t302 JOIN t301 ON t302.c8 = +t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING INDEX t301_c4 (c4=?)}
  0 0 1 {SEARCH TABLE t301 USING INDEX t301 (c8=?)}
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test