Overview
| Comment: | A few more test cases for the IS operator. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-is-operator |
| Files: | files | file ages | folders |
| SHA1: | f397c8622ae5a36a71f81d9f2549ca314005ece5 |
| User & Date: | drh 2015-05-14 14:03:21 |
Context
|
2015-05-14
| ||
| 14:32 | The IS operator can now use indexes the same as the == operator. check-in: 3428043cd0 user: drh tags: trunk | |
| 14:03 | A few more test cases for the IS operator. Closed-Leaf check-in: f397c8622a user: drh tags: index-is-operator | |
| 13:41 | More test cases. Remove some invalid testcase() macros. Rearrange some code for improved testability. check-in: b3676377b2 user: drh tags: index-is-operator | |
Changes
Changes to test/transitive1.test.
61
62
63
64
65
66
67
68
69
70
71
72
73
74
..
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
|
ORDER BY +w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-301 {
SELECT *
FROM t301 CROSS JOIN t302
WHERE w=y AND y IS NOT NULL
ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-310 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y>1
ORDER BY +w
} {3 4 3 6 5 6 5 7}
................................................................................
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y BETWEEN 1 AND 4
ORDER BY w DESC;
} {3 4 3 6 1 2 1 3}
# Ticket [c620261b5b5dc] circa 2013-10-28.
# Make sureconstraints are not used with LEFT JOINs.
#
# The next case is from the ticket report. It outputs no rows in 3.8.1
# prior to the bug-fix.
#
do_execsql_test transitive1-400 {
CREATE TABLE t401(a);
CREATE TABLE t402(b);
CREATE TABLE t403(c INTEGER PRIMARY KEY);
INSERT INTO t401 VALUES(1);
INSERT INTO t403 VALUES(1);
SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
} {1-row}
# The following is a script distilled from the XBMC project where the
# bug was originally encountered. The correct answer is a single row
# of output. Before the bug was fixed, zero rows were generated.
#
do_execsql_test transitive1-410 {
CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
|
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
...
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
|
ORDER BY +w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-301 {
SELECT *
FROM t301 CROSS JOIN t302
WHERE w=y AND y IS NOT NULL
ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-302 {
SELECT *
FROM t301 CROSS JOIN t302
WHERE w IS y AND y IS NOT NULL
ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-310 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y>1
ORDER BY +w
} {3 4 3 6 5 6 5 7}
................................................................................
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y BETWEEN 1 AND 4
ORDER BY w DESC;
} {3 4 3 6 1 2 1 3}
# Ticket [c620261b5b5dc] circa 2013-10-28.
# Make sure constraints are not used with LEFT JOINs.
#
# The next case is from the ticket report. It outputs no rows in 3.8.1
# prior to the bug-fix.
#
do_execsql_test transitive1-400 {
CREATE TABLE t401(a);
CREATE TABLE t402(b);
CREATE TABLE t403(c INTEGER PRIMARY KEY);
INSERT INTO t401 VALUES(1);
INSERT INTO t403 VALUES(1);
SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
} {1-row}
do_execsql_test transitive1-401 {
SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
} {1-row}
do_execsql_test transitive1-402 {
SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
} {1-row}
do_execsql_test transitive1-403 {
SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
} {1-row}
# The following is a script distilled from the XBMC project where the
# bug was originally encountered. The correct answer is a single row
# of output. Before the bug was fixed, zero rows were generated.
#
do_execsql_test transitive1-410 {
CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
|
Changes to test/vtab2.test.
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
149
150
151
152
153
154
155
156
|
do_test vtab2-3.2 {
execsql {
SELECT *, b.rowid
FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
WHERE a.rowid=1
}
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
do_test vtab2-4.1 {
execsql {
BEGIN TRANSACTION;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
CREATE TABLE fkey(
to_tbl,
................................................................................
sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C9 USING s }
} {/1 {malformed database schema.* already exists}/}
}
finish_test
|
>
>
>
>
>
>
>
>
<
|
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
157
158
159
160
161
162
163
|
do_test vtab2-3.2 {
execsql {
SELECT *, b.rowid
FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
WHERE a.rowid=1
}
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
do_test vtab2-3.3 {
execsql {
SELECT *, b.rowid
FROM schema a LEFT JOIN schema b ON a.dflt_value IS b.dflt_value
AND a.dflt_value IS NOT NULL
WHERE a.rowid=1
}
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
do_test vtab2-4.1 {
execsql {
BEGIN TRANSACTION;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
CREATE TABLE fkey(
to_tbl,
................................................................................
sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C9 USING s }
} {/1 {malformed database schema.* already exists}/}
}
finish_test
|
Changes to test/vtab6.test.
228 229 230 231 232 233 234 235 236 237 238 239 240 241 |
SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
}
} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
do_test vtab6-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
}
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.5 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
}
} {2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.6 {
|
> > > > > |
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 |
SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
}
} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
do_test vtab6-2.4 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
}
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.4.1 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d
}
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.5 {
execsql {
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
}
} {2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.6 {
|