test/where9.test

   1 # 2008 December 30
   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 # This file implements regression tests for SQLite library.  The
  12 # focus of this file is testing the multi-index OR clause optimizer.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 ifcapable !or_opt||!compound {
  19   finish_test
  20   return
  21 }
  22
  23 # Evaluate SQL.  Return the result set followed by the
  24 # and the number of full-scan steps.
  25 #
  26 proc count_steps {sql} {
  27   set r [db eval $sql]
  28   lappend r scan [db status step] sort [db status sort]
  29 }
  30
  31
  32 # Construct test data.
  33 #
  34 do_test where9-1.1 {
  35   db eval {
  36     CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
  37     INSERT INTO t1 VALUES(1,11,1001,1.001,100.1,'bcdefghij','yxwvuts');
  38     INSERT INTO t1 VALUES(2,22,1001,2.002,100.1,'cdefghijk','yxwvuts');
  39     INSERT INTO t1 VALUES(3,33,1001,3.003,100.1,'defghijkl','xwvutsr');
  40     INSERT INTO t1 VALUES(4,44,2002,4.004,200.2,'efghijklm','xwvutsr');
  41     INSERT INTO t1 VALUES(5,55,2002,5.005,200.2,'fghijklmn','xwvutsr');
  42     INSERT INTO t1 VALUES(6,66,2002,6.006,200.2,'ghijklmno','xwvutsr');
  43     INSERT INTO t1 VALUES(7,77,3003,7.007,300.3,'hijklmnop','xwvutsr');
  44     INSERT INTO t1 VALUES(8,88,3003,8.008,300.3,'ijklmnopq','wvutsrq');
  45     INSERT INTO t1 VALUES(9,99,3003,9.009,300.3,'jklmnopqr','wvutsrq');
  46     INSERT INTO t1 VALUES(10,110,4004,10.01,400.4,'klmnopqrs','wvutsrq');
  47     INSERT INTO t1 VALUES(11,121,4004,11.011,400.4,'lmnopqrst','wvutsrq');
  48     INSERT INTO t1 VALUES(12,132,4004,12.012,400.4,'mnopqrstu','wvutsrq');
  49     INSERT INTO t1 VALUES(13,143,5005,13.013,500.5,'nopqrstuv','vutsrqp');
  50     INSERT INTO t1 VALUES(14,154,5005,14.014,500.5,'opqrstuvw','vutsrqp');
  51     INSERT INTO t1 VALUES(15,165,5005,15.015,500.5,'pqrstuvwx','vutsrqp');
  52     INSERT INTO t1 VALUES(16,176,6006,16.016,600.6,'qrstuvwxy','vutsrqp');
  53     INSERT INTO t1 VALUES(17,187,6006,17.017,600.6,'rstuvwxyz','vutsrqp');
  54     INSERT INTO t1 VALUES(18,198,6006,18.018,600.6,'stuvwxyza','utsrqpo');
  55     INSERT INTO t1 VALUES(19,209,7007,19.019,700.7,'tuvwxyzab','utsrqpo');
  56     INSERT INTO t1 VALUES(20,220,7007,20.02,700.7,'uvwxyzabc','utsrqpo');
  57     INSERT INTO t1 VALUES(21,231,7007,21.021,700.7,'vwxyzabcd','utsrqpo');
  58     INSERT INTO t1 VALUES(22,242,8008,22.022,800.8,'wxyzabcde','utsrqpo');
  59     INSERT INTO t1 VALUES(23,253,8008,23.023,800.8,'xyzabcdef','tsrqpon');
  60     INSERT INTO t1 VALUES(24,264,8008,24.024,800.8,'yzabcdefg','tsrqpon');
  61     INSERT INTO t1 VALUES(25,275,9009,25.025,900.9,'zabcdefgh','tsrqpon');
  62     INSERT INTO t1 VALUES(26,286,9009,26.026,900.9,'abcdefghi','tsrqpon');
  63     INSERT INTO t1 VALUES(27,297,9009,27.027,900.9,'bcdefghij','tsrqpon');
  64     INSERT INTO t1 VALUES(28,308,10010,28.028,1001.0,'cdefghijk','srqponm');
  65     INSERT INTO t1 VALUES(29,319,10010,29.029,1001.0,'defghijkl','srqponm');
  66     INSERT INTO t1 VALUES(30,330,10010,30.03,1001.0,'efghijklm','srqponm');
  67     INSERT INTO t1 VALUES(31,341,11011,31.031,1101.1,'fghijklmn','srqponm');
  68     INSERT INTO t1 VALUES(32,352,11011,32.032,1101.1,'ghijklmno','srqponm');
  69     INSERT INTO t1 VALUES(33,363,11011,33.033,1101.1,'hijklmnop','rqponml');
  70     INSERT INTO t1 VALUES(34,374,12012,34.034,1201.2,'ijklmnopq','rqponml');
  71     INSERT INTO t1 VALUES(35,385,12012,35.035,1201.2,'jklmnopqr','rqponml');
  72     INSERT INTO t1 VALUES(36,396,12012,36.036,1201.2,'klmnopqrs','rqponml');
  73     INSERT INTO t1 VALUES(37,407,13013,37.037,1301.3,'lmnopqrst','rqponml');
  74     INSERT INTO t1 VALUES(38,418,13013,38.038,1301.3,'mnopqrstu','qponmlk');
  75     INSERT INTO t1 VALUES(39,429,13013,39.039,1301.3,'nopqrstuv','qponmlk');
  76     INSERT INTO t1 VALUES(40,440,14014,40.04,1401.4,'opqrstuvw','qponmlk');
  77     INSERT INTO t1 VALUES(41,451,14014,41.041,1401.4,'pqrstuvwx','qponmlk');
  78     INSERT INTO t1 VALUES(42,462,14014,42.042,1401.4,'qrstuvwxy','qponmlk');
  79     INSERT INTO t1 VALUES(43,473,15015,43.043,1501.5,'rstuvwxyz','ponmlkj');
  80     INSERT INTO t1 VALUES(44,484,15015,44.044,1501.5,'stuvwxyza','ponmlkj');
  81     INSERT INTO t1 VALUES(45,495,15015,45.045,1501.5,'tuvwxyzab','ponmlkj');
  82     INSERT INTO t1 VALUES(46,506,16016,46.046,1601.6,'uvwxyzabc','ponmlkj');
  83     INSERT INTO t1 VALUES(47,517,16016,47.047,1601.6,'vwxyzabcd','ponmlkj');
  84     INSERT INTO t1 VALUES(48,528,16016,48.048,1601.6,'wxyzabcde','onmlkji');
  85     INSERT INTO t1 VALUES(49,539,17017,49.049,1701.7,'xyzabcdef','onmlkji');
  86     INSERT INTO t1 VALUES(50,550,17017,50.05,1701.7,'yzabcdefg','onmlkji');
  87     INSERT INTO t1 VALUES(51,561,17017,51.051,1701.7,'zabcdefgh','onmlkji');
  88     INSERT INTO t1 VALUES(52,572,18018,52.052,1801.8,'abcdefghi','onmlkji');
  89     INSERT INTO t1 VALUES(53,583,18018,53.053,1801.8,'bcdefghij','nmlkjih');
  90     INSERT INTO t1 VALUES(54,594,18018,54.054,1801.8,'cdefghijk','nmlkjih');
  91     INSERT INTO t1 VALUES(55,605,19019,55.055,1901.9,'defghijkl','nmlkjih');
  92     INSERT INTO t1 VALUES(56,616,19019,56.056,1901.9,'efghijklm','nmlkjih');
  93     INSERT INTO t1 VALUES(57,627,19019,57.057,1901.9,'fghijklmn','nmlkjih');
  94     INSERT INTO t1 VALUES(58,638,20020,58.058,2002.0,'ghijklmno','mlkjihg');
  95     INSERT INTO t1 VALUES(59,649,20020,59.059,2002.0,'hijklmnop','mlkjihg');
  96     INSERT INTO t1 VALUES(60,660,20020,60.06,2002.0,'ijklmnopq','mlkjihg');
  97     INSERT INTO t1 VALUES(61,671,21021,61.061,2102.1,'jklmnopqr','mlkjihg');
  98     INSERT INTO t1 VALUES(62,682,21021,62.062,2102.1,'klmnopqrs','mlkjihg');
  99     INSERT INTO t1 VALUES(63,693,21021,63.063,2102.1,'lmnopqrst','lkjihgf');
 100     INSERT INTO t1 VALUES(64,704,22022,64.064,2202.2,'mnopqrstu','lkjihgf');
 101     INSERT INTO t1 VALUES(65,715,22022,65.065,2202.2,'nopqrstuv','lkjihgf');
 102     INSERT INTO t1 VALUES(66,726,22022,66.066,2202.2,'opqrstuvw','lkjihgf');
 103     INSERT INTO t1 VALUES(67,737,23023,67.067,2302.3,'pqrstuvwx','lkjihgf');
 104     INSERT INTO t1 VALUES(68,748,23023,68.068,2302.3,'qrstuvwxy','kjihgfe');
 105     INSERT INTO t1 VALUES(69,759,23023,69.069,2302.3,'rstuvwxyz','kjihgfe');
 106     INSERT INTO t1 VALUES(70,770,24024,70.07,2402.4,'stuvwxyza','kjihgfe');
 107     INSERT INTO t1 VALUES(71,781,24024,71.071,2402.4,'tuvwxyzab','kjihgfe');
 108     INSERT INTO t1 VALUES(72,792,24024,72.072,2402.4,'uvwxyzabc','kjihgfe');
 109     INSERT INTO t1 VALUES(73,803,25025,73.073,2502.5,'vwxyzabcd','jihgfed');
 110     INSERT INTO t1 VALUES(74,814,25025,74.074,2502.5,'wxyzabcde','jihgfed');
 111     INSERT INTO t1 VALUES(75,825,25025,75.075,2502.5,'xyzabcdef','jihgfed');
 112     INSERT INTO t1 VALUES(76,836,26026,76.076,2602.6,'yzabcdefg','jihgfed');
 113     INSERT INTO t1 VALUES(77,847,26026,77.077,2602.6,'zabcdefgh','jihgfed');
 114     INSERT INTO t1 VALUES(78,858,26026,78.078,2602.6,'abcdefghi','ihgfedc');
 115     INSERT INTO t1 VALUES(79,869,27027,79.079,2702.7,'bcdefghij','ihgfedc');
 116     INSERT INTO t1 VALUES(80,880,27027,80.08,2702.7,'cdefghijk','ihgfedc');
 117     INSERT INTO t1 VALUES(81,891,27027,81.081,2702.7,'defghijkl','ihgfedc');
 118     INSERT INTO t1 VALUES(82,902,28028,82.082,2802.8,'efghijklm','ihgfedc');
 119     INSERT INTO t1 VALUES(83,913,28028,83.083,2802.8,'fghijklmn','hgfedcb');
 120     INSERT INTO t1 VALUES(84,924,28028,84.084,2802.8,'ghijklmno','hgfedcb');
 121     INSERT INTO t1 VALUES(85,935,29029,85.085,2902.9,'hijklmnop','hgfedcb');
 122     INSERT INTO t1 VALUES(86,946,29029,86.086,2902.9,'ijklmnopq','hgfedcb');
 123     INSERT INTO t1 VALUES(87,957,29029,87.087,2902.9,'jklmnopqr','hgfedcb');
 124     INSERT INTO t1 VALUES(88,968,30030,88.088,3003.0,'klmnopqrs','gfedcba');
 125     INSERT INTO t1 VALUES(89,979,30030,89.089,3003.0,'lmnopqrst','gfedcba');
 126     INSERT INTO t1 VALUES(90,NULL,30030,90.09,3003.0,'mnopqrstu','gfedcba');
 127     INSERT INTO t1 VALUES(91,1001,NULL,91.091,3103.1,'nopqrstuv','gfedcba');
 128     INSERT INTO t1 VALUES(92,1012,31031,NULL,3103.1,'opqrstuvw','gfedcba');
 129     INSERT INTO t1 VALUES(93,1023,31031,93.093,NULL,'pqrstuvwx','fedcbaz');
 130     INSERT INTO t1 VALUES(94,1034,32032,94.094,3203.2,NULL,'fedcbaz');
 131     INSERT INTO t1 VALUES(95,1045,32032,95.095,3203.2,'rstuvwxyz',NULL);
 132     INSERT INTO t1 VALUES(96,NULL,NULL,96.096,3203.2,'stuvwxyza','fedcbaz');
 133     INSERT INTO t1 VALUES(97,1067,33033,NULL,NULL,'tuvwxyzab','fedcbaz');
 134     INSERT INTO t1 VALUES(98,1078,33033,98.098,3303.3,NULL,NULL);
 135     INSERT INTO t1 VALUES(99,NULL,NULL,NULL,NULL,NULL,NULL);
 136     CREATE INDEX t1b ON t1(b);
 137     CREATE INDEX t1c ON t1(c);
 138     CREATE INDEX t1d ON t1(d);
 139     CREATE INDEX t1e ON t1(e);
 140     CREATE INDEX t1f ON t1(f);
 141     CREATE INDEX t1g ON t1(g);
 142     CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
 143     INSERT INTO t2 SELECT * FROM t1;
 144     CREATE INDEX t2b ON t2(b,c);
 145     CREATE INDEX t2c ON t2(c,e);
 146     CREATE INDEX t2d ON t2(d,g);
 147     CREATE INDEX t2e ON t2(e,f,g);
 148     CREATE INDEX t2f ON t2(f,b,d,c);
 149     CREATE INDEX t2g ON t2(g,f);
 150     CREATE TABLE t3(x,y);
 151     INSERT INTO t3 VALUES(1,80);
 152     INSERT INTO t3 VALUES(2,80);
 153     CREATE TABLE t4(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
 154     INSERT INTO t4 SELECT * FROM t1;
 155     CREATE INDEX t4b ON t4(b);
 156     CREATE INDEX t4c ON t4(c);
 157   }
 158 } {}
 159
 160 do_test where9-1.2.1 {
 161   count_steps {
 162     SELECT a FROM t1
 163      WHERE b IS NULL
 164         OR c IS NULL
 165         OR d IS NULL
 166     ORDER BY a
 167   }
 168 } {90 91 92 96 97 99 scan 0 sort 1}
 169 do_test where9-1.2.2 {
 170   count_steps {
 171     SELECT a FROM t1
 172      WHERE +b IS NULL
 173         OR c IS NULL
 174         OR d IS NULL
 175     ORDER BY a
 176   }
 177 } {90 91 92 96 97 99 scan 98 sort 0}
 178 do_test where9-1.2.3 {
 179   count_steps {
 180     SELECT a FROM t1
 181      WHERE b IS NULL
 182         OR +c IS NULL
 183         OR d IS NULL
 184     ORDER BY a
 185   }
 186 } {90 91 92 96 97 99 scan 98 sort 0}
 187 do_test where9-1.2.4 {
 188   count_steps {
 189     SELECT a FROM t1
 190      WHERE b IS NULL
 191         OR c IS NULL
 192         OR +d IS NULL
 193     ORDER BY a
 194   }
 195 } {90 91 92 96 97 99 scan 98 sort 0}
 196 do_test where9-1.2.5 {
 197   count_steps {
 198     SELECT a FROM t4
 199      WHERE b IS NULL
 200         OR c IS NULL
 201         OR d IS NULL
 202     ORDER BY a
 203   }
 204 } {90 91 92 96 97 99 scan 98 sort 0}
 205
 206 do_test where9-1.3.1 {
 207   count_steps {
 208     SELECT a FROM t1
 209      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 210         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 211         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 212     ORDER BY a
 213   }
 214 } {90 91 92 97 scan 0 sort 1}
 215 do_test where9-1.3.2 {
 216   count_steps {
 217     SELECT a FROM t4
 218      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 219         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 220         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 221     ORDER BY a
 222   }
 223 } {90 91 92 97 scan 98 sort 0}
 224 do_test where9-1.3.3 {
 225   count_steps {
 226     SELECT a FROM t4
 227      WHERE (b NOT NULL AND c NOT NULL AND d IS NULL)
 228         OR (b IS NULL AND c NOT NULL AND d NOT NULL)
 229         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 230     ORDER BY a
 231   }
 232 } {90 91 92 97 scan 98 sort 0}
 233 do_test where9-1.3.4 {
 234   count_steps {
 235     SELECT a FROM (t4)
 236      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 237         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 238         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 239     ORDER BY a
 240   }
 241 } {90 91 92 97 scan 98 sort 0}
 242
 243 do_test where9-1.4 {
 244   count_steps {
 245     SELECT a FROM t1
 246      WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
 247     ORDER BY a
 248   }
 249 } {87 88 89 90 91 scan 0 sort 1}
 250 do_test where9-1.5 {
 251   # When this test was originally written, SQLite used a rowset object
 252   # to optimize the "ORDER BY a" clause. Now that it is using a rowhash,
 253   # this is not possible. So we have to comment out one term of the OR
 254   # expression in order to prevent SQLite from deeming a full-table
 255   # scan to be a better strategy than using multiple indexes, which would
 256   # defeat the point of the test.
 257   count_steps {
 258     SELECT a FROM t1
 259      WHERE a=83
 260         OR b=913
 261         OR c=28028
 262         OR (d>=82 AND d<83)
 263 /*      OR (e>2802 AND e<2803)  */
 264         OR f='fghijklmn'
 265         OR g='hgfedcb'
 266     ORDER BY a
 267   }
 268 } {5 31 57 82 83 84 85 86 87 scan 0 sort 1}
 269 do_test where9-1.6 {
 270   count_steps {
 271     SELECT a FROM t1
 272      WHERE b=1012
 273         OR (d IS NULL AND e IS NOT NULL)
 274   }
 275 } {92 scan 0 sort 0}
 276 do_test where9-1.7 {
 277   count_steps {
 278     SELECT a FROM t1
 279      WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL))
 280        AND f!=g
 281   }
 282 } {92 scan 0 sort 0}
 283 do_test where9-1.8 {
 284   count_steps {
 285     SELECT a FROM t1
 286      WHERE (b=1012 OR (d IS NULL AND e IS NOT NULL))
 287        AND f==g
 288   }
 289 } {scan 0 sort 0}
 290
 291 do_test where9-2.1 {
 292   count_steps {
 293     SELECT t2.a FROM t1, t2
 294      WHERE t1.a=80
 295        AND (t1.c=t2.c OR t1.d=t2.d)
 296     ORDER BY 1
 297   }
 298 } {79 80 81 scan 0 sort 1}
 299 do_test where9-2.2 {
 300   count_steps {
 301     SELECT t2.a FROM t1, t2
 302      WHERE t1.a=80
 303        AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
 304     ORDER BY 1
 305   }
 306 } {2 28 54 80 scan 0 sort 1}
 307 do_test where9-2.3 {
 308   count_steps {
 309     SELECT coalesce(t2.a,9999)
 310       FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f
 311      WHERE t1.a=80
 312     ORDER BY 1
 313   }
 314 } {2 28 54 80 scan 0 sort 1}
 315 do_test where9-2.4 {
 316   count_steps {
 317     SELECT coalesce(t2.a,9999)
 318       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
 319      WHERE t1.a=80
 320     ORDER BY 1
 321   }
 322 } {9999 scan 0 sort 1}
 323 do_test where9-2.5 {
 324   count_steps {
 325     SELECT t1.a, coalesce(t2.a,9999)
 326       FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
 327      WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
 328     ORDER BY 1
 329   }
 330 } {80 80 80 2 80 28 80 54 scan 0 sort 1}
 331 do_test where9-2.6 {
 332   count_steps {
 333     SELECT t1.a, coalesce(t2.a,9999)
 334       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
 335      WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
 336     ORDER BY 1
 337   }
 338 } {80 9999 scan 0 sort 1}
 339 do_test where9-2.7 {
 340   count_steps {
 341     SELECT t3.x, t1.a, coalesce(t2.a,9999)
 342       FROM t3 JOIN
 343            t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
 344      WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
 345     ORDER BY 1, 2
 346   }
 347 } {1 80 9999 2 80 9999 scan 1 sort 1}
 348 do_test where9-2.8 {
 349   count_steps {
 350     SELECT t3.x, t1.a, coalesce(t2.a,9999)
 351       FROM t3 JOIN
 352            t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
 353      WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
 354     ORDER BY 1, 2, 3
 355   }
 356 } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}
 357
 358
 359 ifcapable explain {
 360   do_eqp_test where9-3.1 {
 361     SELECT t2.a FROM t1, t2
 362     WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
 363   } [string map {"\n  " \n} {
 364     QUERY PLAN
 365     |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
 366     `--MULTI-INDEX OR
 367        |--INDEX 1
 368        |  `--SEARCH t2 USING INDEX t2d (d=?)
 369        `--INDEX 3
 370           `--SEARCH t2 USING COVERING INDEX t2f (f=?)
 371   }]
 372   do_eqp_test where9-3.2 {
 373     SELECT coalesce(t2.a,9999)
 374     FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
 375     WHERE t1.a=80
 376   } [string map {"\n  " \n} {
 377     QUERY PLAN
 378     |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
 379     `--MULTI-INDEX OR
 380        |--INDEX 1
 381        |  `--SEARCH t2 USING INDEX t2d (d=?) LEFT-JOIN
 382        `--INDEX 2
 383           `--SEARCH t2 USING COVERING INDEX t2f (f=?) LEFT-JOIN
 384   }]
 385 }
 386
 387 # Make sure that INDEXED BY and multi-index OR clauses play well with
 388 # one another.
 389 #
 390 do_test where9-4.1 {
 391   count_steps {
 392     SELECT a FROM t1
 393      WHERE b>1000
 394        AND (c=31031 OR d IS NULL)
 395      ORDER BY +a
 396   }
 397 } {92 93 97 scan 0 sort 1}
 398 do_test where9-4.2 {
 399   count_steps {
 400     SELECT a FROM t1
 401      WHERE b>1000
 402        AND (c=31031 OR +d IS NULL)
 403      ORDER BY +a
 404   }
 405 } {92 93 97 scan 0 sort 1}
 406 do_test where9-4.3 {
 407   count_steps {
 408     SELECT a FROM t1
 409      WHERE +b>1000
 410        AND (c=31031 OR d IS NULL)
 411      ORDER BY +a
 412   }
 413 } {92 93 97 scan 0 sort 1}
 414 do_test where9-4.4 {
 415   count_steps {
 416     SELECT a FROM t1 INDEXED BY t1b
 417      WHERE b>1000
 418        AND (c=31031 OR d IS NULL)
 419      ORDER BY +a
 420   }
 421 } {92 93 97 scan 0 sort 1}
 422 do_test where9-4.5 {
 423   catchsql {
 424     SELECT a FROM t1 INDEXED BY t1b
 425      WHERE +b>1000
 426        AND (c=31031 OR d IS NULL)
 427      ORDER BY +a
 428   }
 429 } {0 {92 93 97}}
 430 do_test where9-4.6 {
 431   count_steps {
 432     SELECT a FROM t1 NOT INDEXED
 433      WHERE b>1000
 434        AND (c=31031 OR d IS NULL)
 435      ORDER BY +a
 436   }
 437 } {92 93 97 scan 98 sort 1}
 438 do_test where9-4.7 {
 439   catchsql {
 440     SELECT a FROM t1 INDEXED BY t1c
 441      WHERE b>1000
 442        AND (c=31031 OR d IS NULL)
 443      ORDER BY +a
 444   }
 445 } {0 {92 93 97}}
 446 do_test where9-4.8 {
 447   catchsql {
 448     SELECT a FROM t1 INDEXED BY t1d
 449      WHERE b>1000
 450        AND (c=31031 OR d IS NULL)
 451      ORDER BY +a
 452   }
 453 } {0 {92 93 97}}
 454
 455 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
 456 # the former is an equality test which is expected to return fewer rows.
 457 #
 458 do_eqp_test where9-5.1 {
 459   SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
 460 } {
 461   QUERY PLAN
 462   `--MULTI-INDEX OR
 463      |--INDEX 1
 464      |  `--SEARCH t1 USING INDEX t1c (c=?)
 465      `--INDEX 2
 466         `--SEARCH t1 USING INDEX t1d (d=?)
 467 }
 468
 469 # In contrast, b=1000 is preferred over any OR-clause.
 470 #
 471 do_eqp_test where9-5.2 {
 472   SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
 473 } {SEARCH t1 USING INDEX t1b (b=?)}
 474
 475 # Likewise, inequalities in an AND are preferred over inequalities in
 476 # an OR.
 477 #
 478 do_eqp_test where9-5.3 {
 479   SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
 480 } {SEARCH t1 USING INDEX t1b (b>?)}
 481
 482 ############################################################################
 483 # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
 484
 485 do_test where9-6.2.1 {
 486   db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}
 487 } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}
 488
 489 do_test where9-6.2.2 {   ;# Deletes entries 90 91 92 96 97 99
 490   count_steps {
 491      BEGIN;
 492      DELETE FROM t1
 493      WHERE b IS NULL
 494         OR c IS NULL
 495         OR d IS NULL
 496   }
 497 } {scan 0 sort 0}
 498
 499 do_test where9-6.2.3 {
 500   db eval {
 501     SELECT count(*) FROM t1 UNION ALL
 502     SELECT a FROM t1 WHERE a>=85;
 503     ROLLBACK;
 504   }
 505 } {93 85 86 87 88 89 93 94 95 98}
 506
 507 do_test where9-6.2.4 {   ;# Deletes entries 90 91 92 96 97 99
 508   count_steps {
 509      BEGIN;
 510      DELETE FROM t1
 511      WHERE +b IS NULL
 512         OR c IS NULL
 513         OR d IS NULL
 514   }
 515 } {scan 98 sort 0}
 516
 517 do_test where9-6.2.5 {
 518   db eval {
 519      SELECT count(*) FROM t1 UNION ALL
 520      SELECT a FROM t1 WHERE a>=85;
 521      ROLLBACK;
 522   }
 523 } {93 85 86 87 88 89 93 94 95 98}
 524
 525 do_test where9-6.2.6 {
 526   count_steps {
 527      BEGIN;
 528      UPDATE t1 SET a=a+100
 529      WHERE (b IS NULL
 530             OR c IS NULL
 531             OR d IS NULL)
 532        AND a!=92
 533        AND a!=97
 534   }
 535 } {scan 0 sort 0}   ;# Add 100 to entries 90 91 96 99
 536
 537 do_test where9-6.2.7 {
 538   db eval {
 539      SELECT count(*) FROM t1 UNION ALL
 540      SELECT a FROM t1 WHERE a>=85;
 541      ROLLBACK
 542   }
 543 } {99 85 86 87 88 89 92 93 94 95 97 98 190 191 196 199}
 544
 545 do_test where9-6.2.8 {   ;# Deletes entries 90 91 92 97 99
 546   count_steps {
 547      BEGIN;
 548      DELETE FROM t1
 549      WHERE (b IS NULL
 550             OR c IS NULL
 551             OR d IS NULL)
 552        AND a!=96
 553   }
 554 } {scan 0 sort 0}
 555
 556 do_test where9-6.2.9 {
 557   db eval {
 558      SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85;
 559      ROLLBACK;
 560   }
 561 } {94 85 86 87 88 89 93 94 95 96 98}
 562
 563 do_test where9-6.3.1 {
 564   count_steps {
 565     BEGIN;
 566     DELETE FROM t1
 567      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 568         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 569         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 570   }
 571 } {scan 0 sort 0}   ;# DELETEs rows 90 91 92 97
 572 do_test where9-6.3.2 {
 573   db eval {
 574     SELECT count(*) FROM t1 UNION ALL
 575     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 576     ROLLBACK;
 577   }
 578 } {95 85 86 87 88 89 93 94 95 96 98 99}
 579
 580 do_test where9-6.3.3 {
 581   count_steps {
 582     BEGIN;
 583     UPDATE t1 SET a=a+100
 584      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 585         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 586         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 587   }
 588 } {scan 0 sort 0}   ;# Add 100 to rowids 90 91 92 97
 589 do_test where9-6.3.4 {
 590   db eval {
 591     SELECT count(*) FROM t1 UNION ALL
 592     SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
 593     ROLLBACK;
 594   }
 595 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
 596
 597 do_test where9-6.3.5 {
 598   count_steps {
 599     BEGIN;
 600     DELETE FROM t1
 601      WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
 602         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 603         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 604   }
 605 } {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
 606 do_test where9-6.3.6 {
 607   db eval {
 608     SELECT count(*) FROM t1 UNION ALL
 609     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 610     ROLLBACK;
 611   }
 612 } {95 85 86 87 88 89 93 94 95 96 98 99}
 613
 614 do_test where9-6.3.7 {
 615   count_steps {
 616     BEGIN;
 617     UPDATE t1 SET a=a+100
 618      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 619         OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
 620         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 621   }
 622 } {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
 623 do_test where9-6.3.8 {
 624   db eval {
 625     SELECT count(*) FROM t1 UNION ALL
 626     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 627     ROLLBACK;
 628   }
 629 } {99 85 86 87 88 89 93 94 95 96 98 99}
 630
 631
 632 do_test where9-6.4.1 {
 633   count_steps {
 634     BEGIN;
 635     DELETE FROM t1
 636      WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
 637   }
 638 } {scan 0 sort 0}  ;# DELETE rows 87 88 89 90 91
 639 do_test where9-6.4.2 {
 640   db eval {
 641     SELECT count(*) FROM t1 UNION ALL
 642     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 643     ROLLBACK;
 644   }
 645 } {94 85 86 92 93 94 95 96 97 98 99}
 646 do_test where9-6.4.3 {
 647   count_steps {
 648     BEGIN;
 649     UPDATE t1 SET a=a+100
 650      WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
 651   }
 652 } {scan 0 sort 0}  ;# Add 100 to rowids 87 88 89 90 91
 653 do_test where9-6.4.4 {
 654   db eval {
 655     SELECT count(*) FROM t1 UNION ALL
 656     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 657     ROLLBACK;
 658   }
 659 } {99 85 86 92 93 94 95 96 97 98 99}
 660
 661
 662 do_test where9-6.5.1 {
 663   count_steps {
 664     BEGIN;
 665     DELETE FROM t1
 666      WHERE a=83
 667         OR b=913
 668         OR c=28028
 669         OR (d>=82 AND d<83)
 670         OR (e>2802 AND e<2803)
 671         OR f='fghijklmn'
 672         OR g='hgfedcb'
 673   }
 674 } {scan 0 sort 0}   ;#  DELETE rows 5 31 57 82 83 84 85 86 87
 675 do_test where9-6.5.2 {
 676   db eval {
 677     SELECT count(*) FROM t1 UNION ALL
 678     SELECT a FROM t1 WHERE a IN (5,31,57,82,83,84,85,86,87);
 679     ROLLBACK;
 680   }
 681 } {90}
 682
 683 do_test where9-6.5.3 {
 684   count_steps {
 685     BEGIN;
 686     UPDATE t1 SET a=a+100
 687      WHERE a=83
 688         OR b=913
 689         OR c=28028
 690         OR (d>=82 AND d<83)
 691         OR (e>2802 AND e<2803)
 692         OR f='fghijklmn'
 693         OR g='hgfedcb'
 694   }
 695 } {scan 0 sort 0}   ;#  Add 100 to rowids 5 31 57 82 83 84 85 86 87
 696 do_test where9-6.5.4 {
 697   db eval {
 698     SELECT count(*) FROM t1 UNION ALL
 699     SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
 700     ROLLBACK;
 701   }
 702 } {99 105 131 157 182 183 184 185 186 187}
 703
 704 do_test where9-6.6.1 {
 705   count_steps {
 706     BEGIN;
 707     DELETE FROM t1
 708      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 709         OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
 710         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 711   }
 712 } {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
 713 do_test where9-6.6.2 {
 714   db eval {
 715     SELECT count(*) FROM t1 UNION ALL
 716     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 717     ROLLBACK;
 718   }
 719 } {95 85 86 87 88 89 93 94 95 96 98 99}
 720
 721 do_test where9-6.6.3 {
 722   count_steps {
 723     BEGIN;
 724     UPDATE t1 SET a=a+100
 725      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 726         OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
 727         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 728   }
 729 } {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
 730 do_test where9-6.6.4 {
 731   db eval {
 732     SELECT count(*) FROM t1 UNION ALL
 733     SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
 734     ROLLBACK;
 735   }
 736 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
 737
 738 do_test where9-6.7.1 {
 739   count_steps {
 740     BEGIN;
 741     DELETE FROM t1 NOT INDEXED
 742      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 743         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 744         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 745   }
 746 } {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
 747 do_test where9-6.7.2 {
 748   db eval {
 749     SELECT count(*) FROM t1 UNION ALL
 750     SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
 751     ROLLBACK;
 752   }
 753 } {95 85 86 87 88 89 93 94 95 96 98 99}
 754
 755 do_test where9-6.7.3 {
 756   count_steps {
 757     BEGIN;
 758     UPDATE t1 NOT INDEXED SET a=a+100
 759      WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 760         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 761         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 762   }
 763 } {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
 764 do_test where9-6.7.4 {
 765   db eval {
 766     SELECT count(*) FROM t1 UNION ALL
 767     SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
 768     ROLLBACK;
 769   }
 770 } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}
 771
 772 do_test where9-6.8.1 {
 773   catchsql {
 774     DELETE FROM t1 INDEXED BY t1b
 775      WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
 776         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 777         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 778   }
 779 } {0 {}}
 780 do_test where9-6.8.2 {
 781   catchsql {
 782     UPDATE t1 INDEXED BY t1b SET a=a+100
 783      WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
 784         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 785         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 786   }
 787 } {0 {}}
 788
 789 set solution_possible 0
 790 ifcapable stat4 {
 791   if {[permutation] != "no_optimization"} { set solution_possible 1 }
 792 }
 793 if $solution_possible {
 794   # When STAT3 is enabled, the "b NOT NULL" terms get translated
 795   # into b>NULL, which can be satified by the index t1b.  It is a very
 796   # expensive way to do the query, but it works, and so a solution is possible.
 797   do_test where9-6.8.3-stat4 {
 798     catchsql {
 799       UPDATE t1 INDEXED BY t1b SET a=a+100
 800        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 801           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 802           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 803     }
 804   } {0 {}}
 805   do_test where9-6.8.4-stat4 {
 806     catchsql {
 807       DELETE FROM t1 INDEXED BY t1b
 808        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 809           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 810           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 811     }
 812   } {0 {}}
 813 } else {
 814   do_test where9-6.8.3 {
 815     catchsql {
 816       UPDATE t1 INDEXED BY t1b SET a=a+100
 817        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 818           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 819           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 820     }
 821   } {0 {}}
 822   do_test where9-6.8.4 {
 823     catchsql {
 824       DELETE FROM t1 INDEXED BY t1b
 825        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
 826           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
 827           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
 828     }
 829   } {0 {}}
 830 }
 831 ############################################################################
 832 # Test cases where terms inside an OR series are combined with AND terms
 833 # external to the OR clause.  In other words, cases where
 834 #
 835 #              x AND (y OR z)
 836 #
 837 # is able to use indices on x,y and x,z, or indices y,x and z,x.
 838 #
 839 do_test where9-7.0 {
 840   execsql {
 841     CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
 842     INSERT INTO t5
 843      SELECT a, b, c, e, d, f, g,
 844             CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
 845             CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
 846        FROM t1;
 847     CREATE INDEX t5xb ON t5(x, b);
 848     CREATE INDEX t5xc ON t5(x, c);
 849     CREATE INDEX t5xd ON t5(x, d);
 850     CREATE INDEX t5xe ON t5(x, e);
 851     CREATE INDEX t5xf ON t5(x, f);
 852     CREATE INDEX t5xg ON t5(x, g);
 853     CREATE INDEX t5yb ON t5(y, b);
 854     CREATE INDEX t5yc ON t5(y, c);
 855     CREATE INDEX t5yd ON t5(y, d);
 856     CREATE INDEX t5ye ON t5(y, e);
 857     CREATE INDEX t5yf ON t5(y, f);
 858     CREATE INDEX t5yg ON t5(y, g);
 859     CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
 860     INSERT INTO t6 SELECT * FROM t5;
 861     ANALYZE t5;
 862   }
 863 } {}
 864 do_test where9-7.1.1 {
 865   count_steps {
 866     SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
 867   }
 868 } {79 81 83 scan 0 sort 1}
 869 do_test where9-7.1.2 {
 870   execsql {
 871     SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
 872   }
 873 } {79 81 83}
 874 do_test where9-7.1.3 {
 875   count_steps {
 876     SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
 877   }
 878 } {80 scan 0 sort 1}
 879 do_test where9-7.1.4 {
 880   execsql {
 881     SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
 882   }
 883 } {80}
 884 do_test where9-7.2.1 {
 885   count_steps {
 886     SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
 887   }
 888 } {83 scan 0 sort 1}
 889 do_test where9-7.2.2 {
 890   execsql {
 891     SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
 892   }
 893 } {83}
 894 do_test where9-7.3.1 {
 895   count_steps {
 896     SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
 897   }
 898 } {79 81 scan 0 sort 1}
 899 do_test where9-7.3.2 {
 900   execsql {
 901     SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
 902   }
 903 } {79 81}
 904
 905 # Fix for ticket [b7c8682cc17f32903f03a610bd0d35ffd3c1e6e4]
 906 # "Incorrect result from LEFT JOIN with OR in the WHERE clause"
 907 #
 908 do_test where9-8.1 {
 909   db eval {
 910     CREATE TABLE t81(a INTEGER PRIMARY KEY, b, c, d);
 911     CREATE TABLE t82(x INTEGER PRIMARY KEY, y);
 912     CREATE TABLE t83(p INTEGER PRIMARY KEY, q);
 913
 914     INSERT INTO t81 VALUES(2,3,4,5);
 915     INSERT INTO t81 VALUES(3,4,5,6);
 916     INSERT INTO t82 VALUES(2,4);
 917     INSERT INTO t83 VALUES(5,55);
 918
 919     SELECT *
 920       FROM t81 LEFT JOIN t82 ON y=b JOIN t83
 921      WHERE c==p OR d==p
 922      ORDER BY +a;
 923   }
 924 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
 925 do_test where9-8.2 {
 926   db eval {
 927     SELECT *
 928       FROM t81 LEFT JOIN (t82) ON y=b JOIN t83
 929      WHERE c==p OR d==p
 930      ORDER BY +a;
 931   }
 932 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
 933 do_test where9-8.3 {
 934   db eval {
 935     SELECT *
 936       FROM (t81) LEFT JOIN (main.t82) ON y=b JOIN t83
 937      WHERE c==p OR d==p
 938      ORDER BY +a;
 939   }
 940 } {2 3 4 5 {} {} 5 55 3 4 5 6 2 4 5 55}
 941
 942 # Fix for ticket [f2369304e47167e3e644e2f1fe9736063391d7b7]
 943 # Incorrect results when OR is used in the ON clause of a LEFT JOIN
 944 #
 945 do_test where9-9.1 {
 946   db eval {
 947     CREATE TABLE t91(x); INSERT INTO t91 VALUES(1);
 948     CREATE TABLE t92(y INTEGER PRIMARY KEY,a,b);
 949     INSERT INTO t92 VALUES(1,2,3);
 950     SELECT 1 FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
 951     SELECT 2 FROM t91 LEFT JOIN t92 ON a=2 AND b=3;
 952     SELECT 3 FROM t91 LEFT JOIN t92 ON (a=2 OR b=3) AND y IS NULL;
 953     SELECT 4 FROM t91 LEFT JOIN t92 ON (a=2 AND b=3) AND y IS NULL;
 954     CREATE TEMP TABLE x9 AS SELECT * FROM t91 LEFT JOIN t92 ON a=2 OR b=3;
 955     SELECT 5 FROM x9 WHERE y IS NULL;
 956     SELECT 6 FROM t91 LEFT JOIN t92 ON a=2 OR b=3 WHERE y IS NULL;
 957     SELECT 7 FROM t91 LEFT JOIN t92 ON a=2 AND b=3 WHERE y IS NULL;
 958     SELECT 8 FROM t91 LEFT JOIN t92 ON a=22 OR b=33 WHERE y IS NULL;
 959     SELECT 9 FROM t91 LEFT JOIN t92 ON a=22 AND b=33 WHERE y IS NULL;
 960   }
 961 } {1 2 3 4 8 9}
 962
 963 # Fix for ticket [bc878246eafe0f52c519e29049b2fe4a99491b27]
 964 # Incorrect result when OR is used in a join to the right of a LEFT JOIN
 965 #
 966 do_test where9-10.1 {
 967   db eval {
 968     CREATE TABLE t101 (id INTEGER PRIMARY KEY);
 969     INSERT INTO t101 VALUES (1);
 970     SELECT * FROM t101 AS t0
 971          LEFT JOIN t101 AS t1 ON t1.id BETWEEN 10 AND 20
 972          JOIN t101 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
 973   }
 974 } {1 {} 1}
 975 do_test where9-10.2 {
 976   db eval {
 977     CREATE TABLE t102 (id TEXT UNIQUE NOT NULL);
 978     INSERT INTO t102 VALUES ('1');
 979     SELECT * FROM t102 AS t0
 980          LEFT JOIN t102 AS t1 ON t1.id GLOB 'abc%'
 981          JOIN t102 AS t2 ON (t2.id = t0.id OR (t2.id<>555 AND t2.id=t1.id));
 982   }
 983 } {1 {} 1}
 984
 985 # dbsqlfuzz 9df1d53c24c4c96af0dae15ee764897af415ac76
 986 # The MULTI-INDEX OR processing evaluates the same WHERE-clause sub-expression
 987 # twice.  But if that sub-expression contains a UNION ALL SELECT statement
 988 # subject to query flattening, the sub-expression might be transformed in a
 989 # way that it can only be code-generated once.  An assert() will fail on
 990 # the second attempt to generate code from the same sub-expression.
 991 # The solution is to make a copy of sub-expressions used by MULTI-INDEX OR
 992 #
 993 reset_db
 994 do_execsql_test where9-11.1 {
 995   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
 996   CREATE TABLE t2_a(k INTEGER PRIMARY KEY, v TEXT);
 997   CREATE TABLE t2_b(k INTEGER PRIMARY KEY, v TEXT);
 998   CREATE VIEW t2 AS SELECT * FROM t2_a UNION ALL SELECT * FROM t2_b;
 999   SELECT 1 FROM t1 JOIN t1 USING(a)
1000    WHERE (a=1)
1001       OR (a=2 AND (SELECT 4 FROM t2,(SELECT 5 FROM t1 ORDER BY a) WHERE a));
1002 } {}
1003
1004 finish_test