/ Check-in [267492d3]
Login
Overview
Comment:Do not allow automatic indices for the RHS of IN expressions which are not correlated subqueries. Ticket [8011086c85c6c404014c9]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:267492d3a7eff7b7b24ed6bd6d6ff55148fe37f3
User & Date: drh 2010-07-28 02:53:37
References
2010-08-04
12:13
Backport fix [267492d3a7eff7b] for the performance regression caused by automatic indexing and reported by ticket [8011086c85c6c4040]. check-in: 3f367fe0 user: drh tags: branch-3.7.0
Context
2010-07-28
15:49
Add documentation for the SQLITE_STATUS_MALLOC_COUNT parameter to sqlite3_status(). check-in: dbfbdb60 user: drh tags: trunk
02:53
Do not allow automatic indices for the RHS of IN expressions which are not correlated subqueries. Ticket [8011086c85c6c404014c9] check-in: 267492d3 user: drh tags: trunk
2010-07-27
16:42
Update the makefiles to prefer TCL version 8.5 instead of 8.4. check-in: 8118de2a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

1508
1509
1510
1511
1512
1513
1514

1515
1516
1517
1518



1519
1520
1521

1522

1523
1524
1525
1526
1527
1528
1529
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.
    ** We will have to generate an ephemeral table to do the job.
    */

    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;



    }else if( pX->pLeft->iColumn<0 && !ExprHasAnyProperty(pX, EP_xIsSelect) ){
      eType = IN_INDEX_ROWID;
    }

    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);

  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif








>




>
>
>
|
|
|
>

>







1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.
    ** We will have to generate an ephemeral table to do the job.
    */
    double savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
    }else{
      testcase( pParse->nQueryLoop>(double)1 );
      pParse->nQueryLoop = (double)1;
      if( pX->pLeft->iColumn<0 && !ExprHasAnyProperty(pX, EP_xIsSelect) ){
        eType = IN_INDEX_ROWID;
      }
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif

Changes to test/autoindex1.test.

131
132
133
134
135
136
137
138






























139
      JOIN t4 AS x6 ON x6.a=x5.b
      JOIN t4 AS x7 ON x7.a=x6.b
      JOIN t4 AS x8 ON x8.a=x7.b
      JOIN t4 AS x9 ON x9.a=x8.b
      JOIN t4 AS x10 ON x10.a=x9.b;
  }
} {4087}































finish_test








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

131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
      JOIN t4 AS x6 ON x6.a=x5.b
      JOIN t4 AS x7 ON x7.a=x6.b
      JOIN t4 AS x8 ON x8.a=x7.b
      JOIN t4 AS x9 ON x9.a=x8.b
      JOIN t4 AS x10 ON x10.a=x9.b;
  }
} {4087}

# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_test autoindex1-500 {
  db eval {
    CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
    CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
  }
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
do_test autoindex1-501 {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}}
do_test autoindex1-502 {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a=123
       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
    

finish_test