/ Check-in [f397c862]
Login

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

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: 3428043c user: drh tags: trunk
14:03
A few more test cases for the IS operator. Closed-Leaf check-in: f397c862 user: drh tags: index-is-operator
13:41
More test cases. Remove some invalid testcase() macros. Rearrange some code for improved testability. check-in: b3676377 user: drh tags: index-is-operator
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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 {