SQLite

Check-in [0b452734fa]
Login

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

Overview
Comment:Enhance tests for ticket [4dd95f6943].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0b452734faa0839c817f040322e7733e423bfce2
User & Date: dan 2013-03-13 07:02:04.083
Context
2013-03-18
11:39
Version 3.7.16 (check-in: 66d5f2b767 user: drh tags: trunk, release, version-3.7.16)
2013-03-14
18:34
Use mmap() to read from the database file in rollback mode. This branch is unix only for now. (check-in: 6f21d9cbf5 user: dan tags: experimental-mmap)
2013-03-13
20:52
Merge updates from trunk. (check-in: d63fa039a0 user: mistachkin tags: toTypeFuncs)
07:02
Enhance tests for ticket [4dd95f6943]. (check-in: 0b452734fa user: dan tags: trunk)
00:13
Fix the ORDER BY with IN constraint logic so that it works with all combinations of DESC on the ORDER BY clause, on the RHS of the IN operator, and in the index used by ORDER BY and IN. Fix for ticket [4dd95f6943fbd18]. (check-in: 839aa91faf user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/tkt-4dd95f6943.test.
︙︙
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

77
78
79

80
81
82

83
84
85




































86
87
88
89
90
91
92
93
94
95
96










97
98
99
100

101

  INSERT INTO t2 VALUES (3, 3), (3, 4), (3, 2), (3, 1), (3, 5), (3, 6);
  INSERT INTO t2 VALUES (2, 3), (2, 4), (2, 2), (2, 1), (2, 5), (2, 6);
  INSERT INTO t2 VALUES (4, 3), (4, 4), (4, 2), (4, 1), (4, 5), (4, 6);
  INSERT INTO t2 VALUES (6, 3), (6, 4), (6, 2), (6, 1), (6, 5), (6, 6);

  CREATE TABLE t3(a, b);
  INSERT INTO t3 VALUES (2, 2), (4, 4), (5, 5);
  CREATE INDEX t3i1 ON t3(a ASC);
  CREATE INDEX t3i2 ON t3(b DESC);
}

foreach {tn1 idx} {
  1 { CREATE INDEX i1 ON t2(x ASC,  y ASC) }
  2 { CREATE INDEX i1 ON t2(x ASC,  y DESC) }
  3 { CREATE INDEX i1 ON t2(x DESC, y ASC) }
  4 { CREATE INDEX i1 ON t2(x DESC, y DESC) }

  5 { CREATE INDEX i1 ON t2(y ASC,  x ASC) }
  6 { CREATE INDEX i1 ON t2(y ASC,  x DESC) }
  7 { CREATE INDEX i1 ON t2(y DESC, x ASC) }
  8 { CREATE INDEX i1 ON t2(y DESC, x DESC) }
} {
  do_execsql_test 2.$tn1.1 { DROP INDEX IF EXISTS i1; }
  do_execsql_test 2.$tn1.2 $idx

  foreach {tn2 inexpr} {
    1  "(2, 4, 5)"
    2  "(SELECT a FROM t3)"
    3  "(SELECT b FROM t3)"
  } {
    do_execsql_test 2.$tn1.3 "
      SELECT x, y FROM t2 WHERE x = 1 AND y IN $inexpr ORDER BY x ASC, y ASC;
    " {1 2  1 4  1 5}

    do_execsql_test 2.$tn1.4 "
      SELECT x, y FROM t2 WHERE x = 2 AND y IN $inexpr ORDER BY x ASC, y DESC;
    " {2 5  2 4  2 2}

    do_execsql_test 2.$tn1.5 "
      SELECT x, y FROM t2 WHERE x = 3 AND y IN $inexpr ORDER BY x DESC, y ASC;
    " {3 2  3 4  3 5}

    do_execsql_test 2.$tn1.6 "
      SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
    " {4 5  4 4  4 2}




































  }
}

do_execsql_test 3.0 {
  CREATE TABLE t7(x);
  INSERT INTO t7 VALUES (1), (2), (3);
  CREATE INDEX i7 ON t7(x);

  CREATE TABLE t8(y);
  INSERT INTO t8 VALUES (1), (2), (3);











  CREATE UNIQUE INDEX i8 ON t8(y DESC);
  SELECT x FROM t7 WHERE x IN (SELECT y FROM t8) ORDER BY x ASC;
} {1 2 3}


finish_test








|
|

















|
|
|

|


>
|


>
|


>
|


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










|
>
>
>
>
>
>
>
>
>
>
|
|
|
|
>


44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

  INSERT INTO t2 VALUES (3, 3), (3, 4), (3, 2), (3, 1), (3, 5), (3, 6);
  INSERT INTO t2 VALUES (2, 3), (2, 4), (2, 2), (2, 1), (2, 5), (2, 6);
  INSERT INTO t2 VALUES (4, 3), (4, 4), (4, 2), (4, 1), (4, 5), (4, 6);
  INSERT INTO t2 VALUES (6, 3), (6, 4), (6, 2), (6, 1), (6, 5), (6, 6);

  CREATE TABLE t3(a, b);
  INSERT INTO t3 VALUES (2, 2), (4, 4), (5, 5);
  CREATE UNIQUE INDEX t3i1 ON t3(a ASC);
  CREATE UNIQUE INDEX t3i2 ON t3(b DESC);
}

