subquery/negative-cases/subq-input-typecheck.sql

0001 -- The test file contains negative test cases
0002 -- of invalid queries where error messages are expected.
0003
0004 CREATE TEMPORARY VIEW t1 AS SELECT * FROM VALUES
0005   (1, 2, 3)
0006 AS t1(t1a, t1b, t1c);
0007
0008 CREATE TEMPORARY VIEW t2 AS SELECT * FROM VALUES
0009   (1, 0, 1)
0010 AS t2(t2a, t2b, t2c);
0011
0012 CREATE TEMPORARY VIEW t3 AS SELECT * FROM VALUES
0013   (3, 1, 2)
0014 AS t3(t3a, t3b, t3c);
0015
0016 CREATE TEMPORARY VIEW t4 AS SELECT * FROM VALUES
0017   (CAST(1 AS DOUBLE), CAST(2 AS STRING), CAST(3 AS STRING))
0018 AS t1(t4a, t4b, t4c);
0019
0020 CREATE TEMPORARY VIEW t5 AS SELECT * FROM VALUES
0021   (CAST('2011-01-01 01:01:01' AS TIMESTAMP), CAST(2 AS STRING), CAST(3 AS BIGINT))
0022 AS t1(t5a, t5b, t5c);
0023
0024 -- TC 01.01
0025 SELECT
0026   ( SELECT max(t2b), min(t2b)
0027     FROM t2
0028     WHERE t2.t2b = t1.t1b
0029     GROUP BY t2.t2b
0030   )
0031 FROM t1;
0032
0033 -- TC 01.01
0034 SELECT
0035   ( SELECT max(t2b), min(t2b)
0036     FROM t2
0037     WHERE t2.t2b > 0
0038     GROUP BY t2.t2b
0039   )
0040 FROM t1;
0041
0042 -- TC 01.03
0043 SELECT * FROM t1
0044 WHERE
0045 t1a IN (SELECT t2a, t2b
0046         FROM t2
0047         WHERE t1a = t2a);
0048
0049 -- TC 01.04
0050 SELECT * FROM T1
0051 WHERE
0052 (t1a, t1b) IN (SELECT t2a
0053                FROM t2
0054                WHERE t1a = t2a);
0055 -- TC 01.05
0056 SELECT * FROM t4
0057 WHERE
0058 (t4a, t4b, t4c) IN (SELECT t5a,
0059                            t5b,
0060                            t5c
0061                     FROM t5);