/ Check-in [3ad2531e]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improve the error messages generated by the rtree module when a constraint fails.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3ad2531efb64b7c53b777ddf3681203217052b32
User & Date: dan 2016-05-23 16:16:13
Context
2016-05-23
19:02
Avoid a minor error message when running RTREE without an sqlite_stat1 table. check-in: 276e92f5 user: drh tags: trunk
18:06
Remove an extra "finish_test" from the end of rtreeC.test. check-in: bfbb6dd8 user: drh tags: tree-stat1-fix
16:16
Improve the error messages generated by the rtree module when a constraint fails. check-in: 3ad2531e user: dan tags: trunk
16:15
Lemon enhancement: avoid unnecessary reduce actions that convert one non-terminal into another but have no side effects. check-in: a86e782a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

2796
2797
2798
2799
2800
2801
2802















































2803
2804
2805
2806
2807
2808
2809
....
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
....
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
  if( f<d ){
    f = (float)(d*(d<0 ? RNDTOWARDS : RNDAWAY));
  }
  return f;
}
#endif /* !defined(SQLITE_RTREE_INT_ONLY) */

















































/*
** The xUpdate method for rtree module virtual tables.
*/
static int rtreeUpdate(
  sqlite3_vtab *pVtab, 
  int nData, 
................................................................................

#ifndef SQLITE_RTREE_INT_ONLY
    if( pRtree->eCoordType==RTREE_COORD_REAL32 ){
      for(ii=0; ii<nData-4; ii+=2){
        cell.aCoord[ii].f = rtreeValueDown(azData[ii+3]);
        cell.aCoord[ii+1].f = rtreeValueUp(azData[ii+4]);
        if( cell.aCoord[ii].f>cell.aCoord[ii+1].f ){
          rc = SQLITE_CONSTRAINT;
          goto constraint;
        }
      }
    }else
#endif
    {
      for(ii=0; ii<nData-4; ii+=2){
        cell.aCoord[ii].i = sqlite3_value_int(azData[ii+3]);
        cell.aCoord[ii+1].i = sqlite3_value_int(azData[ii+4]);
        if( cell.aCoord[ii].i>cell.aCoord[ii+1].i ){
          rc = SQLITE_CONSTRAINT;
          goto constraint;
        }
      }
    }

    /* If a rowid value was supplied, check if it is already present in 
    ** the table. If so, the constraint has failed. */
................................................................................
        sqlite3_bind_int64(pRtree->pReadRowid, 1, cell.iRowid);
        steprc = sqlite3_step(pRtree->pReadRowid);
        rc = sqlite3_reset(pRtree->pReadRowid);
        if( SQLITE_ROW==steprc ){
          if( sqlite3_vtab_on_conflict(pRtree->db)==SQLITE_REPLACE ){
            rc = rtreeDeleteRowid(pRtree, cell.iRowid);
          }else{
            rc = SQLITE_CONSTRAINT;
            goto constraint;
          }
        }
      }
      bHaveRowid = 1;
    }
  }







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|










|







 







|







2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
....
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
....
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
  if( f<d ){
    f = (float)(d*(d<0 ? RNDTOWARDS : RNDAWAY));
  }
  return f;
}
#endif /* !defined(SQLITE_RTREE_INT_ONLY) */

