Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Avoid spurious "no such table" errors in statements of the form "INSERT INTO tbl WITH xxx AS (...) SELECT * FROM xxx". |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
cccff8a0b427feb05cc8952a765b829e |
| User & Date: | dan 2014-01-18 08:27:02.507 |
Context
|
2014-01-18
| ||
| 15:22 | Add asserts() for a couple of unreachable conditions. Add the Mandelbrot Set query as a test case. (check-in: 2ad4583c0c user: drh tags: trunk) | |
| 08:27 | Avoid spurious "no such table" errors in statements of the form "INSERT INTO tbl WITH xxx AS (...) SELECT * FROM xxx". (check-in: cccff8a0b4 user: dan tags: trunk) | |
|
2014-01-17
| ||
| 20:36 | Add extra tests to with2.test. (check-in: eecc325afd user: dan tags: trunk) | |
Changes
Changes to src/insert.c.
| ︙ | ︙ | |||
663 664 665 666 667 668 669 | ** INSERT INTO <table1> SELECT * FROM <table2>; ** ** Then special optimizations can be applied that make the transfer ** very fast and which reduce fragmentation of indices. ** ** This is the 2nd template. */ | < | | 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 |
** INSERT INTO <table1> SELECT * FROM <table2>;
**
** Then special optimizations can be applied that make the transfer
** very fast and which reduce fragmentation of indices.
**
** This is the 2nd template.
*/
if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
assert( !pTrigger );
assert( pList==0 );
goto insert_end;
}
#endif /* SQLITE_OMIT_XFER_OPT */
/* If this is an AUTOINCREMENT table, look up the sequence number in the
|
| ︙ | ︙ | |||
1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 |
Vdbe *v; /* The VDBE we are building */
int regAutoinc; /* Memory register used by AUTOINC */
int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
int regData, regRowid; /* Registers holding data and rowid */
if( pSelect==0 ){
return 0; /* Must be of the form INSERT INTO ... SELECT ... */
}
if( sqlite3TriggerList(pParse, pDest) ){
return 0; /* tab1 must not have triggers */
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( pDest->tabFlags & TF_Virtual ){
return 0; /* tab1 must not be a virtual table */
| > > > > > > | 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 |
Vdbe *v; /* The VDBE we are building */
int regAutoinc; /* Memory register used by AUTOINC */
int destHasUniqueIdx = 0; /* True if pDest has a UNIQUE index */
int regData, regRowid; /* Registers holding data and rowid */
if( pSelect==0 ){
return 0; /* Must be of the form INSERT INTO ... SELECT ... */
}
if( pParse->pWith || pSelect->pWith ){
/* Do not attempt to process this query if there are an WITH clauses
** attached to it. Proceeding may generate a false "no such table: xxx"
** error if pSelect reads from a CTE named "xxx". */
return 0;
}
if( sqlite3TriggerList(pParse, pDest) ){
return 0; /* tab1 must not have triggers */
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( pDest->tabFlags & TF_Virtual ){
return 0; /* tab1 must not be a virtual table */
|
| ︙ | ︙ |
Changes to test/with2.test.
| ︙ | ︙ | |||
170 171 172 173 174 175 176 177 178 179 |
do_execsql_test 4.2 [genstmt 10] 1
do_execsql_test 4.3 [genstmt 100] 1
do_execsql_test 4.4 [genstmt 255] 1
set nLimit [sqlite3_limit db SQLITE_LIMIT_COLUMN -1]
do_execsql_test 4.5 [genstmt [expr $nLimit-1]] 1
do_execsql_test 4.6 [genstmt $nLimit] 1
do_catchsql_test 4.7 [genstmt [expr $nLimit+1]] {1 {too many columns in index}}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 |
do_execsql_test 4.2 [genstmt 10] 1
do_execsql_test 4.3 [genstmt 100] 1
do_execsql_test 4.4 [genstmt 255] 1
set nLimit [sqlite3_limit db SQLITE_LIMIT_COLUMN -1]
do_execsql_test 4.5 [genstmt [expr $nLimit-1]] 1
do_execsql_test 4.6 [genstmt $nLimit] 1
do_catchsql_test 4.7 [genstmt [expr $nLimit+1]] {1 {too many columns in index}}
#---------------------------------------------------------------------------
# Check that adding a WITH clause to an INSERT disables the xfer
# optimization.
#
proc do_xfer_test {tn bXfer sql {res {}}} {
set ::sqlite3_xferopt_count 0
uplevel [list do_test $tn [subst -nocommands {
set dres [db eval {$sql}]
list [set ::sqlite3_xferopt_count] [set dres]
}] [list $bXfer $res]]
}
do_execsql_test 5.1 {
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(a, b);
CREATE TABLE t2(a, b);
}
do_xfer_test 5.2 1 { INSERT INTO t1 SELECT * FROM t2 }
do_xfer_test 5.3 0 { INSERT INTO t1 SELECT a, b FROM t2 }
do_xfer_test 5.4 0 { INSERT INTO t1 SELECT b, a FROM t2 }
do_xfer_test 5.5 0 {
WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM x
}
do_xfer_test 5.6 0 {
WITH x AS (SELECT a, b FROM t2) INSERT INTO t1 SELECT * FROM t2
}
do_xfer_test 5.7 0 {
INSERT INTO t1 WITH x AS ( SELECT * FROM t2 ) SELECT * FROM x
}
do_xfer_test 5.8 0 {
INSERT INTO t1 WITH x(a,b) AS ( SELECT * FROM t2 ) SELECT * FROM x
}
finish_test
|