SQLite

Check-in [b0450120ea]
Login

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

Overview
Comment:Fix long-standing bugs with the handling of LIMIT clausing in compound SELECT statements with FROM clause subqueries. Ticket [38cb5df375078d3f9].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b0450120eabfe5455cea1ed04cec587a5540b6b7
User & Date: drh 2010-10-06 18:55:37.000
Context
2010-10-06
20:25
Update the configure script and Makefile so that they work with 3.7.3. (check-in: 97b63a1168 user: drh tags: trunk)
18:55
Fix long-standing bugs with the handling of LIMIT clausing in compound SELECT statements with FROM clause subqueries. Ticket [38cb5df375078d3f9]. (check-in: b0450120ea user: drh tags: trunk)
16:42
Fix minor test suite problems causing errors on OSX. (check-in: 759c954fee user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
    addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit);
    sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
    addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addr1);
    sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor);
    sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor);
    sqlite3VdbeJumpHere(v, addr2);
    pSelect->iLimit = 0;
  }
}

/*
** Add code to implement the OFFSET
*/
static void codeOffset(







<







438
439
440
441
442
443
444

445
446
447
448
449
450
451
    addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit);
    sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
    addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, addr1);
    sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor);
    sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor);
    sqlite3VdbeJumpHere(v, addr2);

  }
}

/*
** Add code to implement the OFFSET
*/
static void codeOffset(
717
718
719
720
721
722
723
724


725
726
727
728
729
730
731
732
733
734
735
    default: {
      assert( eDest==SRT_Discard );
      break;
    }
#endif
  }

  /* Jump to the end of the loop if the LIMIT is reached.


  */
  if( p->iLimit ){
    assert( pOrderBy==0 );  /* If there is an ORDER BY, the call to
                            ** pushOntoSorter() would have cleared p->iLimit */
    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  }
}

/*
** Given an expression list, generate a KeyInfo structure that records
** the collating sequence for each expression in that expression list.







|
>
>

|
<
<







716
717
718
719
720
721
722
723
724
725
726
727


728
729
730
731
732
733
734
    default: {
      assert( eDest==SRT_Discard );
      break;
    }
#endif
  }

  /* Jump to the end of the loop if the LIMIT is reached.  Except, if
  ** there is a sorter, in which case the sorter has already limited
  ** the output for us.
  */
  if( pOrderBy==0 && p->iLimit ){


    sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1);
  }
}

/*
** Given an expression list, generate a KeyInfo structure that records
** the collating sequence for each expression in that expression list.
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
      }
      break;
    }
  }
  sqlite3ReleaseTempReg(pParse, regRow);
  sqlite3ReleaseTempReg(pParse, regRowid);

  /* LIMIT has been implemented by the pushOntoSorter() routine.
  */
  assert( p->iLimit==0 );

  /* The bottom of the loop
  */
  sqlite3VdbeResolveLabel(v, addrContinue);
  sqlite3VdbeAddOp2(v, OP_Next, iTab, addr);
  sqlite3VdbeResolveLabel(v, addrBreak);
  if( eDest==SRT_Output || eDest==SRT_Coroutine ){
    sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0);







<
<
<
<







855
856
857
858
859
860
861




862
863
864
865
866
867
868
      }
      break;
    }
  }
  sqlite3ReleaseTempReg(pParse, regRow);
  sqlite3ReleaseTempReg(pParse, regRowid);





  /* The bottom of the loop
  */
  sqlite3VdbeResolveLabel(v, addrContinue);
  sqlite3VdbeAddOp2(v, OP_Next, iTab, addr);
  sqlite3VdbeResolveLabel(v, addrBreak);
  if( eDest==SRT_Output || eDest==SRT_Coroutine ){
    sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0);
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
      }
    }
  }
 
  /* Separate the left and the right query from one another
  */
  p->pPrior = 0;
  pPrior->pRightmost = 0;
  sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER");
  if( pPrior->pPrior==0 ){
    sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER");
  }

  /* Compute the limit registers */
  computeLimitRegisters(pParse, p, labelEnd);







