/ Check-in [189cba00]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to test/e_expr.test.

1763
1764
1765
1766
1767
1768
1769



1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782







































1783
1784
} null {}
do_expr_test e_expr-35.1.6 { 
  (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
} integer 4

# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
# return a result set with a single column.



#
set M {only a single result allowed for a SELECT that is part of an expression}
foreach {tn sql} {
  1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
  2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
  3     { SELECT (SELECT 1, 2) }
  4     { SELECT (SELECT NULL, NULL, NULL) }
  5     { SELECT (SELECT * FROM t2) }
  6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
} {
  do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
}








































finish_test








>
>
>













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


1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
} null {}
do_expr_test e_expr-35.1.6 { 
  (SELECT a FROM t2 UNION SELECT COALESCE(b, 55) FROM t2 ORDER BY 1)
} integer 4

# EVIDENCE-OF: R-46899-53765 A SELECT used as a scalar quantity must
# return a result set with a single column.
#
# The following block tests that errors are returned in a bunch of cases
# where a subquery returns more than one column.
#
set M {only a single result allowed for a SELECT that is part of an expression}
foreach {tn sql} {
  1     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2) }
  2     { SELECT (SELECT * FROM t2 UNION SELECT a+1, b+1 FROM t2 ORDER BY 1) }
  3     { SELECT (SELECT 1, 2) }
  4     { SELECT (SELECT NULL, NULL, NULL) }
  5     { SELECT (SELECT * FROM t2) }
  6     { SELECT (SELECT * FROM (SELECT 1, 2, 3)) }
} {
  do_catchsql_test e_expr-35.2.$tn $sql [list 1 $M]
}

# EVIDENCE-OF: R-35764-28041 The result of the expression is the value
# of the only column in the first row returned by the SELECT statement.
#
# EVIDENCE-OF: R-41898-06686 If the SELECT yields more than one result
# row, all rows after the first are ignored.
#
do_execsql_test e_expr-36.3.1 {
  CREATE TABLE t4(x, y);
  INSERT INTO t4 VALUES(1, 'one');
  INSERT INTO t4 VALUES(2, 'two');
  INSERT INTO t4 VALUES(3, 'three');
} {}

foreach {tn expr restype resval} {
    2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
    3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
    4  { ( SELECT x FROM t4 ORDER BY x DESC ) }        integer 3
    5  { ( SELECT x FROM t4 ORDER BY y DESC ) }        integer 2
    6  { ( SELECT y FROM t4 ORDER BY y DESC ) }        text    two

    7  { ( SELECT sum(x) FROM t4 )           }         integer 6
    8  { ( SELECT group_concat(y,'') FROM t4 ) }       text    onetwothree
    9  { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 

} {
  do_expr_test e_expr-36.3.$tn $expr $restype $resval
}

# EVIDENCE-OF: R-25492-41572 If the SELECT yields no rows, then the
# value of the expression is NULL.
#
foreach {tn expr} {
    1  { ( SELECT x FROM t4 WHERE x>3 ORDER BY x )      }
    2  { ( SELECT x FROM t4 WHERE y<'one' ORDER BY y )  }
} {
  do_expr_test e_expr-36.4.$tn $expr null {}
}


finish_test