/ Check-in [200a8135]
Login

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

Overview
Comment:Fix a case where SQLite was failing to detect a syntax error in queries like "SELECT ... FROM (<select-1> UNION ALL <select-2>)" when <select-1> and <select-2> return different numbers of result columns.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 200a81358c3117401d2258dd06bb8d2ea4f0ef51
User & Date: dan 2012-08-28 14:45:50
Context
2012-08-29
10:28
Fix a typo in a comment in vdbe.c. No changes to code. check-in: 9fbe858b user: drh tags: trunk
2012-08-28
14:45
Fix a case where SQLite was failing to detect a syntax error in queries like "SELECT ... FROM (<select-1> UNION ALL <select-2>)" when <select-1> and <select-2> return different numbers of result columns. check-in: 200a8135 user: dan tags: trunk
04:20
Issue a log message if the temporary directory has not been set when running on WinRT. check-in: 9ee39102 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

2684
2685
2686
2687
2688
2689
2690






2691
2692
2693
2694
2695
2696
2697
....
2828
2829
2830
2831
2832
2833
2834

2835
2836
2837
2838
2839
2840
2841
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
**        operator other than UNION ALL because all the other compound
**        operators have an implied DISTINCT which is disallowed by
**        restriction (4).






**
**  (18)  If the sub-query is a compound select, then all terms of the
**        ORDER by clause of the parent must be simple references to 
**        columns of the sub-query.
**
**  (19)  The subquery does not use LIMIT or the outer query does not
**        have a WHERE clause.
................................................................................
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
      assert( pSub->pSrc!=0 );
      if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
       || (pSub1->pPrior && pSub1->op!=TK_ALL) 
       || pSub1->pSrc->nSrc<1

      ){
        return 0;
      }
      testcase( pSub1->pSrc->nSrc>1 );
    }

    /* Restriction 18. */







>
>
>
>
>
>







 







>







2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
....
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
**
**        The parent and sub-query may contain WHERE clauses. Subject to
**        rules (11), (13) and (14), they may also contain ORDER BY,
**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
**        operator other than UNION ALL because all the other compound
**        operators have an implied DISTINCT which is disallowed by
**        restriction (4).
**
**        Also, each component of the sub-query must return the same number
**        of result columns. This is actually a requirement for any compound
**        SELECT statement, but all the code here does is make sure that no
**        such (illegal) sub-query is flattened. The caller will detect the
**        syntax error and return a detailed message.
**
**  (18)  If the sub-query is a compound select, then all terms of the
**        ORDER by clause of the parent must be simple references to 
**        columns of the sub-query.
**
**  (19)  The subquery does not use LIMIT or the outer query does not
**        have a WHERE clause.
................................................................................
    for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct );
      testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate );
      assert( pSub->pSrc!=0 );
      if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0
       || (pSub1->pPrior && pSub1->op!=TK_ALL) 
       || pSub1->pSrc->nSrc<1
       || pSub->pEList->nExpr!=pSub1->pEList->nExpr
      ){
        return 0;
      }
      testcase( pSub1->pSrc->nSrc>1 );
    }

    /* Restriction 18. */

Changes to test/select6.test.

18
19
20
21
22
23
24

25
26
27
28
29
30
31
...
508
509
510
511
512
513
514
515




516







































517
source $testdir/tester.tcl

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
  return
}


do_test select6-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
................................................................................
} {2 12 3 13 4 14}
do_test select6-9.11 {
  execsql {
    SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
  }
} {2 12 3 13 4 14}














































finish_test







>







 








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

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
source $testdir/tester.tcl

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
  return
}
set ::testprefix select6

do_test select6-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
................................................................................
} {2 12 3 13 4 14}
do_test select6-9.11 {
  execsql {
    SELECT x, y FROM (SELECT x, (SELECT 10)+x y FROM t1 LIMIT -1 OFFSET 1);
  }
} {2 12 3 13 4 14}


#-------------------------------------------------------------------------
# Test that if a UNION ALL sub-query that would otherwise be eligible for
# flattening consists of two or more SELECT statements that do not all 
# return the same number of result columns, the error is detected.
#
do_execsql_test 10.1 {
  CREATE TABLE t(i,j,k);
  CREATE TABLE j(l,m);
  CREATE TABLE k(o);
}

set err [list 1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}]

do_execsql_test 10.2 {
  SELECT * FROM (SELECT * FROM t), j;
}
do_catchsql_test 10.3 {
  SELECT * FROM t UNION ALL SELECT * FROM j
} $err
do_catchsql_test 10.4 {
  SELECT * FROM (SELECT i FROM t UNION ALL SELECT l, m FROM j)
} $err
do_catchsql_test 10.5 {
  SELECT * FROM (SELECT j FROM t UNION ALL SELECT * FROM j)
} $err
do_catchsql_test 10.6 {
  SELECT * FROM (SELECT * FROM t UNION ALL SELECT * FROM j)
} $err
do_catchsql_test 10.7 {
  SELECT * FROM (
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j UNION ALL
    SELECT * FROM k
  )
} $err
do_catchsql_test 10.8 {
  SELECT * FROM (
    SELECT * FROM k UNION ALL
    SELECT * FROM t UNION ALL 
    SELECT l,m,l FROM j 
  )
} $err


finish_test