<







2172
2173
2174
2175
2176
2177
2178

2179
2180
2181
2182
2183
2184
2185
      }
    }
  }
 
  /* Separate the left and the right query from one another
  */
  p->pPrior = 0;

  sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER");
  if( pPrior->pPrior==0 ){
    sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER");
  }

  /* Compute the limit registers */
  computeLimitRegisters(pParse, p, labelEnd);
Added test/tkt-38cb5df375.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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
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
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
# 2010 October 6
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. Specifically,
# it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
# been resolved.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_test tkt-38cb5df375.0 {
  execsql {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 SELECT a+2 FROM t1;
    INSERT INTO t1 SELECT a+4 FROM t1;
  }
} {}

foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.1.$ii {
    execsql {
      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.2.$ii {
    execsql {
      SELECT 9 FROM (SELECT * FROM t1)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
      LIMIT $::ii;
    }
  } [lrange {9 9 9 9 9 9 9 9 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.3.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.4.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      LIMIT $::ii;
    }
  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4} {
  do_test tkt-38cb5df375.5.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1)
      UNION SELECT 9 FROM (SELECT a FROM t1)
      LIMIT $::ii;
    }
  } [lrange {0 9} 0 [expr {$ii-1}]]
}

foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
  do_test tkt-38cb5df375.11.$ii {
    execsql {
      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      LIMIT $::ii;
    }
  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
  do_test tkt-38cb5df375.12.$ii {
    execsql {
      SELECT 9 FROM (SELECT * FROM t1)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
      LIMIT $::ii;
    }
  } [lrange {9 9 9 9 9 9 9 9 1 2 3} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6} {
  do_test tkt-38cb5df375.13.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
      LIMIT $::ii;
    }
  } [lrange {1 2 3 1 2 3} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6} {
  do_test tkt-38cb5df375.14.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 3)
      LIMIT $::ii;
    }
  } [lrange {0 0 0 9 9 9} 0 [expr {$ii-1}]]
}

foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.21.$ii {
    execsql {
      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.22.$ii {
    execsql {
      SELECT 9 FROM (SELECT * FROM t1)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.23.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
      ORDER BY 1 DESC
      LIMIT $::ii;
    }
  } [lrange {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
  do_test tkt-38cb5df375.24.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}

foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
  do_test tkt-38cb5df375.31.$ii {
    execsql {
      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
  do_test tkt-38cb5df375.32.$ii {
    execsql {
      SELECT 9 FROM (SELECT * FROM t1)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.33.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 4)
      UNION ALL SELECT 90+a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 91 92 93} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.34.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 2)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 5)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.35.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.35b.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.35c.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.35d.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
      INTERSECT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.35e.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {3 4 5} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.36.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {0 0 0 9 9 9 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.37.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
      UNION SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {0 9} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7} {
  do_test tkt-38cb5df375.38.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
      EXCEPT SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {0} 0 [expr {$ii-1}]]
}

foreach ii {1 2 3 4 5 6 7 8 9} {
  do_test tkt-38cb5df375.41.$ii {
    execsql {
      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
      UNION ALL SELECT 88 FROM (SELECT a FROM t1 LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {0 0 0 9 9 9 9 88 88} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9} {
  do_test tkt-38cb5df375.42.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
      UNION ALL SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
}
foreach ii {1 2 3 4 5 6 7 8 9} {
  do_test tkt-38cb5df375.43.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
      UNION SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
      ORDER BY 1
      LIMIT $::ii;
    }
  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
}

foreach ii {1 2 3 4 5 6 7} {
  set jj [expr {7-$ii}]
  do_test tkt-38cb5df375.51.$ii {
    execsql {
      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT $::ii)
      ORDER BY a DESC
      LIMIT $::jj;
    }
  } [lrange {8 7 6 5 4 3 2 1} 0 [expr {$jj-1}]]
}


finish_test