SQLite

View Ticket
Login
Ticket Hash: d6b36be38a06c0525731e6357125e6a35ecc4b79
Title: Sub-select in LIMIT clause may access db without locks.
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2012-12-07 19:41:22
Version Found In: trunk
Description:
This problem affects a SELECT statement only if all the following are true:

<ul>
  <li> The FROM clause consists of a single sub-select.
  <li> The FROM clause sub-select cannot be flattened into its parent.
  <li> Either the LIMIT or OFFSET clause of the outer SELECT is also a
       sub-select.
</ul>

When SQLite processes a SELECT statement for which the FROM clause contains a
single sub-select that is not eligible for the flattening optimization, it
creates a co-routine that returns one row each time it is invoked to
implement the sub-select.

The vdbe code is:

<ul>
   <li> Jump to start of main routine,
   <li> Body of co-routine,
   <li> Main routine (invokes sub-routine).
   <li> VerifyCookie routine.
</ul>

Because of the way the SQL compiler works, the VerifyCookie routine is
currently being invoked the first time the co-routine is invoked, not by the
start of the main routine. If the main routine attempts to access the database
before the co-routine is invoked, then it is an error. This may occur if either
the LIMIT or the OFFSET expression of the main SELECT are themselves
sub-selects that access the database. For example:

<verbatim>
  CREATE TABLE abc(a, b, c);
  CREATE TABLE def(a, b, c);

  SELECT * FROM (SELECT * FROM def LIMIT 1 OFFSET 1) LIMIT (SELECT 20 FROM abc)
</verbatim>