/ Check-in [66954bdd]
Login

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

Overview
Comment:Make sure compound queries inside a subquery only return a single result column. Ticket #2347. (CVS 3967)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 66954bdd81dabfb60306de8480b5477a4acb1d9e
User & Date: drh 2007-05-09 22:56:39
Context
2007-05-10
10:46
Add code to enforce the MAX_EXPR_DEPTH limit. (CVS 3968) check-in: 2c9c94a2 user: danielk1977 tags: trunk
2007-05-09
22:56
Make sure compound queries inside a subquery only return a single result column. Ticket #2347. (CVS 3967) check-in: 66954bdd user: drh tags: trunk
20:35
Fix a typo in a comment. Ticket #2348. (CVS 3966) check-in: c0dbac46 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
...
430
431
432
433
434
435
436















437
438
439
440
441
442
443
...
492
493
494
495
496
497
498




499
500
501
502
503
504
505
....
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
**    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.342 2007/05/08 13:58:28 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  sqlite3VdbeAddOp(v, OP_Distinct, iTab, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp(v, OP_Pop, N+1, 0);
  sqlite3VdbeAddOp(v, OP_Goto, 0, addrRepeat);
  VdbeComment((v, "# skip indistinct records"));
  sqlite3VdbeAddOp(v, OP_IdxInsert, iTab, 0);
}

















/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** If srcTab and nColumn are both zero, then the pEList expressions
** are evaluated in order to get the data for this row.  If nColumn>0
................................................................................
    assert( pEList!=0 );
    assert( pEList->nExpr==nColumn );
    codeDistinct(v, distinct, iContinue, nColumn);
    if( pOrderBy==0 ){
      codeOffset(v, p, iContinue, nColumn);
    }
  }





  switch( eDest ){
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
#ifndef SQLITE_OMIT_COMPOUND_SELECT
    case SRT_Union: {
................................................................................
  */
  if( pParse->nErr>0 ) goto select_end;

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( (eDest==SRT_Mem || eDest==SRT_Set) && pEList->nExpr>1 ){
    sqlite3ErrorMsg(pParse, "only a single result allowed for "
       "a SELECT that is part of an expression");
    goto select_end;
  }
#endif

  /* ORDER BY is ignored for some destinations.
  */
  if( IgnorableOrderby(eDest) ){







|







 







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







 







>
>
>
>







 







|
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
...
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
....
2904
2905
2906
2907
2908
2909
2910
2911


2912
2913
2914
2915
2916
2917
2918
**    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.343 2007/05/09 22:56:39 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  sqlite3VdbeAddOp(v, OP_Distinct, iTab, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp(v, OP_Pop, N+1, 0);
  sqlite3VdbeAddOp(v, OP_Goto, 0, addrRepeat);
  VdbeComment((v, "# skip indistinct records"));
  sqlite3VdbeAddOp(v, OP_IdxInsert, iTab, 0);
}

/*
** Generate an error message when a SELECT is used within a subexpression
** (example:  "a IN (SELECT * FROM table)") but it has more than 1 result
** column.  We do this in a subroutine because the error occurs in multiple
** places.
*/
static int checkForMultiColumnSelectError(Parse *pParse, int eDest, int nExpr){
  if( nExpr>1 && (eDest==SRT_Mem || eDest==SRT_Set) ){
    sqlite3ErrorMsg(pParse, "only a single result allowed for "
       "a SELECT that is part of an expression");
    return 1;
  }else{
    return 0;
  }
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** If srcTab and nColumn are both zero, then the pEList expressions
** are evaluated in order to get the data for this row.  If nColumn>0
................................................................................
    assert( pEList!=0 );
    assert( pEList->nExpr==nColumn );
    codeDistinct(v, distinct, iContinue, nColumn);
    if( pOrderBy==0 ){
      codeOffset(v, p, iContinue, nColumn);
    }
  }

  if( checkForMultiColumnSelectError(pParse, eDest, pEList->nExpr) ){
    return 0;
  }

  switch( eDest ){
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
#ifndef SQLITE_OMIT_COMPOUND_SELECT
    case SRT_Union: {
................................................................................
  */
  if( pParse->nErr>0 ) goto select_end;

  /* If writing to memory or generating a set
  ** only a single column may be output.
  */
#ifndef SQLITE_OMIT_SUBQUERY
  if( checkForMultiColumnSelectError(pParse, eDest, pEList->nExpr) ){


    goto select_end;
  }
#endif

  /* ORDER BY is ignored for some destinations.
  */
  if( IgnorableOrderby(eDest) ){

Changes to test/select7.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
99
100
101
102
103
104
105
106
107

108




























109
#    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 compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.8 2006/10/13 15:34:17 drh Exp $


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

ifcapable compound {

................................................................................
      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 
           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 
           EXCEPT 
           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
      );
    }
  } {2 3}

}






























finish_test







|







 







|
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
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
#    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 compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.9 2007/05/09 22:56:39 drh Exp $


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

ifcapable compound {

................................................................................
      SELECT P.pk from PHOTO P WHERE NOT EXISTS ( 
           SELECT T2.pk from TAG T2 WHERE T2.fk = P.pk 
           EXCEPT 
           SELECT T3.pk from TAG T3 WHERE T3.fk = P.pk AND T3.name LIKE '%foo%'
      );
    }
  } {2 3}
}

# ticket #2347
#
ifcapable {subquery && compound} {
  do_test select7-5.1 {
    catchsql {
      CREATE TABLE t2(a,b);
      SELECT 5 IN (SELECT a,b FROM t2);
    }
  } [list 1 \
     {only a single result allowed for a SELECT that is part of an expression}]
  do_test select7-5.2 {
    catchsql {
      SELECT 5 IN (SELECT * FROM t2);
    }
  } [list 1 \
     {only a single result allowed for a SELECT that is part of an expression}]
  do_test select7-5.3 {
    catchsql {
      SELECT 5 IN (SELECT a,b FROM t2 UNION SELECT b,a FROM t2);
    }
  } [list 1 \
     {only a single result allowed for a SELECT that is part of an expression}]
  do_test select7-5.4 {
    catchsql {
      SELECT 5 IN (SELECT * FROM t2 UNION SELECT * FROM t2);
    }
  } [list 1 \
     {only a single result allowed for a SELECT that is part of an expression}]
}

finish_test