test/orderby5.test

   1 # 2013-06-14
   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 SQLite library.  The
  12 # focus of this file is testing that the optimizations that disable
  13 # ORDER BY clauses work correctly
  14 #
  15
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set ::testprefix orderby5
  20
  21 # Generate test data for a join.  Verify that the join gets the
  22 # correct answer.
  23 #
  24 do_execsql_test 1.1 {
  25   CREATE TABLE t1(a,b,c);
  26   CREATE INDEX t1bc ON t1(b,c);
  27
  28   EXPLAIN QUERY PLAN
  29   SELECT DISTINCT a, b, c FROM t1 WHERE a=0;
  30 } {~/B-TREE/}
  31 do_execsql_test 1.2.1 {
  32   EXPLAIN QUERY PLAN
  33   SELECT DISTINCT a, c, b FROM t1 WHERE a=0;
  34 } {~/B-TREE/}
  35 do_execsql_test 1.2.2 {
  36   EXPLAIN QUERY PLAN
  37   SELECT DISTINCT a, c, b FROM t1 WHERE a='xyz' COLLATE nocase;
  38 } {/B-TREE/}
  39 do_execsql_test 1.2.3 {
  40   EXPLAIN QUERY PLAN
  41   SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz';
  42 } {/B-TREE/}
  43 do_execsql_test 1.2.4 {
  44   EXPLAIN QUERY PLAN
  45   SELECT DISTINCT a COLLATE nocase, c, b FROM t1 WHERE a='xyz' COLLATE nocase;
  46 } {~/B-TREE/}
  47 do_execsql_test 1.3 {
  48   EXPLAIN QUERY PLAN
  49   SELECT DISTINCT b, a, c FROM t1 WHERE a=0;
  50 } {~/B-TREE/}
  51 do_execsql_test 1.4 {
  52   EXPLAIN QUERY PLAN
  53   SELECT DISTINCT b, c, a FROM t1 WHERE a=0;
  54 } {~/B-TREE/}
  55 do_execsql_test 1.5 {
  56   EXPLAIN QUERY PLAN
  57   SELECT DISTINCT c, a, b FROM t1 WHERE a=0;
  58 } {~/B-TREE/}
  59 do_execsql_test 1.6 {
  60   EXPLAIN QUERY PLAN
  61   SELECT DISTINCT c, b, a FROM t1 WHERE a=0;
  62 } {~/B-TREE/}
  63 do_execsql_test 1.7 {
  64   EXPLAIN QUERY PLAN
  65   SELECT DISTINCT c, b, a FROM t1 WHERE +a=0;
  66 } {/B-TREE/}
  67
  68 # In some cases, it is faster to do repeated index lookups than it is to
  69 # sort.  But in other cases, it is faster to sort than to do repeated index
  70 # lookups.
  71 #
  72 do_execsql_test 2.1a {
  73   CREATE TABLE t2(a,b,c);
  74   CREATE INDEX t2bc ON t2(b,c);
  75   ANALYZE;
  76   INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');
  77   INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');
  78   ANALYZE sqlite_master;
  79
  80   EXPLAIN QUERY PLAN
  81   SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
  82 } {~/B-TREE/}
  83
  84 do_execsql_test 2.1b {
  85   EXPLAIN QUERY PLAN
  86   SELECT * FROM t1 WHERE likelihood(a=0, 0.03) ORDER BY a, b, c;
  87 } {/B-TREE/}
  88
  89 do_execsql_test 2.2 {
  90   EXPLAIN QUERY PLAN
  91   SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
  92 } {/B-TREE/}
  93 do_execsql_test 2.3 {
  94   EXPLAIN QUERY PLAN
  95   SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c;
  96 } {~/B-TREE/}
  97 do_execsql_test 2.4 {
  98   EXPLAIN QUERY PLAN
  99   SELECT * FROM t1 WHERE a=0 ORDER BY b, c, a;
 100 } {~/B-TREE/}
 101 do_execsql_test 2.5 {
 102   EXPLAIN QUERY PLAN
 103   SELECT * FROM t1 WHERE a=0 ORDER BY a, c, b;
 104 } {/B-TREE/}
 105 do_execsql_test 2.6 {
 106   EXPLAIN QUERY PLAN
 107   SELECT * FROM t1 WHERE a=0 ORDER BY c, a, b;
 108 } {/B-TREE/}
 109 do_execsql_test 2.7 {
 110   EXPLAIN QUERY PLAN
 111   SELECT * FROM t1 WHERE a=0 ORDER BY c, b, a;
 112 } {/B-TREE/}
 113
 114
 115 do_execsql_test 3.0 {
 116   CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f);
 117   CREATE INDEX t3bcde ON t3(b, c, d, e);
 118   EXPLAIN QUERY PLAN
 119   SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
 120 } {~/B-TREE/}
 121 do_execsql_test 3.1 {
 122   DROP TABLE t3;
 123   CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c, d, e, f) WITHOUT rowid;
 124   CREATE INDEX t3bcde ON t3(b, c, d, e);
 125   EXPLAIN QUERY PLAN
 126   SELECT a FROM t3 WHERE b=2 AND c=3 ORDER BY d DESC, e DESC, b, c, a DESC;
 127 } {~/B-TREE/}
 128
 129 #-------------------------------------------------------------------------
 130 do_execsql_test 4.1.0 {
 131   CREATE TABLE t4(b COLLATE nocase);
 132   INSERT INTO t4 VALUES('abc');
 133   INSERT INTO t4 VALUES('ABC');
 134   INSERT INTO t4 VALUES('aBC');
 135 }
 136 do_execsql_test 4.1.1 {
 137   SELECT * FROM t4 ORDER BY b COLLATE binary
 138 } {ABC aBC abc}
 139 do_execsql_test 4.1.2 {
 140   SELECT * FROM t4 WHERE b='abc' ORDER BY b COLLATE binary
 141 } {ABC aBC abc}
 142
 143 do_execsql_test 4.2.1 {
 144   CREATE TABLE Records(typeID INTEGER, key TEXT COLLATE nocase, value TEXT);
 145   CREATE INDEX RecordsIndex ON Records(typeID, key, value);
 146 }
 147 do_execsql_test 4.2.2 {
 148   explain query plan
 149   SELECT typeID, key, value FROM Records
 150   WHERE typeID = 2 AND key = 'x'
 151   ORDER BY key, value;
 152 } {~/TEMP B-TREE/}
 153 do_execsql_test 4.2.3 {
 154   explain query plan
 155   SELECT typeID, key, value FROM Records
 156   WHERE typeID = 2 AND (key = 'x' COLLATE binary)
 157   ORDER BY key, value;
 158 } {~/TEMP B-TREE/}
 159 do_execsql_test 4.2.4 {
 160   explain query plan
 161   SELECT typeID, key, value FROM Records
 162   WHERE typeID = 2
 163   ORDER BY key, value;
 164 } {~/TEMP B-TREE/}
 165
 166 db collate hello [list string match]
 167 do_execsql_test 4.3.1 {
 168   CREATE TABLE t5(a INTEGER PRIMARY KEY, b COLLATE hello, c, d);
 169 }
 170 db close
 171 sqlite3 db test.db
 172 do_catchsql_test 4.3.2 {
 173   SELECT a FROM t5 WHERE b='def' ORDER BY b;
 174 } {1 {no such collation sequence: hello}}
 175
 176 # 2020-02-13 ticket 41c1456a6e61c0e7
 177 do_execsql_test 4.4.0 {
 178   DROP TABLE t1;
 179   CREATE TABLE t1(a);
 180   DROP TABLE t2;
 181   CREATE TABLE t2(b INTEGER PRIMARY KEY, c INT);
 182   SELECT DISTINCT *
 183     FROM t1 LEFT JOIN t2 ON b=c AND b=(SELECT a FROM t1)
 184    WHERE c>10;
 185 } {}
 186
 187 finish_test