Annotation of embedaddon/sqlite3/ext/rtree/rtree6.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to rtree6.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / ext / rtree