/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
          x = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
          sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
          VdbeComment((v, "%s.%s", pTab->zName,
                       pTab->aCol[pPk->aiColumn[i]].zName));
        }
      }
      if( isUpdate ){
        if( pIdx->autoIndex==2 ){
          /* For a PRIMARY KEY index on a WITHOUT ROWID table, always conflict
          ** on an INSERT.  On an UPDATE, only conflict if the PRIMARY KEY
          ** has changed. */
          int addrPkConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
          for(i=0; i<pPk->nKeyCol-1; i++){
            x = pPk->aiColumn[i];
            sqlite3VdbeAddOp3(v, OP_Ne, regOldData+1+x,
                              addrPkConflict, regIdx+i);
          }
          x = pPk->aiColumn[i];
          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+1+x, addrUniqueOk, regIdx+i);
        }else{
          /* For a UNIQUE index on a WITHOUT ROWID table, conflict only if the
          ** PRIMARY KEY value of the match is different from the old
          ** PRIMARY KEY value from before the update. */
          int addrConflict = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
          for(i=0; i<pPk->nKeyCol-1; i++){
            sqlite3VdbeAddOp3(v, OP_Ne, regOldData+pPk->aiColumn[i]+1,
                              addrConflict, regR+i);
          }
          sqlite3VdbeAddOp3(v, OP_Eq, regOldData+pPk->aiColumn[i]+1,
                            addrUniqueOk, regR+i);
        }
      }
    }
    sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);

    /* Generate code that executes if the new index entry is not unique */
    assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<







1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530

1531
1532
1533
1534
1535
1536
1537
          x = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[i]);
          sqlite3VdbeAddOp3(v, OP_Column, iThisCur, x, regR+i);
          VdbeComment((v, "%s.%s", pTab->zName,
                       pTab->aCol[pPk->aiColumn[i]].zName));
        }
      }
      if( isUpdate ){
        /* If currently processing the PRIMARY KEY of a WITHOUT ROWID 
        ** table, only conflict if the new PRIMARY KEY values are actually
        ** different from the old.
        **
        ** For a UNIQUE index, only conflict if the PRIMARY KEY values
        ** of the matched index row are different from the original PRIMARY
        ** KEY values of this row before the update.  */
        char *p4; 
        int addrJump = sqlite3VdbeCurrentAddr(v)+pPk->nKeyCol;
        int op = OP_Ne;
        int regCmp = (pIdx->autoIndex==2 ? regIdx : regR);

        for(i=0; i<pPk->nKeyCol; i++){
          char *p4 = (char*)sqlite3LocateCollSeq(pParse, pPk->azColl[i]);
          x = pPk->aiColumn[i];
          if( i==(pPk->nKeyCol-1) ){
            addrJump = addrUniqueOk;
            op = OP_Eq;
          }
          sqlite3VdbeAddOp4(v, op, 
              regOldData+1+x, addrJump, regCmp+i, p4, P4_COLLSEQ
          );

        }
      }
    }
    sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn);

    /* Generate code that executes if the new index entry is not unique */
    assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail

Changes to test/tester.tcl.

1018
1019
1020
1021
1022
1023
1024












































1025
1026
1027
1028
1029
1030
1031
  puts "----  ------------  ------  ------  ------  ---------------  --  -"
  $db eval "explain $sql" {} {
    puts [format {%-4d  %-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
      $addr $opcode $p1 $p2 $p3 $p4 $p5 $comment
    ]
  }
}













































