github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt-80ba201079.test (about)

     1  # 2010 December 6
     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. Specifically,
    12  # it tests that ticket [80ba201079ea608071d22a57856b940ea3ac53ce] is
    13  # resolved.  That ticket is about an incorrect result that appears when
    14  # an index is added.  The root cause is that a constant is being used
    15  # without initialization when the OR optimization applies in the WHERE clause.
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  set ::testprefix tkt-80ba201079
    21  
    22  do_test tkt-80ba2-100 {
    23    db eval {
    24      CREATE TABLE t1(a);
    25      INSERT INTO t1 VALUES('A');
    26      CREATE TABLE t2(b);
    27      INSERT INTO t2 VALUES('B');
    28      CREATE TABLE t3(c);
    29      INSERT INTO t3 VALUES('C');
    30      SELECT * FROM t1, t2
    31       WHERE (a='A' AND b='X')
    32          OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
    33    }
    34  } {A B}
    35  do_test tkt-80ba2-101 {
    36    db eval {
    37      CREATE INDEX i1 ON t1(a);
    38      SELECT * FROM t1, t2
    39       WHERE (a='A' AND b='X')
    40          OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
    41    }
    42  } {A B}
    43  do_test tkt-80ba2-102 {
    44    optimization_control db factor-constants 0
    45    db cache flush
    46    db eval {
    47      SELECT * FROM t1, t2
    48       WHERE (a='A' AND b='X')
    49          OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
    50    }
    51  } {A B}
    52  optimization_control db all 1
    53  
    54  # Verify that the optimization_control command is actually working
    55  #
    56  do_test tkt-80ba2-150 {
    57    optimization_control db factor-constants 1
    58    db cache flush
    59    set x1 [db eval {EXPLAIN 
    60      SELECT * FROM t1, t2
    61       WHERE (a='A' AND b='X')
    62          OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
    63    optimization_control db factor-constants 0
    64    db cache flush
    65    set x2 [db eval {EXPLAIN 
    66      SELECT * FROM t1, t2
    67       WHERE (a='A' AND b='X')
    68          OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
    69  
    70    expr {$x1==$x2}
    71  } {0}
    72  
    73  do_test tkt-80ba2-200 {
    74    db eval {
    75      CREATE TABLE entry_types (
    76                          id     integer primary key,
    77                          name   text
    78                      );
    79      INSERT INTO "entry_types" VALUES(100,'cli_command');
    80      INSERT INTO "entry_types" VALUES(300,'object_change');
    81      CREATE TABLE object_changes (
    82                          change_id    integer primary key,
    83                          system_id    int,
    84                          obj_id       int,
    85                          obj_context  text,
    86                          change_type  int,
    87                          command_id   int
    88                      );
    89      INSERT INTO "object_changes" VALUES(1551,1,114608,'exported_pools',1,2114);
    90      INSERT INTO "object_changes" VALUES(2048,1,114608,'exported_pools',2,2319);
    91      CREATE TABLE timeline (
    92                          rowid        integer primary key,
    93                          timestamp    text,
    94                          system_id    int,
    95                          entry_type   int,
    96                          entry_id     int
    97                      );
    98      INSERT INTO "timeline" VALUES(6735,'2010-11-21 17:08:27.000',1,300,2048);
    99      INSERT INTO "timeline" VALUES(6825,'2010-11-21 17:09:21.000',1,300,2114);
   100      SELECT entry_type,
   101             entry_types.name,
   102             entry_id
   103        FROM timeline JOIN entry_types ON entry_type = entry_types.id
   104       WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
   105          OR (entry_types.name = 'object_change'
   106               AND entry_id IN (SELECT change_id
   107                                FROM object_changes
   108                                 WHERE obj_context = 'exported_pools'));
   109    }
   110  } {300 object_change 2048}
   111  do_test tkt-80ba2-201 {
   112    db eval {
   113      CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
   114      SELECT entry_type,
   115             entry_types.name,
   116             entry_id
   117        FROM timeline JOIN entry_types ON entry_type = entry_types.id
   118       WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
   119          OR (entry_types.name = 'object_change'
   120               AND entry_id IN (SELECT change_id
   121                                FROM object_changes
   122                                 WHERE obj_context = 'exported_pools'));
   123    }
   124  } {300 object_change 2048}
   125  do_test tkt-80ba2-202 {
   126    optimization_control db factor-constants 0
   127    db cache flush
   128    db eval {
   129      SELECT entry_type,
   130             entry_types.name,
   131             entry_id
   132        FROM timeline JOIN entry_types ON entry_type = entry_types.id
   133       WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
   134          OR (entry_types.name = 'object_change'
   135               AND entry_id IN (SELECT change_id
   136                                FROM object_changes
   137                                 WHERE obj_context = 'exported_pools'));
   138    }
   139  } {300 object_change 2048}
   140  
   141  #-------------------------------------------------------------------------
   142  #
   143  
   144  drop_all_tables
   145  do_execsql_test 301 {
   146    CREATE TABLE t1(a, b, c);
   147    CREATE INDEX i1 ON t1(a);
   148    CREATE INDEX i2 ON t1(b);
   149    CREATE TABLE t2(d, e);
   150  
   151    INSERT INTO t1 VALUES('A', 'B', 'C');
   152    INSERT INTO t2 VALUES('D', 'E');
   153  }
   154  
   155  do_execsql_test 302 {
   156    SELECT * FROM t1, t2 WHERE
   157      (a='A' AND d='E') OR
   158      (b='B' AND c IN ('C', 'D', 'E'))
   159  } {A B C D E}
   160  
   161  do_execsql_test 303 {
   162    SELECT * FROM t1, t2 WHERE
   163      (a='A' AND d='E') OR
   164      (b='B' AND c IN (SELECT c FROM t1))
   165  } {A B C D E}
   166  
   167  ifcapable compound {
   168    do_execsql_test 304 {
   169      SELECT * FROM t1, t2 WHERE
   170        (a='A' AND d='E') OR
   171        (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))
   172    } {A B C D E}
   173  }
   174  
   175  do_execsql_test 305 {
   176    SELECT * FROM t1, t2 WHERE
   177      (b='B' AND c IN ('C', 'D', 'E')) OR
   178      (a='A' AND d='E')
   179  } {A B C D E}
   180  
   181  do_execsql_test 306 {
   182    SELECT * FROM t1, t2 WHERE
   183      (b='B' AND c IN (SELECT c FROM t1)) OR
   184      (a='A' AND d='E')
   185  } {A B C D E}
   186  
   187  ifcapable compound {
   188    do_execsql_test 307 {
   189      SELECT * FROM t1, t2 WHERE
   190        (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR
   191        (a='A' AND d='E')
   192    } {A B C D E}
   193  }
   194  
   195  finish_test