SQLite

Changes On Branch tkt-2326c258
Login

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

Changes In Branch tkt-2326c258 Excluding Merge-Ins

This is equivalent to a diff from f8917ba4 to c6399958

2015-02-24
20:04
Make sure partial indexes are not qualified incorrectly by a constraint that is inside the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead33]. (check-in: 491cfe9b user: drh tags: trunk)
18:39
More test cases to help ensure that partial indexes do not get used if their qualifing constraint is inside the ON clause of a LEFT JOIN. (Closed-Leaf check-in: c6399958 user: drh tags: tkt-2326c258)
16:48
This additional fix prevents a partial index from being qualified for use if the constraint that qualifies the partial index is part of the ON clause of a LEFT JOIN. (check-in: 1a1516e4 user: drh tags: tkt-2326c258)
16:05
Make sure partial automatic indexes are not based on terms in the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead3]. UPDATE: This fix does not work where the partial index is a named index. (check-in: c0f4e308 user: drh tags: tkt-2326c258)
2015-02-23
16:17
Merge latest trunk changes with this branch. (check-in: e5ca79d2 user: dan tags: ota-update)
2015-02-21
15:42
Update document on sqlite3_mprintf() and related functions. Discuss the %w format and point out that obscure ANSI-C formats are not supported. No changes to code. (check-in: f8917ba4 user: drh tags: trunk)
00:56
Fix a compiler warning associated with USE_PREAD64. (check-in: c299e55a user: drh tags: trunk)

Changes to src/where.c.

1610
1611
1612
1613
1614
1615
1616

1617
1618
1619
1620
1621
1622
1623
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624







+







  pTable = pSrc->pTab;
  pWCEnd = &pWC->a[pWC->nTerm];
  pLoop = pLevel->pWLoop;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( pLoop->prereq==0
     && (pTerm->wtFlags & TERM_VIRTUAL)==0
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
     && sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
      pPartial = sqlite3ExprAnd(pParse->db, pPartial,
                                sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
    }
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
4690
4691
4692
4693
4694
4695
4696
4697





4698
4699
4700
4701
4702
4703
4704
4691
4692
4693
4694
4695
4696
4697

4698
4699
4700
4701
4702
4703
4704
4705
4706
4707
4708
4709







-
+
+
+
+
+







/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query.  Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
  int i;
  WhereTerm *pTerm;
  for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab) ) return 1;
    if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab)
     && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
    ){
      return 1;
    }
  }
  return 0;
}

/*
** Add all WhereLoop objects for a single table of the join where the table
** is idenfied by pBuilder->pNew->iTab.  That table is guaranteed to be

Changes to test/autoindex4.test.

44
45
46
47
48
49
50































51
52
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







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


  CREATE TABLE t3(e,f);
  INSERT INTO t3 VALUES(123,654),(555,444),(234,987);

  SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
    FROM t3
   ORDER BY rowid;
} {1 123 654 | 0 555 444 | 4 234 987 |}

# Ticket [2326c258d02ead33d]
# Two joins, one with and the other without an ORDER BY clause.
# The one without ORDER BY correctly returns two rows of result.
# The one with ORDER BY returns no rows. 
#
do_execsql_test autoindex4-3.0 {
  CREATE TABLE A(Name text);
  CREATE TABLE Items(ItemName text , Name text);
  INSERT INTO Items VALUES('Item1','Parent');
  INSERT INTO Items VALUES('Item2','Parent');
  CREATE TABLE B(Name text);
  
  SELECT Items.ItemName
    FROM Items
      LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.1 {
  CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
  
  SELECT Items.ItemName
    FROM Items
      LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
      LEFT JOIN B ON (B.Name = Items.ItemName)
    WHERE Items.Name = 'Parent'
    ORDER BY Items.ItemName;
} {Item1 Item2}


finish_test

Changes to test/index6.test.

263
264
265
266
267
268
269




























270
271
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


  UPDATE OR REPLACE t6 SET b=789;
  SELECT * FROM t6;
} {123 789}
do_execsql_test index6-6.2 {
  PRAGMA integrity_check;
} {ok}

# Test case for ticket [2326c258d02ead33d69faa63de8f4686b9b1b9d9] on
# 2015-02-24.  Any use of a partial index qualifying constraint inside
# the ON clause of a LEFT JOIN was causing incorrect results for all
# versions of SQLite 3.8.0 through 3.8.8.
#
do_execsql_test index6-7.0 {
  CREATE TABLE t7a(x);
  CREATE TABLE t7b(y);
  INSERT INTO t7a(x) VALUES(1);
  CREATE INDEX t7ax ON t7a(x) WHERE x=99;
  PRAGMA automatic_index=OFF;
  SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
} {1 {}}
do_execsql_test index6-7.1 {
  INSERT INTO t7b(y) VALUES(2);
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {}
do_execsql_test index6-7.2 {
  INSERT INTO t7a(x) VALUES(99);
  SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
} {1 {} 99 2}
do_execsql_test index6-7.3 {
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
  EXPLAIN QUERY PLAN
  SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}

finish_test