/ Check-in [a7be554f]
Login

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

Overview
Comment:Merge the compound SELECT operator fix from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | btree-opt2
Files: files | file ages | folders
SHA1: a7be554f4b8534fc237fa4c6defc38fcd4049707
User & Date: drh 2015-06-23 13:02:10
Context
2015-06-23
14:49
Improvements to the way balance_nonroot() constructs the b.apCell array of pointers to cells. check-in: ee44bb25 user: drh tags: btree-opt2
13:02
Merge the compound SELECT operator fix from trunk. check-in: a7be554f user: drh tags: btree-opt2
12:19
Test that the left and right sides of a compound SELECT operator have the same number of expressions in the expanded expression list before beginning to generate code. check-in: 4df852ce user: dan tags: trunk
02:37
Avoid computing cell sizes in balance_nonroot() until they are really needed. This gives an overall 1.7% performance gain for about 1000 extra bytes of code space. check-in: 43844537 user: drh tags: btree-opt2
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

1326
1327
1328
1329
1330
1331
1332







1333
1334
1335
1336
1337
1338
1339
        if( ExprHasProperty(pItem->pExpr, EP_Agg) ){
          sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
              "the GROUP BY clause");
          return WRC_Abort;
        }
      }
    }








    /* Advance to the next term of the compound
    */
    p = p->pPrior;
    nCompound++;
  }








>
>
>
>
>
>
>







1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
        if( ExprHasProperty(pItem->pExpr, EP_Agg) ){
          sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
              "the GROUP BY clause");
          return WRC_Abort;
        }
      }
    }

    /* If this is part of a compound SELECT, check that it has the right
    ** number of expressions in the select list. */
    if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
      sqlite3SelectWrongNumTermsError(pParse, p->pNext);
      return WRC_Abort;
    }

    /* Advance to the next term of the compound
    */
    p = p->pPrior;
    nCompound++;
  }

Changes to src/select.c.

2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
....
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
....
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
  SelectDest *pDest     /* What to do with query results */
);

/*
** Error message for when two or more terms of a compound select have different
** size result sets.
*/
static void selectWrongNumTermsError(Parse *pParse, Select *p){
  if( p->selFlags & SF_Values ){
    sqlite3ErrorMsg(pParse, "all VALUES must have the same number of terms");
  }else{
    sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
      " do not have the same number of result columns", selectOpName(p->op));
  }
}
................................................................................
*/
static int multiSelectValues(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){
  Select *pPrior;
  int nExpr = p->pEList->nExpr;
  int nRow = 1;
  int rc = 0;
  assert( p->selFlags & SF_MultiValue );
  do{
    assert( p->selFlags & SF_Values );
    assert( p->op==TK_ALL || (p->op==TK_SELECT && p->pPrior==0) );
    assert( p->pLimit==0 );
    assert( p->pOffset==0 );
    if( p->pEList->nExpr!=nExpr ){
      selectWrongNumTermsError(pParse, p);
      return 1;
    }
    if( p->pPrior==0 ) break;
    assert( p->pPrior->pNext==p );
    p = p->pPrior;
    nRow++;
  }while(1);
  while( p ){
    pPrior = p->pPrior;
................................................................................
    goto multi_select_end;
  }

  /* Make sure all SELECTs in the statement have the same number of elements
  ** in their result sets.
  */
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    selectWrongNumTermsError(pParse, p);
    rc = 1;
    goto multi_select_end;
  }

#ifndef SQLITE_OMIT_CTE
  if( p->selFlags & SF_Recursive ){
    generateWithRecursiveQuery(pParse, p, &dest);
  }else
#endif








|







 







<








|
<
<
<







 







|
<
<
<
<







2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
....
2115
2116
2117
2118
2119
2120
2121

2122
2123
2124
2125
2126
2127
2128
2129
2130



2131
2132
2133
2134
2135
2136
2137
....
2232
2233
2234
2235
2236
2237
2238
2239




2240
2241
2242
2243
2244
2245
2246
  SelectDest *pDest     /* What to do with query results */
);