# Show the VDBE program for an SQL statement but omit the Trace
# opcode at the beginning.  This procedure can be used to prove
# that different SQL statements generate exactly the same VDBE code.
#
proc explain_no_trace {sql} {
  set tr [db eval "EXPLAIN $sql"]







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







1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
  puts "----  ------------  ------  ------  ------  ---------------  --  -"
  $db eval "explain $sql" {} {
    puts [format {%-4d  %-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
      $addr $opcode $p1 $p2 $p3 $p4 $p5 $comment
    ]
  }
}

proc explain_i {sql {db db}} {
  puts ""
  puts "addr  opcode        p1      p2      p3      p4                p5  #"
  puts "----  ------------  ------  ------  ------  ----------------  --  -"

  set addrTail 0

  $db eval "explain $sql" {} {
    set x($addr) 0
    set op($addr) $opcode

    if {$opcode == "Goto" && $addrTail==0} {
      set addrTail $p2
    }

    if {$opcode == "Next"} {
      for {set i $p2} {$i<$addr} {incr i} {
        incr x($i) 2
      }
    }

    if {$opcode == "Goto" && $p2<$addr && $op($p2)=="Yield"} {
      for {set i [expr $p2+1]} {$i<$addr} {incr i} {
        incr x($i) 2
      }
    }
  }

  $db eval "explain $sql" {} {
    if {$addr == $addrTail} {
      puts ""
    }
    set I [string repeat " " $x($addr)]
    puts [format {%-4d  %s%-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
      $addr $I $opcode $p1 $p2 $p3 $p4 $p5 $comment
    ]

    if {$opcode == "Halt" && $comment == "End of coroutine"} {
      puts ""
    }
  }
  puts "----  ------------  ------  ------  ------  ----------------  --  -"
}

# Show the VDBE program for an SQL statement but omit the Trace
# opcode at the beginning.  This procedure can be used to prove
# that different SQL statements generate exactly the same VDBE code.
#
proc explain_no_trace {sql} {
  set tr [db eval "EXPLAIN $sql"]

Changes to test/without_rowid1.test.

11
12
13
14
15
16
17

18
19
20
21
22
23
24
...
103
104
105
106
107
108
109
110













































111

#
# This file implements regression tests for SQLite library.  The
# focus of this file is testing WITHOUT ROWID tables.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# Create and query a WITHOUT ROWID table.
#
do_execsql_test without_rowid1-1.0 {
  CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
  CREATE INDEX t1bd ON t1(b, d);
  INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
................................................................................
  } {t1 t1 t1 t1bd}
}
ifcapable stat4 {
  do_execsql_test without_rowid1-1.52 {
    SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
  } {t1 t1 t1 t1bd}
}














































finish_test








>







 








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

>
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
#
# This file implements regression tests for SQLite library.  The
# focus of this file is testing WITHOUT ROWID tables.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix without_rowid1

# Create and query a WITHOUT ROWID table.
#
do_execsql_test without_rowid1-1.0 {
  CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
  CREATE INDEX t1bd ON t1(b, d);
  INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
................................................................................
  } {t1 t1 t1 t1bd}
}
ifcapable stat4 {
  do_execsql_test without_rowid1-1.52 {
    SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
  } {t1 t1 t1 t1bd}
}

#----------

do_execsql_test 2.1.1 {
  CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
  INSERT INTO t4 VALUES('abc', 'def');
  SELECT * FROM t4;
} {abc def}
do_execsql_test 2.1.2 {
  UPDATE t4 SET a = 'ABC';
  SELECT * FROM t4;
} {ABC def}

do_execsql_test 2.2.1 {
  DROP TABLE t4;
  CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
  INSERT INTO t4(a, b) VALUES('abc', 'def');
  SELECT * FROM t4;
} {def abc}

do_execsql_test 2.2.2 {
  UPDATE t4 SET a = 'ABC', b = 'xyz';
  SELECT * FROM t4;
} {xyz ABC}

do_execsql_test 2.3.1 {
  CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
  INSERT INTO t5(a, b) VALUES('abc', 'def');
  UPDATE t5 SET a='abc', b='def';
} {}

do_execsql_test 2.4.1 {
  CREATE TABLE t6 (
    a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
  ) WITHOUT ROWID;

  INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
  UPDATE t6 SET a='ABC', c='ghi';
} {}

do_execsql_test 2.4.2 {
  SELECT * FROM t6 ORDER BY b, a;
  SELECT * FROM t6 ORDER BY c;
} {ABC def ghi ABC def ghi}


finish_test