modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/rtree/rtree6.test (about)

     1  # 2008 Sep 1
     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  #
    13  
    14  if {![info exists testdir]} {
    15    set testdir [file join [file dirname [info script]] .. .. test]
    16  } 
    17  source $testdir/tester.tcl
    18  
    19  ifcapable {!rtree || rtree_int_only} {
    20    finish_test
    21    return
    22  }
    23  
    24  #   Operator    Byte Value
    25  #   ----------------------
    26  #      =        0x41 ('A')
    27  #     <=        0x42 ('B')
    28  #      <        0x43 ('C')
    29  #     >=        0x44 ('D')
    30  #      >        0x45 ('E')
    31  #   ----------------------
    32  
    33  proc rtree_strategy {sql} {
    34    set ret [list]
    35    db eval "explain $sql" a {
    36      if {$a(opcode) eq "VFilter"} {
    37        lappend ret $a(p4)
    38      }
    39    }
    40    set ret
    41  }
    42  
    43  proc query_plan {sql} {
    44    set ret [list]
    45    db eval "explain query plan $sql" a {
    46      lappend ret $a(detail)
    47    }
    48    set ret
    49  }
    50  
    51  do_test rtree6-1.1 {
    52    execsql {
    53      CREATE TABLE t2(k INTEGER PRIMARY KEY, v);
    54      CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
    55    }
    56  } {}
    57  
    58  do_test rtree6-1.2 {
    59    rtree_strategy {SELECT * FROM t1 WHERE x1>10}
    60  } {E0}
    61  
    62  do_test rtree6-1.3 {
    63    rtree_strategy {SELECT * FROM t1 WHERE x1<10}
    64  } {C0}
    65  
    66  do_test rtree6-1.4 {
    67    rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
    68  } {C0}
    69  
    70  do_test rtree6-1.5 {
    71    rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
    72  } {C0}
    73  
    74  do_eqp_test rtree6.2.1 {
    75    SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
    76  } {
    77    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0} 
    78    0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    79  }
    80  
    81  do_eqp_test rtree6.2.2 {
    82    SELECT * FROM t1,t2 WHERE k=ii AND x1<10
    83  } {
    84    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0} 
    85    0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    86  }
    87  
    88  do_eqp_test rtree6.2.3 {
    89    SELECT * FROM t1,t2 WHERE k=ii
    90  } {
    91    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
    92    0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
    93  }
    94  
    95  do_eqp_test rtree6.2.4.1 {
    96    SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
    97  } {
    98    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
    99    0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
   100  }
   101  do_eqp_test rtree6.2.4.2 {
   102    SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
   103  } {
   104    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
   105    0 1 1 {SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)}
   106  }
   107  
   108  do_eqp_test rtree6.2.5 {
   109    SELECT * FROM t1,t2 WHERE k=ii AND x1<v
   110  } {
   111    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
   112    0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
   113  }
   114  
   115  do_execsql_test rtree6-3.1 {
   116    CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
   117    INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
   118    SELECT * FROM t3 WHERE 
   119      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   120      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   121      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   122      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   123      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   124      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
   125  } {1 1.0 1.0 2.0 2.0}
   126  
   127  do_test rtree6.3.2 {
   128    rtree_strategy {
   129      SELECT * FROM t3 WHERE 
   130        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   131        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   132        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   133        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 
   134    }
   135  } {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
   136  do_test rtree6.3.3 {
   137    rtree_strategy {
   138      SELECT * FROM t3 WHERE 
   139        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   140        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   141        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   142        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   143        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   144        x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
   145    }
   146  } {E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0E0}
   147  
   148  do_execsql_test rtree6-3.4 {
   149    SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
   150  } {}
   151  do_execsql_test rtree6-3.5 {
   152    SELECT * FROM t3 WHERE 
   153      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   154      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   155      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   156      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   157      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
   158      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>1.1
   159  } {}
   160  
   161  
   162  finish_test