/ Check-in [8d24e080]
Login

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

Overview
Comment:Add tests to check that affinities work with != operators on virtual table column values. No changes to code.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-extra-ops
Files: files | file ages | folders
SHA3-256:8d24e0803d180448e637e33030a4ebf2c20eb1cab1074290c08f2eee0a23f66c
User & Date: dan 2017-09-11 08:53:54
Context
2017-09-11
18:37
Minor adjustments to indentation and spacing for clarity. No changes to code. check-in: d3153abd user: drh tags: vtab-extra-ops
08:53
Add tests to check that affinities work with != operators on virtual table column values. No changes to code. check-in: 8d24e080 user: dan tags: vtab-extra-ops
2017-09-09
19:41
Enhance the vtab interface to handle IS, !=, IS NOT, IS NULL and IS NOT NULL constraints. check-in: 34c8e952 user: dan tags: vtab-extra-ops
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/bestindex5.test.

183
184
185
186
187
188
189
190


























































191
192
  "WHERE a IS '1' AND b IS '2'"
  1 2 3.0
}
do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
  {WHERE b IS '5' AND a IS '4'} 
  4 5 6.0
}



























































finish_test









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


183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
  "WHERE a IS '1' AND b IS '2'"
  1 2 3.0
}
do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
  {WHERE b IS '5' AND a IS '4'} 
  4 5 6.0
}

#---------------------------------------------------------------------
do_execsql_test 2.0.0 {
  DELETE FROM t1x;
  INSERT INTO t1x VALUES('a', 'b', 'c');
}
do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c}
do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {}

do_execsql_test 2.1.0 {
  DELETE FROM t1x;
  INSERT INTO t1x VALUES(7, 8, 9);
}
do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0}
do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {}
do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }


do_execsql_test 2.2.1 {
  CREATE TABLE t3(a INTEGER, b TEXT);
  INSERT INTO t3 VALUES(45, 46);
}
do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); }
do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); }
do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46}
do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46}

#---------------------------------------------------------------------
# Test the != operator on a virtual table with column affinities.
#
proc vtab_simple_integer {method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t4(x INTEGER)"
    }
    xBestIndex {
      return [list cost 999999.0]
    }
    xFilter {
      return [list sql "SELECT rowid, * FROM t4x"]
    }
  }
  return ""
}

do_execsql_test 3.0 {
  CREATE TABLE t4x(a INTEGER);
  INSERT INTO t4x VALUES(245);
  CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer');
}
do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245}
do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245}
do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {}
do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {}

do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {}


finish_test