diff options
Diffstat (limited to 'tests/fixtures/identity.sql')
| -rw-r--r-- | tests/fixtures/identity.sql | 33 |
1 files changed, 20 insertions, 13 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 228f109..13adf7f 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -107,6 +107,7 @@ CURRENT_DATE('UTC') CURRENT_DATE AT TIME ZONE 'UTC' CURRENT_DATE AT TIME ZONE zone_column CURRENT_DATE AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokio' +f1 AT TIME ZONE INTERVAL '-10:00' AS f1 ARRAY() ARRAY(1, 2) ARRAY(time, foo) @@ -137,19 +138,26 @@ x ILIKE '%y%' x LIKE '%y%' ESCAPE '\' x ILIKE '%y%' ESCAPE '\' 1 AS escape -INTERVAL '1' day +INTERVAL '1' DAY INTERVAL '1' MONTH INTERVAL '1' YEAR INTERVAL '-1' CURRENT_DATE INTERVAL '-31' CAST(GETDATE() AS DATE) INTERVAL (1 + 3) DAYS -INTERVAL '1' day * 5 -5 * INTERVAL '1' day -CASE WHEN TRUE THEN INTERVAL '15' days END +INTERVAL '1' DAY * 5 +5 * INTERVAL '1' DAY +CASE WHEN TRUE THEN INTERVAL '15' DAYS END +CASE WHEN TRUE THEN 1 ELSE interval END +CASE WHEN TRUE THEN 1 ELSE "INTERVAL" END +SELECT * WHERE interval IS NULL +SELECT * WHERE NOT interval IS NULL +SELECT * WHERE INTERVAL "is" > 1 +SELECT * WHERE INTERVAL x.is > 1 CAST('45' AS INTERVAL DAYS) CAST(x AS UUID) FILTER(a, x -> x.a.b.c.d.e.f.g) FILTER(a, x -> FOO(x.a.b.c.d.e.f.g) + x.a.b.c.d.e.f.g) +TIMESTAMP_FROM_PARTS(2019, 1, 10, 2, 3, 4, 123456789, 'America/Los_Angeles') TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY) DATETIME_DIFF(CURRENT_DATE, 1, DAY) QUANTILE(x, 0.5) @@ -164,7 +172,7 @@ REPLACE(1) DATE(x) = DATE(y) TIMESTAMP(DATE(x)) TIMESTAMP_TRUNC(COALESCE(time_field, CURRENT_TIMESTAMP()), DAY) -COUNT(DISTINCT CASE WHEN DATE_TRUNC(DATE(time_field), isoweek) = DATE_TRUNC(DATE(time_field2), isoweek) THEN report_id ELSE NULL END) +COUNT(DISTINCT CASE WHEN DATE_TRUNC('ISOWEEK', DATE(time_field)) = DATE_TRUNC('ISOWEEK', DATE(time_field2)) THEN report_id ELSE NULL END) COUNT(a, b) x[y - 1] CASE WHEN SUM(x) > 3 THEN 1 END OVER (PARTITION BY x) @@ -224,7 +232,7 @@ SELECT DISTINCT ON (x, y + 1) * FROM z SELECT DISTINCT ON (x.y) * FROM z SELECT DISTINCT FROM_SOMETHING SELECT top.x -SELECT TIMESTAMP(DATE_TRUNC(DATE(time_field), MONTH)) AS time_value FROM "table" +SELECT TIMESTAMP(DATE_TRUNC('MONTH', DATE(time_field))) AS time_value FROM "table" SELECT GREATEST((3 + 1), LEAST(3, 4)) SELECT TRANSFORM(a, b -> b) AS x SELECT AGGREGATE(a, (a, b) -> a + b) AS x @@ -335,17 +343,13 @@ SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5) SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5 ON x) SELECT a FROM test TABLESAMPLE (BUCKET 1 OUT OF 5 ON RAND()) SELECT a FROM test TABLESAMPLE (0.1 PERCENT) -SELECT a FROM test TABLESAMPLE (100) SELECT a FROM test TABLESAMPLE (100 ROWS) -SELECT a FROM test TABLESAMPLE BERNOULLI (50) -SELECT a FROM test TABLESAMPLE SYSTEM (75) SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) SELECT 1 FROM a.b.table1 AS t UNPIVOT((c3) FOR c4 IN (a, b)) SELECT a FROM test PIVOT(SOMEAGG(x, y, z) FOR q IN (1)) SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) PIVOT(MAX(b) FOR c IN ('d')) SELECT a FROM (SELECT a, b FROM test) PIVOT(SUM(x) FOR y IN ('z', 'q')) SELECT a FROM test UNPIVOT(x FOR y IN (z, q)) AS x -SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1) SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) UNPIVOT(x FOR y IN (z, q)) AS x SELECT ABS(a) FROM test SELECT AVG(a) FROM test @@ -590,6 +594,7 @@ CREATE TABLE IF NOT EXISTS customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDEN CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1 MINVALUE -1 MAXVALUE 1 NO CYCLE)) CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 10)) CREATE TABLE customer (pk BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (CYCLE)) +CREATE TABLE customer (period INT NOT NULL) CREATE TABLE foo (baz_id INT REFERENCES baz (id) DEFERRABLE) CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC COMPRESS 'a') CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title' INLINE LENGTH 1 COMPRESS ('a', 'b')) @@ -621,6 +626,9 @@ CREATE FUNCTION a(b INT, c VARCHAR) AS 'SELECT 1' CREATE FUNCTION a() LANGUAGE sql CREATE FUNCTION a() LANGUAGE sql RETURNS INT CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1 +CREATE FUNCTION a.b(x TEXT) RETURNS TEXT CONTAINS SQL AS RETURN x +CREATE FUNCTION a.b(x TEXT) RETURNS TEXT LANGUAGE SQL MODIFIES SQL DATA AS RETURN x +CREATE FUNCTION a.b(x TEXT) LANGUAGE SQL READS SQL DATA RETURNS TEXT AS RETURN x CREATE FUNCTION a.b.c() CREATE INDEX abc ON t(a) CREATE INDEX "abc" ON t(a) @@ -635,6 +643,7 @@ CREATE DATABASE x CREATE DATABASE IF NOT EXISTS y CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END' CREATE OR REPLACE STAGE +CREATE TABLE T3 AS (SELECT DISTINCT A FROM T1 EXCEPT (SELECT A FROM T2) LIMIT 1) DESCRIBE x DROP INDEX a.b.c DROP FUNCTION a.b.c (INT) @@ -741,6 +750,7 @@ SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z SELECT ((SELECT 1) + 1) SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES SELECT CAST(x AS INT) /* comment */ FROM foo +SELECT c /* c1 */ AS alias /* c2 */ SELECT a /* x */, b /* x */ SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */ SELECT * FROM foo /* x */, bla /* x */ @@ -756,9 +766,6 @@ INSERT INTO foo SELECT * FROM bar /* comment */ /* c */ WITH x AS (SELECT 1) SELECT * FROM x SELECT a FROM x WHERE a COLLATE 'utf8_general_ci' = 'b' SELECT x AS INTO FROM bla -SELECT * INTO newevent FROM event -SELECT * INTO TEMPORARY newevent FROM event -SELECT * INTO UNLOGGED newevent FROM event ALTER TABLE integers ADD COLUMN k INT ALTER TABLE integers ADD COLUMN k INT FIRST ALTER TABLE integers ADD COLUMN k INT AFTER m |