/*
** Error message for when two or more terms of a compound select have different
** size result sets.
*/
void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p){
  if( p->selFlags & SF_Values ){
    sqlite3ErrorMsg(pParse, "all VALUES must have the same number of terms");
  }else{
    sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
      " do not have the same number of result columns", selectOpName(p->op));
  }
}
................................................................................
*/
static int multiSelectValues(
  Parse *pParse,        /* Parsing context */
  Select *p,            /* The right-most of SELECTs to be coded */
  SelectDest *pDest     /* What to do with query results */
){
  Select *pPrior;

  int nRow = 1;
  int rc = 0;
  assert( p->selFlags & SF_MultiValue );
  do{
    assert( p->selFlags & SF_Values );
    assert( p->op==TK_ALL || (p->op==TK_SELECT && p->pPrior==0) );
    assert( p->pLimit==0 );
    assert( p->pOffset==0 );
    assert( p->pNext==0 || p->pEList->nExpr==p->pNext->pEList->nExpr );



    if( p->pPrior==0 ) break;
    assert( p->pPrior->pNext==p );
    p = p->pPrior;
    nRow++;
  }while(1);
  while( p ){
    pPrior = p->pPrior;
................................................................................
    goto multi_select_end;
  }

  /* Make sure all SELECTs in the statement have the same number of elements
  ** in their result sets.
  */
  assert( p->pEList && pPrior->pEList );
  assert( p->pEList->nExpr==pPrior->pEList->nExpr );





#ifndef SQLITE_OMIT_CTE
  if( p->selFlags & SF_Recursive ){
    generateWithRecursiveQuery(pParse, p, &dest);
  }else
#endif

Changes to src/sqliteInt.h.

3566
3567
3568
3569
3570
3571
3572

3573
3574
3575
3576
3577
3578
3579
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);

int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);







>







3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
void sqlite3AlterFunctions(void);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);

Changes to test/in.test.

446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473







474







475
476
477
478
479
480
481
ifcapable compound {
do_test in-12.10 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.11 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.12 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.13 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }







} {1 {only a single result allowed for a SELECT that is part of an expression}}







}; #ifcapable compound


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.







|






|






|






>
>
>
>
>
>
>

>
>
>
>
>
>
>







446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
ifcapable compound {
do_test in-12.10 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_test in-12.11 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
do_test in-12.12 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
do_test in-12.13 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
do_test in-12.14 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
do_test in-12.15 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
}; #ifcapable compound


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.

Changes to test/select7.test.

11
12
13
14
15
16
17

18
19
20
21
22
23
24
...
196
197
198
199
200
201
202
203

















204


# views.
#
# $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $


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


ifcapable compound {

# A 3-way INTERSECT.  Ticket #875
ifcapable tempdb {
  do_test select7-1.1 {
    execsql {
................................................................................
do_test select7-7.7 {
  execsql {
    CREATE TABLE t5(a TEXT, b INT);
    INSERT INTO t5 VALUES(123, 456);
    SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
  }
} {text 123}


















finish_test









>







 








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

>
>
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
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
# views.
#
# $Id: select7.test,v 1.11 2007/09/12 17:01:45 danielk1977 Exp $


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

ifcapable compound {

# A 3-way INTERSECT.  Ticket #875
ifcapable tempdb {
  do_test select7-1.1 {
    execsql {
................................................................................
do_test select7-7.7 {
  execsql {
    CREATE TABLE t5(a TEXT, b INT);
    INSERT INTO t5 VALUES(123, 456);
    SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
  }
} {text 123}

do_execsql_test 8.0 { 
  CREATE TABLE t01(x, y);
  CREATE TABLE t02(x, y);
}

do_catchsql_test 8.1 {
  SELECT * FROM (
    SELECT * FROM t01 UNION SELECT x FROM t02
  ) WHERE y=1
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

do_catchsql_test 8.2 {
  CREATE VIEW v0 as SELECT x, y FROM t01 UNION SELECT x FROM t02;
  EXPLAIN QUERY PLAN SELECT * FROM v0 WHERE x='0' OR y;
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}


finish_test