/ Check-in [96f3b629]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add LEFT JOIN test cases for multi-index OR in where9.test. (CVS 6085)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 96f3b62914adde34079f08428b4e2fe81c193612
User & Date: drh 2008-12-30 16:35:53
Context
2008-12-30
17:55
Fix a bug in the multi-index OR cost estimator. Remove leftover "breakpoint" commands from test scripts. (CVS 6086) check-in: b090d573 user: drh tags: trunk
16:35
Add LEFT JOIN test cases for multi-index OR in where9.test. (CVS 6085) check-in: 96f3b629 user: drh tags: trunk
16:18
Get EXPLAIN QUERY PLAN working with the multi-index OR optimization. Added new test script "where9.test". (CVS 6084) check-in: 4b646022 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/where9.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
144
145
146
147
148
149
150



151
152
153
154
155
156
157
...
266
267
268
269
270
271
272


































273
274
275
276
277
278
279
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.1 2008/12/30 16:18:48 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
................................................................................
    INSERT INTO t2 SELECT * FROM t1;
    CREATE INDEX t2b ON t2(b,c);
    CREATE INDEX t2c ON t2(c,e);
    CREATE INDEX t2d ON t2(d,g);
    CREATE INDEX t2e ON t2(e,f,g);
    CREATE INDEX t2f ON t2(f,b,d,c);
    CREATE INDEX t2g ON t2(g,f);



  }
} {}

do_test where9-1.2.1 {
  count_steps {
    SELECT a FROM t1
     WHERE b IS NULL
................................................................................
  count_steps {
    SELECT coalesce(t2.a,9999)
      FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
     WHERE t1.a=80
    ORDER BY 1
  }
} {9999 scan 0 sort 1}




































ifcapable explain {
  do_test where9-3.1 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT t2.a FROM t1, t2







|







 







>
>
>







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.2 2008/12/30 16:35:53 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
................................................................................
    INSERT INTO t2 SELECT * FROM t1;
    CREATE INDEX t2b ON t2(b,c);
    CREATE INDEX t2c ON t2(c,e);
    CREATE INDEX t2d ON t2(d,g);
    CREATE INDEX t2e ON t2(e,f,g);
    CREATE INDEX t2f ON t2(f,b,d,c);
    CREATE INDEX t2g ON t2(g,f);
    CREATE TABLE t3(x,y);
    INSERT INTO t3 VALUES(1,80);
    INSERT INTO t3 VALUES(2,80);
  }
} {}

do_test where9-1.2.1 {
  count_steps {
    SELECT a FROM t1
     WHERE b IS NULL
................................................................................
  count_steps {
    SELECT coalesce(t2.a,9999)
      FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
     WHERE t1.a=80
    ORDER BY 1
  }
} {9999 scan 0 sort 1}
do_test where9-2.5 {
  count_steps {
    SELECT t1.a, coalesce(t2.a,9999)
      FROM t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
     WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1
  }
} {80 2 80 28 80 54 80 80 scan 0 sort 0}
do_test where9-2.6 {
  count_steps {
    SELECT t1.a, coalesce(t2.a,9999)
      FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
     WHERE t1.a=80 OR t1.b=880 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1
  }
} {80 9999 scan 0 sort 0}
do_test where9-2.7 {
  count_steps {
    SELECT t3.x, t1.a, coalesce(t2.a,9999)
      FROM t3 JOIN
           t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
     WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1, 2
  }
} {1 80 9999 2 80 9999 scan 1 sort 1}
do_test where9-2.8 {
  count_steps {
    SELECT t3.x, t1.a, coalesce(t2.a,9999)
      FROM t3 JOIN
           t1 LEFT JOIN t2 ON (t1.c=t2.c AND t1.d=t2.d) OR (t1.f)=t2.f
     WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1, 2
  }
} {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}


ifcapable explain {
  do_test where9-3.1 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT t2.a FROM t1, t2