/ Check-in [99b1fa4b]
Login

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

Overview
Comment:Add tests for updates of without-rowid tables that use non-BINARY collation sequences for the primary key columns. And a minor bugfix to the same.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 99b1fa4b1664a79eae1dddce2b9a848384cdb1d7
User & Date: dan 2013-11-05 14:19:22
Context
2013-11-05
15:02
Updates to the backcompat.test test script so that it works with really old (3.6.*) versions. check-in: ace7e7b6 user: dan tags: omit-rowid
14:19
Add tests for updates of without-rowid tables that use non-BINARY collation sequences for the primary key columns. And a minor bugfix to the same. check-in: 99b1fa4b user: dan tags: omit-rowid
13:33
Standardize the error messages generated by constraint failures to a format of "$TYPE constraint failed: $DETAIL". This involves many changes to the expected output of test cases. check-in: 54b22192 user: drh tags: omit-rowid
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1502   1502             x = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
  1503   1503             sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
  1504   1504             VdbeComment((v, "%s.%s", pTab->zName,
  1505   1505                          pTab->aCol[pPk->aiColumn[i]].zName));
  1506   1506           }
  1507   1507         }
  1508   1508         if( isUpdate ){
  1509         -        if( pIdx->autoIndex==2 ){
  1510         -          /* For a PRIMARY KEY index on a WITHOUT ROWID table, always conflict
  1511         -          ** on an INSERT.  On an UPDATE, only conflict if the PRIMARY KEY
  1512         -          ** has changed. */
  1513         -          int addrPkConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
  1514         -          for(i=0; i<pPk->nKeyCol-1; i++){
  1515         -            x = pPk->aiColumn[i];
  1516         -            sqlite3VdbeAddOp3(v, OP_Ne, regOldData+1+x,
  1517         -                              addrPkConflict, regIdx+i);
  1518         -          }
         1509  +        /* If currently processing the PRIMARY KEY of a WITHOUT ROWID 
         1510  +        ** table, only conflict if the new PRIMARY KEY values are actually
         1511  +        ** different from the old.
         1512  +        **
         1513  +        ** For a UNIQUE index, only conflict if the PRIMARY KEY values
         1514  +        ** of the matched index row are different from the original PRIMARY
         1515  +        ** KEY values of this row before the update.  */
         1516  +        char *p4; 
         1517  +        int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
         1518  +        int op = OP_Ne;
         1519  +        int regCmp = (pIdx->autoIndex==2 ? regIdx : regR);
         1520  +
         1521  +        for(i=0; i<pPk->nKeyCol; i++){
         1522  +          char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]);
  1519   1523             x = pPk->aiColumn[i];
  1520         -          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+1+x, addrUniqueOk, regIdx+i);
  1521         -        }else{
  1522         -          /* For a UNIQUE index on a WITHOUT ROWID table, conflict only if the
  1523         -          ** PRIMARY KEY value of the match is different from the old
  1524         -          ** PRIMARY KEY value from before the update. */
  1525         -          int addrConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
  1526         -          for(i=0; i<pPk->nKeyCol-1; i++){
  1527         -            sqlite3VdbeAddOp3(v, OP_Ne, regOldData+pPk->aiColumn[i]+1,
  1528         -                              addrConflict, regR+i);
         1524  +          if( i==(pPk->nKeyCol-1) ){
         1525  +            addrJump = addrUniqueOk;
         1526  +            op = OP_Eq;
  1529   1527             }
  1530         -          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+pPk->aiColumn[i]+1,
  1531         -                            addrUniqueOk, regR+i);
         1528  +          sqlite3VdbeAddOp4(v, op, 
         1529  +              regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ
         1530  +          );
  1532   1531           }
  1533   1532         }
  1534   1533       }
  1535   1534       sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);
  1536   1535   
  1537   1536       /* Generate code that executes if the new index entry is not unique */
  1538   1537       assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail

