SQLite

Check-in [1ed6b06e]
Login

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

Overview
Comment:When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1ed6b06ea3c432f920fb2b66b6042be906c5d21c
User & Date: drh 2016-03-09 15:09:22
Context
2016-03-09
15:14
Add another test case for bug [5e3c8867]. (check-in: d91e57e4 user: dan tags: trunk)
15:09
When optimizing expressions of the form "x IN (SELECT ...)" make sure that the subquery is not correlated. Fix for ticket [5e3c886796e5512]. (check-in: 1ed6b06e user: drh tags: trunk)
13:39
Automatically disable SQLITE_ENABLE_EXPLAIN_COMMENTS if SQLITE_OMIT_EXPLAIN is defined. (check-in: 8d4b6b2b user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

1580
1581
1582
1583
1584
1585
1586

1587
1588
1589
1590
1591
1592
1593
** errors have been found.
*/
#ifndef SQLITE_OMIT_SUBQUERY
static int isCandidateForInOpt(Select *p){
  SrcList *pSrc;
  ExprList *pEList;
  Table *pTab;

  if( p==0 ) return 0;                   /* right-hand side of IN is SELECT */
  if( p->pPrior ) return 0;              /* Not a compound SELECT */
  if( p->selFlags & (SF_Distinct|SF_Aggregate) ){
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
    return 0; /* No DISTINCT keyword and no aggregate functions */
  }







>







1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
** errors have been found.
*/
#ifndef SQLITE_OMIT_SUBQUERY
static int isCandidateForInOpt(Select *p){
  SrcList *pSrc;
  ExprList *pEList;
  Table *pTab;
  Expr *pRes;                            /* Result expression */
  if( p==0 ) return 0;                   /* right-hand side of IN is SELECT */
  if( p->pPrior ) return 0;              /* Not a compound SELECT */
  if( p->selFlags & (SF_Distinct|SF_Aggregate) ){
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
    testcase( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
    return 0; /* No DISTINCT keyword and no aggregate functions */
  }
1601
1602
1603
1604
1605
1606
1607

1608

1609
1610
1611
1612
1613
1614
1615
  if( pSrc->a[0].pSelect ) return 0;     /* FROM is not a subquery or view */
  pTab = pSrc->a[0].pTab;
  if( NEVER(pTab==0) ) return 0;
  assert( pTab->pSelect==0 );            /* FROM clause is not a view */
  if( IsVirtual(pTab) ) return 0;        /* FROM clause not a virtual table */
  pEList = p->pEList;
  if( pEList->nExpr!=1 ) return 0;       /* One column in the result set */

  if( pEList->a[0].pExpr->op!=TK_COLUMN ) return 0; /* Result is a column */

  return 1;
}
#endif /* SQLITE_OMIT_SUBQUERY */

/*
** Code an OP_Once instruction and allocate space for its flag. Return the 
** address of the new instruction.







>
|
>







1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
  if( pSrc->a[0].pSelect ) return 0;     /* FROM is not a subquery or view */
  pTab = pSrc->a[0].pTab;
  if( NEVER(pTab==0) ) return 0;
  assert( pTab->pSelect==0 );            /* FROM clause is not a view */
  if( IsVirtual(pTab) ) return 0;        /* FROM clause not a virtual table */
  pEList = p->pEList;
  if( pEList->nExpr!=1 ) return 0;       /* One column in the result set */
  pRes = pEList->a[0].pExpr;
  if( pRes->op!=TK_COLUMN ) return 0;    /* Result is a column */
  if( pRes->iTable!=pSrc->a[0].iCursor ) return 0;  /* Not a correlated subq */
  return 1;
}
#endif /* SQLITE_OMIT_SUBQUERY */

/*
** Code an OP_Once instruction and allocate space for its flag. Return the 
** address of the new instruction.

Changes to test/where2.test.

760
761
762
763
764
765
766











767
768
#
do_execsql_test where2-13.1 {
  CREATE TABLE t13(a,b);
  CREATE INDEX t13a ON t13(a);
  INSERT INTO t13 VALUES(4,5);
  SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4;
} {4 5}












finish_test







>
>
>
>
>
>
>
>
>
>
>


760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
#
do_execsql_test where2-13.1 {
  CREATE TABLE t13(a,b);
  CREATE INDEX t13a ON t13(a);
  INSERT INTO t13 VALUES(4,5);
  SELECT * FROM t13 WHERE (1=2 AND a=3) OR a=4;
} {4 5}

# https://www.sqlite.org/src/info/5e3c886796e5512e  (2016-03-09)
# Correlated subquery on the RHS of an IN operator 
#
do_execsql_test where2-14.1 {
  CREATE TABLE t14a(x INTEGER PRIMARY KEY);
  INSERT INTO t14a(x) VALUES(1),(2),(3),(4);
  CREATE TABLE t14b(y INTEGER PRIMARY KEY);
  INSERT INTO t14b(y) VALUES(1);
  SELECT x FROM t14a WHERE x NOT IN (SELECT x FROM t14b);
} {}

finish_test