test/whereG.test

   1 # 2013-09-05
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # Test cases for query planning decisions and the likely(), unlikely(), and
  13 # likelihood() functions.
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set testprefix whereG
  18
  19 do_execsql_test whereG-1.0 {
  20   CREATE TABLE composer(
  21     cid INTEGER PRIMARY KEY,
  22     cname TEXT
  23   );
  24   CREATE TABLE album(
  25     aid INTEGER PRIMARY KEY,
  26     aname TEXT
  27   );
  28   CREATE TABLE track(
  29     tid INTEGER PRIMARY KEY,
  30     cid INTEGER REFERENCES composer,
  31     aid INTEGER REFERENCES album,
  32     title TEXT
  33   );
  34   CREATE INDEX track_i1 ON track(cid);
  35   CREATE INDEX track_i2 ON track(aid);
  36   INSERT INTO composer VALUES(1, 'W. A. Mozart');
  37   INSERT INTO composer VALUES(2, 'Beethoven');
  38   INSERT INTO composer VALUES(3, 'Thomas Tallis');
  39   INSERT INTO composer VALUES(4, 'Joseph Hayden');
  40   INSERT INTO composer VALUES(5, 'Thomas Weelkes');
  41   INSERT INTO composer VALUES(6, 'J. S. Bach');
  42   INSERT INTO composer VALUES(7, 'Orlando Gibbons');
  43   INSERT INTO composer VALUES(8, 'Josquin des Prés');
  44   INSERT INTO composer VALUES(9, 'Byrd');
  45   INSERT INTO composer VALUES(10, 'Francis Poulenc');
  46   INSERT INTO composer VALUES(11, 'Mendelsshon');
  47   INSERT INTO composer VALUES(12, 'Zoltán Kodály');
  48   INSERT INTO composer VALUES(13, 'Handel');
  49   INSERT INTO album VALUES(100, 'Kodály: Missa Brevis');
  50   INSERT INTO album VALUES(101, 'Messiah');
  51   INSERT INTO album VALUES(102, 'Missa Brevis in D-, K.65');
  52   INSERT INTO album VALUES(103, 'The complete English anthems');
  53   INSERT INTO album VALUES(104, 'Mass in B Minor, BWV 232');
  54   INSERT INTO track VALUES(10005, 12, 100, 'Sanctus');
  55   INSERT INTO track VALUES(10007, 12, 100, 'Agnus Dei');
  56   INSERT INTO track VALUES(10115, 13, 101, 'Surely He Hath Borne Our Griefs');
  57   INSERT INTO track VALUES(10129, 13, 101, 'Since By Man Came Death');
  58   INSERT INTO track VALUES(10206, 1, 102, 'Agnus Dei');
  59   INSERT INTO track VALUES(10301, 3, 103, 'If Ye Love Me');
  60   INSERT INTO track VALUES(10402, 6, 104, 'Domine Deus');
  61   INSERT INTO track VALUES(10403, 6, 104, 'Qui tollis');
  62 } {}
  63 do_eqp_test whereG-1.1 {
  64   SELECT DISTINCT aname
  65     FROM album, composer, track
  66    WHERE unlikely(cname LIKE '%bach%')
  67      AND composer.cid=track.cid
  68      AND album.aid=track.aid;
  69 } {composer*track*album}
  70 do_execsql_test whereG-1.2 {
  71   SELECT DISTINCT aname
  72     FROM album, composer, track
  73    WHERE unlikely(cname LIKE '%bach%')
  74      AND composer.cid=track.cid
  75      AND album.aid=track.aid;
  76 } {{Mass in B Minor, BWV 232}}
  77
  78 do_eqp_test whereG-1.3 {
  79   SELECT DISTINCT aname
  80     FROM album, composer, track
  81    WHERE likelihood(cname LIKE '%bach%', 0.5)
  82      AND composer.cid=track.cid
  83      AND album.aid=track.aid;
  84 } {/.*track.*composer.*album.*/}
  85 do_execsql_test whereG-1.4 {
  86   SELECT DISTINCT aname
  87     FROM album, composer, track
  88    WHERE likelihood(cname LIKE '%bach%', 0.5)
  89      AND composer.cid=track.cid
  90      AND album.aid=track.aid;
  91 } {{Mass in B Minor, BWV 232}}
  92
  93 do_eqp_test whereG-1.5 {
  94   SELECT DISTINCT aname
  95     FROM album, composer, track
  96    WHERE cname LIKE '%bach%'
  97      AND composer.cid=track.cid
  98      AND album.aid=track.aid;
  99 } {/.*track.*(composer.*album|album.*composer).*/}
 100 do_execsql_test whereG-1.6 {
 101   SELECT DISTINCT aname
 102     FROM album, composer, track
 103    WHERE cname LIKE '%bach%'
 104      AND composer.cid=track.cid
 105      AND album.aid=track.aid;
 106 } {{Mass in B Minor, BWV 232}}
 107
 108 do_eqp_test whereG-1.7 {
 109   SELECT DISTINCT aname
 110     FROM album, composer, track
 111    WHERE cname LIKE '%bach%'
 112      AND unlikely(composer.cid=track.cid)
 113      AND unlikely(album.aid=track.aid);
 114 } {/.*track.*(composer.*album|album.*composer).*/}
 115 do_execsql_test whereG-1.8 {
 116   SELECT DISTINCT aname
 117     FROM album, composer, track
 118    WHERE cname LIKE '%bach%'
 119      AND unlikely(composer.cid=track.cid)
 120      AND unlikely(album.aid=track.aid);
 121 } {{Mass in B Minor, BWV 232}}
 122
 123 do_test whereG-2.1 {
 124   catchsql {
 125     SELECT DISTINCT aname
 126       FROM album, composer, track
 127      WHERE likelihood(cname LIKE '%bach%', -0.01)
 128        AND composer.cid=track.cid
 129        AND album.aid=track.aid;
 130   }
 131 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
 132 do_test whereG-2.2 {
 133   catchsql {
 134     SELECT DISTINCT aname
 135       FROM album, composer, track
 136      WHERE likelihood(cname LIKE '%bach%', 1.01)
 137        AND composer.cid=track.cid
 138        AND album.aid=track.aid;
 139   }
 140 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
 141 do_test whereG-2.3 {
 142   catchsql {
 143     SELECT DISTINCT aname
 144       FROM album, composer, track
 145      WHERE likelihood(cname LIKE '%bach%', track.cid)
 146        AND composer.cid=track.cid
 147        AND album.aid=track.aid;
 148   }
 149 } {1 {second argument to likelihood() must be a constant between 0.0 and 1.0}}
 150
 151 # Commuting a term of the WHERE clause should not change the query plan
 152 #
 153 do_execsql_test whereG-3.0 {
 154   CREATE TABLE a(a1 PRIMARY KEY, a2);
 155   CREATE TABLE b(b1 PRIMARY KEY, b2);
 156 } {}
 157 do_eqp_test whereG-3.1 {
 158   SELECT * FROM a, b WHERE b1=a1 AND a2=5;
 159 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 160 do_eqp_test whereG-3.2 {
 161   SELECT * FROM a, b WHERE a1=b1 AND a2=5;
 162 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 163 do_eqp_test whereG-3.3 {
 164   SELECT * FROM a, b WHERE a2=5 AND b1=a1;
 165 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 166 do_eqp_test whereG-3.4 {
 167   SELECT * FROM a, b WHERE a2=5 AND a1=b1;
 168 } {/.*SCAN a.*SEARCH b USING INDEX .*b_1 .b1=..*/}
 169
 170 # Ticket [1e64dd782a126f48d78c43a664844a41d0e6334e]:
 171 # Incorrect result in a nested GROUP BY/DISTINCT due to the use of an OP_SCopy
 172 # where an OP_Copy was needed.
 173 #
 174 do_execsql_test whereG-4.0 {
 175   CREATE TABLE t4(x);
 176   INSERT INTO t4 VALUES('right'),('wrong');
 177   SELECT DISTINCT x
 178    FROM (SELECT x FROM t4 GROUP BY x)
 179    WHERE x='right'
 180    ORDER BY x;
 181 } {right}
 182
 183 #-------------------------------------------------------------------------
 184 # Test that likelihood() specifications on indexed terms are taken into
 185 # account by various forms of loops.
 186 #
 187 #   5.1.*: open ended range scans
 188 #   5.2.*: skip-scans
 189 #
 190 reset_db
 191
 192 do_execsql_test 5.1 {
 193   CREATE TABLE t1(a, b, c);
 194   CREATE INDEX i1 ON t1(a, b);
 195 }
 196 do_eqp_test 5.1.2 {
 197   SELECT * FROM t1 WHERE a>?
 198 } {SEARCH t1 USING INDEX i1 (a>?)}
 199 do_eqp_test 5.1.3 {
 200   SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
 201 } {SCAN t1}
 202 do_eqp_test 5.1.4 {
 203   SELECT * FROM t1 WHERE likely(a>?)
 204 } {SCAN t1}
 205
 206 do_test 5.2 {
 207   for {set i 0} {$i < 100} {incr i} {
 208     execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
 209   }
 210   execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
 211   execsql { ANALYZE }
 212 } {}
 213 do_eqp_test 5.2.2 {
 214   SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
 215 } {SEARCH t1 USING INDEX i1 (ANY(a) AND b>?)}
 216 do_eqp_test 5.2.3 {
 217   SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
 218 } {SCAN t1}
 219 do_eqp_test 5.2.4 {
 220   SELECT * FROM t1 WHERE likely(b>?)
 221 } {SCAN t1}
 222
 223 ifcapable stat4 {
 224   do_eqp_test 5.3.1.stat4 {
 225     SELECT * FROM t1 WHERE a=?
 226   } {SCAN t1}
 227 } else {
 228   do_eqp_test 5.3.1 {
 229     SELECT * FROM t1 WHERE a=?
 230   } {SEARCH t1 USING INDEX i1}
 231 }
 232 do_eqp_test 5.3.2 {
 233   SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
 234 } {SCAN t1}
 235 do_eqp_test 5.3.3 {
 236   SELECT * FROM t1 WHERE likely(a=?)
 237 } {SCAN t1}
 238
 239 # 2015-06-18
 240 # Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70]
 241 #
 242 do_execsql_test 6.0 {
 243   DROP TABLE IF EXISTS t1;
 244   CREATE TABLE t1(i int, x, y, z);
 245   INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4);
 246   DROP TABLE IF EXISTS t2;
 247   CREATE TABLE t2(i int, bool char);
 248   INSERT INTO t2 VALUES(1,'T'), (2,'F');
 249   SELECT count(*) FROM t1 LEFT JOIN t2 ON t1.i=t2.i AND bool='T';
 250   SELECT count(*) FROM t1 LEFT JOIN t2 ON likely(t1.i=t2.i) AND bool='T';
 251 } {4 4}
 252
 253 # 2015-06-20
 254 # Crash discovered by AFL
 255 #
 256 do_execsql_test 7.0 {
 257   DROP TABLE IF EXISTS t1;
 258   CREATE TABLE t1(a, b, PRIMARY KEY(a,b));
 259   INSERT INTO t1 VALUES(9,1),(1,2);
 260   DROP TABLE IF EXISTS t2;
 261   CREATE TABLE t2(x, y, PRIMARY KEY(x,y));
 262   INSERT INTO t2 VALUES(3,3),(4,4);
 263   SELECT likely(a), x FROM t1, t2 ORDER BY 1, 2;
 264 } {1 3 1 4 9 3 9 4}
 265 do_execsql_test 7.1 {
 266   SELECT unlikely(a), x FROM t1, t2 ORDER BY 1, 2;
 267 } {1 3 1 4 9 3 9 4}
 268 do_execsql_test 7.2 {
 269   SELECT likelihood(a,0.5), x FROM t1, t2 ORDER BY 1, 2;
 270 } {1 3 1 4 9 3 9 4}
 271 do_execsql_test 7.3 {
 272   SELECT coalesce(a,a), x FROM t1, t2 ORDER BY 1, 2;
 273 } {1 3 1 4 9 3 9 4}
 274
 275 # 2019-08-22
 276 # Ticket https://www.sqlite.org/src/info/7e07a3dbf5a8cd26
 277 #
 278 do_execsql_test 8.1 {
 279   DROP TABLE IF EXISTS t0;
 280   CREATE TABLE t0 (c0);
 281   INSERT INTO t0(c0) VALUES ('a');
 282   SELECT LIKELY(t0.rowid) <= '0' FROM t0;
 283 } {1}
 284 do_execsql_test 8.2 {
 285   SELECT * FROM t0 WHERE LIKELY(t0.rowid) <= '0';
 286 } {a}
 287 do_execsql_test 8.3 {
 288   SELECT (t0.rowid) <= '0' FROM t0;
 289 } {0}
 290 do_execsql_test 8.4 {
 291   SELECT * FROM t0 WHERE (t0.rowid) <= '0';
 292 } {}
 293 do_execsql_test 8.5 {
 294   SELECT unlikely(t0.rowid) <= '0', likelihood(t0.rowid,0.5) <= '0' FROM t0;
 295 } {1 1}
 296 do_execsql_test 8.6 {
 297   SELECT * FROM t0 WHERE unlikely(t0.rowid) <= '0';
 298 } {a}
 299 do_execsql_test 8.7 {
 300   SELECT * FROM t0 WHERE likelihood(t0.rowid, 0.5) <= '0';
 301 } {a}
 302 do_execsql_test 8.8 {
 303   SELECT unlikely(t0.rowid <= '0'),
 304          likely(t0.rowid <= '0'),
 305          likelihood(t0.rowid <= '0',0.5)
 306     FROM t0;
 307 } {0 0 0}
 308 do_execsql_test 8.9 {
 309   SELECT * FROM t0 WHERE unlikely(t0.rowid <= '0');
 310 } {}
 311 do_execsql_test 8.10 {
 312   SELECT * FROM t0 WHERE likelihood(t0.rowid <= '0', 0.5);
 313 } {}
 314
 315 # 2019-12-31: assertion fault discovered by Yongheng's fuzzer.
 316 # Harmless memIsValid() due to the code generators failure to
 317 # release the registers used by OP_ResultRow.
 318 #
 319 do_execsql_test 9.10 {
 320   DROP TABLE IF EXISTS t1;
 321   CREATE TABLE t1(a, b FLOAT);
 322   INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
 323   SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
 324 } {NULL '' 'X'}
 325
 326 # 2020-06-14: assert() changed back into testcase()
 327 # ticket 9fb26d37cefaba40
 328 #
 329 reset_db
 330 do_execsql_test 10.1 {
 331   CREATE TABLE a(b TEXT);  INSERT INTO a VALUES(0),(4),(9);
 332   CREATE TABLE c(d NUM);
 333   CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
 334   SELECT g = g FROM f GROUP BY h;
 335 } {1}
 336
 337 reset_db
 338 do_execsql_test 11.0 {
 339   CREATE TABLE t1(x PRIMARY KEY, y);
 340   INSERT INTO t1 VALUES('AAA', 'BBB');
 341
 342   CREATE TABLE t2(z);
 343   INSERT INTO t2 VALUES('t2');
 344
 345   CREATE TABLE t3(x PRIMARY KEY, y);
 346   INSERT INTO t3 VALUES('AAA', 'AAA');
 347 }
 348
 349 do_execsql_test 11.1.1 {
 350   SELECT * FROM t1 JOIN t2 ON unlikely(x=y) AND y='AAA'
 351 }
 352 do_execsql_test 11.1.2 {
 353   SELECT * FROM t1 JOIN t2 ON likely(x=y) AND y='AAA'
 354 }
 355 do_execsql_test 11.1.3 {
 356   SELECT * FROM t1 JOIN t2 ON x=y AND y='AAA'
 357 }
 358
 359 do_execsql_test 11.2.1 {
 360   SELECT * FROM t3 JOIN t2 ON unlikely(x=y) AND y='AAA'
 361 } {AAA AAA t2}
 362 do_execsql_test 11.2.2 {
 363   SELECT * FROM t3 JOIN t2 ON likely(x=y) AND y='AAA'
 364 } {AAA AAA t2}
 365 do_execsql_test 11.2.3 {
 366   SELECT * FROM t3 JOIN t2 ON x=y AND y='AAA'
 367 } {AAA AAA t2}
 368
 369 # 2021-06-14 forum https://sqlite.org/forum/forumpost/3b940c437a
 370 # Affinity problem when a likely() function is used as a column in
 371 # an index.
 372 #
 373 reset_db
 374 do_execsql_test 12.0 {
 375   CREATE TABLE t1(a REAL);
 376   INSERT INTO t1(a) VALUES(123);
 377   CREATE INDEX t1x1 ON t1(likely(a));
 378   SELECT typeof(likely(a)) FROM t1 NOT INDEXED;
 379   SELECT typeof(likely(a)) FROM t1 INDEXED BY t1x1;
 380 } {real real}
 381 do_execsql_test 12.1 {
 382   CREATE INDEX t1x2 ON t1(abs(a));
 383   SELECT typeof(abs(a)) FROM t1 NOT INDEXED;
 384   SELECT typeof(abs(a)) FROM t1 INDEXED BY t1x2;
 385 } {real real}
 386
 387
 388 finish_test