/ Check-in [4062ddf3]
Login

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

Overview
Comment:Get the column affinities right when processing an IN operator where the right-hand side is a subquery with an ORDER BY clause. Ticket #2295. This bug has likely been present since version 3.0.0 and it has just now been noticed, so it seems to be a obscure case. (CVS 3837)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:4062ddf3c7f4fd150292304fa33ca76dc35571a1
User & Date: drh 2007-04-12 03:54:39
Context
2007-04-12
21:25
Changes toward fixes for tickets #2296 and #2291. (CVS 3838) check-in: 0dd3e2e4 user: drh tags: trunk
03:54
Get the column affinities right when processing an IN operator where the right-hand side is a subquery with an ORDER BY clause. Ticket #2295. This bug has likely been present since version 3.0.0 and it has just now been noticed, so it seems to be a obscure case. (CVS 3837) check-in: 4062ddf3 user: drh tags: trunk
2007-04-11
17:54
Blind check-in of changes to fix #2294 - I have no ability to test. Gentle wince users: please test this change and if it works leave a remark on the #2294 ticket. Tnx. (CVS 3836) check-in: ca56c3e2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
549
550
551
552
553
554
555

556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
...
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.333 2007/04/06 01:04:40 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
      int addr1 = sqlite3VdbeCurrentAddr(v);
      int addr2;

      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);

      if( pOrderBy ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pOrderBy, p);
      }else{
        char affinity = (iParm>>16)&0xFF;
        affinity = sqlite3CompareAffinity(pEList->a[0].pExpr, affinity);
        sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &affinity, 1);
        sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      }
      sqlite3VdbeJumpHere(v, addr2);
      break;
    }

    /* If any row exist in the result set, record that fact and abort.
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "c", P3_STATIC);
      sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */







|







 







>







<
<
|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563


564
565
566
567
568
569
570
571
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.334 2007/04/12 03:54:39 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
      int addr1 = sqlite3VdbeCurrentAddr(v);
      int addr2;

      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, addr1+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      addr2 = sqlite3VdbeAddOp(v, OP_Goto, 0, 0);
      p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr,(iParm>>16)&0xff);
      if( pOrderBy ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pOrderBy, p);
      }else{


        sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1);
        sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      }
      sqlite3VdbeJumpHere(v, addr2);
      break;
    }

    /* If any row exist in the result set, record that fact and abort.
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, &p->affinity, 1);
      sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1262
1263
1264
1265
1266
1267
1268

1269
1270
1271
1272
1273
1274
1275
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.550 2007/04/06 11:26:00 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

#if defined(SQLITE_TCL) || defined(TCLSH)
# include <tcl.h>
#endif
................................................................................
  ExprList *pEList;      /* The fields of the result */
  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
  u8 usesEphm;           /* True if uses an OpenEphemeral opcode */
  u8 disallowOrderBy;    /* Do not allow an ORDER BY to be attached if TRUE */

  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pRightmost;    /* Right-most select in a compound select statement */







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.551 2007/04/12 03:54:39 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

#if defined(SQLITE_TCL) || defined(TCLSH)
# include <tcl.h>
#endif
................................................................................
  ExprList *pEList;      /* The fields of the result */
  u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  u8 isDistinct;         /* True if the DISTINCT keyword is present */
  u8 isResolved;         /* True once sqlite3SelectResolve() has run. */
  u8 isAgg;              /* True if this is an aggregate query */
  u8 usesEphm;           /* True if uses an OpenEphemeral opcode */
  u8 disallowOrderBy;    /* Do not allow an ORDER BY to be attached if TRUE */
  char affinity;         /* MakeRecord with this affinity for SRT_Set */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pRightmost;    /* Right-most select in a compound select statement */

Changes to test/subselect.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
170
171
172
173
174
175
176
177
























178
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.13 2005/09/08 10:37:01 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
} {4}
do_test subselect-3.10 {
  execsql {
    SELECT x FROM t3 WHERE x IN
       (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
  }
} {4}

























finish_test







|







 








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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.14 2007/04/12 03:54:39 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
................................................................................
} {4}
do_test subselect-3.10 {
  execsql {
    SELECT x FROM t3 WHERE x IN
       (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
  }
} {4}

# Ticket #2295.
# Make sure type affinities work correctly on subqueries with
# an ORDER BY clause.
#
do_test subselect-4.1 {
  execsql {
    CREATE TABLE t4(a TEXT, b TEXT);
    INSERT INTO t4 VALUES('a','1');
    INSERT INTO t4 VALUES('b','2');
    INSERT INTO t4 VALUES('c','3');
    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b);
  }
} {a b c}
do_test subselect-4.2 {
  execsql {
    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1);
  }
} {a}
do_test subselect-4.3 {
  execsql {
    SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1);
  }
} {c}

finish_test