foreach {tn1 idx} {
  1 { CREATE INDEX i1 ON t2(x ASC,  y ASC) }
  2 { CREATE INDEX i1 ON t2(x ASC,  y DESC) }
  3 { CREATE INDEX i1 ON t2(x DESC, y ASC) }
  4 { CREATE INDEX i1 ON t2(x DESC, y DESC) }

  5 { CREATE INDEX i1 ON t2(y ASC,  x ASC) }
  6 { CREATE INDEX i1 ON t2(y ASC,  x DESC) }
  7 { CREATE INDEX i1 ON t2(y DESC, x ASC) }
  8 { CREATE INDEX i1 ON t2(y DESC, x DESC) }
} {
  do_execsql_test 2.$tn1.1 { DROP INDEX IF EXISTS i1; }
  do_execsql_test 2.$tn1.2 $idx

  foreach {tn2 inexpr} {
    3  "(2, 4, 5)"
    4  "(SELECT a FROM t3)"
    5  "(SELECT b FROM t3)"
  } {
    do_execsql_test 2.$tn1.$tn2.1 "
      SELECT x, y FROM t2 WHERE x = 1 AND y IN $inexpr ORDER BY x ASC, y ASC;
    " {1 2  1 4  1 5}

    do_execsql_test 2.$tn1.$tn2.2 "
      SELECT x, y FROM t2 WHERE x = 2 AND y IN $inexpr ORDER BY x ASC, y DESC;
    " {2 5  2 4  2 2}

    do_execsql_test 2.$tn1.$tn2.3 "
      SELECT x, y FROM t2 WHERE x = 3 AND y IN $inexpr ORDER BY x DESC, y ASC;
    " {3 2  3 4  3 5}

    do_execsql_test 2.$tn1.$tn2.4 "
      SELECT x, y FROM t2 WHERE x = 4 AND y IN $inexpr ORDER BY x DESC, y DESC;
    " {4 5  4 4  4 2}
    
    do_execsql_test 2.$tn1.$tn2.5 "
      SELECT a, x, y FROM t2, t3 WHERE a = 4 AND x = 1 AND y IN $inexpr 
      ORDER BY a, x ASC, y ASC;
    " {4 1 2  4 1 4  4 1 5}
    do_execsql_test 2.$tn1.$tn2.6 "
      SELECT a, x, y FROM t2, t3 WHERE a = 2 AND x = 1 AND y IN $inexpr 
      ORDER BY x ASC, y ASC;
    " {2 1 2  2 1 4  2 1 5}

    do_execsql_test 2.$tn1.$tn2.7 "
      SELECT a, x, y FROM t2, t3 WHERE a = 4 AND x = 1 AND y IN $inexpr 
      ORDER BY a, x ASC, y DESC;
    " {4 1 5  4 1 4  4 1 2}
    do_execsql_test 2.$tn1.8 "
      SELECT a, x, y FROM t2, t3 WHERE a = 2 AND x = 1 AND y IN $inexpr 
      ORDER BY x ASC, y DESC;
    " {2 1 5  2 1 4  2 1 2}

    do_execsql_test 2.$tn1.$tn2.9 "
      SELECT a, x, y FROM t2, t3 WHERE a = 4 AND x = 1 AND y IN $inexpr 
      ORDER BY a, x DESC, y ASC;
    " {4 1 2  4 1 4  4 1 5}
    do_execsql_test 2.$tn1.10 "
      SELECT a, x, y FROM t2, t3 WHERE a = 2 AND x = 1 AND y IN $inexpr 
      ORDER BY x DESC, y ASC;
    " {2 1 2  2 1 4  2 1 5}

    do_execsql_test 2.$tn1.$tn2.11 "
      SELECT a, x, y FROM t2, t3 WHERE a = 4 AND x = 1 AND y IN $inexpr 
      ORDER BY a, x DESC, y DESC;
    " {4 1 5  4 1 4  4 1 2}
    do_execsql_test 2.$tn1.$tn2.12 "
      SELECT a, x, y FROM t2, t3 WHERE a = 2 AND x = 1 AND y IN $inexpr 
      ORDER BY x DESC, y DESC;
    " {2 1 5  2 1 4  2 1 2}
  }
}

do_execsql_test 3.0 {
  CREATE TABLE t7(x);
  INSERT INTO t7 VALUES (1), (2), (3);
  CREATE INDEX i7 ON t7(x);

  CREATE TABLE t8(y);
  INSERT INTO t8 VALUES (1), (2), (3);
}

foreach {tn idxdir sortdir sortdata} {
  1 ASC  ASC  {1 2 3}
  2 ASC  DESC {3 2 1}
  3 DESC ASC  {1 2 3}
  4 ASC  DESC {3 2 1}
} {

  do_execsql_test 3.$tn "
    DROP INDEX IF EXISTS i8;
    CREATE UNIQUE INDEX i8 ON t8(y $idxdir);
    SELECT x FROM t7 WHERE x IN (SELECT y FROM t8) ORDER BY x $sortdir;
  " $sortdata
}

finish_test