Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem in the schemalint code that comes up when a sub-query uses one or more of the same tables as its parent. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | schemalint |
| Files: | files | file ages | folders |
| SHA1: |
fc18cc9293fb0080b7152c16baac49f4 |
| User & Date: | dan 2016-03-09 08:07:31 |
Context
|
2016-03-09
| ||
| 08:08 | Merge latest trunk changes with this branch. check-in: 59caca43 user: dan tags: schemalint | |
| 08:07 | Fix a problem in the schemalint code that comes up when a sub-query uses one or more of the same tables as its parent. check-in: fc18cc92 user: dan tags: schemalint | |
|
2016-02-22
| ||
| 19:51 | Add test script shell6.test, containing tests for schemalint. check-in: 0b734065 user: dan tags: schemalint | |
Changes
Changes to src/shell_indexes.c.
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 ... 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 ... 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 ... 863 864 865 866 867 868 869 870 871 872 873 874 875 876 ... 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 |
}else{
sqlite3_free(pNew);
}
return rc;
}
static int idxCreateTables(
sqlite3 *db, /* User database */
sqlite3 *dbm, /* In-memory database to create tables in */
IdxScan *pScan, /* List of scans */
char **pzErrmsg /* OUT: Error message */
){
int rc = SQLITE_OK;
IdxScan *pIter;
for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
rc = idxGetTableInfo(db, pIter, pzErrmsg);
if( rc==SQLITE_OK ){
int rc2;
sqlite3_stmt *pSql = 0;
rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg,
"SELECT sql FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
);
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
................................................................................
void (*xOut)(void*, const char*), /* Output callback */
void *pOutCtx, /* Context for xOut() */
char **pzErr /* OUT: Error message (sqlite3_malloc) */
){
sqlite3 *dbm = pCtx->dbm;
sqlite3_stmt *pExplain = 0;
sqlite3_stmt *pSelect = 0;
int rc, rc2;
int bFound = 0;
rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql);
if( rc==SQLITE_OK ){
rc = idxPrepareStmt(dbm, &pSelect, pzErr,
"SELECT rowid, sql FROM sqlite_master WHERE name = ?"
);
}
while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
int i;
const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
int nDetail = strlen(zDetail);
................................................................................
nIdx++;
}
sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
if( SQLITE_ROW==sqlite3_step(pSelect) ){
i64 iRowid = sqlite3_column_int64(pSelect, 0);
const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
if( iRowid>=pCtx->iIdxRowid ){
xOut(pOutCtx, zSql);
bFound = 1;
}
}
rc = sqlite3_reset(pSelect);
break;
}
}
}
rc2 = sqlite3_reset(pExplain);
if( rc==SQLITE_OK ) rc = rc2;
if( rc==SQLITE_OK ){
if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
xOut(pOutCtx, "");
}
while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
int iSelectid = sqlite3_column_int(pExplain, 0);
int iOrder = sqlite3_column_int(pExplain, 1);
int iFrom = sqlite3_column_int(pExplain, 2);
const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
................................................................................
}
}
find_indexes_out:
rc2 = sqlite3_finalize(pExplain);
if( rc==SQLITE_OK ) rc = rc2;
rc2 = sqlite3_finalize(pSelect);
if( rc==SQLITE_OK ) rc = rc2;
return rc;
}
/*
** The xOut callback is invoked to return command output to the user. The
................................................................................
, 0, 0, pzErrmsg
);
}
/* Prepare an INSERT statement for writing to aux.depmask */
if( rc==SQLITE_OK ){
rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg,
"INSERT OR IGNORE INTO depmask SELECT mask | ?1 FROM depmask;"
);
}
/* Analyze the SELECT statement in zSql. */
if( rc==SQLITE_OK ){
ctx.dbm = dbm;
sqlite3_db_config(db, SQLITE_DBCONFIG_WHEREINFO, idxWhereInfo, (void*)&ctx);
|
< > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > > > > > > > | | > > > | |
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 ... 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 ... 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 ... 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 ... 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 |
}else{
sqlite3_free(pNew);
}
return rc;
}
static int idxCreateTables(
sqlite3 *db, /* User database */
sqlite3 *dbm, /* In-memory database to create tables in */
IdxScan *pScan, /* List of scans */
char **pzErrmsg /* OUT: Error message */
){
int rc = SQLITE_OK;
IdxScan *pIter;
for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
rc = idxGetTableInfo(db, pIter, pzErrmsg);
/* Test if table has already been created. If so, jump to the next
** iteration of the loop. */
if( rc==SQLITE_OK ){
sqlite3_stmt *pSql = 0;
rc = idxPrintfPrepareStmt(dbm, &pSql, pzErrmsg,
"SELECT 1 FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
);
if( rc==SQLITE_OK ){
int bSkip = 0;
if( sqlite3_step(pSql)==SQLITE_ROW ) bSkip = 1;
rc = sqlite3_finalize(pSql);
if( bSkip ) continue;
}
}
if( rc==SQLITE_OK ){
int rc2;
sqlite3_stmt *pSql = 0;
rc = idxPrintfPrepareStmt(db, &pSql, pzErrmsg,
"SELECT sql FROM sqlite_master WHERE tbl_name = %Q", pIter->zTable
);
while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
................................................................................
void (*xOut)(void*, const char*), /* Output callback */
void *pOutCtx, /* Context for xOut() */
char **pzErr /* OUT: Error message (sqlite3_malloc) */
){
sqlite3 *dbm = pCtx->dbm;
sqlite3_stmt *pExplain = 0;
sqlite3_stmt *pSelect = 0;
sqlite3_stmt *pInsert = 0;
int rc, rc2;
int bFound = 0;
rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql);
if( rc==SQLITE_OK ){
rc = idxPrepareStmt(dbm, &pSelect, pzErr,
"SELECT rowid, sql FROM sqlite_master WHERE name = ?"
);
}
if( rc==SQLITE_OK ){
rc = idxPrepareStmt(dbm, &pInsert, pzErr,
"INSERT OR IGNORE INTO aux.indexes VALUES(?)"
);
}
while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
int i;
const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
int nDetail = strlen(zDetail);
................................................................................
nIdx++;
}
sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
if( SQLITE_ROW==sqlite3_step(pSelect) ){
i64 iRowid = sqlite3_column_int64(pSelect, 0);
const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
if( iRowid>=pCtx->iIdxRowid ){
sqlite3_bind_text(pInsert, 1, zSql, -1, SQLITE_STATIC);
sqlite3_step(pInsert);
rc = sqlite3_reset(pInsert);
if( rc ) goto find_indexes_out;
}
}
rc = sqlite3_reset(pSelect);
break;
}
}
}
rc2 = sqlite3_reset(pExplain);
if( rc==SQLITE_OK ) rc = rc2;
if( rc==SQLITE_OK ){
sqlite3_stmt *pLoop = 0;
rc = idxPrepareStmt(dbm, &pLoop, pzErr, "SELECT name FROM aux.indexes");
if( rc==SQLITE_OK ){
while( SQLITE_ROW==sqlite3_step(pLoop) ){
bFound = 1;
xOut(pOutCtx, sqlite3_column_text(pLoop, 0));
}
rc = sqlite3_finalize(pLoop);
}
if( rc==SQLITE_OK ){
if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
xOut(pOutCtx, "");
}
}
while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
int iSelectid = sqlite3_column_int(pExplain, 0);
int iOrder = sqlite3_column_int(pExplain, 1);
int iFrom = sqlite3_column_int(pExplain, 2);
const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
................................................................................
}
}
find_indexes_out:
rc2 = sqlite3_finalize(pExplain);
if( rc==SQLITE_OK ) rc = rc2;
rc2 = sqlite3_finalize(pSelect);
if( rc==SQLITE_OK ) rc = rc2;
rc2 = sqlite3_finalize(pInsert);
if( rc==SQLITE_OK ) rc = rc2;
return rc;
}
/*
** The xOut callback is invoked to return command output to the user. The
................................................................................
, 0, 0, pzErrmsg
);
}
/* Prepare an INSERT statement for writing to aux.depmask */
if( rc==SQLITE_OK ){
rc = idxPrepareStmt(dbm, &ctx.pInsertMask, pzErrmsg,
"INSERT OR IGNORE INTO aux.depmask SELECT mask | ?1 FROM aux.depmask;"
);
}
/* Analyze the SELECT statement in zSql. */
if( rc==SQLITE_OK ){
ctx.dbm = dbm;
sqlite3_db_config(db, SQLITE_DBCONFIG_WHEREINFO, idxWhereInfo, (void*)&ctx);
|
Changes to src/where.c.
3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 3997 3998 3999 4000 |
/* ORDER BY callbacks */
if( p->pOrderBy ){
int i;
int bFirst = 1;
for(i=0; i<p->pOrderBy->nExpr; i++){
Expr *pExpr = p->pOrderBy->a[i].pExpr;
CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
assert( pColl || pParse->rc );
pExpr = sqlite3ExprSkipCollate(pExpr);
if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
int iCol = pExpr->iColumn;
if( pColl && iCol>=0 ){
int bDesc = p->pOrderBy->a[i].sortOrder;
x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, bDesc);
}
|
< |
3986 3987 3988 3989 3990 3991 3992 3993 3994 3995 3996 3997 3998 3999 |
/* ORDER BY callbacks */
if( p->pOrderBy ){
int i;
int bFirst = 1;
for(i=0; i<p->pOrderBy->nExpr; i++){
Expr *pExpr = p->pOrderBy->a[i].pExpr;
CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr);
pExpr = sqlite3ExprSkipCollate(pExpr);
if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){
int iCol = pExpr->iColumn;
if( pColl && iCol>=0 ){
int bDesc = p->pOrderBy->a[i].sortOrder;
x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, bDesc);
}
|
Deleted test/schemalint.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix schemalint proc xTrace {zMsg} { lappend ::trace_out $zMsg } db trace xTrace proc do_trace_test {tn sql res} { uplevel [list do_test $tn [subst -nocommands { set ::trace_out [list] set stmt [sqlite3_prepare db "$sql" -1 x] sqlite3_finalize [set stmt] set ::trace_out }] [list {*}$res]] } do_execsql_test 1.0 { CREATE TABLE t1(a, b, c); CREATE TABLE t2(x, y, z); } do_trace_test 1.1 { SELECT b, c, y, z FROM t1, t2 WHERE c=? AND z=? } { {"t1" {cols "b" "c"} {eq "c" "BINARY" 0}} {"t2" {cols "y" "z"} {eq "z" "BINARY" 0}} } do_trace_test 1.2 { SELECT a FROM t1 WHERE b>10 } { {"t1" {cols "a" "b"} {range "b" "BINARY" 0}} } do_trace_test 1.3 { SELECT b FROM t1 WHERE b IN (10, 20, 30) } { {"t1" {cols "b"} {eq "b" "BINARY" 0}} } do_trace_test 1.4 { SELECT * FROM t1, t2 WHERE x=a } { {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 2}} {"t2" {cols "x" "y" "z"} {eq "x" "BINARY" 1}} } do_trace_test 1.5 { SELECT * FROM t1 WHERE a IN (1, 2, 3) } { {"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0}} } #----------------------------------------------------------------------- # Cases involving OR clauses in the WHERE clause. # do_trace_test 2.1 { SELECT * FROM t1 WHERE a=? OR b=? } { {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0}}}} } do_trace_test 2.2 { SELECT * FROM t1 WHERE a=? OR (b=? AND c=?) } { {"t1" {cols "a" "b" "c"} {or {{eq "a" "BINARY" 0}} {{eq "b" "BINARY" 0} {eq "c" "BINARY" 0}}}} } do_trace_test 2.3 { SELECT * FROM t1 WHERE (a=? AND b=?) OR c=? } { {"t1" {cols "a" "b" "c"} {or {{eq "c" "BINARY" 0}} {{eq "a" "BINARY" 0} {eq "b" "BINARY" 0}}}} } #----------------------------------------------------------------------- # Cases involving ORDER BY. # do_trace_test 3.1 { SELECT * FROM t1 ORDER BY a; } {{"t1" {cols "a" "b" "c"} {orderby "a" "BINARY" ASC}}} do_trace_test 3.2 { SELECT * FROM t1 WHERE a=? ORDER BY b; } {{"t1" {cols "a" "b" "c"} {eq "a" "BINARY" 0} {orderby "b" "BINARY" ASC}}} do_trace_test 3.3 { SELECT min(a) FROM t1; } {{"t1" {cols "a"} {orderby "a" "BINARY" ASC}}} do_trace_test 3.4 { SELECT max(a) FROM t1; } {{"t1" {cols "a"} {orderby "a" "BINARY" DESC}}} finish_test |
< < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
Changes to test/shell6.test.
178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
#
do_setup_rec_test 10.1 {
CREATE TABLE t5(a, b);
CREATE TABLE t6(c, d);
} {
SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
CREATE INDEX t6_idx_00000063 ON t6(c)
CREATE INDEX t5_idx_000123a7 ON t5(a, b)
0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}
finish_test
|
< > |
178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
#
do_setup_rec_test 10.1 {
CREATE TABLE t5(a, b);
CREATE TABLE t6(c, d);
} {
SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
CREATE INDEX t5_idx_000123a7 ON t5(a, b)
CREATE INDEX t6_idx_00000063 ON t6(c)
0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?)
0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}
finish_test
|