SQLite

Check-in [fc18cc9293]
Login

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
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: fc18cc9293fb0080b7152c16baac49f44e2db7b3
User & Date: dan 2016-03-09 08:07:31.577
Context
2016-03-09
08:08
Merge latest trunk changes with this branch. (check-in: 59caca4387 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: fc18cc9293 user: dan tags: schemalint)
2016-02-22
19:51
Add test script shell6.test, containing tests for schemalint. (check-in: 0b73406595 user: dan tags: schemalint)
Changes
Unified Diff Ignore Whitespace Patch
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

  }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) ){








<










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








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

  }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) ){
︙︙
795
796
797
798
799
800
801

802
803
804
805
806
807
808
809





810
811
812
813
814
815
816

  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);









>








>
>
>
>
>








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

  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);
︙︙
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

          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);








|
>
|
>










>
>
>
>
>
>
>
>
>
>
|
|
>








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

          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);
︙︙
863
864
865
866
867
868
869


870
871
872
873
874
875
876

    }
  }

 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








>
>








897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912

    }
  }

 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
︙︙
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921

        , 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);








|








943
944
945
946
947
948
949
950
951
952
953
954
955
956
957

        , 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