SQLite: Check-in [ec1e85a6]

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 \| SQL 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

Context

2017-01-05
17:27		Version 3.16.2 second release candidate (check-in: 285c5a54 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: ec1e85a6 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: 590ca83b user: dan tags: trunk)
15:50		Version 3.16.2 release candidate (check-in: 9d8ffe13 user: drh tags: branch-3.16)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

    */
    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);
................................................................................
    }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

    */
    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);
................................................................................
    }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.

#***********************************************************************
# 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;
................................................................................
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








>







 








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

#***********************************************************************
# 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;
................................................................................
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