/ Check-in [189cba00]
Login

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

Overview
Comment:Add a few more subquery tests to e_expr.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 189cba0072dd0b90e064f889457921aeaeefda01
User & Date: dan 2010-09-03 10:58:47
Context
2010-09-03
12:05
Improved documentation of the sqlite3_column_count() and sqlite3_data_count() interfaces. check-in: 0593373d user: drh tags: trunk
10:58
Add a few more subquery tests to e_expr.test. check-in: 189cba00 user: dan tags: trunk
04:29
Reduce the amount of memory taken up by WAL mmaped regions under Windows. check-in: f213e133 user: shaneh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_expr.test.

  1763   1763   } null {}
  1764   1764   do_expr_test e_expr-35.1.6 { 
  1765   1765     (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
  1766   1766   } integer 4
  1767   1767   
  1768   1768   # EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
  1769   1769   # return a result set with a single column.
         1770  +#
         1771  +# The following block tests that errors are returned in a bunch of cases
         1772  +# where a subquery returns more than one column.
  1770   1773   #
  1771   1774   set M {only a single result allowed for a SELECT that is part of an expression}
  1772   1775   foreach {tn sql} {
  1773   1776     1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
  1774   1777     2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
  1775   1778     3     { SELECT (SELECT 1, 2) }
  1776   1779     4     { SELECT (SELECT NULL, NULL, NULL) }
  1777   1780     5     { SELECT (SELECT * FROM t2) }
  1778   1781     6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
  1779   1782   } {
  1780   1783     do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
  1781   1784   }
         1785  +
         1786  +# EVIDENCE-OF: R-35764-28041 The result of the expression is the value
         1787  +# of the only column in the first row returned by the SELECT statement.
         1788  +#
         1789  +# EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
         1790  +# row, all rows after the first are ignored.
         1791  +#
         1792  +do_execsql_test e_expr-36.3.1 {
         1793  +  CREATE TABLE t4(x, y);
         1794  +  INSERT INTO t4 VALUES(1, 'one');
         1795  +  INSERT INTO t4 VALUES(2, 'two');
         1796  +  INSERT INTO t4 VALUES(3, 'three');
         1797  +} {}
         1798  +
         1799  +foreach {tn expr restype resval} {
         1800  +    2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
         1801  +    3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
         1802  +    4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
         1803  +    5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
         1804  +    6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two
         1805  +
         1806  +    7  { ( SELECT sum(x) FROM t4 )           }         integer 6
         1807  +    8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
         1808  +    9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 
         1809  +
         1810  +} {
         1811  +  do_expr_test e_expr-36.3.$tn $expr $restype $resval
         1812  +}
         1813  +
         1814  +# EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
         1815  +# value of the expression is NULL.
         1816  +#
         1817  +foreach {tn expr} {
         1818  +    1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
         1819  +    2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
         1820  +} {
         1821  +  do_expr_test e_expr-36.4.$tn $expr null {}
         1822  +}
         1823  +
  1782   1824   
  1783   1825   finish_test
  1784   1826