ext/rtree/rtree1.test

   1 # 2008 Feb 19
   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 # The focus of this file is testing the r-tree extension.
  13 #
  14
  15 if {![info exists testdir]} {
  16   set testdir [file join [file dirname [info script]] .. .. test]
  17 }
  18 source [file join [file dirname [info script]] rtree_util.tcl]
  19 source $testdir/tester.tcl
  20 set testprefix rtree1
  21
  22 # Test plan:
  23 #
  24 #   rtree-1.*: Creating/destroying r-tree tables.
  25 #   rtree-2.*: Test the implicit constraints - unique rowid and
  26 #              (coord[N]<=coord[N+1]) for even values of N. Also
  27 #              automatic assigning of rowid values.
  28 #   rtree-3.*: Linear scans of r-tree data.
  29 #   rtree-4.*: Test INSERT
  30 #   rtree-5.*: Test DELETE
  31 #   rtree-6.*: Test UPDATE
  32 #   rtree-7.*: Test renaming an r-tree table.
  33 #   rtree-8.*: Test constrained scans of r-tree data.
  34 #
  35 #   rtree-12.*: Test that on-conflict clauses are supported.
  36 #   rtree-13.*: Test that bug [d2889096e7bdeac6d] has been fixed.
  37 #   rtree-14.*: Test if a non-integer is inserted into the PK column of an
  38 #               r-tree table, it is converted to an integer before being
  39 #               inserted. Also that if a non-numeric is inserted into one
  40 #               of the min/max dimension columns, it is converted to the
  41 #               required type before being inserted.
  42 #   rtree-15.*: Check that DROP TABLE works within a transaction that
  43 #               writes to an r-tree table.
  44 #
  45
  46 ifcapable !rtree {
  47   finish_test
  48   return
  49 }
  50
  51 #----------------------------------------------------------------------------
  52 # Test cases rtree-1.* test CREATE and DROP table statements.
  53 #
  54
  55 # Test creating and dropping an rtree table.
  56 #
  57 do_test rtree-1.1.1 {
  58   execsql { CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2) }
  59 } {}
  60 do_test rtree-1.1.2a {
  61   execsql { SELECT name FROM sqlite_master ORDER BY name }
  62 } {t1 t1_node t1_parent t1_rowid}
  63 do_execsql_test rtree-1.1.2b {
  64   SELECT name FROM pragma_table_list WHERE type='shadow' ORDER BY name;
  65 } {t1_node t1_parent t1_rowid}
  66 do_test rtree-1.1.3 {
  67   execsql {
  68     DROP TABLE t1;
  69     SELECT name FROM sqlite_master ORDER BY name;
  70   }
  71 } {}
  72
  73 # Test creating and dropping an rtree table with an odd name in
  74 # an attached database.
  75 #
  76 do_test rtree-1.2.1 {
  77   file delete -force test2.db
  78   execsql {
  79     ATTACH 'test2.db' AS aux;
  80     CREATE VIRTUAL TABLE aux.'a" "b' USING rtree(ii, x1, x2, y1, y2);
  81   }
  82 } {}
  83 do_test rtree-1.2.2 {
  84   execsql { SELECT name FROM sqlite_master ORDER BY name }
  85 } {}
  86 do_test rtree-1.2.3 {
  87   execsql { SELECT name FROM aux.sqlite_master ORDER BY name }
  88 } {{a" "b} {a" "b_node} {a" "b_parent} {a" "b_rowid}}
  89 do_test rtree-1.2.4 {
  90   execsql {
  91     DROP TABLE aux.'a" "b';
  92     SELECT name FROM aux.sqlite_master ORDER BY name;
  93   }
  94 } {}
  95
  96 # Test that the logic for checking the number of columns specified
  97 # for an rtree table. Acceptable values are odd numbers between 3 and
  98 # 11, inclusive.
  99 #
 100 set cols [list i1 i2 i3 i4 i5 i6 i7 i8 i9 iA iB iC iD iE iF iG iH iI iJ iK]
 101 for {set nCol 1} {$nCol<[llength $cols]} {incr nCol} {
 102
 103   set columns [join [lrange $cols 0 [expr {$nCol-1}]] ,]
 104
 105   set X {0 {}}
 106   if {$nCol%2 == 0}  { set X {1 {Wrong number of columns for an rtree table}} }
 107   if {$nCol < 3}     { set X {1 {Too few columns for an rtree table}} }
 108   if {$nCol > 11}    { set X {1 {Too many columns for an rtree table}} }
 109
 110   do_test rtree-1.3.$nCol {
 111     catchsql "
 112       CREATE VIRTUAL TABLE t1 USING rtree($columns);
 113     "
 114   } $X
 115
 116   catchsql { DROP TABLE t1 }
 117 }
 118 do_catchsql_test rtree-1.3.1000 {
 119   CREATE VIRTUAL TABLE t1000 USING rtree;
 120 } {1 {Too few columns for an rtree table}}
 121
 122 # Like execsql except display output as integer where that can be
 123 # done without loss of information.
 124 #
 125 proc execsql_intout {sql} {
 126   set out {}
 127   foreach term [execsql $sql] {
 128     regsub {\.0$} $term {} term
 129     lappend out $term
 130   }
 131   return $out
 132 }
 133
 134 # Test that it is possible to open an existing database that contains
 135 # r-tree tables.
 136 #
 137 do_execsql_test rtree-1.4.1a {
 138   CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2);
 139   INSERT INTO t1 VALUES(1, 5.0, 10.0);
 140   SELECT substr(hex(data),1,40) FROM t1_node;
 141 } {00000001000000000000000140A0000041200000}
 142 do_execsql_test rtree-1.4.1b {
 143   INSERT INTO t1 VALUES(2, 15.0, 20.0);
 144 } {}
 145 do_test rtree-1.4.2 {
 146   db close
 147   sqlite3 db test.db
 148   execsql_intout { SELECT * FROM t1 ORDER BY ii }
 149 } {1 5 10 2 15 20}
 150 do_test rtree-1.4.3 {
 151   execsql { DROP TABLE t1 }
 152 } {}
 153
 154 # Test that it is possible to create an r-tree table with ridiculous
 155 # column names.
 156 #
 157 do_test rtree-1.5.1 {
 158   execsql_intout {
 159     CREATE VIRTUAL TABLE t1 USING rtree("the key", "x dim.", "x2'dim");
 160     INSERT INTO t1 VALUES(1, 2, 3);
 161     SELECT "the key", "x dim.", "x2'dim" FROM t1;
 162   }
 163 } {1 2 3}
 164 do_test rtree-1.5.1 {
 165   execsql { DROP TABLE t1 }
 166 } {}
 167
 168 # Force the r-tree constructor to fail.
 169 #
 170 do_test rtree-1.6.1 {
 171   execsql { CREATE TABLE t1_rowid(a); }
 172   catchsql {
 173     CREATE VIRTUAL TABLE t1 USING rtree("the key", "x dim.", "x2'dim");
 174   }
 175 } {1 {table "t1_rowid" already exists}}
 176 do_test rtree-1.6.1 {
 177   execsql { DROP TABLE t1_rowid }
 178 } {}
 179
 180 #----------------------------------------------------------------------------
 181 # Test cases rtree-2.*
 182 #
 183 do_test rtree-2.1.1 {
 184   execsql {
 185     CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
 186     SELECT * FROM t1;
 187   }
 188 } {}
 189
 190 do_test rtree-2.1.2 {
 191   execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
 192   execsql_intout { SELECT * FROM t1 }
 193 } {1 1 3 2 4}
 194 do_test rtree-2.1.3 {
 195   execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
 196   execsql { SELECT rowid FROM t1 ORDER BY rowid }
 197 } {1 2}
 198 do_test rtree-2.1.3 {
 199   execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
 200   execsql { SELECT ii FROM t1 ORDER BY ii }
 201 } {1 2 3}
 202
 203 do_test rtree-2.2.1 {
 204   catchsql { INSERT INTO t1 VALUES(2, 1, 3, 2, 4) }
 205 } {1 {UNIQUE constraint failed: t1.ii}}
 206 do_test rtree-2.2.2 {
 207   catchsql { INSERT INTO t1 VALUES(4, 1, 3, 4, 2) }
 208 } {1 {rtree constraint failed: t1.(y1<=y2)}}
 209 do_test rtree-2.2.3 {
 210   catchsql { INSERT INTO t1 VALUES(4, 3, 1, 2, 4) }
 211 } {1 {rtree constraint failed: t1.(x1<=x2)}}
 212 do_test rtree-2.2.4 {
 213   execsql { SELECT ii FROM t1 ORDER BY ii }
 214 } {1 2 3}
 215
 216 do_test rtree-2.X {
 217   execsql { DROP TABLE t1 }
 218 } {}
 219
 220 #----------------------------------------------------------------------------
 221 # Test cases rtree-3.* test linear scans of r-tree table data. To test
 222 # this we have to insert some data into an r-tree, but that is not the
 223 # focus of these tests.
 224 #
 225 do_test rtree-3.1.1 {
 226   execsql {
 227     CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
 228     SELECT * FROM t1;
 229   }
 230 } {}
 231 do_test rtree-3.1.2 {
 232   execsql_intout {
 233     INSERT INTO t1 VALUES(5, 1, 3, 2, 4);
 234     SELECT * FROM t1;
 235   }
 236 } {5 1 3 2 4}
 237 do_test rtree-3.1.3 {
 238   execsql_intout {
 239     INSERT INTO t1 VALUES(6, 2, 6, 4, 8);
 240     SELECT * FROM t1;
 241   }
 242 } {5 1 3 2 4 6 2 6 4 8}
 243
 244 # Test the constraint on the coordinates (c[i]<=c[i+1] where (i%2==0)):
 245 do_test rtree-3.2.1 {
 246   catchsql { INSERT INTO t1 VALUES(7, 2, 6, 4, 3) }
 247 } {1 {rtree constraint failed: t1.(y1<=y2)}}
 248 do_test rtree-3.2.2 {
 249   catchsql { INSERT INTO t1 VALUES(8, 2, 6, 3, 3) }
 250 } {0 {}}
 251
 252 #----------------------------------------------------------------------------
 253 # Test cases rtree-5.* test DELETE operations.
 254 #
 255 do_test rtree-5.1.1 {
 256   execsql { CREATE VIRTUAL TABLE t2 USING rtree(ii, x1, x2) }
 257 } {}
 258 do_test rtree-5.1.2 {
 259   execsql_intout {
 260     INSERT INTO t2 VALUES(1, 10, 20);
 261     INSERT INTO t2 VALUES(2, 30, 40);
 262     INSERT INTO t2 VALUES(3, 50, 60);
 263     SELECT * FROM t2 ORDER BY ii;
 264   }
 265 } {1 10 20 2 30 40 3 50 60}
 266 do_test rtree-5.1.3 {
 267   execsql_intout {
 268     DELETE FROM t2 WHERE ii=2;
 269     SELECT * FROM t2 ORDER BY ii;
 270   }
 271 } {1 10 20 3 50 60}
 272 do_test rtree-5.1.4 {
 273   execsql_intout {
 274     DELETE FROM t2 WHERE ii=1;
 275     SELECT * FROM t2 ORDER BY ii;
 276   }
 277 } {3 50 60}
 278 do_test rtree-5.1.5 {
 279   execsql {
 280     DELETE FROM t2 WHERE ii=3;
 281     SELECT * FROM t2 ORDER BY ii;
 282   }
 283 } {}
 284 do_test rtree-5.1.6 {
 285   execsql { SELECT * FROM t2_rowid }
 286 } {}
 287
 288 #----------------------------------------------------------------------------
 289 # Test cases rtree-5.* test UPDATE operations.
 290 #
 291 do_test rtree-6.1.1 {
 292   execsql { CREATE VIRTUAL TABLE t3 USING rtree(ii, x1, x2, y1, y2) }
 293 } {}
 294 do_test rtree-6.1.2 {
 295   execsql_intout {
 296     INSERT INTO t3 VALUES(1, 2, 3, 4, 5);
 297     UPDATE t3 SET x2=5;
 298     SELECT * FROM t3;
 299   }
 300 } {1 2 5 4 5}
 301 do_test rtree-6.1.3 {
 302   execsql { UPDATE t3 SET ii = 2 }
 303   execsql_intout { SELECT * FROM t3 }
 304 } {2 2 5 4 5}
 305
 306 #----------------------------------------------------------------------------
 307 # Test cases rtree-7.* test rename operations.
 308 #
 309 do_test rtree-7.1.1 {
 310   execsql {
 311     CREATE VIRTUAL TABLE t4 USING rtree(ii, x1, x2, y1, y2, z1, z2);
 312     INSERT INTO t4 VALUES(1, 2, 3, 4, 5, 6, 7);
 313   }
 314 } {}
 315 do_test rtree-7.1.2 {
 316   execsql { ALTER TABLE t4 RENAME TO t5 }
 317   execsql_intout { SELECT * FROM t5 }
 318 } {1 2 3 4 5 6 7}
 319 do_test rtree-7.1.3 {
 320   db close
 321   sqlite3 db test.db
 322   execsql_intout { SELECT * FROM t5 }
 323 } {1 2 3 4 5 6 7}
 324 do_test rtree-7.1.4 {
 325   execsql { ALTER TABLE t5 RENAME TO 'raisara "one"'''}
 326   execsql_intout { SELECT * FROM "raisara ""one""'" }
 327 } {1 2 3 4 5 6 7}
 328 do_test rtree-7.1.5 {
 329   execsql_intout { SELECT * FROM 'raisara "one"''' }
 330 } {1 2 3 4 5 6 7}
 331 do_test rtree-7.1.6 {
 332   execsql { ALTER TABLE "raisara ""one""'" RENAME TO "abc 123" }
 333   execsql_intout { SELECT * FROM "abc 123" }
 334 } {1 2 3 4 5 6 7}
 335 do_test rtree-7.1.7 {
 336   db close
 337   sqlite3 db test.db
 338   execsql_intout { SELECT * FROM "abc 123" }
 339 } {1 2 3 4 5 6 7}
 340
 341 # An error midway through a rename operation.
 342 do_test rtree-7.2.1 {
 343   execsql {
 344     CREATE TABLE t4_node(a);
 345   }
 346   catchsql { ALTER TABLE "abc 123" RENAME TO t4 }
 347 } {1 {SQL logic error}}
 348 do_test rtree-7.2.2 {
 349   execsql_intout { SELECT * FROM "abc 123" }
 350 } {1 2 3 4 5 6 7}
 351 do_test rtree-7.2.3 {
 352   execsql {
 353     DROP TABLE t4_node;
 354     CREATE TABLE t4_rowid(a);
 355   }
 356   catchsql { ALTER TABLE "abc 123" RENAME TO t4 }
 357 } {1 {SQL logic error}}
 358 do_test rtree-7.2.4 {
 359   db close
 360   sqlite3 db test.db
 361   execsql_intout { SELECT * FROM "abc 123" }
 362 } {1 2 3 4 5 6 7}
 363 do_test rtree-7.2.5 {
 364   execsql { DROP TABLE t4_rowid }
 365   execsql { ALTER TABLE "abc 123" RENAME TO t4 }
 366   execsql_intout { SELECT * FROM t4 }
 367 } {1 2 3 4 5 6 7}
 368
 369
 370 #----------------------------------------------------------------------------
 371 # Test cases rtree-8.*
 372 #
 373
 374 # Test that the function to determine if a leaf cell is part of the
 375 # result set works.
 376 do_test rtree-8.1.1 {
 377   execsql {
 378     CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2);
 379     INSERT INTO t6 VALUES(1, 3, 7);
 380     INSERT INTO t6 VALUES(2, 4, 6);
 381   }
 382 } {}
 383 do_test rtree-8.1.2 { execsql { SELECT ii FROM t6 WHERE x1>2 } }   {1 2}
 384 do_test rtree-8.1.3 { execsql { SELECT ii FROM t6 WHERE x1>3 } }   {2}
 385 do_test rtree-8.1.4 { execsql { SELECT ii FROM t6 WHERE x1>4 } }   {}
 386 do_test rtree-8.1.5 { execsql { SELECT ii FROM t6 WHERE x1>5 } }   {}
 387 do_test rtree-8.1.6 { execsql { SELECT ii FROM t6 WHERE x1>''} }   {}
 388 do_test rtree-8.1.7 { execsql { SELECT ii FROM t6 WHERE x1>null}}  {}
 389 do_test rtree-8.1.8 { execsql { SELECT ii FROM t6 WHERE x1>'2'} }   {1 2}
 390 do_test rtree-8.1.9 { execsql { SELECT ii FROM t6 WHERE x1>'3'} }   {2}
 391 do_test rtree-8.2.2 { execsql { SELECT ii FROM t6 WHERE x1>=2 } }  {1 2}
 392 do_test rtree-8.2.3 { execsql { SELECT ii FROM t6 WHERE x1>=3 } }  {1 2}
 393 do_test rtree-8.2.4 { execsql { SELECT ii FROM t6 WHERE x1>=4 } }  {2}
 394 do_test rtree-8.2.5 { execsql { SELECT ii FROM t6 WHERE x1>=5 } }  {}
 395 do_test rtree-8.2.6 { execsql { SELECT ii FROM t6 WHERE x1>=''} }  {}
 396 do_test rtree-8.2.7 { execsql { SELECT ii FROM t6 WHERE x1>=null}} {}
 397 do_test rtree-8.2.8 { execsql { SELECT ii FROM t6 WHERE x1>='4'} } {2}
 398 do_test rtree-8.2.9 { execsql { SELECT ii FROM t6 WHERE x1>='5'} } {}
 399 do_test rtree-8.3.2 { execsql { SELECT ii FROM t6 WHERE x1<2 } }   {}
 400 do_test rtree-8.3.3 { execsql { SELECT ii FROM t6 WHERE x1<3 } }   {}
 401 do_test rtree-8.3.4 { execsql { SELECT ii FROM t6 WHERE x1<4 } }   {1}
 402 do_test rtree-8.3.5 { execsql { SELECT ii FROM t6 WHERE x1<5 } }   {1 2}
 403 do_test rtree-8.3.6 { execsql { SELECT ii FROM t6 WHERE x1<''} }   {1 2}
 404 do_test rtree-8.3.7 { execsql { SELECT ii FROM t6 WHERE x1<null}}  {}
 405 do_test rtree-8.3.8 { execsql { SELECT ii FROM t6 WHERE x1<'3'} }  {}
 406 do_test rtree-8.3.9 { execsql { SELECT ii FROM t6 WHERE x1<'4'} }  {1}
 407 do_test rtree-8.4.2 { execsql { SELECT ii FROM t6 WHERE x1<=2 } }  {}
 408 do_test rtree-8.4.3 { execsql { SELECT ii FROM t6 WHERE x1<=3 } }  {1}
 409 do_test rtree-8.4.4 { execsql { SELECT ii FROM t6 WHERE x1<=4 } }  {1 2}
 410 do_test rtree-8.4.5 { execsql { SELECT ii FROM t6 WHERE x1<=5 } }  {1 2}
 411 do_test rtree-8.4.6 { execsql { SELECT ii FROM t6 WHERE x1<=''} }  {1 2}
 412 do_test rtree-8.4.7 { execsql { SELECT ii FROM t6 WHERE x1<=null}} {}
 413 do_test rtree-8.5.2 { execsql { SELECT ii FROM t6 WHERE x1=2 } }   {}
 414 do_test rtree-8.5.3 { execsql { SELECT ii FROM t6 WHERE x1=3 } }   {1}
 415 do_test rtree-8.5.4 { execsql { SELECT ii FROM t6 WHERE x1=4 } }   {2}
 416 do_test rtree-8.5.5 { execsql { SELECT ii FROM t6 WHERE x1=5 } }   {}
 417 do_test rtree-8.5.6 { execsql { SELECT ii FROM t6 WHERE x1=''} }   {}
 418 do_test rtree-8.5.7 { execsql { SELECT ii FROM t6 WHERE x1=null}}  {}
 419
 420
 421 #----------------------------------------------------------------------------
 422 # Test cases rtree-9.*
 423 #
 424 # Test that ticket #3549 is fixed.
 425 do_test rtree-9.1 {
 426   execsql {
 427     CREATE TABLE foo (id INTEGER PRIMARY KEY);
 428     CREATE VIRTUAL TABLE bar USING rtree (id, minX, maxX, minY, maxY);
 429     INSERT INTO foo VALUES (null);
 430     INSERT INTO foo SELECT null FROM foo;
 431     INSERT INTO foo SELECT null FROM foo;
 432     INSERT INTO foo SELECT null FROM foo;
 433     INSERT INTO foo SELECT null FROM foo;
 434     INSERT INTO foo SELECT null FROM foo;
 435     INSERT INTO foo SELECT null FROM foo;
 436     DELETE FROM foo WHERE id > 40;
 437     INSERT INTO bar SELECT NULL, 0, 0, 0, 0 FROM foo;
 438   }
 439 } {}
 440
 441 # This used to crash.
 442 do_test rtree-9.2 {
 443   execsql {
 444     SELECT count(*) FROM bar b1, bar b2, foo s1 WHERE s1.id = b1.id;
 445   }
 446 } {1600}
 447 do_test rtree-9.3 {
 448   execsql {
 449     SELECT count(*) FROM bar b1, bar b2, foo s1
 450     WHERE b1.minX <= b2.maxX AND s1.id = b1.id;
 451   }
 452 } {1600}
 453
 454 #-------------------------------------------------------------------------
 455 # Ticket #3970: Check that the error message is meaningful when a
 456 # keyword is used as a column name.
 457 #
 458 do_test rtree-10.1 {
 459   catchsql { CREATE VIRTUAL TABLE t7 USING rtree(index, x1, y1, x2, y2) }
 460 } {1 {near "index": syntax error}}
 461
 462 #-------------------------------------------------------------------------
 463 # Test last_insert_rowid().
 464 #
 465 do_test rtree-11.1 {
 466   execsql {
 467     CREATE VIRTUAL TABLE t8 USING rtree(idx, x1, x2, y1, y2);
 468     INSERT INTO t8 VALUES(1, 1.0, 1.0, 2.0, 2.0);
 469     SELECT last_insert_rowid();
 470   }
 471 } {1}
 472 do_test rtree-11.2 {
 473   execsql {
 474     INSERT INTO t8 VALUES(NULL, 1.0, 1.0, 2.0, 2.0);
 475     SELECT last_insert_rowid();
 476   }
 477 } {2}
 478
 479 #-------------------------------------------------------------------------
 480 # Test on-conflict clause handling.
 481 #
 482 db_delete_and_reopen
 483 do_execsql_test 12.0.1 {
 484   CREATE VIRTUAL TABLE t1 USING rtree_i32(idx, x1, x2, y1, y2);
 485   INSERT INTO t1 VALUES(1,   1, 2, 3, 4);
 486   SELECT substr(hex(data),1,56) FROM t1_node;
 487 } {00000001000000000000000100000001000000020000000300000004}
 488 do_execsql_test 12.0.2 {
 489   INSERT INTO t1 VALUES(2,   2, 3, 4, 5);
 490   INSERT INTO t1 VALUES(3,   3, 4, 5, 6);
 491
 492   CREATE TABLE source(idx, x1, x2, y1, y2);
 493   INSERT INTO source VALUES(5, 8, 8, 8, 8);
 494   INSERT INTO source VALUES(2, 7, 7, 7, 7);
 495 }
 496 db_save_and_close
 497 foreach {tn sql_template testdata} {
 498   1    "INSERT %CONF% INTO t1 VALUES(2, 7, 7, 7, 7)" {
 499     ROLLBACK 0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
 500     ABORT    0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 501     IGNORE   0 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 502     FAIL     0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 503     REPLACE  0 0 {1 1 2 3 4   2 7 7 7 7   3 3 4 5 6   4 4 5 6 7}
 504   }
 505
 506   2    "INSERT %CONF% INTO t1 SELECT * FROM source" {
 507     ROLLBACK 1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
 508     ABORT    1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 509     IGNORE   1 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7  5 8 8 8 8}
 510     FAIL     1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7  5 8 8 8 8}
 511     REPLACE  1 0 {1 1 2 3 4   2 7 7 7 7   3 3 4 5 6   4 4 5 6 7  5 8 8 8 8}
 512   }
 513
 514   3    "UPDATE %CONF% t1 SET idx = 2 WHERE idx = 4" {
 515     ROLLBACK 0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
 516     ABORT    0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 517     IGNORE   0 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 518     FAIL     0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 519     REPLACE  0 0 {1 1 2 3 4   2 4 5 6 7   3 3 4 5 6}
 520   }
 521
 522   3    "UPDATE %CONF% t1 SET idx = ((idx+1)%5)+1 WHERE idx > 2" {
 523     ROLLBACK 1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
 524     ABORT    1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 525     IGNORE   1 0 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
 526     FAIL     1 1 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
 527     REPLACE  1 0 {1 4 5 6 7   2 2 3 4 5                           5 3 4 5 6}
 528   }
 529
 530   4    "INSERT %CONF% INTO t1 VALUES(2, 7, 6, 7, 7)" {
 531     ROLLBACK 0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
 532     ABORT    0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 533     IGNORE   0 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 534     FAIL     0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 535     REPLACE  0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
 536   }
 537
 538 } {
 539   foreach {mode uses error data} $testdata {
 540     db_restore_and_reopen
 541
 542     set sql [string map [list %CONF% "OR $mode"] $sql_template]
 543     set testname "12.$tn.[string tolower $mode]"
 544
 545     execsql {
 546       BEGIN;
 547         INSERT INTO t1 VALUES(4,   4, 5, 6, 7);
 548     }
 549
 550     set res(0) {0 {}}
 551     set res(1) {1 {UNIQUE constraint failed: t1.idx}}
 552     set res(2) {1 {rtree constraint failed: t1.(x1<=x2)}}
 553
 554     do_catchsql_test $testname.1 $sql $res($error)
 555     do_test $testname.2 [list sql_uses_stmt db $sql] $uses
 556     do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data
 557
 558     do_rtree_integrity_test $testname.4 t1
 559     db close
 560   }
 561 }
 562
 563 #-------------------------------------------------------------------------
 564 # Test that bug [d2889096e7bdeac6d] has been fixed.
 565 #
 566 reset_db
 567 do_execsql_test 13.1 {
 568   CREATE VIRTUAL TABLE t9 USING rtree(id, xmin, xmax);
 569   INSERT INTO t9 VALUES(1,0,0);
 570   INSERT INTO t9 VALUES(2,0,0);
 571   SELECT * FROM t9 WHERE id IN (1, 2);
 572 } {1 0.0 0.0 2 0.0 0.0}
 573
 574 do_execsql_test 13.2 {
 575   WITH r(x) AS (
 576     SELECT 1 UNION ALL
 577     SELECT 2 UNION ALL
 578     SELECT 3
 579   )
 580   SELECT * FROM r CROSS JOIN t9 WHERE id=x;
 581 } {1 1 0.0 0.0 2 2 0.0 0.0}
 582
 583 #-------------------------------------------------------------------------
 584 # Test if a non-integer is inserted into the PK column of an r-tree
 585 # table, it is converted to an integer before being inserted. Also
 586 # that if a non-numeric is inserted into one of the min/max dimension
 587 # columns, it is converted to the required type before being inserted.
 588 #
 589 do_execsql_test 14.1 {
 590   CREATE VIRTUAL TABLE t10 USING rtree(ii, x1, x2);
 591 }
 592
 593 do_execsql_test 14.2 {
 594   INSERT INTO t10 VALUES(NULL,   1, 2);
 595   INSERT INTO t10 VALUES(NULL,   2, 3);
 596   INSERT INTO t10 VALUES('4xxx', 3, 4);
 597   INSERT INTO t10 VALUES(5.0,    4, 5);
 598   INSERT INTO t10 VALUES(6.4,    5, 6);
 599 }
 600 do_execsql_test 14.3 {
 601   SELECT * FROM t10;
 602 } {
 603   1 1.0 2.0   2 2.0 3.0   4 3.0 4.0   5 4.0 5.0   6 5.0 6.0
 604 }
 605
 606 do_execsql_test 14.4 {
 607   DELETE FROM t10;
 608   INSERT INTO t10 VALUES(1, 'one', 'two');
 609   INSERT INTO t10 VALUES(2, '52xyz', '81...');
 610 }
 611 do_execsql_test 14.5 {
 612   SELECT * FROM t10;
 613 } {
 614   1 0.0 0.0
 615   2 52.0 81.0
 616 }
 617 do_execsql_test 14.6 {
 618   INSERT INTO t10 VALUES(0,10,20);
 619   SELECT * FROM t10 WHERE ii=NULL;
 620 } {}
 621 do_execsql_test 14.7 {
 622   SELECT * FROM t10 WHERE ii='xyz';
 623 } {}
 624 do_execsql_test 14.8 {
 625   SELECT * FROM t10 WHERE ii='0.0';
 626 } {0 10.0 20.0}
 627 do_execsql_test 14.9 {
 628   SELECT * FROM t10 WHERE ii=0.0;
 629 } {0 10.0 20.0}
 630
 631
 632 do_execsql_test 14.104 {
 633   DROP TABLE t10;
 634   CREATE VIRTUAL TABLE t10 USING rtree_i32(ii, x1, x2);
 635   INSERT INTO t10 VALUES(1, 'one', 'two');
 636   INSERT INTO t10 VALUES(2, '52xyz', '81...');
 637   INSERT INTO t10 VALUES(3, 42.3, 49.9);
 638 }
 639 do_execsql_test 14.105 {
 640   SELECT * FROM t10;
 641 } {
 642   1 0 0
 643   2 52 81
 644   3 42 49
 645 }
 646
 647 #-------------------------------------------------------------------------
 648 #
 649 do_execsql_test 15.0 {
 650   CREATE VIRTUAL TABLE rt USING rtree(id, x1,x2, y1,y2);
 651   CREATE TEMP TABLE t13(a, b, c);
 652 }
 653 do_execsql_test 15.1 {
 654   BEGIN;
 655   INSERT INTO rt VALUES(1,2,3,4,5);
 656 }
 657 do_execsql_test 15.2 {
 658   DROP TABLE t13;
 659   COMMIT;
 660 }
 661
 662 # Test cases for the new auxiliary columns feature
 663 #
 664 do_catchsql_test 16.100 {
 665   CREATE VIRTUAL TABLE t16 USING rtree(id,x0,x1,y0,+aux1,x1);
 666 } {1 {Auxiliary rtree columns must be last}}
 667 do_test 16.110 {
 668   set sql {
 669     CREATE VIRTUAL TABLE t16 USING rtree(
 670       id, x00, x01, x10, x11, x20, x21, x30, x31, x40, x41
 671   }
 672   for {set i 12} {$i<=100} {incr i} {
 673      append sql ", +a$i"
 674   }
 675   append sql ");"
 676   execsql $sql
 677 } {}
 678 do_test 16.120 {
 679   set sql {
 680     CREATE VIRTUAL TABLE t16b USING rtree(
 681       id, x00, x01, x10, x11, x20, x21, x30, x31, x40, x41
 682   }
 683   for {set i 12} {$i<=101} {incr i} {
 684      append sql ", +a$i"
 685   }
 686   append sql ");"
 687   catchsql $sql
 688 } {1 {Too many columns for an rtree table}}
 689
 690 do_execsql_test 16.130 {
 691   DROP TABLE IF EXISTS rt1;
 692   CREATE VIRTUAL TABLE rt1 USING rtree(id, x1, x2, +aux);
 693   INSERT INTO rt1 VALUES(1, 1, 2, 'aux1');
 694   INSERT INTO rt1 VALUES(2, 2, 3, 'aux2');
 695   INSERT INTO rt1 VALUES(3, 3, 4, 'aux3');
 696   INSERT INTO rt1 VALUES(4, 4, 5, 'aux4');
 697   SELECT * FROM rt1 WHERE id IN (1, 2, 3, 4);
 698 } {1 1.0 2.0 aux1 2 2.0 3.0 aux2 3 3.0 4.0 aux3 4 4.0 5.0 aux4}
 699
 700 reset_db
 701 do_execsql_test 17.0 {
 702   CREATE VIRTUAL TABLE t1 USING rtree(id, x1 PRIMARY KEY, x2, y1, y2);
 703   CREATE VIRTUAL TABLE t2 USING rtree(id, x1, x2, y1, y2 UNIQUE);
 704 }
 705 do_execsql_test 17.1 {
 706   REINDEX t1;
 707   REINDEX t2;
 708 } {}
 709
 710 do_execsql_test 17.2 {
 711   REINDEX;
 712 } {}
 713
 714 reset_db
 715 do_execsql_test 18.0 {
 716   CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2);
 717   INSERT INTO rt0(c0,c1,c2) VALUES(9,2,3);
 718   SELECT c0 FROM rt0 WHERE rt0.c1 > '-1';
 719   SELECT rt0.c1 > '-1' FROM rt0;
 720 } {9 1}
 721
 722 expand_all_sql db
 723
 724 # 2020-02-28 ticket e63b4d1a65546532
 725 reset_db
 726 do_execsql_test 19.0 {
 727   CREATE VIRTUAL TABLE rt0 USING rtree(a,b,c);
 728   INSERT INTO rt0(a,b,c) VALUES(0,0.0,0.0);
 729   CREATE VIEW v0(x) AS SELECT DISTINCT rt0.b FROM rt0;
 730   SELECT v0.x FROM v0, rt0;
 731 } {0.0}
 732 do_execsql_test 19.1 {
 733   SELECT v0.x FROM v0, rt0 WHERE v0.x = rt0.b;
 734 } {0.0}
 735
 736 # 2022-06-20 https://sqlite.org/forum/forumpost/57bdf2217d
 737 #
 738 reset_db
 739 do_execsql_test 20.0 {
 740   CREATE VIRTUAL TABLE rt0 USING rtree(id, x0, x1);
 741   CREATE TABLE t0(a INT);
 742   CREATE TABLE t1(b INT);
 743   INSERT INTO rt0 VALUES(0, 0, 0);
 744 }
 745 do_catchsql_test 20.1 {
 746   SELECT * FROM t1 JOIN t0 ON x0>a RIGHT JOIN rt0 ON true WHERE +x0 = 0;
 747 } {1 {ON clause references tables to its right}}
 748 do_catchsql_test 20.2 {
 749   SELECT * FROM t1 JOIN t0 ON x0>a RIGHT JOIN rt0 ON true WHERE x0 = 0;
 750 } {1 {ON clause references tables to its right}}
 751 db null -
 752 do_execsql_test 20.3 {
 753   SELECT * FROM t1 JOIN t0 ON true RIGHT JOIN rt0 ON x0>a WHERE +x0 = 0;
 754 } {- - 0 0.0 0.0}
 755 do_execsql_test 20.4 {
 756   SELECT * FROM t1 JOIN t0 ON true RIGHT JOIN rt0 ON x0>a WHERE x0 = 0;
 757 } {- - 0 0.0 0.0}
 758
 759 # 2023-05-19 https://sqlite.org/forum/forumpost/da61c4a1b5b4af19
 760 # Do not omit constraints that involve equality comparisons of
 761 # floating-point values.
 762 #
 763 reset_db
 764 do_execsql_test 21.0 {
 765   CREATE VIRTUAL TABLE t1 USING rtree(id, x0, x1);
 766   INSERT INTO t1 VALUES(0, 1, 9223372036854775807);
 767   SELECT count(*) FROM t1 WHERE x1=9223372036854775807;
 768 } {0}
 769 do_execsql_test 21.1 {
 770  SELECT x1=9223372036854775807 FROM t1;
 771 } {0}
 772
 773 # 2023-05-22 https://sqlite.org/forum/forumpost/da70ee0d0d
 774 # Round-off error associated with using large integer constraints on
 775 # a rtree search.
 776 #
 777 if {$tcl_platform(machine)!="i686" || $tcl_platform(os)!="Linux"} {
 778   reset_db
 779   do_execsql_test 22.0 {
 780     CREATE VIRTUAL TABLE t1 USING rtree ( id, x0, x1 );
 781     INSERT INTO t1 VALUES (123, 9223372036854775799, 9223372036854775800);
 782     SELECT id FROM t1 WHERE x0 > 9223372036854775807;
 783   } {123}
 784   do_execsql_test 22.1 {
 785     SELECT id, x0 > 9223372036854775807 AS 'a0' FROM t1;
 786   } {123 1}
 787 }
 788
 789 # 2023-10-14 dbsqlfuzz --sql-fuzz find.  rtreecheck() should not call
 790 # BEGIN/COMMIT because that causes problems with statement transactions,
 791 # and it is unnecessary.
 792 #
 793 reset_db
 794 do_test 23.0 {
 795   db eval {CREATE TABLE t1(a,b,c);}
 796   catch {db eval {CREATE TABLE t2 AS SELECT rtreecheck('t1') AS y;}}
 797   db eval {PRAGMA integrity_check;}
 798 } {ok}
 799
 800 finish_test