SQLite

Check-in [ec1e85a657]
Login

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

Overview
Comment:Fix handling the case where a sub-query in a FROM clause is itself a UNION ALL, and one side of that UNION ALL is a query on a view that includes an ORDER BY. Fix for ticket [190c2507].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | branch-3.16
Files: files | file ages | folders
SHA1: ec1e85a657a6fad77102c440326cfa0c788da12d
User & Date: dan 2017-01-05 17:25:53.835
Context
2017-01-05
17:27
Version 3.16.2 second release candidate (check-in: 285c5a54dd user: drh tags: branch-3.16)
17:25
Fix handling the case where a sub-query in a FROM clause is itself a UNION ALL, and one side of that UNION ALL is a query on a view that includes an ORDER BY. Fix for ticket [190c2507]. (check-in: ec1e85a657 user: dan tags: branch-3.16)
17:23
Fix handling the case where a sub-query in a FROM clause is itself a UNION ALL, and one side of that UNION ALL is a query on a view that includes an ORDER BY. Fix for ticket [190c2507]. (check-in: 590ca83b8e user: dan tags: trunk)
15:50
Version 3.16.2 release candidate (check-in: 9d8ffe13bd user: drh tags: branch-3.16)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753

    */
    u8 ecelFlags;
    if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){
      ecelFlags = SQLITE_ECEL_DUP;
    }else{
      ecelFlags = 0;
    }
    assert( eDest!=SRT_Table || pSort==0 );
    if( pSort && hasDistinct==0 && eDest!=SRT_EphemTab ){
      /* For each expression in pEList that is a copy of an expression in
      ** the ORDER BY clause (pSort->pOrderBy), set the associated 
      ** iOrderByCol value to one more than the index of the ORDER BY 
      ** expression within the sort-key that pushOntoSorter() will generate.
      ** This allows the pEList field to be omitted from the sorted record,
      ** saving space and CPU cycles.  */
      ecelFlags |= (SQLITE_ECEL_OMITREF|SQLITE_ECEL_REF);








<
|








738
739
740
741
742
743
744

745
746
747
748
749
750
751
752

    */
    u8 ecelFlags;
    if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){
      ecelFlags = SQLITE_ECEL_DUP;
    }else{
      ecelFlags = 0;
    }

    if( pSort && hasDistinct==0 && eDest!=SRT_EphemTab && eDest!=SRT_Table ){
      /* For each expression in pEList that is a copy of an expression in
      ** the ORDER BY clause (pSort->pOrderBy), set the associated 
      ** iOrderByCol value to one more than the index of the ORDER BY 
      ** expression within the sort-key that pushOntoSorter() will generate.
      ** This allows the pEList field to be omitted from the sorted record,
      ** saving space and CPU cycles.  */
      ecelFlags |= (SQLITE_ECEL_OMITREF|SQLITE_ECEL_REF);
︙︙
1281
1282
1283
1284
1285
1286
1287

1288
1289
1290
1291
1292
1293
1294

    }else{
      iRead = iCol++;
    }
    sqlite3VdbeAddOp3(v, OP_Column, iSortTab, iRead, regRow+i);
    VdbeComment((v, "%s", aOutEx[i].zName ? aOutEx[i].zName : aOutEx[i].zSpan));
  }
  switch( eDest ){

    case SRT_EphemTab: {
      sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
      sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
      sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY








>








1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294

    }else{
      iRead = iCol++;
    }
    sqlite3VdbeAddOp3(v, OP_Column, iSortTab, iRead, regRow+i);
    VdbeComment((v, "%s", aOutEx[i].zName ? aOutEx[i].zName : aOutEx[i].zSpan));
  }
  switch( eDest ){
    case SRT_Table:
    case SRT_EphemTab: {
      sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
      sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
      sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
︙︙
Changes to test/selectC.test.
︙︙
10
11
12
13
14
15
16

17
18
19
20
21
22
23

#***********************************************************************
# This file implements regression tests for SQLite library. 
#
# $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $

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


# Ticket #
do_test selectC-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    INSERT INTO t1 VALUES(1,'aaa','bbb');
    INSERT INTO t1 SELECT * FROM t1;








>








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#***********************************************************************
# This file implements regression tests for SQLite library. 
#
# $Id: selectC.test,v 1.5 2009/05/17 15:26:21 drh Exp $

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

# Ticket #
do_test selectC-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    INSERT INTO t1 VALUES(1,'aaa','bbb');
    INSERT INTO t1 SELECT * FROM t1;
︙︙
228
229
230
231
232
233
234
235

































236

do_execsql_test selectC-4.2 {
  select a from (select distinct a, b from t_distinct_bug)
} {1 1 1}

do_execsql_test selectC-4.3 {
  select a, udf() from (select distinct a, b from t_distinct_bug)
} {1 1 1 2 1 3}


































finish_test









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


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

do_execsql_test selectC-4.2 {
  select a from (select distinct a, b from t_distinct_bug)
} {1 1 1}

do_execsql_test selectC-4.3 {
  select a, udf() from (select distinct a, b from t_distinct_bug)
} {1 1 1 2 1 3}

#-------------------------------------------------------------------------
# Test that the problem in ticket #190c2507 has been fixed.
#
do_execsql_test 5.0 {
  CREATE TABLE x1(a);
  CREATE TABLE x2(b);
  CREATE TABLE x3(c);
  CREATE VIEW vvv AS SELECT b FROM x2 ORDER BY 1;

  INSERT INTO x1 VALUES('a'), ('b');
  INSERT INTO x2 VALUES(22), (23), (25), (24), (21);
  INSERT INTO x3 VALUES(302), (303), (301);
}

do_execsql_test 5.1 {
  CREATE TABLE x4 AS SELECT b FROM vvv UNION ALL SELECT c from x3;
  SELECT * FROM x4;
} {21 22 23 24 25 302 303 301}

do_execsql_test 5.2 {
  SELECT * FROM x1, x4
} {
  a 21 a 22 a 23 a 24 a 25 a 302 a 303 a 301
  b 21 b 22 b 23 b 24 b 25 b 302 b 303 b 301
}

do_execsql_test 5.3 {
  SELECT * FROM x1, (SELECT b FROM vvv UNION ALL SELECT c from x3);
} {
  a 21 a 22 a 23 a 24 a 25 a 302 a 303 a 301
  b 21 b 22 b 23 b 24 b 25 b 302 b 303 b 301
}

finish_test