/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/bestindex5.test.

   183    183     "WHERE a IS '1' AND b IS '2'"
   184    184     1 2 3.0
   185    185   }
   186    186   do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
   187    187     {WHERE b IS '5' AND a IS '4'} 
   188    188     4 5 6.0
   189    189   }
          190  +
          191  +#---------------------------------------------------------------------
          192  +do_execsql_test 2.0.0 {
          193  +  DELETE FROM t1x;
          194  +  INSERT INTO t1x VALUES('a', 'b', 'c');
          195  +}
          196  +do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c}
          197  +do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {}
          198  +
          199  +do_execsql_test 2.1.0 {
          200  +  DELETE FROM t1x;
          201  +  INSERT INTO t1x VALUES(7, 8, 9);
          202  +}
          203  +do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0}
          204  +do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {}
          205  +do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
          206  +do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
          207  +
          208  +
          209  +do_execsql_test 2.2.1 {
          210  +  CREATE TABLE t3(a INTEGER, b TEXT);
          211  +  INSERT INTO t3 VALUES(45, 46);
          212  +}
          213  +do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); }
          214  +do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); }
          215  +do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46}
          216  +do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46}
          217  +
          218  +#---------------------------------------------------------------------
          219  +# Test the != operator on a virtual table with column affinities.
          220  +#
          221  +proc vtab_simple_integer {method args} {
          222  +  switch -- $method {
          223  +    xConnect {
          224  +      return "CREATE TABLE t4(x INTEGER)"
          225  +    }
          226  +    xBestIndex {
          227  +      return [list cost 999999.0]
          228  +    }
          229  +    xFilter {
          230  +      return [list sql "SELECT rowid, * FROM t4x"]
          231  +    }
          232  +  }
          233  +  return ""
          234  +}
          235  +
          236  +do_execsql_test 3.0 {
          237  +  CREATE TABLE t4x(a INTEGER);
          238  +  INSERT INTO t4x VALUES(245);
          239  +  CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer');
          240  +}
          241  +do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245}
          242  +do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245}
          243  +do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {}
          244  +do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {}
          245  +
          246  +do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {}
          247  +
   190    248   
   191    249   finish_test
   192    250