Return to rtree6.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / ext / rtree

sqlite3

    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 {
   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: } {Ea}
   61: 
   62: do_test rtree6-1.3 {
   63:   rtree_strategy {SELECT * FROM t1 WHERE x1<10}
   64: } {Ca}
   65: 
   66: do_test rtree6-1.4 {
   67:   rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
   68: } {Ca}
   69: 
   70: do_test rtree6-1.5 {
   71:   rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
   72: } {Ca}
   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:Ca (~0 rows)} 
   78:   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   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:Ca (~0 rows)} 
   85:   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   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: (~0 rows)} 
   92:   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   93: }
   94: 
   95: do_eqp_test rtree6.2.4 {
   96:   SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
   97: } {
   98:   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~0 rows)} 
   99:   0 1 1 {SCAN TABLE t2 (~100000 rows)}
  100: }
  101: 
  102: do_eqp_test rtree6.2.5 {
  103:   SELECT * FROM t1,t2 WHERE k=ii AND x1<v
  104: } {
  105:   0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
  106:   0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
  107: }
  108: 
  109: do_execsql_test rtree6-3.1 {
  110:   CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
  111:   INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
  112:   SELECT * FROM t3 WHERE 
  113:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  114:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  115:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  116:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  117:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  118:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
  119: } {1 1.0 1.0 2.0 2.0}
  120: 
  121: do_test rtree6.3.2 {
  122:   rtree_strategy {
  123:     SELECT * FROM t3 WHERE 
  124:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  125:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  126:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  127:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 
  128:   }
  129: } {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
  130: do_test rtree6.3.3 {
  131:   rtree_strategy {
  132:     SELECT * FROM t3 WHERE 
  133:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  134:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  135:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  136:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  137:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  138:       x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
  139:   }
  140: } {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
  141: 
  142: do_execsql_test rtree6-3.4 {
  143:   SELECT * FROM t3 WHERE x1>0.5 AND x1>0.8 AND x1>1.1
  144: } {}
  145: do_execsql_test rtree6-3.5 {
  146:   SELECT * FROM t3 WHERE 
  147:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  148:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  149:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  150:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  151:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
  152:     x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>1.1
  153: } {}
  154: 
  155: 
  156: finish_test