/ Check-in [a55842cf]
Login

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

Overview
Comment:Move the test for an (illegal) scalar sub-query that returns more than one column to earlier in SELECT processing in order to avoid an assert() that can happen later on.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a55842cfb56b659c88832dce9ce7bafb50258211
User & Date: dan 2010-09-02 19:01:16
Context
2010-09-03
03:32
Fix a discrepancy between the documented behavior of SQLITE_DBCONFIG_LOOKASIDE and what it actually does. Also add evidence marks on the DBCONFIG_LOOKASIDE implementation. check-in: f483be44 user: drh tags: trunk
2010-09-02
19:01
Move the test for an (illegal) scalar sub-query that returns more than one column to earlier in SELECT processing in order to avoid an assert() that can happen later on. check-in: a55842cf user: dan tags: trunk
18:13
Fix an off-by-one error in the scratch memory allocator. check-in: 5a959160 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3621   3621     isAgg = (p->selFlags & SF_Aggregate)!=0;
  3622   3622     assert( pEList!=0 );
  3623   3623   
  3624   3624     /* Begin generating code.
  3625   3625     */
  3626   3626     v = sqlite3GetVdbe(pParse);
  3627   3627     if( v==0 ) goto select_end;
         3628  +
         3629  +  /* If writing to memory or generating a set
         3630  +  ** only a single column may be output.
         3631  +  */
         3632  +#ifndef SQLITE_OMIT_SUBQUERY
         3633  +  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
         3634  +    goto select_end;
         3635  +  }
         3636  +#endif
  3628   3637   
  3629   3638     /* Generate code for all sub-queries in the FROM clause
  3630   3639     */
  3631   3640   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3632   3641     for(i=0; !p->pPrior && i<pTabList->nSrc; i++){
  3633   3642       struct SrcList_item *pItem = &pTabList->a[i];
  3634   3643       SelectDest dest;
................................................................................
  3693   3702         if( mxSelect && cnt>mxSelect ){
  3694   3703           sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  3695   3704           return 1;
  3696   3705         }
  3697   3706       }
  3698   3707       return multiSelect(pParse, p, pDest);
  3699   3708     }
  3700         -#endif
  3701         -
  3702         -  /* If writing to memory or generating a set
  3703         -  ** only a single column may be output.
  3704         -  */
  3705         -#ifndef SQLITE_OMIT_SUBQUERY
  3706         -  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
  3707         -    goto select_end;
  3708         -  }
  3709   3709   #endif
  3710   3710   
  3711   3711     /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  3712   3712     ** GROUP BY might use an index, DISTINCT never does.
  3713   3713     */
  3714   3714     assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 );
  3715   3715     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){

Changes to test/e_expr.test.

  1725   1725     2 { EXISTS (SELECT NULL FROM t1) }  { EXISTS (SELECT 'bread' FROM t1) }
  1726   1726   } {
  1727   1727     set res [db one "SELECT $e1"]
  1728   1728     do_expr_test e_expr-34.5.${tn}a $e1 integer $res
  1729   1729     do_expr_test e_expr-34.5.${tn}b $e2 integer $res
  1730   1730   }
  1731   1731   
  1732         -
  1733         -
  1734   1732   #-------------------------------------------------------------------------
  1735         -# Test statements related to the IN and NOT IN operators.
         1733  +# Test statements related to scalar sub-queries.
         1734  +#
         1735  +
         1736  +catch { db close }
         1737  +file delete -force test.db
         1738  +sqlite3 db test.db
         1739  +do_test e_expr-35.0 {
         1740  +  execsql {
         1741  +    CREATE TABLE t2(a, b);
         1742  +    INSERT INTO t2 VALUES('one', 'two');
         1743  +    INSERT INTO t2 VALUES('three', NULL);
         1744  +    INSERT INTO t2 VALUES(4, 5.0);
         1745  +  }
         1746  +} {}
         1747  +
         1748  +# EVIDENCE-OF: R-00980-39256 A SELECT statement enclosed in parentheses
         1749  +# may appear as a scalar quantity.
         1750  +#
         1751  +# EVIDENCE-OF: R-56294-03966 All types of SELECT statement, including
         1752  +# aggregate and compound SELECT queries (queries with keywords like
         1753  +# UNION or EXCEPT) are allowed as scalar subqueries.
         1754  +#
         1755  +do_expr_test e_expr-35.1.1 { (SELECT 35)   } integer 35
         1756  +do_expr_test e_expr-35.1.2 { (SELECT NULL) } null {}
         1757  +
         1758  +do_expr_test e_expr-35.1.3 { (SELECT count(*) FROM t2) } integer 3
         1759  +do_expr_test e_expr-35.1.4 { (SELECT 4 FROM t2) } integer 4
         1760  +
         1761  +do_expr_test e_expr-35.1.5 { 
         1762  +  (SELECT b FROM t2 UNION SELECT a+1 FROM t2)
         1763  +} null {}
         1764  +do_expr_test e_expr-35.1.6 { 
         1765  +  (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
         1766  +} integer 4
         1767  +
         1768  +# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
         1769  +# return a result set with a single column.
  1736   1770   #
         1771  +set M {only a single result allowed for a SELECT that is part of an expression}
         1772  +foreach {tn sql} {
         1773  +  1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
         1774  +  2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
         1775  +  3     { SELECT (SELECT 1, 2) }
         1776  +  4     { SELECT (SELECT NULL, NULL, NULL) }
         1777  +  5     { SELECT (SELECT * FROM t2) }
         1778  +  6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
         1779  +} {
         1780  +  do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
         1781  +}
  1737   1782   
  1738   1783   finish_test
  1739   1784   

Changes to test/in.test.

   400    400         SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2
   401    401       );
   402    402     }
   403    403   } {1 {only a single result allowed for a SELECT that is part of an expression}}
   404    404   do_test in-12.6 {
   405    405     catchsql {
   406    406       SELECT * FROM t2 WHERE a IN (
   407         -      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
          407  +      SELECT a, b FROM t3 UNION ALL SELECT a FROM t2
   408    408       );
   409    409     }
   410    410   } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
   411    411   do_test in-12.7 {
   412    412     catchsql {
   413    413       SELECT * FROM t2 WHERE a IN (
   414         -      SELECT a FROM t3 UNION SELECT a, b FROM t2
          414  +      SELECT a, b FROM t3 UNION SELECT a FROM t2
   415    415       );
   416    416     }
   417    417   } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
   418    418   do_test in-12.8 {
   419    419     catchsql {
   420    420       SELECT * FROM t2 WHERE a IN (
   421         -      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
          421  +      SELECT a, b FROM t3 EXCEPT SELECT a FROM t2
   422    422       );
   423    423     }
   424    424   } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
   425    425   do_test in-12.9 {
   426    426     catchsql {
   427    427       SELECT * FROM t2 WHERE a IN (
   428         -      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
          428  +      SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
   429    429       );
   430    430     }
   431    431   } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
   432    432   }
   433    433   
          434  +do_test in-12.10 {
          435  +  catchsql {
          436  +    SELECT * FROM t2 WHERE a IN (
          437  +      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
          438  +    );
          439  +  }
          440  +} {1 {only a single result allowed for a SELECT that is part of an expression}}
          441  +do_test in-12.11 {
          442  +  catchsql {
          443  +    SELECT * FROM t2 WHERE a IN (
          444  +      SELECT a FROM t3 UNION SELECT a, b FROM t2
          445  +    );
          446  +  }
          447  +} {1 {only a single result allowed for a SELECT that is part of an expression}}
          448  +do_test in-12.12 {
          449  +  catchsql {
          450  +    SELECT * FROM t2 WHERE a IN (
          451  +      SELECT a FROM t3 EXCEPT SELECT a, b FROM t2
          452  +    );
          453  +  }
          454  +} {1 {only a single result allowed for a SELECT that is part of an expression}}
          455  +do_test in-12.13 {
          456  +  catchsql {
          457  +    SELECT * FROM t2 WHERE a IN (
          458  +      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
          459  +    );
          460  +  }
          461  +} {1 {only a single result allowed for a SELECT that is part of an expression}}
          462  +
   434    463   
   435    464   #------------------------------------------------------------------------
   436    465   # The following tests check that NULL is handled correctly when it 
   437    466   # appears as part of a set of values on the right-hand side of an
   438    467   # IN or NOT IN operator.
   439    468   #
   440    469   # When it appears in such a set, NULL is handled as an "unknown value".