/*
** A constraint has failed while inserting a row into an rtree table. 
** Assuming no OOM error occurs, this function sets the error message 
** (at pRtree->base.zErrMsg) to an appropriate value and returns
** SQLITE_CONSTRAINT.
**
** Parameter iCol is the index of the leftmost column involved in the
** constraint failure. If it is 0, then the constraint that failed is
** the unique constraint on the id column. Otherwise, it is the rtree
** (c1<=c2) constraint on columns iCol and iCol+1 that has failed.
**
** If an OOM occurs, SQLITE_NOMEM is returned instead of SQLITE_CONSTRAINT.
*/
static int rtreeConstraintError(Rtree *pRtree, int iCol){
  sqlite3_stmt *pStmt = 0;
  char *zSql; 
  int rc;

  assert( iCol==0 || iCol%2 );
  zSql = sqlite3_mprintf("SELECT * FROM %Q.%Q", pRtree->zDb, pRtree->zName);
  if( zSql ){
    rc = sqlite3_prepare_v2(pRtree->db, zSql, -1, &pStmt, 0);
  }else{
    rc = SQLITE_NOMEM;
  }
  sqlite3_free(zSql);

  if( rc==SQLITE_OK ){
    if( iCol==0 ){
      const char *zCol = sqlite3_column_name(pStmt, 0);
      pRtree->base.zErrMsg = sqlite3_mprintf(
          "UNIQUE constraint failed: %s.%s", pRtree->zName, zCol
      );
    }else{
      const char *zCol1 = sqlite3_column_name(pStmt, iCol);
      const char *zCol2 = sqlite3_column_name(pStmt, iCol+1);
      pRtree->base.zErrMsg = sqlite3_mprintf(
          "rtree constraint failed: %s.(%s<=%s)", pRtree->zName, zCol1, zCol2
      );
    }
  }

  sqlite3_finalize(pStmt);
  return (rc==SQLITE_OK ? SQLITE_CONSTRAINT : rc);
}



/*
** The xUpdate method for rtree module virtual tables.
*/
static int rtreeUpdate(
  sqlite3_vtab *pVtab, 
  int nData, 
................................................................................

#ifndef SQLITE_RTREE_INT_ONLY
    if( pRtree->eCoordType==RTREE_COORD_REAL32 ){
      for(ii=0; ii<nData-4; ii+=2){
        cell.aCoord[ii].f = rtreeValueDown(azData[ii+3]);
        cell.aCoord[ii+1].f = rtreeValueUp(azData[ii+4]);
        if( cell.aCoord[ii].f>cell.aCoord[ii+1].f ){
          rc = rtreeConstraintError(pRtree, ii+1);
          goto constraint;
        }
      }
    }else
#endif
    {
      for(ii=0; ii<nData-4; ii+=2){
        cell.aCoord[ii].i = sqlite3_value_int(azData[ii+3]);
        cell.aCoord[ii+1].i = sqlite3_value_int(azData[ii+4]);
        if( cell.aCoord[ii].i>cell.aCoord[ii+1].i ){
          rc = rtreeConstraintError(pRtree, ii+1);
          goto constraint;
        }
      }
    }

    /* If a rowid value was supplied, check if it is already present in 
    ** the table. If so, the constraint has failed. */
................................................................................
        sqlite3_bind_int64(pRtree->pReadRowid, 1, cell.iRowid);
        steprc = sqlite3_step(pRtree->pReadRowid);
        rc = sqlite3_reset(pRtree->pReadRowid);
        if( SQLITE_ROW==steprc ){
          if( sqlite3_vtab_on_conflict(pRtree->db)==SQLITE_REPLACE ){
            rc = rtreeDeleteRowid(pRtree, cell.iRowid);
          }else{
            rc = rtreeConstraintError(pRtree, 0);
            goto constraint;
          }
        }
      }
      bHaveRowid = 1;
    }
  }

Changes to ext/rtree/rtree1.test.

190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
...
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
...
506
507
508
509
510
511
512
513


514
515
516
517
518
519
520
do_test rtree-2.1.3 {
  execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
  execsql { SELECT ii FROM t1 ORDER BY ii }
} {1 2 3}

do_test rtree-2.2.1 {
  catchsql { INSERT INTO t1 VALUES(2, 1, 3, 2, 4) }
} {1 {constraint failed}}
do_test rtree-2.2.2 {
  catchsql { INSERT INTO t1 VALUES(4, 1, 3, 4, 2) }
} {1 {constraint failed}}
do_test rtree-2.2.3 {
  catchsql { INSERT INTO t1 VALUES(4, 3, 1, 2, 4) }
} {1 {constraint failed}}
do_test rtree-2.2.4 {
  execsql { SELECT ii FROM t1 ORDER BY ii }
} {1 2 3}