Changes to test/tester.tcl.

  1018   1018     puts "----  ------------  ------  ------  ------  ---------------  --  -"
  1019   1019     $db eval "explain $sql" {} {
  1020   1020       puts [format {%-4d  %-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
  1021   1021         $addr $opcode $p1 $p2 $p3 $p4 $p5 $comment
  1022   1022       ]
  1023   1023     }
  1024   1024   }
         1025  +
         1026  +proc explain_i {sql {db db}} {
         1027  +  puts ""
         1028  +  puts "addr  opcode        p1      p2      p3      p4                p5  #"
         1029  +  puts "----  ------------  ------  ------  ------  ----------------  --  -"
         1030  +
         1031  +  set addrTail 0
         1032  +
         1033  +  $db eval "explain $sql" {} {
         1034  +    set x($addr) 0
         1035  +    set op($addr) $opcode
         1036  +
         1037  +    if {$opcode == "Goto" && $addrTail==0} {
         1038  +      set addrTail $p2
         1039  +    }
         1040  +
         1041  +    if {$opcode == "Next"} {
         1042  +      for {set i $p2} {$i<$addr} {incr i} {
         1043  +        incr x($i) 2
         1044  +      }
         1045  +    }
         1046  +
         1047  +    if {$opcode == "Goto" && $p2<$addr && $op($p2)=="Yield"} {
         1048  +      for {set i [expr $p2+1]} {$i<$addr} {incr i} {
         1049  +        incr x($i) 2
         1050  +      }
         1051  +    }
         1052  +  }
         1053  +
         1054  +  $db eval "explain $sql" {} {
         1055  +    if {$addr == $addrTail} {
         1056  +      puts ""
         1057  +    }
         1058  +    set I [string repeat " " $x($addr)]
         1059  +    puts [format {%-4d  %s%-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
         1060  +      $addr $I $opcode $p1 $p2 $p3 $p4 $p5 $comment
         1061  +    ]
         1062  +
         1063  +    if {$opcode == "Halt" && $comment == "End of coroutine"} {
         1064  +      puts ""
         1065  +    }
         1066  +  }
         1067  +  puts "----  ------------  ------  ------  ------  ----------------  --  -"
         1068  +}
  1025   1069   
  1026   1070   # Show the VDBE program for an SQL statement but omit the Trace
  1027   1071   # opcode at the beginning.  This procedure can be used to prove
  1028   1072   # that different SQL statements generate exactly the same VDBE code.
  1029   1073   #
  1030   1074   proc explain_no_trace {sql} {
  1031   1075     set tr [db eval "EXPLAIN $sql"]

Changes to test/without_rowid1.test.

    11     11   #
    12     12   # This file implements regression tests for SQLite library.  The
    13     13   # focus of this file is testing WITHOUT ROWID tables.
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
           18  +set testprefix without_rowid1
    18     19   
    19     20   # Create and query a WITHOUT ROWID table.
    20     21   #
    21     22   do_execsql_test without_rowid1-1.0 {
    22     23     CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
    23     24     CREATE INDEX t1bd ON t1(b, d);
    24     25     INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
................................................................................
   103    104     } {t1 t1 t1 t1bd}
   104    105   }
   105    106   ifcapable stat4 {
   106    107     do_execsql_test without_rowid1-1.52 {
   107    108       SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
   108    109     } {t1 t1 t1 t1bd}
   109    110   }
          111  +
          112  +#----------
          113  +
          114  +do_execsql_test 2.1.1 {
          115  +  CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
          116  +  INSERT INTO t4 VALUES('abc', 'def');
          117  +  SELECT * FROM t4;
          118  +} {abc def}
          119  +do_execsql_test 2.1.2 {
          120  +  UPDATE t4 SET a = 'ABC';
          121  +  SELECT * FROM t4;
          122  +} {ABC def}
          123  +
          124  +do_execsql_test 2.2.1 {
          125  +  DROP TABLE t4;
          126  +  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
          127  +  INSERT INTO t4(a, b) VALUES('abc', 'def');
          128  +  SELECT * FROM t4;
          129  +} {def abc}
          130  +
          131  +do_execsql_test 2.2.2 {
          132  +  UPDATE t4 SET a = 'ABC', b = 'xyz';
          133  +  SELECT * FROM t4;
          134  +} {xyz ABC}
          135  +
          136  +do_execsql_test 2.3.1 {
          137  +  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
          138  +  INSERT INTO t5(a, b) VALUES('abc', 'def');
          139  +  UPDATE t5 SET a='abc', b='def';
          140  +} {}
          141  +
          142  +do_execsql_test 2.4.1 {
          143  +  CREATE TABLE t6 (
          144  +    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
          145  +  ) WITHOUT ROWID;
          146  +
          147  +  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
          148  +  UPDATE t6 SET a='ABC', c='ghi';
          149  +} {}
          150  +
          151  +do_execsql_test 2.4.2 {
          152  +  SELECT * FROM t6 ORDER BY b, a;
          153  +  SELECT * FROM t6 ORDER BY c;
          154  +} {ABC def ghi ABC def ghi}
          155  +
   110    156   
   111    157   finish_test
          158  +