do_test rtree-2.X {
  execsql { DROP TABLE t1 }
} {}
................................................................................
    SELECT * FROM t1;
  }
} {5 1 3 2 4 6 2 6 4 8}

# Test the constraint on the coordinates (c[i]<=c[i+1] where (i%2==0)):
do_test rtree-3.2.1 {
  catchsql { INSERT INTO t1 VALUES(7, 2, 6, 4, 3) }
} {1 {constraint failed}}
do_test rtree-3.2.2 {
  catchsql { INSERT INTO t1 VALUES(8, 2, 6, 3, 3) }
} {0 {}}

#----------------------------------------------------------------------------
# Test cases rtree-5.* test DELETE operations.
#
................................................................................
    ABORT    1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    IGNORE   1 0 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
    FAIL     1 1 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
    REPLACE  1 0 {1 4 5 6 7   2 2 3 4 5                           5 3 4 5 6}
  }

  4    "INSERT %CONF% INTO t1 VALUES(2, 7, 6, 7, 7)" {
    ROLLBACK 0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
    ABORT    0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    IGNORE   0 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    FAIL     0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    REPLACE  0 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
  }

} {
  foreach {mode uses error data} $testdata {
    db_restore_and_reopen

    set sql [string map [list %CONF% "OR $mode"] $sql_template]
................................................................................

    execsql {
      BEGIN;
        INSERT INTO t1 VALUES(4,   4, 5, 6, 7);
    }

    set res(0) {0 {}}
    set res(1) {1 {constraint failed}}


    do_catchsql_test $testname.1 $sql $res($error)
    do_test $testname.2 [list sql_uses_stmt db $sql] $uses
    do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data

    do_test $testname.4 { rtree_check db t1 } 0
    db close
  }







|


|


|







 







|







 







|
|

|
|







 







|
>
>







190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
...
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
...
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
do_test rtree-2.1.3 {
  execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
  execsql { SELECT ii FROM t1 ORDER BY ii }
} {1 2 3}

do_test rtree-2.2.1 {
  catchsql { INSERT INTO t1 VALUES(2, 1, 3, 2, 4) }
} {1 {UNIQUE constraint failed: t1.ii}}
do_test rtree-2.2.2 {
  catchsql { INSERT INTO t1 VALUES(4, 1, 3, 4, 2) }
} {1 {rtree constraint failed: t1.(y1<=y2)}}
do_test rtree-2.2.3 {
  catchsql { INSERT INTO t1 VALUES(4, 3, 1, 2, 4) }
} {1 {rtree constraint failed: t1.(x1<=x2)}}
do_test rtree-2.2.4 {
  execsql { SELECT ii FROM t1 ORDER BY ii }
} {1 2 3}

do_test rtree-2.X {
  execsql { DROP TABLE t1 }
} {}
................................................................................
    SELECT * FROM t1;
  }
} {5 1 3 2 4 6 2 6 4 8}

# Test the constraint on the coordinates (c[i]<=c[i+1] where (i%2==0)):
do_test rtree-3.2.1 {
  catchsql { INSERT INTO t1 VALUES(7, 2, 6, 4, 3) }
} {1 {rtree constraint failed: t1.(y1<=y2)}}
do_test rtree-3.2.2 {
  catchsql { INSERT INTO t1 VALUES(8, 2, 6, 3, 3) }
} {0 {}}

#----------------------------------------------------------------------------
# Test cases rtree-5.* test DELETE operations.
#
................................................................................
    ABORT    1 1 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    IGNORE   1 0 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
    FAIL     1 1 {1 1 2 3 4   2 2 3 4 5               4 4 5 6 7   5 3 4 5 6}
    REPLACE  1 0 {1 4 5 6 7   2 2 3 4 5                           5 3 4 5 6}
  }

  4    "INSERT %CONF% INTO t1 VALUES(2, 7, 6, 7, 7)" {
    ROLLBACK 0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6}
    ABORT    0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    IGNORE   0 0 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    FAIL     0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
    REPLACE  0 2 {1 1 2 3 4   2 2 3 4 5   3 3 4 5 6   4 4 5 6 7}
  }

} {
  foreach {mode uses error data} $testdata {
    db_restore_and_reopen

    set sql [string map [list %CONF% "OR $mode"] $sql_template]
................................................................................

    execsql {
      BEGIN;
        INSERT INTO t1 VALUES(4,   4, 5, 6, 7);
    }

    set res(0) {0 {}}
    set res(1) {1 {UNIQUE constraint failed: t1.idx}}
    set res(2) {1 {rtree constraint failed: t1.(x1<=x2)}}

    do_catchsql_test $testname.1 $sql $res($error)
    do_test $testname.2 [list sql_uses_stmt db $sql] $uses
    do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data

    do_test $testname.4 { rtree_check db t1 } 0
    db close
  }

Changes to ext/rtree/rtree3.test.

43
44
45
46
47
48
49

50
51
52
53
54
55
56
57
...
230
231
232
233
234
235
236




























237
#
#   rtree3-6: Test OOM while deleting all rows of a table, one at a time.
#
#   rtree3-7: OOM during an ALTER TABLE RENAME TABLE command.
#
#   rtree3-8: Test OOM while registering the r-tree module with sqlite.
#


do_faultsim_test rtree3-1 -faults oom* -prep {
  faultsim_delete_and_reopen
} -body {
  execsql {
    BEGIN TRANSACTION;
    CREATE VIRTUAL TABLE rt USING rtree(ii, x1, x2, y1, y2);
    INSERT INTO rt VALUES(NULL, 3, 5, 7, 9);
................................................................................
  execsql { SELECT * FROM rt }
} -body {
  execsql { SELECT ii FROM rt WHERE ii MATCH cube(4.5, 5.5, 6.5, 1, 1, 1) }
} -test {
  faultsim_test_result {0 2}
}





























finish_test







>
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
#
#   rtree3-6: Test OOM while deleting all rows of a table, one at a time.
#
#   rtree3-7: OOM during an ALTER TABLE RENAME TABLE command.
#
#   rtree3-8: Test OOM while registering the r-tree module with sqlite.
#
#   rtree3-11: OOM following a constraint failure
#
do_faultsim_test rtree3-1 -faults oom* -prep {
  faultsim_delete_and_reopen
} -body {
  execsql {
    BEGIN TRANSACTION;
    CREATE VIRTUAL TABLE rt USING rtree(ii, x1, x2, y1, y2);
    INSERT INTO rt VALUES(NULL, 3, 5, 7, 9);
................................................................................
  execsql { SELECT * FROM rt }
} -body {
  execsql { SELECT ii FROM rt WHERE ii MATCH cube(4.5, 5.5, 6.5, 1, 1, 1) }
} -test {
  faultsim_test_result {0 2}
}


do_test rtree3-11.prep {
  faultsim_delete_and_reopen
  execsql { 
    CREATE VIRTUAL TABLE rt USING rtree(ii, x1, x2, y1, y2);
    INSERT INTO rt VALUES(1, 2, 3, 4, 5);
  }
  faultsim_save_and_close
} {}
do_faultsim_test rtree3-10.1 -faults oom-* -prep {
  faultsim_restore_and_reopen
  execsql { SELECT * FROM rt }
} -body {
  execsql { INSERT INTO rt VALUES(1, 2, 3, 4, 5) }
} -test {
  faultsim_test_result {1 {UNIQUE constraint failed: rt.ii}} \
                       {1 {constraint failed}}
}
do_faultsim_test rtree3-10.2 -faults oom-* -prep {
  faultsim_restore_and_reopen
  execsql { SELECT * FROM rt }
} -body {
  execsql { INSERT INTO rt VALUES(2, 2, 3, 5, 4) }
} -test {
  faultsim_test_result {1 {rtree constraint failed: rt.(y1<=y2)}} \
                       {1 {constraint failed}}
}

finish_test