diff options
Diffstat (limited to 'tests')
32 files changed, 8443 insertions, 415 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index 48480f9..2c8ac7b 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -9,6 +9,7 @@ from sqlglot import ( transpile, ) from sqlglot.helper import logger as helper_logger +from sqlglot.parser import logger as parser_logger from tests.dialects.test_dialect import Validator @@ -17,6 +18,29 @@ class TestBigQuery(Validator): maxDiff = None def test_bigquery(self): + self.validate_identity( + "create or replace view test (tenant_id OPTIONS(description='Test description on table creation')) select 1 as tenant_id, 1 as customer_id;", + "CREATE OR REPLACE VIEW test (tenant_id OPTIONS (description='Test description on table creation')) AS SELECT 1 AS tenant_id, 1 AS customer_id", + ) + + with self.assertLogs(helper_logger) as cm: + statements = parse( + """ + BEGIN + DECLARE 1; + IF from_date IS NULL THEN SET x = 1; + END IF; + END + """, + read="bigquery", + ) + self.assertIn("unsupported syntax", cm.output[0]) + + for actual, expected in zip( + statements, ("BEGIN DECLARE 1", "IF from_date IS NULL THEN SET x = 1", "END IF", "END") + ): + self.assertEqual(actual.sql(dialect="bigquery"), expected) + with self.assertLogs(helper_logger) as cm: self.validate_identity( "SELECT * FROM t AS t(c1, c2)", @@ -77,14 +101,16 @@ class TestBigQuery(Validator): with self.assertRaises(ParseError): transpile("DATE_ADD(x, day)", read="bigquery") - for_in_stmts = parse( - "FOR record IN (SELECT word FROM shakespeare) DO SELECT record.word; END FOR;", - read="bigquery", - ) - self.assertEqual( - [s.sql(dialect="bigquery") for s in for_in_stmts], - ["FOR record IN (SELECT word FROM shakespeare) DO SELECT record.word", "END FOR"], - ) + with self.assertLogs(parser_logger) as cm: + for_in_stmts = parse( + "FOR record IN (SELECT word FROM shakespeare) DO SELECT record.word; END FOR;", + read="bigquery", + ) + self.assertEqual( + [s.sql(dialect="bigquery") for s in for_in_stmts], + ["FOR record IN (SELECT word FROM shakespeare) DO SELECT record.word", "END FOR"], + ) + assert "'END FOR'" in cm.output[0] self.validate_identity("SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)") self.validate_identity("TIME('2008-12-25 15:30:00+08')") @@ -135,7 +161,7 @@ class TestBigQuery(Validator): self.validate_identity("""CREATE TABLE x (a STRUCT<b STRING OPTIONS (description='b')>)""") self.validate_identity("CAST(x AS TIMESTAMP)") self.validate_identity("REGEXP_EXTRACT(`foo`, 'bar: (.+?)', 1, 1)") - self.validate_identity("BEGIN A B C D E F") + self.validate_identity("BEGIN DECLARE y INT64", check_command_warning=True) self.validate_identity("BEGIN TRANSACTION") self.validate_identity("COMMIT TRANSACTION") self.validate_identity("ROLLBACK TRANSACTION") diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py index 84903aa..f36af41 100644 --- a/tests/dialects/test_clickhouse.py +++ b/tests/dialects/test_clickhouse.py @@ -42,7 +42,6 @@ class TestClickhouse(Validator): self.validate_identity("SELECT isNaN(1.0)") self.validate_identity("SELECT startsWith('Spider-Man', 'Spi')") self.validate_identity("SELECT xor(TRUE, FALSE)") - self.validate_identity("ATTACH DATABASE DEFAULT ENGINE = ORDINARY") self.validate_identity("CAST(['hello'], 'Array(Enum8(''hello'' = 1))')") self.validate_identity("SELECT x, COUNT() FROM y GROUP BY x WITH TOTALS") self.validate_identity("SELECT INTERVAL t.days DAY") @@ -76,6 +75,9 @@ class TestClickhouse(Validator): self.validate_identity("CAST(x as MEDIUMINT)", "CAST(x AS Int32)") self.validate_identity("SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src") self.validate_identity( + "ATTACH DATABASE DEFAULT ENGINE = ORDINARY", check_command_warning=True + ) + self.validate_identity( "SELECT n, source FROM (SELECT toFloat32(number % 10) AS n, 'original' AS source FROM numbers(10) WHERE number % 3 = 1) ORDER BY n WITH FILL" ) self.validate_identity( @@ -728,3 +730,19 @@ LIFETIME(MIN 0 MAX 0)""", ) self.validate_identity("""CREATE TABLE ip_data (ip4 IPv4, ip6 IPv6) ENGINE=TinyLog()""") self.validate_identity("""CREATE TABLE dates (dt1 Date32) ENGINE=TinyLog()""") + self.validate_all( + """ + CREATE TABLE t ( + a AggregateFunction(quantiles(0.5, 0.9), UInt64), + b AggregateFunction(quantiles, UInt64), + c SimpleAggregateFunction(sum, Float64) + )""", + write={ + "clickhouse": """CREATE TABLE t ( + a AggregateFunction(quantiles(0.5, 0.9), UInt64), + b AggregateFunction(quantiles, UInt64), + c SimpleAggregateFunction(sum, Float64) +)""" + }, + pretty=True, + ) diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 3cf4ddc..22e7d49 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -11,6 +11,7 @@ from sqlglot import ( parse_one, ) from sqlglot.dialects import BigQuery, Hive, Snowflake +from sqlglot.parser import logger as parser_logger class Validator(unittest.TestCase): @@ -19,8 +20,14 @@ class Validator(unittest.TestCase): def parse_one(self, sql): return parse_one(sql, read=self.dialect) - def validate_identity(self, sql, write_sql=None, pretty=False): - expression = self.parse_one(sql) + def validate_identity(self, sql, write_sql=None, pretty=False, check_command_warning=False): + if check_command_warning: + with self.assertLogs(parser_logger) as cm: + expression = self.parse_one(sql) + assert f"'{sql[:100]}' contains unsupported syntax" in cm.output[0] + else: + expression = self.parse_one(sql) + self.assertEqual(write_sql or sql, expression.sql(dialect=self.dialect, pretty=pretty)) return expression diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index e5f7e0c..f3b41b4 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -196,11 +196,13 @@ class TestDuckDB(Validator): self.validate_identity("SELECT ROW(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)") self.validate_identity("SELECT (x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y)") self.validate_identity("SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a)") - self.validate_identity("ATTACH DATABASE ':memory:' AS new_database") self.validate_identity("FROM x SELECT x UNION SELECT 1", "SELECT x FROM x UNION SELECT 1") self.validate_identity("FROM (FROM tbl)", "SELECT * FROM (SELECT * FROM tbl)") self.validate_identity("FROM tbl", "SELECT * FROM tbl") self.validate_identity( + "ATTACH DATABASE ':memory:' AS new_database", check_command_warning=True + ) + self.validate_identity( "SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}" ) self.validate_identity( diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py index 8b5a945..d1b7589 100644 --- a/tests/dialects/test_hive.py +++ b/tests/dialects/test_hive.py @@ -399,6 +399,7 @@ class TestHive(Validator): ) def test_hive(self): + self.validate_identity("SET hiveconf:some_var = 5", check_command_warning=True) self.validate_identity("(VALUES (1 AS a, 2 AS b, 3))") self.validate_identity("SELECT * FROM my_table TIMESTAMP AS OF DATE_ADD(CURRENT_DATE, -1)") self.validate_identity("SELECT * FROM my_table VERSION AS OF DATE_ADD(CURRENT_DATE, -1)") @@ -441,13 +442,6 @@ class TestHive(Validator): ) self.validate_all( - "SET hiveconf:some_var = 5", - write={ - "hive": "SET hiveconf:some_var = 5", - "spark": "SET hiveconf:some_var = 5", - }, - ) - self.validate_all( "SELECT ${hiveconf:some_var}", write={ "hive": "SELECT ${hiveconf:some_var}", diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 85bf261..3a3e49e 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -126,7 +126,7 @@ class TestMySQL(Validator): self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')") self.validate_identity("SELECT @var1 := 1, @var2") self.validate_identity("UNLOCK TABLES") - self.validate_identity("LOCK TABLES `app_fields` WRITE") + self.validate_identity("LOCK TABLES `app_fields` WRITE", check_command_warning=True) self.validate_identity("SELECT 1 XOR 0") self.validate_identity("SELECT 1 && 0", "SELECT 1 AND 0") self.validate_identity("SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2") diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index fce714e..bc8f8bb 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -12,6 +12,7 @@ class TestOracle(Validator): exp.AlterTable ) + self.validate_identity("TIMESTAMP(3) WITH TIME ZONE") self.validate_identity("CURRENT_TIMESTAMP(precision)") self.validate_identity("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol") self.validate_identity("ALTER TABLE Payments ADD Stock NUMBER NOT NULL") diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index f46eeba..dc00c85 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -7,214 +7,10 @@ class TestPostgres(Validator): maxDiff = None dialect = "postgres" - def test_ddl(self): - expr = parse_one("CREATE TABLE t (x INTERVAL day)", read="postgres") - cdef = expr.find(exp.ColumnDef) - cdef.args["kind"].assert_is(exp.DataType) - self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)") - - self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)") - self.validate_identity("CREATE TABLE test (elems JSONB[])") - self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)") - self.validate_identity("CREATE TABLE test (foo HSTORE)") - self.validate_identity("CREATE TABLE test (foo JSONB)") - self.validate_identity("CREATE TABLE test (foo VARCHAR(64)[])") - self.validate_identity("CREATE TABLE test (foo INT) PARTITION BY HASH(foo)") - self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a") - self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a, b") - self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING *") - self.validate_identity("UPDATE tbl_name SET foo = 123 RETURNING a") - self.validate_identity("CREATE TABLE cities_partdef PARTITION OF cities DEFAULT") - self.validate_identity( - "CREATE CONSTRAINT TRIGGER my_trigger AFTER INSERT OR DELETE OR UPDATE OF col_a, col_b ON public.my_table DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION do_sth()" - ) - self.validate_identity( - "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)" - ) - self.validate_identity( - "CREATE TABLE measurement_y2016m07 PARTITION OF measurement (unitsales DEFAULT 0) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')" - ) - self.validate_identity( - "CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11)" - ) - self.validate_identity( - "CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12)" - ) - self.validate_identity( - "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b')" - ) - self.validate_identity( - "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b') PARTITION BY RANGE(population)" - ) - self.validate_identity( - "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)" - ) - self.validate_identity( - "CREATE INDEX index_issues_on_title_trigram ON public.issues USING gin(title public.gin_trgm_ops)" - ) - self.validate_identity( - "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *" - ) - self.validate_identity( - "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = 1 RETURNING *" - ) - self.validate_identity( - "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = excluded.id RETURNING *" - ) - self.validate_identity( - "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO NOTHING RETURNING *" - ) - self.validate_identity( - "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO UPDATE SET x.id = 1 RETURNING *" - ) - self.validate_identity( - "DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid RETURNING a" - ) - self.validate_identity( - "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])", - "CREATE TABLE test (x TIMESTAMP[][])", - ) - self.validate_identity( - "CREATE UNLOGGED TABLE foo AS WITH t(c) AS (SELECT 1) SELECT * FROM (SELECT c AS c FROM t) AS temp" - ) - self.validate_identity( - "WITH t(c) AS (SELECT 1) SELECT * INTO UNLOGGED foo FROM (SELECT c AS c FROM t) AS temp" - ) - - self.validate_all( - "CREATE OR REPLACE FUNCTION function_name (input_a character varying DEFAULT NULL::character varying)", - write={ - "postgres": "CREATE OR REPLACE FUNCTION function_name(input_a VARCHAR DEFAULT CAST(NULL AS VARCHAR))", - }, - ) - self.validate_all( - "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)", - write={ - "postgres": "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)" - }, - ) - self.validate_all( - "CREATE TABLE products (product_no INT CONSTRAINT must_be_different UNIQUE, name TEXT CONSTRAINT present NOT NULL, price DECIMAL)", - write={ - "postgres": "CREATE TABLE products (product_no INT CONSTRAINT must_be_different UNIQUE, name TEXT CONSTRAINT present NOT NULL, price DECIMAL)" - }, - ) - self.validate_all( - "CREATE TABLE products (product_no INT, name TEXT, price DECIMAL, UNIQUE (product_no, name))", - write={ - "postgres": "CREATE TABLE products (product_no INT, name TEXT, price DECIMAL, UNIQUE (product_no, name))" - }, - ) - self.validate_all( - "CREATE TABLE products (" - "product_no INT UNIQUE," - " name TEXT," - " price DECIMAL CHECK (price > 0)," - " discounted_price DECIMAL CONSTRAINT positive_discount CHECK (discounted_price > 0)," - " CHECK (product_no > 1)," - " CONSTRAINT valid_discount CHECK (price > discounted_price))", - write={ - "postgres": "CREATE TABLE products (" - "product_no INT UNIQUE," - " name TEXT," - " price DECIMAL CHECK (price > 0)," - " discounted_price DECIMAL CONSTRAINT positive_discount CHECK (discounted_price > 0)," - " CHECK (product_no > 1)," - " CONSTRAINT valid_discount CHECK (price > discounted_price))" - }, - ) - self.validate_identity( - """ - CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial - ON public.ci_builds - USING btree (commit_id, artifacts_expire_at, id) - WHERE ( - ((type)::text = 'Ci::Build'::text) - AND ((retried = false) OR (retried IS NULL)) - AND ((name)::text = ANY (ARRAY[ - ('sast'::character varying)::text, - ('dependency_scanning'::character varying)::text, - ('sast:container'::character varying)::text, - ('container_scanning'::character varying)::text, - ('dast'::character varying)::text - ])) - ) - """, - "CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial ON public.ci_builds USING btree(commit_id, artifacts_expire_at, id) WHERE ((CAST((type) AS TEXT) = CAST('Ci::Build' AS TEXT)) AND ((retried = FALSE) OR (retried IS NULL)) AND (CAST((name) AS TEXT) = ANY (ARRAY[CAST((CAST('sast' AS VARCHAR)) AS TEXT), CAST((CAST('dependency_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('sast:container' AS VARCHAR)) AS TEXT), CAST((CAST('container_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('dast' AS VARCHAR)) AS TEXT)])))", - ) - self.validate_identity( - "CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)" - ) - - with self.assertRaises(ParseError): - transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres") - with self.assertRaises(ParseError): - transpile( - "CREATE TABLE products (price DECIMAL, CHECK price > 1)", - read="postgres", - ) - - def test_unnest(self): - self.validate_identity( - "SELECT * FROM UNNEST(ARRAY[1, 2], ARRAY['foo', 'bar', 'baz']) AS x(a, b)" - ) - - self.validate_all( - "SELECT UNNEST(c) FROM t", - write={ - "hive": "SELECT EXPLODE(c) FROM t", - "postgres": "SELECT UNNEST(c) FROM t", - "presto": "SELECT IF(_u.pos = _u_2.pos_2, _u_2.col) AS col FROM t CROSS JOIN UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(c)))) AS _u(pos) CROSS JOIN UNNEST(c) WITH ORDINALITY AS _u_2(col, pos_2) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > CARDINALITY(c) AND _u_2.pos_2 = CARDINALITY(c))", - }, - ) - self.validate_all( - "SELECT UNNEST(ARRAY[1])", - write={ - "hive": "SELECT EXPLODE(ARRAY(1))", - "postgres": "SELECT UNNEST(ARRAY[1])", - "presto": "SELECT IF(_u.pos = _u_2.pos_2, _u_2.col) AS col FROM UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(ARRAY[1])))) AS _u(pos) CROSS JOIN UNNEST(ARRAY[1]) WITH ORDINALITY AS _u_2(col, pos_2) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > CARDINALITY(ARRAY[1]) AND _u_2.pos_2 = CARDINALITY(ARRAY[1]))", - }, - ) - - def test_array_offset(self): - with self.assertLogs(helper_logger) as cm: - self.validate_all( - "SELECT col[1]", - write={ - "bigquery": "SELECT col[0]", - "duckdb": "SELECT col[1]", - "hive": "SELECT col[0]", - "postgres": "SELECT col[1]", - "presto": "SELECT col[1]", - }, - ) - - self.assertEqual( - cm.output, - [ - "WARNING:sqlglot:Applying array index offset (-1)", - "WARNING:sqlglot:Applying array index offset (1)", - "WARNING:sqlglot:Applying array index offset (1)", - "WARNING:sqlglot:Applying array index offset (1)", - ], - ) - - def test_operator(self): - expr = parse_one("1 OPERATOR(+) 2 OPERATOR(*) 3", read="postgres") - - expr.left.assert_is(exp.Operator) - expr.left.left.assert_is(exp.Literal) - expr.left.right.assert_is(exp.Literal) - expr.right.assert_is(exp.Literal) - self.assertEqual(expr.sql(dialect="postgres"), "1 OPERATOR(+) 2 OPERATOR(*) 3") - - self.validate_identity("SELECT operator FROM t") - self.validate_identity("SELECT 1 OPERATOR(+) 2") - self.validate_identity("SELECT 1 OPERATOR(+) /* foo */ 2") - self.validate_identity("SELECT 1 OPERATOR(pg_catalog.+) 2") - def test_postgres(self): - self.validate_identity("EXEC AS myfunc @id = 123") + self.validate_identity("SELECT CURRENT_USER") + self.validate_identity("CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) * -100") + self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True) expr = parse_one( "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)", read="postgres" @@ -782,6 +578,246 @@ class TestPostgres(Validator): self.assertIsInstance(parse_one("id::UUID", read="postgres"), exp.Cast) + def test_ddl(self): + expr = parse_one("CREATE TABLE t (x INTERVAL day)", read="postgres") + cdef = expr.find(exp.ColumnDef) + cdef.args["kind"].assert_is(exp.DataType) + self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)") + + self.validate_identity("CREATE INDEX et_vid_idx ON et(vid) INCLUDE (fid)") + self.validate_identity("CREATE INDEX idx_x ON x USING BTREE(x, y) WHERE (NOT y IS NULL)") + self.validate_identity("CREATE TABLE test (elems JSONB[])") + self.validate_identity("CREATE TABLE public.y (x TSTZRANGE NOT NULL)") + self.validate_identity("CREATE TABLE test (foo HSTORE)") + self.validate_identity("CREATE TABLE test (foo JSONB)") + self.validate_identity("CREATE TABLE test (foo VARCHAR(64)[])") + self.validate_identity("CREATE TABLE test (foo INT) PARTITION BY HASH(foo)") + self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a") + self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING a, b") + self.validate_identity("INSERT INTO x VALUES (1, 'a', 2.0) RETURNING *") + self.validate_identity("UPDATE tbl_name SET foo = 123 RETURNING a") + self.validate_identity("CREATE TABLE cities_partdef PARTITION OF cities DEFAULT") + self.validate_identity("CREATE TABLE t (c CHAR(2) UNIQUE NOT NULL) INHERITS (t1)") + self.validate_identity("CREATE TABLE s.t (c CHAR(2) UNIQUE NOT NULL) INHERITS (s.t1, s.t2)") + self.validate_identity("CREATE FUNCTION x(INT) RETURNS INT SET search_path = 'public'") + self.validate_identity( + "CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (MODULUS 3, REMAINDER 2)" + ) + self.validate_identity( + "CREATE TABLE measurement_y2016m07 PARTITION OF measurement (unitsales DEFAULT 0) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')" + ) + self.validate_identity( + "CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11)" + ) + self.validate_identity( + "CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12)" + ) + self.validate_identity( + "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b')" + ) + self.validate_identity( + "CREATE TABLE cities_ab PARTITION OF cities (CONSTRAINT city_id_nonzero CHECK (city_id <> 0)) FOR VALUES IN ('a', 'b') PARTITION BY RANGE(population)" + ) + self.validate_identity( + "CREATE INDEX foo ON bar.baz USING btree(col1 varchar_pattern_ops ASC, col2)" + ) + self.validate_identity( + "CREATE INDEX index_issues_on_title_trigram ON public.issues USING gin(title public.gin_trgm_ops)" + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO NOTHING RETURNING *" + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = 1 RETURNING *" + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT (id) DO UPDATE SET x.id = excluded.id RETURNING *" + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO NOTHING RETURNING *" + ) + self.validate_identity( + "INSERT INTO x VALUES (1, 'a', 2.0) ON CONFLICT ON CONSTRAINT pkey DO UPDATE SET x.id = 1 RETURNING *" + ) + self.validate_identity( + "DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid RETURNING a" + ) + self.validate_identity( + "WITH t(c) AS (SELECT 1) SELECT * INTO UNLOGGED foo FROM (SELECT c AS c FROM t) AS temp" + ) + self.validate_identity( + "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])", + "CREATE TABLE test (x TIMESTAMP[][])", + ) + self.validate_identity( + "CREATE FUNCTION add(INT, INT) RETURNS INT SET search_path TO 'public' AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE", + check_command_warning=True, + ) + self.validate_identity( + "CREATE FUNCTION x(INT) RETURNS INT SET foo FROM CURRENT", + check_command_warning=True, + ) + self.validate_identity( + "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT", + check_command_warning=True, + ) + self.validate_identity( + "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE CALLED ON NULL INPUT", + check_command_warning=True, + ) + self.validate_identity( + "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE STRICT", + check_command_warning=True, + ) + self.validate_identity( + "CREATE CONSTRAINT TRIGGER my_trigger AFTER INSERT OR DELETE OR UPDATE OF col_a, col_b ON public.my_table DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION do_sth()", + check_command_warning=True, + ) + self.validate_identity( + "CREATE UNLOGGED TABLE foo AS WITH t(c) AS (SELECT 1) SELECT * FROM (SELECT c AS c FROM t) AS temp", + check_command_warning=True, + ) + self.validate_identity( + "CREATE FUNCTION x(INT) RETURNS INT SET search_path TO 'public'", + "CREATE FUNCTION x(INT) RETURNS INT SET search_path = 'public'", + ) + self.validate_identity( + "CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE[][])", + "CREATE TABLE test (x TIMESTAMP[][])", + ) + + self.validate_all( + "CREATE OR REPLACE FUNCTION function_name (input_a character varying DEFAULT NULL::character varying)", + write={ + "postgres": "CREATE OR REPLACE FUNCTION function_name(input_a VARCHAR DEFAULT CAST(NULL AS VARCHAR))", + }, + ) + self.validate_all( + "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)", + write={ + "postgres": "CREATE TABLE products (product_no INT UNIQUE, name TEXT, price DECIMAL)" + }, + ) + self.validate_all( + "CREATE TABLE products (product_no INT CONSTRAINT must_be_different UNIQUE, name TEXT CONSTRAINT present NOT NULL, price DECIMAL)", + write={ + "postgres": "CREATE TABLE products (product_no INT CONSTRAINT must_be_different UNIQUE, name TEXT CONSTRAINT present NOT NULL, price DECIMAL)" + }, + ) + self.validate_all( + "CREATE TABLE products (product_no INT, name TEXT, price DECIMAL, UNIQUE (product_no, name))", + write={ + "postgres": "CREATE TABLE products (product_no INT, name TEXT, price DECIMAL, UNIQUE (product_no, name))" + }, + ) + self.validate_all( + "CREATE TABLE products (" + "product_no INT UNIQUE," + " name TEXT," + " price DECIMAL CHECK (price > 0)," + " discounted_price DECIMAL CONSTRAINT positive_discount CHECK (discounted_price > 0)," + " CHECK (product_no > 1)," + " CONSTRAINT valid_discount CHECK (price > discounted_price))", + write={ + "postgres": "CREATE TABLE products (" + "product_no INT UNIQUE," + " name TEXT," + " price DECIMAL CHECK (price > 0)," + " discounted_price DECIMAL CONSTRAINT positive_discount CHECK (discounted_price > 0)," + " CHECK (product_no > 1)," + " CONSTRAINT valid_discount CHECK (price > discounted_price))" + }, + ) + self.validate_identity( + """ + CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial + ON public.ci_builds + USING btree (commit_id, artifacts_expire_at, id) + WHERE ( + ((type)::text = 'Ci::Build'::text) + AND ((retried = false) OR (retried IS NULL)) + AND ((name)::text = ANY (ARRAY[ + ('sast'::character varying)::text, + ('dependency_scanning'::character varying)::text, + ('sast:container'::character varying)::text, + ('container_scanning'::character varying)::text, + ('dast'::character varying)::text + ])) + ) + """, + "CREATE INDEX index_ci_builds_on_commit_id_and_artifacts_expireatandidpartial ON public.ci_builds USING btree(commit_id, artifacts_expire_at, id) WHERE ((CAST((type) AS TEXT) = CAST('Ci::Build' AS TEXT)) AND ((retried = FALSE) OR (retried IS NULL)) AND (CAST((name) AS TEXT) = ANY (ARRAY[CAST((CAST('sast' AS VARCHAR)) AS TEXT), CAST((CAST('dependency_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('sast:container' AS VARCHAR)) AS TEXT), CAST((CAST('container_scanning' AS VARCHAR)) AS TEXT), CAST((CAST('dast' AS VARCHAR)) AS TEXT)])))", + ) + self.validate_identity( + "CREATE INDEX index_ci_pipelines_on_project_idandrefandiddesc ON public.ci_pipelines USING btree(project_id, ref, id DESC)" + ) + + with self.assertRaises(ParseError): + transpile("CREATE TABLE products (price DECIMAL CHECK price > 0)", read="postgres") + with self.assertRaises(ParseError): + transpile( + "CREATE TABLE products (price DECIMAL, CHECK price > 1)", + read="postgres", + ) + + def test_unnest(self): + self.validate_identity( + "SELECT * FROM UNNEST(ARRAY[1, 2], ARRAY['foo', 'bar', 'baz']) AS x(a, b)" + ) + + self.validate_all( + "SELECT UNNEST(c) FROM t", + write={ + "hive": "SELECT EXPLODE(c) FROM t", + "postgres": "SELECT UNNEST(c) FROM t", + "presto": "SELECT IF(_u.pos = _u_2.pos_2, _u_2.col) AS col FROM t CROSS JOIN UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(c)))) AS _u(pos) CROSS JOIN UNNEST(c) WITH ORDINALITY AS _u_2(col, pos_2) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > CARDINALITY(c) AND _u_2.pos_2 = CARDINALITY(c))", + }, + ) + self.validate_all( + "SELECT UNNEST(ARRAY[1])", + write={ + "hive": "SELECT EXPLODE(ARRAY(1))", + "postgres": "SELECT UNNEST(ARRAY[1])", + "presto": "SELECT IF(_u.pos = _u_2.pos_2, _u_2.col) AS col FROM UNNEST(SEQUENCE(1, GREATEST(CARDINALITY(ARRAY[1])))) AS _u(pos) CROSS JOIN UNNEST(ARRAY[1]) WITH ORDINALITY AS _u_2(col, pos_2) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > CARDINALITY(ARRAY[1]) AND _u_2.pos_2 = CARDINALITY(ARRAY[1]))", + }, + ) + + def test_array_offset(self): + with self.assertLogs(helper_logger) as cm: + self.validate_all( + "SELECT col[1]", + write={ + "bigquery": "SELECT col[0]", + "duckdb": "SELECT col[1]", + "hive": "SELECT col[0]", + "postgres": "SELECT col[1]", + "presto": "SELECT col[1]", + }, + ) + + self.assertEqual( + cm.output, + [ + "WARNING:sqlglot:Applying array index offset (-1)", + "WARNING:sqlglot:Applying array index offset (1)", + "WARNING:sqlglot:Applying array index offset (1)", + "WARNING:sqlglot:Applying array index offset (1)", + ], + ) + + def test_operator(self): + expr = parse_one("1 OPERATOR(+) 2 OPERATOR(*) 3", read="postgres") + + expr.left.assert_is(exp.Operator) + expr.left.left.assert_is(exp.Literal) + expr.left.right.assert_is(exp.Literal) + expr.right.assert_is(exp.Literal) + self.assertEqual(expr.sql(dialect="postgres"), "1 OPERATOR(+) 2 OPERATOR(*) 3") + + self.validate_identity("SELECT operator FROM t") + self.validate_identity("SELECT 1 OPERATOR(+) 2") + self.validate_identity("SELECT 1 OPERATOR(+) /* foo */ 2") + self.validate_identity("SELECT 1 OPERATOR(pg_catalog.+) 2") + def test_bool_or(self): self.validate_all( "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a", diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index 88fef67..9ccd955 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -332,10 +332,12 @@ class TestRedshift(Validator): "CREATE TABLE sales (salesid INTEGER NOT NULL) DISTKEY(listid) COMPOUND SORTKEY(listid, sellerid) DISTSTYLE AUTO" ) self.validate_identity( - "COPY customer FROM 's3://mybucket/customer' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'" + "COPY customer FROM 's3://mybucket/customer' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'", + check_command_warning=True, ) self.validate_identity( - "UNLOAD ('select * from venue') TO 's3://mybucket/unload/' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'" + "UNLOAD ('select * from venue') TO 's3://mybucket/unload/' IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'", + check_command_warning=True, ) self.validate_identity( "CREATE TABLE SOUP (SOUP1 VARCHAR(50) NOT NULL ENCODE ZSTD, SOUP2 VARCHAR(70) NULL ENCODE DELTA)" diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 0882290..7e41fd4 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -10,6 +10,7 @@ class TestSnowflake(Validator): dialect = "snowflake" def test_snowflake(self): + self.validate_identity("ALTER TABLE table1 CLUSTER BY (name DESC)") self.validate_identity( "INSERT OVERWRITE TABLE t SELECT 1", "INSERT OVERWRITE INTO t SELECT 1" ) @@ -39,8 +40,8 @@ WHERE )""", ) - self.validate_identity("RM @parquet_stage") - self.validate_identity("REMOVE @parquet_stage") + self.validate_identity("RM @parquet_stage", check_command_warning=True) + self.validate_identity("REMOVE @parquet_stage", check_command_warning=True) self.validate_identity("SELECT TIMESTAMP_FROM_PARTS(d, t)") self.validate_identity("SELECT GET_PATH(v, 'attr[0].name') FROM vartab") self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))") @@ -84,6 +85,10 @@ WHERE "SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)" ) self.validate_identity( + "SELECT p FROM t WHERE p:val NOT IN ('2')", + "SELECT p FROM t WHERE NOT GET_PATH(p, 'val') IN ('2')", + ) + self.validate_identity( """SELECT PARSE_JSON('{"x": "hello"}'):x LIKE 'hello'""", """SELECT GET_PATH(PARSE_JSON('{"x": "hello"}'), 'x') LIKE 'hello'""", ) @@ -777,9 +782,10 @@ WHERE self.validate_identity("SELECT * FROM @namespace.mystage/path/to/file.json.gz") self.validate_identity("SELECT * FROM @namespace.%table_name/path/to/file.json.gz") self.validate_identity("SELECT * FROM '@external/location' (FILE_FORMAT => 'path.to.csv')") - self.validate_identity("PUT file:///dir/tmp.csv @%table") + self.validate_identity("PUT file:///dir/tmp.csv @%table", check_command_warning=True) self.validate_identity( - 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)' + 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)', + check_command_warning=True, ) self.validate_identity( "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla" @@ -1095,7 +1101,7 @@ WHERE ) def test_stored_procedures(self): - self.validate_identity("CALL a.b.c(x, y)") + self.validate_identity("CALL a.b.c(x, y)", check_command_warning=True) self.validate_identity( "CREATE PROCEDURE a.b.c(x INT, y VARIANT) RETURNS OBJECT EXECUTE AS CALLER AS 'BEGIN SELECT 1; END;'" ) @@ -1449,10 +1455,10 @@ MATCH_RECOGNIZE ( def test_show(self): # Parsed as Command - self.validate_identity("SHOW TABLES LIKE 'line%' IN tpch.public") - - ast = parse_one("SHOW TABLES HISTORY IN tpch.public", read="snowflake") - self.assertIsInstance(ast, exp.Command) + self.validate_identity( + "SHOW TABLES LIKE 'line%' IN tpch.public", check_command_warning=True + ) + self.validate_identity("SHOW TABLES HISTORY IN tpch.public", check_command_warning=True) # Parsed as Show self.validate_identity("SHOW PRIMARY KEYS") @@ -1469,6 +1475,18 @@ MATCH_RECOGNIZE ( 'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', 'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"', ) + self.validate_identity( + "show terse schemas in database db1 starts with 'a' limit 10 from 'b'", + "SHOW TERSE SCHEMAS IN DATABASE db1 STARTS WITH 'a' LIMIT 10 FROM 'b'", + ) + self.validate_identity( + "show terse objects in schema db1.schema1 starts with 'a' limit 10 from 'b'", + "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'", + ) + self.validate_identity( + "show terse objects in db1.schema1 starts with 'a' limit 10 from 'b'", + "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'", + ) ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', read="snowflake") table = ast.find(exp.Table) @@ -1489,6 +1507,16 @@ MATCH_RECOGNIZE ( self.assertEqual(literal.sql(dialect="snowflake"), "'_testing%'") + ast = parse_one("SHOW SCHEMAS IN DATABASE db1", read="snowflake") + self.assertEqual(ast.args.get("scope_kind"), "DATABASE") + table = ast.find(exp.Table) + self.assertEqual(table.sql(dialect="snowflake"), "db1") + + ast = parse_one("SHOW OBJECTS IN db1.schema1", read="snowflake") + self.assertEqual(ast.args.get("scope_kind"), "SCHEMA") + table = ast.find(exp.Table) + self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1") + def test_swap(self): ast = parse_one("ALTER TABLE a SWAP WITH b", read="snowflake") assert isinstance(ast, exp.AlterTable) diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py index 56a573a..6044037 100644 --- a/tests/dialects/test_spark.py +++ b/tests/dialects/test_spark.py @@ -227,7 +227,6 @@ TBLPROPERTIES ( ) def test_spark(self): - self.validate_identity("FROM_UTC_TIMESTAMP(CAST(x AS TIMESTAMP), 'utc')") expr = parse_one("any_value(col, true)", read="spark") self.assertIsInstance(expr.args.get("ignore_nulls"), exp.Boolean) self.assertEqual(expr.sql(dialect="spark"), "ANY_VALUE(col, TRUE)") @@ -277,6 +276,25 @@ TBLPROPERTIES ( ) self.validate_all( + "SELECT TO_UTC_TIMESTAMP('2016-08-31', 'Asia/Seoul')", + write={ + "bigquery": "SELECT DATETIME(TIMESTAMP(CAST('2016-08-31' AS DATETIME), 'Asia/Seoul'), 'UTC')", + "duckdb": "SELECT CAST('2016-08-31' AS TIMESTAMP) AT TIME ZONE 'Asia/Seoul' AT TIME ZONE 'UTC'", + "postgres": "SELECT CAST('2016-08-31' AS TIMESTAMP) AT TIME ZONE 'Asia/Seoul' AT TIME ZONE 'UTC'", + "presto": "SELECT WITH_TIMEZONE(CAST('2016-08-31' AS TIMESTAMP), 'Asia/Seoul') AT TIME ZONE 'UTC'", + "redshift": "SELECT CAST('2016-08-31' AS TIMESTAMP) AT TIME ZONE 'Asia/Seoul' AT TIME ZONE 'UTC'", + "snowflake": "SELECT CONVERT_TIMEZONE('Asia/Seoul', 'UTC', CAST('2016-08-31' AS TIMESTAMPNTZ))", + "spark": "SELECT TO_UTC_TIMESTAMP(CAST('2016-08-31' AS TIMESTAMP), 'Asia/Seoul')", + }, + ) + self.validate_all( + "SELECT FROM_UTC_TIMESTAMP('2016-08-31', 'Asia/Seoul')", + write={ + "presto": "SELECT CAST('2016-08-31' AS TIMESTAMP) AT TIME ZONE 'Asia/Seoul'", + "spark": "SELECT FROM_UTC_TIMESTAMP(CAST('2016-08-31' AS TIMESTAMP), 'Asia/Seoul')", + }, + ) + self.validate_all( "foo.bar", read={ "": "STRUCT_EXTRACT(foo, bar)", diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py index 85d4ebf..f3894fd 100644 --- a/tests/dialects/test_teradata.py +++ b/tests/dialects/test_teradata.py @@ -42,11 +42,13 @@ class TestTeradata(Validator): ) def test_statistics(self): - self.validate_identity("COLLECT STATISTICS ON tbl INDEX(col)") - self.validate_identity("COLLECT STATS ON tbl COLUMNS(col)") - self.validate_identity("COLLECT STATS COLUMNS(col) ON tbl") - self.validate_identity("HELP STATISTICS personel.employee") - self.validate_identity("HELP STATISTICS personnel.employee FROM my_qcd") + self.validate_identity("COLLECT STATISTICS ON tbl INDEX(col)", check_command_warning=True) + self.validate_identity("COLLECT STATS ON tbl COLUMNS(col)", check_command_warning=True) + self.validate_identity("COLLECT STATS COLUMNS(col) ON tbl", check_command_warning=True) + self.validate_identity("HELP STATISTICS personel.employee", check_command_warning=True) + self.validate_identity( + "HELP STATISTICS personnel.employee FROM my_qcd", check_command_warning=True + ) def test_create(self): self.validate_identity( diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 7cf9971..101d356 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -1,4 +1,5 @@ from sqlglot import exp, parse, parse_one +from sqlglot.parser import logger as parser_logger from tests.dialects.test_dialect import Validator @@ -7,7 +8,7 @@ class TestTSQL(Validator): def test_tsql(self): self.validate_identity("ROUND(x, 1, 0)") - self.validate_identity("EXEC MyProc @id=7, @name='Lochristi'") + self.validate_identity("EXEC MyProc @id=7, @name='Lochristi'", check_command_warning=True) # https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187879(v=sql.105)?redirectedfrom=MSDN # tsql allows .. which means use the default schema self.validate_identity("SELECT * FROM a..b") @@ -225,7 +226,7 @@ class TestTSQL(Validator): "MERGE INTO mytable WITH (HOLDLOCK) AS T USING mytable_merge AS S " "ON (T.user_id = S.user_id) WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (S.c1, S.c2)" ) - self.validate_identity("UPDATE STATISTICS x") + self.validate_identity("UPDATE STATISTICS x", check_command_warning=True) self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b INTO @y FROM y") self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b FROM y") self.validate_identity("INSERT INTO x (y) OUTPUT x.a, x.b INTO l SELECT * FROM z") @@ -238,14 +239,16 @@ class TestTSQL(Validator): self.validate_identity("END") self.validate_identity("@x") self.validate_identity("#x") - self.validate_identity("DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'") - self.validate_identity("PRINT @TestVariable") + self.validate_identity("PRINT @TestVariable", check_command_warning=True) self.validate_identity("SELECT Employee_ID, Department_ID FROM @MyTableVar") self.validate_identity("INSERT INTO @TestTable VALUES (1, 'Value1', 12, 20)") self.validate_identity("SELECT * FROM #foo") self.validate_identity("SELECT * FROM ##foo") self.validate_identity("SELECT a = 1", "SELECT 1 AS a") self.validate_identity( + "DECLARE @TestVariable AS VARCHAR(100)='Save Our Planet'", check_command_warning=True + ) + self.validate_identity( "SELECT a = 1 UNION ALL SELECT a = b", "SELECT 1 AS a UNION ALL SELECT b AS a" ) self.validate_identity( @@ -789,7 +792,8 @@ class TestTSQL(Validator): def test_udf(self): self.validate_identity( - "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)" + "DECLARE @DWH_DateCreated DATETIME = CONVERT(DATETIME, getdate(), 104)", + check_command_warning=True, ) self.validate_identity( "CREATE PROCEDURE foo @a INTEGER, @b INTEGER AS SELECT @a = SUM(bla) FROM baz AS bar" @@ -882,8 +886,9 @@ WHERE "END", ] - for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): - self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + with self.assertLogs(parser_logger) as cm: + for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): + self.assertEqual(expr.sql(dialect="tsql"), expected_sql) sql = """ CREATE PROC [dbo].[transform_proc] AS @@ -902,8 +907,9 @@ WHERE "CREATE TABLE [target_schema].[target_table] (a INTEGER) WITH (DISTRIBUTION=REPLICATE, HEAP)", ] - for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): - self.assertEqual(expr.sql(dialect="tsql"), expected_sql) + with self.assertLogs(parser_logger) as cm: + for expr, expected_sql in zip(parse(sql, read="tsql"), expected_sqls): + self.assertEqual(expr.sql(dialect="tsql"), expected_sql) def test_charindex(self): self.validate_all( @@ -932,7 +938,11 @@ WHERE ) def test_len(self): - self.validate_all("LEN(x)", read={"": "LENGTH(x)"}, write={"spark": "LENGTH(x)"}) + self.validate_all( + "LEN(x)", read={"": "LENGTH(x)"}, write={"spark": "LENGTH(CAST(x AS STRING))"} + ) + self.validate_all("LEN(1)", write={"tsql": "LEN(1)", "spark": "LENGTH(CAST(1 AS STRING))"}) + self.validate_all("LEN('x')", write={"tsql": "LEN('x')", "spark": "LENGTH('x')"}) def test_replicate(self): self.validate_all("REPLICATE('x', 2)", write={"spark": "REPEAT('x', 2)"}) diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 13adf7f..54d41b4 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -188,12 +188,10 @@ CASE 1 WHEN 1 THEN timestamp ELSE date END x AT TIME ZONE 'UTC' CAST('2025-11-20 00:00:00+00' AS TIMESTAMP) AT TIME ZONE 'Africa/Cairo' SET x = 1 -SET -v SET x = ';' SET variable = value SET GLOBAL variable = value SET LOCAL variable = value -SET @user OFF @x @"x" COMMIT @@ -600,7 +598,6 @@ CREATE TABLE foo (baz CHAR(4) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC COM CREATE TABLE foo (baz DATE FORMAT 'YYYY/MM/DD' TITLE 'title' INLINE LENGTH 1 COMPRESS ('a', 'b')) CREATE TABLE t (title TEXT) CREATE TABLE foo (baz INT, inline TEXT) -CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT) CREATE ALGORITHM=UNDEFINED DEFINER=foo@% SQL SECURITY DEFINER VIEW a AS (SELECT a FROM b) CREATE TEMPORARY TABLE x AS SELECT a FROM d CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d @@ -642,7 +639,6 @@ CREATE SCHEMA IF NOT EXISTS y 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 @@ -656,30 +652,17 @@ CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS WITH a AS (SELECT 1) SEL CACHE LAZY TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a CACHE TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a CACHE TABLE x AS (SELECT 1 AS y) -CALL catalog.system.iceberg_procedure_name(named_arg_1 => 'arg_1', named_arg_2 => 'arg_2') DROP PROCEDURE a.b.c (INT) INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD') SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y INSERT OVERWRITE TABLE a.b PARTITION(ds = 'YYYY-MM-DD', hour = 'hh') SELECT x FROM y -ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS=3 -ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER -ALTER RULE foo ON bla RENAME TO baz -ALTER ROLE CURRENT_USER WITH REPLICATION -ALTER SEQUENCE IF EXISTS baz RESTART WITH boo -ALTER TYPE electronic_mail RENAME TO email -ALTER VIEW foo ALTER COLUMN bla SET DEFAULT 'NOT SET' -ALTER DOMAIN foo VALIDATE CONSTRAINT bla -ALTER schema doo -ANALYZE a.y DELETE FROM x WHERE y > 1 DELETE FROM y DELETE FROM event USING sales WHERE event.eventid = sales.eventid DELETE FROM event USING sales, bla WHERE event.eventid = sales.eventid DELETE FROM event USING sales AS s WHERE event.eventid = s.eventid DELETE FROM event AS event USING sales AS s WHERE event.eventid = s.eventid -PREPARE statement -EXECUTE statement DROP TABLE a DROP TABLE a.b DROP TABLE IF EXISTS a @@ -691,12 +674,10 @@ DROP VIEW a DROP VIEW a.b DROP VIEW IF EXISTS a DROP VIEW IF EXISTS a.b -SHOW TABLES USE db BEGIN ROLLBACK ROLLBACK TO b -EXPLAIN SELECT * FROM x INSERT INTO x SELECT * FROM y INSERT INTO x (SELECT * FROM y) INSERT INTO x WITH y AS (SELECT 1) SELECT * FROM y @@ -712,7 +693,6 @@ INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y INSERT OVERWRITE DIRECTORY 'x' SELECT 1 INSERT OVERWRITE LOCAL DIRECTORY 'x' SELECT 1 INSERT OVERWRITE LOCAL DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' LINES TERMINATED BY '4' NULL DEFINED AS '5' SELECT 1 -LOAD foo LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds = 'yyyy') INPUTFORMAT 'y' @@ -728,10 +708,6 @@ UPDATE db.tbl_name SET foo = 123 WHERE tbl_name.bar = 234 UPDATE db.tbl_name SET foo = 123, foo_1 = 234 WHERE tbl_name.bar = 234 UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price UPDATE t1 AS a, t2 AS b, t3 AS c LEFT JOIN t4 AS d ON c.id = d.id SET a.id = 1 -TRUNCATE TABLE x -OPTIMIZE TABLE y -VACUUM FREEZE my_table -COMMENT ON ACCESS METHOD gin IS 'GIN index access method' COMMENT ON COLUMN my_schema.my_table.my_column IS 'Employee ID number' COMMENT ON DATABASE my_database IS 'Development Database' COMMENT ON PROCEDURE my_proc(integer, integer) IS 'Runs a report' @@ -774,20 +750,21 @@ ALTER TABLE IF EXISTS integers ADD COLUMN k INT ALTER TABLE integers ADD COLUMN l INT DEFAULT 10 ALTER TABLE measurements ADD COLUMN mtime TIMESTAMPTZ DEFAULT NOW() ALTER TABLE integers DROP COLUMN k -ALTER TABLE integers DROP PRIMARY KEY ALTER TABLE integers DROP COLUMN IF EXISTS k ALTER TABLE integers DROP COLUMN k CASCADE ALTER TABLE integers ALTER COLUMN i SET DATA TYPE VARCHAR ALTER TABLE integers ALTER COLUMN i SET DATA TYPE VARCHAR USING CONCAT(i, '_', j) ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10 ALTER TABLE integers ALTER COLUMN i DROP DEFAULT +ALTER TABLE ingredients ALTER COLUMN amount COMMENT 'tablespoons' ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT ALTER TABLE orders DROP PARTITION(dt = '2014-05-14', country = 'IN') ALTER TABLE orders DROP IF EXISTS PARTITION(dt = '2014-05-14', country = 'IN') ALTER TABLE orders DROP PARTITION(dt = '2014-05-14', country = 'IN'), PARTITION(dt = '2014-05-15', country = 'IN') ALTER TABLE mydataset.mytable DELETE WHERE x = 1 -ALTER TABLE table1 MODIFY COLUMN name1 SET TAG foo='bar' +ALTER TABLE table1 RENAME COLUMN c1 TO c2 +ALTER TABLE table1 RENAME COLUMN IF EXISTS c1 TO c2 SELECT div.a FROM test_table AS div WITH view AS (SELECT 1 AS x) SELECT * FROM view ARRAY<STRUCT<INT, DOUBLE, ARRAY<INT>>> @@ -807,7 +784,6 @@ ALTER TABLE pets ADD CONSTRAINT pets_persons_fk FOREIGN KEY (owner_first_name, o ALTER TABLE pets ADD CONSTRAINT pets_name_not_cute_chk CHECK (LENGTH(name) < 20) ALTER TABLE people10m ADD CONSTRAINT dateWithinRange CHECK (birthDate > '1900-01-01') ALTER TABLE people10m ADD CONSTRAINT validIds CHECK (id > 1 AND id < 99999999) ENFORCED -ALTER TABLE s_ut ADD CONSTRAINT s_ut_uq UNIQUE hajo ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE INITIALLY DEFERRED NORELY ALTER TABLE baa ADD CONSTRAINT boo PRIMARY KEY (x, y) NOT ENFORCED DEFERRABLE INITIALLY DEFERRED NORELY ALTER TABLE baa ADD CONSTRAINT boo FOREIGN KEY (x, y) REFERENCES persons ON UPDATE NO ACTION ON DELETE NO ACTION MATCH FULL @@ -823,7 +799,6 @@ SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT SELECT * FROM x WHERE name ILIKE ANY XXX('a', 'b') SELECT * FROM x WHERE name LIKE ANY XXX('a', 'b') a OVERLAPS b -GRANT INSERT ON foo TO bla PRAGMA quick_check PRAGMA QUICK_CHECK(0) PRAGMA QUICK_CHECK('sqlite_master') diff --git a/tests/fixtures/jsonpath/LICENSE b/tests/fixtures/jsonpath/LICENSE new file mode 100644 index 0000000..212459f --- /dev/null +++ b/tests/fixtures/jsonpath/LICENSE @@ -0,0 +1,15 @@ +jsonpath-compliance-test-suite
+The BSD-2 license (the "License") set forth below applies to all parts of the jsonpath-compliance-test-suite project. You may not use this file except in compliance with the License.
+
+BSD-2 License
+
+Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
+
+Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
+
+Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
+
+THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+
+
+
diff --git a/tests/fixtures/jsonpath/cts.json b/tests/fixtures/jsonpath/cts.json new file mode 100644 index 0000000..11af641 --- /dev/null +++ b/tests/fixtures/jsonpath/cts.json @@ -0,0 +1,7617 @@ +{ + "description": "JSONPath Compliance Test Suite. This file is autogenerated, do not edit.", + "tests": [ + { + "name": "basic, root", + "selector": "$", + "document": [ + "first", + "second" + ], + "result": [ + [ + "first", + "second" + ] + ] + }, + { + "name": "basic, no leading whitespace", + "selector": " $", + "invalid_selector": true + }, + { + "name": "basic, no trailing whitespace", + "selector": "$ ", + "invalid_selector": true + }, + { + "name": "basic, name shorthand", + "selector": "$.a", + "document": { + "a": "A", + "b": "B" + }, + "result": [ + "A" + ] + }, + { + "name": "basic, name shorthand, extended unicode ☺", + "selector": "$.☺", + "document": { + "☺": "A", + "b": "B" + }, + "result": [ + "A" + ] + }, + { + "name": "basic, name shorthand, underscore", + "selector": "$._", + "document": { + "_": "A", + "_foo": "B" + }, + "result": [ + "A" + ] + }, + { + "name": "basic, name shorthand, symbol", + "selector": "$.&", + "invalid_selector": true + }, + { + "name": "basic, name shorthand, number", + "selector": "$.1", + "invalid_selector": true + }, + { + "name": "basic, name shorthand, absent data", + "selector": "$.c", + "document": { + "a": "A", + "b": "B" + }, + "result": [] + }, + { + "name": "basic, name shorthand, array data", + "selector": "$.a", + "document": [ + "first", + "second" + ], + "result": [] + }, + { + "name": "basic, wildcard shorthand, object data", + "selector": "$.*", + "document": { + "a": "A", + "b": "B" + }, + "result": [ + "A", + "B" + ] + }, + { + "name": "basic, wildcard shorthand, array data", + "selector": "$.*", + "document": [ + "first", + "second" + ], + "result": [ + "first", + "second" + ] + }, + { + "name": "basic, wildcard selector, array data", + "selector": "$[*]", + "document": [ + "first", + "second" + ], + "result": [ + "first", + "second" + ] + }, + { + "name": "basic, wildcard shorthand, then name shorthand", + "selector": "$.*.a", + "document": { + "x": { + "a": "Ax", + "b": "Bx" + }, + "y": { + "a": "Ay", + "b": "By" + } + }, + "result": [ + "Ax", + "Ay" + ] + }, + { + "name": "basic, multiple selectors", + "selector": "$[0,2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 2 + ] + }, + { + "name": "basic, multiple selectors, space instead of comma", + "selector": "$[0 2]", + "invalid_selector": true + }, + { + "name": "basic, multiple selectors, name and index, array data", + "selector": "$['a',1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1 + ] + }, + { + "name": "basic, multiple selectors, name and index, object data", + "selector": "$['a',1]", + "document": { + "a": 1, + "b": 2 + }, + "result": [ + 1 + ] + }, + { + "name": "basic, multiple selectors, index and slice", + "selector": "$[1,5:7]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 5, + 6 + ] + }, + { + "name": "basic, multiple selectors, index and slice, overlapping", + "selector": "$[1,0:3]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 0, + 1, + 2 + ] + }, + { + "name": "basic, multiple selectors, duplicate index", + "selector": "$[1,1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 1 + ] + }, + { + "name": "basic, multiple selectors, wildcard and index", + "selector": "$[*,1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9, + 1 + ] + }, + { + "name": "basic, multiple selectors, wildcard and name", + "selector": "$[*,'a']", + "document": { + "a": "A", + "b": "B" + }, + "result": [ + "A", + "B", + "A" + ] + }, + { + "name": "basic, multiple selectors, wildcard and slice", + "selector": "$[*,0:2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9, + 0, + 1 + ] + }, + { + "name": "basic, multiple selectors, multiple wildcards", + "selector": "$[*,*]", + "document": [ + 0, + 1, + 2 + ], + "result": [ + 0, + 1, + 2, + 0, + 1, + 2 + ] + }, + { + "name": "basic, empty segment", + "selector": "$[]", + "invalid_selector": true + }, + { + "name": "basic, descendant segment, index", + "selector": "$..[1]", + "document": { + "o": [ + 0, + 1, + [ + 2, + 3 + ] + ] + }, + "result": [ + 1, + 3 + ] + }, + { + "name": "basic, descendant segment, name shorthand", + "selector": "$..a", + "document": { + "o": [ + { + "a": "b" + }, + { + "a": "c" + } + ] + }, + "result": [ + "b", + "c" + ] + }, + { + "name": "basic, descendant segment, wildcard shorthand, array data", + "selector": "$..*", + "document": [ + 0, + 1 + ], + "result": [ + 0, + 1 + ] + }, + { + "name": "basic, descendant segment, wildcard selector, array data", + "selector": "$..[*]", + "document": [ + 0, + 1 + ], + "result": [ + 0, + 1 + ] + }, + { + "name": "basic, descendant segment, wildcard shorthand, object data", + "selector": "$..*", + "document": { + "a": "b" + }, + "result": [ + "b" + ] + }, + { + "name": "basic, descendant segment, wildcard shorthand, nested data", + "selector": "$..*", + "document": { + "o": [ + { + "a": "b" + } + ] + }, + "result": [ + [ + { + "a": "b" + } + ], + { + "a": "b" + }, + "b" + ] + }, + { + "name": "basic, descendant segment, multiple selectors", + "selector": "$..['a','d']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + "b", + "e", + "c", + "f" + ] + }, + { + "name": "basic, bald descendant segment", + "selector": "$..", + "invalid_selector": true + }, + { + "name": "filter, existence", + "selector": "$[?@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, existence, present with null", + "selector": "$[?@.a]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": null, + "d": "e" + } + ] + }, + { + "name": "filter, equals string, single quotes", + "selector": "$[?@.a=='b']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, equals numeric string, single quotes", + "selector": "$[?@.a=='1']", + "document": [ + { + "a": "1", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": "1", + "d": "e" + } + ] + }, + { + "name": "filter, equals string, double quotes", + "selector": "$[?@.a==\"b\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, equals numeric string, double quotes", + "selector": "$[?@.a==\"1\"]", + "document": [ + { + "a": "1", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": "1", + "d": "e" + } + ] + }, + { + "name": "filter, equals number", + "selector": "$[?@.a==1]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": 2, + "d": "f" + }, + { + "a": "1", + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "e" + } + ] + }, + { + "name": "filter, equals null", + "selector": "$[?@.a==null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": null, + "d": "e" + } + ] + }, + { + "name": "filter, equals null, absent from data", + "selector": "$[?@.a==null]", + "document": [ + { + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, equals true", + "selector": "$[?@.a==true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": true, + "d": "e" + } + ] + }, + { + "name": "filter, equals false", + "selector": "$[?@.a==false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": false, + "d": "e" + } + ] + }, + { + "name": "filter, deep equality, arrays", + "selector": "$[?@.a==@.b]", + "document": [ + { + "a": false, + "b": [ + 1, + 2 + ] + }, + { + "a": [ + [ + 1, + [ + 2 + ] + ] + ], + "b": [ + [ + 1, + [ + 2 + ] + ] + ] + }, + { + "a": [ + [ + 1, + [ + 2 + ] + ] + ], + "b": [ + [ + [ + 2 + ], + 1 + ] + ] + }, + { + "a": [ + [ + 1, + [ + 2 + ] + ] + ], + "b": [ + [ + 1, + 2 + ] + ] + } + ], + "result": [ + { + "a": [ + [ + 1, + [ + 2 + ] + ] + ], + "b": [ + [ + 1, + [ + 2 + ] + ] + ] + } + ] + }, + { + "name": "filter, deep equality, objects", + "selector": "$[?@.a==@.b]", + "document": [ + { + "a": false, + "b": { + "x": 1, + "y": { + "z": 1 + } + } + }, + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "x": 1, + "y": { + "z": 1 + } + } + }, + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "y": { + "z": 1 + }, + "x": 1 + } + }, + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "x": 1 + } + }, + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "x": 1, + "y": { + "z": 2 + } + } + } + ], + "result": [ + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "x": 1, + "y": { + "z": 1 + } + } + }, + { + "a": { + "x": 1, + "y": { + "z": 1 + } + }, + "b": { + "y": { + "z": 1 + }, + "x": 1 + } + } + ] + }, + { + "name": "filter, not-equals string, single quotes", + "selector": "$[?@.a!='b']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals numeric string, single quotes", + "selector": "$[?@.a!='1']", + "document": [ + { + "a": "1", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "f" + } + ] + }, + { + "name": "filter, not-equals string, single quotes, different type", + "selector": "$[?@.a!='b']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "f" + } + ] + }, + { + "name": "filter, not-equals string, double quotes", + "selector": "$[?@.a!=\"b\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals numeric string, double quotes", + "selector": "$[?@.a!=\"1\"]", + "document": [ + { + "a": "1", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "f" + } + ] + }, + { + "name": "filter, not-equals string, double quotes, different types", + "selector": "$[?@.a!=\"b\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "f" + } + ] + }, + { + "name": "filter, not-equals number", + "selector": "$[?@.a!=1]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 2, + "d": "f" + }, + { + "a": "1", + "d": "f" + } + ], + "result": [ + { + "a": 2, + "d": "f" + }, + { + "a": "1", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals number, different types", + "selector": "$[?@.a!=1]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals null", + "selector": "$[?@.a!=null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals null, absent from data", + "selector": "$[?@.a!=null]", + "document": [ + { + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals true", + "selector": "$[?@.a!=true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, not-equals false", + "selector": "$[?@.a!=false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, less than string, single quotes", + "selector": "$[?@.a<'c']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, less than string, double quotes", + "selector": "$[?@.a<\"c\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, less than number", + "selector": "$[?@.a<10]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 10, + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": 20, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "e" + } + ] + }, + { + "name": "filter, less than null", + "selector": "$[?@.a<null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, less than true", + "selector": "$[?@.a<true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, less than false", + "selector": "$[?@.a<false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, less than or equal to string, single quotes", + "selector": "$[?@.a<='c']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, less than or equal to string, double quotes", + "selector": "$[?@.a<=\"c\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, less than or equal to number", + "selector": "$[?@.a<=10]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 10, + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": 20, + "d": "f" + } + ], + "result": [ + { + "a": 1, + "d": "e" + }, + { + "a": 10, + "d": "e" + } + ] + }, + { + "name": "filter, less than or equal to null", + "selector": "$[?@.a<=null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": null, + "d": "e" + } + ] + }, + { + "name": "filter, less than or equal to true", + "selector": "$[?@.a<=true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": true, + "d": "e" + } + ] + }, + { + "name": "filter, less than or equal to false", + "selector": "$[?@.a<=false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": false, + "d": "e" + } + ] + }, + { + "name": "filter, greater than string, single quotes", + "selector": "$[?@.a>'c']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, greater than string, double quotes", + "selector": "$[?@.a>\"c\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, greater than number", + "selector": "$[?@.a>10]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 10, + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": 20, + "d": "f" + } + ], + "result": [ + { + "a": 20, + "d": "f" + } + ] + }, + { + "name": "filter, greater than null", + "selector": "$[?@.a>null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, greater than true", + "selector": "$[?@.a>true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, greater than false", + "selector": "$[?@.a>false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [] + }, + { + "name": "filter, greater than or equal to string, single quotes", + "selector": "$[?@.a>='c']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, greater than or equal to string, double quotes", + "selector": "$[?@.a>=\"c\"]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, greater than or equal to number", + "selector": "$[?@.a>=10]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 10, + "d": "e" + }, + { + "a": "c", + "d": "f" + }, + { + "a": 20, + "d": "f" + } + ], + "result": [ + { + "a": 10, + "d": "e" + }, + { + "a": 20, + "d": "f" + } + ] + }, + { + "name": "filter, greater than or equal to null", + "selector": "$[?@.a>=null]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": null, + "d": "e" + } + ] + }, + { + "name": "filter, greater than or equal to true", + "selector": "$[?@.a>=true]", + "document": [ + { + "a": true, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": true, + "d": "e" + } + ] + }, + { + "name": "filter, greater than or equal to false", + "selector": "$[?@.a>=false]", + "document": [ + { + "a": false, + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": false, + "d": "e" + } + ] + }, + { + "name": "filter, exists and not-equals null, absent from data", + "selector": "$[?@.a&&@.a!=null]", + "document": [ + { + "d": "e" + }, + { + "a": "c", + "d": "f" + } + ], + "result": [ + { + "a": "c", + "d": "f" + } + ] + }, + { + "name": "filter, and", + "selector": "$[?@.a>0&&@.a<10]", + "document": [ + { + "a": -10, + "d": "e" + }, + { + "a": 5, + "d": "f" + }, + { + "a": 20, + "d": "f" + } + ], + "result": [ + { + "a": 5, + "d": "f" + } + ] + }, + { + "name": "filter, or", + "selector": "$[?@.a=='b'||@.a=='d']", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "c", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, not expression", + "selector": "$[?!(@.a=='b')]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "a", + "d": "e" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "filter, not exists", + "selector": "$[?!@.a]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "filter, not exists, data null", + "selector": "$[?!@.a]", + "document": [ + { + "a": null, + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "filter, non-singular query in comparison, slice", + "selector": "$[?@[0:0]==0]", + "invalid_selector": true + }, + { + "name": "filter, non-singular query in comparison, all children", + "selector": "$[?@[*]==0]", + "invalid_selector": true + }, + { + "name": "filter, non-singular query in comparison, descendants", + "selector": "$[?@..a==0]", + "invalid_selector": true + }, + { + "name": "filter, non-singular query in comparison, combined", + "selector": "$[?@.a[*].a==0]", + "invalid_selector": true + }, + { + "name": "filter, nested", + "selector": "$[?@[?@>1]]", + "document": [ + [ + 0 + ], + [ + 0, + 1 + ], + [ + 0, + 1, + 2 + ], + [ + 42 + ] + ], + "result": [ + [ + 0, + 1, + 2 + ], + [ + 42 + ] + ] + }, + { + "name": "filter, relative non-singular query, index, equal", + "selector": "$[?(@[0, 0]==42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, index, not equal", + "selector": "$[?(@[0, 0]!=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, index, less-or-equal", + "selector": "$[?(@[0, 0]<=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, name, equal", + "selector": "$[?(@['a', 'a']==42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, name, not equal", + "selector": "$[?(@['a', 'a']!=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, name, less-or-equal", + "selector": "$[?(@['a', 'a']<=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, combined, equal", + "selector": "$[?(@[0, '0']==42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, combined, not equal", + "selector": "$[?(@[0, '0']!=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, combined, less-or-equal", + "selector": "$[?(@[0, '0']<=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, wildcard, equal", + "selector": "$[?(@.*==42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, wildcard, not equal", + "selector": "$[?(@.*!=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, wildcard, less-or-equal", + "selector": "$[?(@.*<=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, slice, equal", + "selector": "$[?(@[0:0]==42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, slice, not equal", + "selector": "$[?(@[0:0]!=42)]", + "invalid_selector": true + }, + { + "name": "filter, relative non-singular query, slice, less-or-equal", + "selector": "$[?(@[0:0]<=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, index, equal", + "selector": "$[?($[0, 0]==42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, index, not equal", + "selector": "$[?($[0, 0]!=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, index, less-or-equal", + "selector": "$[?($[0, 0]<=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, name, equal", + "selector": "$[?($['a', 'a']==42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, name, not equal", + "selector": "$[?($['a', 'a']!=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, name, less-or-equal", + "selector": "$[?($['a', 'a']<=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, combined, equal", + "selector": "$[?($[0, '0']==42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, combined, not equal", + "selector": "$[?($[0, '0']!=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, combined, less-or-equal", + "selector": "$[?($[0, '0']<=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, wildcard, equal", + "selector": "$[?($.*==42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, wildcard, not equal", + "selector": "$[?($.*!=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, wildcard, less-or-equal", + "selector": "$[?($.*<=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, slice, equal", + "selector": "$[?($[0:0]==42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, slice, not equal", + "selector": "$[?($[0:0]!=42)]", + "invalid_selector": true + }, + { + "name": "filter, absolute non-singular query, slice, less-or-equal", + "selector": "$[?($[0:0]<=42)]", + "invalid_selector": true + }, + { + "name": "filter, multiple selectors", + "selector": "$[?@.a,?@.b]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ] + }, + { + "name": "filter, multiple selectors, comparison", + "selector": "$[?@.a=='b',?@.b=='x']", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "filter, multiple selectors, overlapping", + "selector": "$[?@.a,?@.d]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ] + }, + { + "name": "filter, multiple selectors, filter and index", + "selector": "$[?@.a,1]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ] + }, + { + "name": "filter, multiple selectors, filter and wildcard", + "selector": "$[?@.a,*]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ] + }, + { + "name": "filter, multiple selectors, filter and slice", + "selector": "$[?@.a,1:]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + }, + { + "g": "h" + } + ], + "result": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + }, + { + "g": "h" + } + ] + }, + { + "name": "filter, multiple selectors, comparison filter, index and slice", + "selector": "$[1, ?@.a=='b', 1:]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "b": "c", + "d": "f" + }, + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ] + }, + { + "name": "filter, equals number, zero and negative zero", + "selector": "$[?@.a==-0]", + "document": [ + { + "a": 0, + "d": "e" + }, + { + "a": 0.1, + "d": "f" + }, + { + "a": "0", + "d": "g" + } + ], + "result": [ + { + "a": 0, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, with and without decimal fraction", + "selector": "$[?@.a==1.0]", + "document": [ + { + "a": 1, + "d": "e" + }, + { + "a": 2, + "d": "f" + }, + { + "a": "1", + "d": "g" + } + ], + "result": [ + { + "a": 1, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, exponent", + "selector": "$[?@.a==1e2]", + "document": [ + { + "a": 100, + "d": "e" + }, + { + "a": 100.1, + "d": "f" + }, + { + "a": "100", + "d": "g" + } + ], + "result": [ + { + "a": 100, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, positive exponent", + "selector": "$[?@.a==1e+2]", + "document": [ + { + "a": 100, + "d": "e" + }, + { + "a": 100.1, + "d": "f" + }, + { + "a": "100", + "d": "g" + } + ], + "result": [ + { + "a": 100, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, negative exponent", + "selector": "$[?@.a==1e-2]", + "document": [ + { + "a": 0.01, + "d": "e" + }, + { + "a": 0.02, + "d": "f" + }, + { + "a": "0.01", + "d": "g" + } + ], + "result": [ + { + "a": 0.01, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, decimal fraction", + "selector": "$[?@.a==1.1]", + "document": [ + { + "a": 1.1, + "d": "e" + }, + { + "a": 1, + "d": "f" + }, + { + "a": "1.1", + "d": "g" + } + ], + "result": [ + { + "a": 1.1, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, decimal fraction, no fractional digit", + "selector": "$[?@.a==1.]", + "invalid_selector": true + }, + { + "name": "filter, equals number, decimal fraction, exponent", + "selector": "$[?@.a==1.1e2]", + "document": [ + { + "a": 110, + "d": "e" + }, + { + "a": 110.1, + "d": "f" + }, + { + "a": "110", + "d": "g" + } + ], + "result": [ + { + "a": 110, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, decimal fraction, positive exponent", + "selector": "$[?@.a==1.1e+2]", + "document": [ + { + "a": 110, + "d": "e" + }, + { + "a": 110.1, + "d": "f" + }, + { + "a": "110", + "d": "g" + } + ], + "result": [ + { + "a": 110, + "d": "e" + } + ] + }, + { + "name": "filter, equals number, decimal fraction, negative exponent", + "selector": "$[?@.a==1.1e-2]", + "document": [ + { + "a": 0.011, + "d": "e" + }, + { + "a": 0.012, + "d": "f" + }, + { + "a": "0.011", + "d": "g" + } + ], + "result": [ + { + "a": 0.011, + "d": "e" + } + ] + }, + { + "name": "filter, equals, special nothing", + "selector": "$.values[?length(@.a) == value($..c)]", + "document": { + "c": "cd", + "values": [ + { + "a": "ab" + }, + { + "c": "d" + }, + { + "a": null + } + ] + }, + "result": [ + { + "c": "d" + }, + { + "a": null + } + ] + }, + { + "name": "index selector, first element", + "selector": "$[0]", + "document": [ + "first", + "second" + ], + "result": [ + "first" + ] + }, + { + "name": "index selector, second element", + "selector": "$[1]", + "document": [ + "first", + "second" + ], + "result": [ + "second" + ] + }, + { + "name": "index selector, out of bound", + "selector": "$[2]", + "document": [ + "first", + "second" + ], + "result": [] + }, + { + "name": "index selector, overflowing index", + "selector": "$[231584178474632390847141970017375815706539969331281128078915168015826259279872]", + "invalid_selector": true + }, + { + "name": "index selector, not actually an index, overflowing index leads into general text", + "selector": "$[231584178474632390847141970017375815706539969331281128078915168SomeRandomText]", + "invalid_selector": true + }, + { + "name": "index selector, negative", + "selector": "$[-1]", + "document": [ + "first", + "second" + ], + "result": [ + "second" + ] + }, + { + "name": "index selector, more negative", + "selector": "$[-2]", + "document": [ + "first", + "second" + ], + "result": [ + "first" + ] + }, + { + "name": "index selector, negative out of bound", + "selector": "$[-3]", + "document": [ + "first", + "second" + ], + "result": [] + }, + { + "name": "index selector, on object", + "selector": "$[0]", + "document": { + "foo": 1 + }, + "result": [] + }, + { + "name": "index selector, leading 0", + "selector": "$[01]", + "invalid_selector": true + }, + { + "name": "index selector, leading -0", + "selector": "$[-01]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes", + "selector": "$[\"a\"]", + "document": { + "a": "A", + "b": "B" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, absent data", + "selector": "$[\"c\"]", + "document": { + "a": "A", + "b": "B" + }, + "result": [] + }, + { + "name": "name selector, double quotes, array data", + "selector": "$[\"a\"]", + "document": [ + "first", + "second" + ], + "result": [] + }, + { + "name": "name selector, double quotes, embedded U+0000", + "selector": "$[\"\u0000\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0001", + "selector": "$[\"\u0001\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0002", + "selector": "$[\"\u0002\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0003", + "selector": "$[\"\u0003\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0004", + "selector": "$[\"\u0004\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0005", + "selector": "$[\"\u0005\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0006", + "selector": "$[\"\u0006\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0007", + "selector": "$[\"\u0007\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0008", + "selector": "$[\"\b\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0009", + "selector": "$[\"\t\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000A", + "selector": "$[\"\n\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000B", + "selector": "$[\"\u000b\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000C", + "selector": "$[\"\f\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000D", + "selector": "$[\"\r\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000E", + "selector": "$[\"\u000e\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+000F", + "selector": "$[\"\u000f\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0010", + "selector": "$[\"\u0010\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0011", + "selector": "$[\"\u0011\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0012", + "selector": "$[\"\u0012\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0013", + "selector": "$[\"\u0013\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0014", + "selector": "$[\"\u0014\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0015", + "selector": "$[\"\u0015\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0016", + "selector": "$[\"\u0016\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0017", + "selector": "$[\"\u0017\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0018", + "selector": "$[\"\u0018\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0019", + "selector": "$[\"\u0019\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001A", + "selector": "$[\"\u001a\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001B", + "selector": "$[\"\u001b\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001C", + "selector": "$[\"\u001c\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001D", + "selector": "$[\"\u001d\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001E", + "selector": "$[\"\u001e\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+001F", + "selector": "$[\"\u001f\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded U+0020", + "selector": "$[\" \"]", + "document": { + " ": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped double quote", + "selector": "$[\"\\\"\"]", + "document": { + "\"": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped reverse solidus", + "selector": "$[\"\\\\\"]", + "document": { + "\\": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped solidus", + "selector": "$[\"\\/\"]", + "document": { + "/": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped backspace", + "selector": "$[\"\\b\"]", + "document": { + "\b": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped form feed", + "selector": "$[\"\\f\"]", + "document": { + "\f": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped line feed", + "selector": "$[\"\\n\"]", + "document": { + "\n": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped carriage return", + "selector": "$[\"\\r\"]", + "document": { + "\r": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped tab", + "selector": "$[\"\\t\"]", + "document": { + "\t": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped ☺, upper case hex", + "selector": "$[\"\\u263A\"]", + "document": { + "☺": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, escaped ☺, lower case hex", + "selector": "$[\"\\u263a\"]", + "document": { + "☺": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, surrogate pair 𝄞", + "selector": "$[\"\\uD834\\uDD1E\"]", + "document": { + "𝄞": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, surrogate pair 😀", + "selector": "$[\"\\uD83D\\uDE00\"]", + "document": { + "😀": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, double quotes, invalid escaped single quote", + "selector": "$[\"\\'\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, embedded double quote", + "selector": "$[\"\"\"]", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, incomplete escape", + "selector": "$[\"\\\"]", + "invalid_selector": true + }, + { + "name": "name selector, single quotes", + "selector": "$['a']", + "document": { + "a": "A", + "b": "B" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, absent data", + "selector": "$['c']", + "document": { + "a": "A", + "b": "B" + }, + "result": [] + }, + { + "name": "name selector, single quotes, array data", + "selector": "$['a']", + "document": [ + "first", + "second" + ], + "result": [] + }, + { + "name": "name selector, single quotes, embedded U+0000", + "selector": "$['\u0000']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0001", + "selector": "$['\u0001']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0002", + "selector": "$['\u0002']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0003", + "selector": "$['\u0003']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0004", + "selector": "$['\u0004']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0005", + "selector": "$['\u0005']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0006", + "selector": "$['\u0006']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0007", + "selector": "$['\u0007']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0008", + "selector": "$['\b']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0009", + "selector": "$['\t']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000A", + "selector": "$['\n']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000B", + "selector": "$['\u000b']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000C", + "selector": "$['\f']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000D", + "selector": "$['\r']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000E", + "selector": "$['\u000e']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+000F", + "selector": "$['\u000f']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0010", + "selector": "$['\u0010']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0011", + "selector": "$['\u0011']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0012", + "selector": "$['\u0012']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0013", + "selector": "$['\u0013']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0014", + "selector": "$['\u0014']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0015", + "selector": "$['\u0015']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0016", + "selector": "$['\u0016']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0017", + "selector": "$['\u0017']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0018", + "selector": "$['\u0018']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0019", + "selector": "$['\u0019']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001A", + "selector": "$['\u001a']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001B", + "selector": "$['\u001b']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001C", + "selector": "$['\u001c']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001D", + "selector": "$['\u001d']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001E", + "selector": "$['\u001e']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+001F", + "selector": "$['\u001f']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded U+0020", + "selector": "$[' ']", + "document": { + " ": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped single quote", + "selector": "$['\\'']", + "document": { + "'": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped reverse solidus", + "selector": "$['\\\\']", + "document": { + "\\": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped solidus", + "selector": "$['\\/']", + "document": { + "/": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped backspace", + "selector": "$['\\b']", + "document": { + "\b": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped form feed", + "selector": "$['\\f']", + "document": { + "\f": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped line feed", + "selector": "$['\\n']", + "document": { + "\n": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped carriage return", + "selector": "$['\\r']", + "document": { + "\r": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped tab", + "selector": "$['\\t']", + "document": { + "\t": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped ☺, upper case hex", + "selector": "$['\\u263A']", + "document": { + "☺": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, escaped ☺, lower case hex", + "selector": "$['\\u263a']", + "document": { + "☺": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, surrogate pair 𝄞", + "selector": "$['\\uD834\\uDD1E']", + "document": { + "𝄞": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, surrogate pair 😀", + "selector": "$['\\uD83D\\uDE00']", + "document": { + "😀": "A" + }, + "result": [ + "A" + ] + }, + { + "name": "name selector, single quotes, invalid escaped double quote", + "selector": "$['\\\"']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, embedded single quote", + "selector": "$[''']", + "invalid_selector": true + }, + { + "name": "name selector, single quotes, incomplete escape", + "selector": "$['\\']", + "invalid_selector": true + }, + { + "name": "name selector, double quotes, empty", + "selector": "$[\"\"]", + "document": { + "a": "A", + "b": "B", + "": "C" + }, + "result": [ + "C" + ] + }, + { + "name": "name selector, single quotes, empty", + "selector": "$['']", + "document": { + "a": "A", + "b": "B", + "": "C" + }, + "result": [ + "C" + ] + }, + { + "name": "slice selector, slice selector", + "selector": "$[1:3]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 2 + ] + }, + { + "name": "slice selector, slice selector with step", + "selector": "$[1:6:2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 3, + 5 + ] + }, + { + "name": "slice selector, slice selector with everything omitted, short form", + "selector": "$[:]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 0, + 1, + 2, + 3 + ] + }, + { + "name": "slice selector, slice selector with everything omitted, long form", + "selector": "$[::]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 0, + 1, + 2, + 3 + ] + }, + { + "name": "slice selector, slice selector with start omitted", + "selector": "$[:2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 1 + ] + }, + { + "name": "slice selector, slice selector with start and end omitted", + "selector": "$[::2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 2, + 4, + 6, + 8 + ] + }, + { + "name": "slice selector, negative step with default start and end", + "selector": "$[::-1]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 3, + 2, + 1, + 0 + ] + }, + { + "name": "slice selector, negative step with default start", + "selector": "$[:0:-1]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 3, + 2, + 1 + ] + }, + { + "name": "slice selector, negative step with default end", + "selector": "$[2::-1]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 2, + 1, + 0 + ] + }, + { + "name": "slice selector, larger negative step", + "selector": "$[::-2]", + "document": [ + 0, + 1, + 2, + 3 + ], + "result": [ + 3, + 1 + ] + }, + { + "name": "slice selector, negative range with default step", + "selector": "$[-1:-3]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [] + }, + { + "name": "slice selector, negative range with negative step", + "selector": "$[-1:-3:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 8 + ] + }, + { + "name": "slice selector, negative range with larger negative step", + "selector": "$[-1:-6:-2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 7, + 5 + ] + }, + { + "name": "slice selector, larger negative range with larger negative step", + "selector": "$[-1:-7:-2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 7, + 5 + ] + }, + { + "name": "slice selector, negative from, positive to", + "selector": "$[-5:7]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 5, + 6 + ] + }, + { + "name": "slice selector, negative from", + "selector": "$[-2:]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 8, + 9 + ] + }, + { + "name": "slice selector, positive from, negative to", + "selector": "$[1:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8 + ] + }, + { + "name": "slice selector, negative from, positive to, negative step", + "selector": "$[-1:1:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 8, + 7, + 6, + 5, + 4, + 3, + 2 + ] + }, + { + "name": "slice selector, positive from, negative to, negative step", + "selector": "$[7:-5:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 7, + 6 + ] + }, + { + "name": "slice selector, too many colons", + "selector": "$[1:2:3:4]", + "invalid_selector": true + }, + { + "name": "slice selector, non-integer array index", + "selector": "$[1:2:a]", + "invalid_selector": true + }, + { + "name": "slice selector, zero step", + "selector": "$[1:2:0]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [] + }, + { + "name": "slice selector, empty range", + "selector": "$[2:2]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [] + }, + { + "name": "slice selector, slice selector with everything omitted with empty array", + "selector": "$[:]", + "document": [], + "result": [] + }, + { + "name": "slice selector, negative step with empty array", + "selector": "$[::-1]", + "document": [], + "result": [] + }, + { + "name": "slice selector, maximal range with positive step", + "selector": "$[0:10]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ] + }, + { + "name": "slice selector, maximal range with negative step", + "selector": "$[9:0:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 8, + 7, + 6, + 5, + 4, + 3, + 2, + 1 + ] + }, + { + "name": "slice selector, excessively large to value", + "selector": "$[2:113667776004]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ] + }, + { + "name": "slice selector, excessively small from value", + "selector": "$[-113667776004:1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 0 + ] + }, + { + "name": "slice selector, excessively large from value with negative step", + "selector": "$[113667776004:0:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9, + 8, + 7, + 6, + 5, + 4, + 3, + 2, + 1 + ] + }, + { + "name": "slice selector, excessively small to value with negative step", + "selector": "$[3:-113667776004:-1]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 3, + 2, + 1, + 0 + ] + }, + { + "name": "slice selector, excessively large step", + "selector": "$[1:10:113667776004]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 1 + ] + }, + { + "name": "slice selector, excessively small step", + "selector": "$[-1:-10:-113667776004]", + "document": [ + 0, + 1, + 2, + 3, + 4, + 5, + 6, + 7, + 8, + 9 + ], + "result": [ + 9 + ] + }, + { + "name": "slice selector, overflowing to value", + "selector": "$[2:231584178474632390847141970017375815706539969331281128078915168015826259279872]", + "invalid_selector": true + }, + { + "name": "slice selector, underflowing from value", + "selector": "$[-231584178474632390847141970017375815706539969331281128078915168015826259279872:1]", + "invalid_selector": true + }, + { + "name": "slice selector, overflowing from value with negative step", + "selector": "$[231584178474632390847141970017375815706539969331281128078915168015826259279872:0:-1]", + "invalid_selector": true + }, + { + "name": "slice selector, underflowing to value with negative step", + "selector": "$[3:-231584178474632390847141970017375815706539969331281128078915168015826259279872:-1]", + "invalid_selector": true + }, + { + "name": "slice selector, overflowing step", + "selector": "$[1:10:231584178474632390847141970017375815706539969331281128078915168015826259279872]", + "invalid_selector": true + }, + { + "name": "slice selector, underflowing step", + "selector": "$[-1:-10:-231584178474632390847141970017375815706539969331281128078915168015826259279872]", + "invalid_selector": true + }, + { + "name": "functions, count, count function", + "selector": "$[?count(@..*)>2]", + "document": [ + { + "a": [ + 1, + 2, + 3 + ] + }, + { + "a": [ + 1 + ], + "d": "f" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": [ + 1, + 2, + 3 + ] + }, + { + "a": [ + 1 + ], + "d": "f" + } + ] + }, + { + "name": "functions, count, single-node arg", + "selector": "$[?count(@.a)>1]", + "document": [ + { + "a": [ + 1, + 2, + 3 + ] + }, + { + "a": [ + 1 + ], + "d": "f" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, count, multiple-selector arg", + "selector": "$[?count(@['a','d'])>1]", + "document": [ + { + "a": [ + 1, + 2, + 3 + ] + }, + { + "a": [ + 1 + ], + "d": "f" + }, + { + "a": 1, + "d": "f" + } + ], + "result": [ + { + "a": [ + 1 + ], + "d": "f" + }, + { + "a": 1, + "d": "f" + } + ] + }, + { + "name": "functions, count, non-query arg, number", + "selector": "$[?count(1)>2]", + "invalid_selector": true + }, + { + "name": "functions, count, non-query arg, string", + "selector": "$[?count('string')>2]", + "invalid_selector": true + }, + { + "name": "functions, count, non-query arg, true", + "selector": "$[?count(true)>2]", + "invalid_selector": true + }, + { + "name": "functions, count, non-query arg, false", + "selector": "$[?count(false)>2]", + "invalid_selector": true + }, + { + "name": "functions, count, non-query arg, null", + "selector": "$[?count(null)>2]", + "invalid_selector": true + }, + { + "name": "functions, count, result must be compared", + "selector": "$[?count(@..*)]", + "invalid_selector": true + }, + { + "name": "functions, count, no params", + "selector": "$[?count()==1]", + "invalid_selector": true + }, + { + "name": "functions, count, too many params", + "selector": "$[?count(@.a,@.b)==1]", + "invalid_selector": true + }, + { + "name": "functions, length, string data", + "selector": "$[?length(@.a)>=2]", + "document": [ + { + "a": "ab" + }, + { + "a": "d" + } + ], + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, length, string data, unicode", + "selector": "$[?length(@)==2]", + "document": [ + "☺", + "☺☺", + "☺☺☺", + "ж", + "жж", + "жжж", + "磨", + "阿美", + "形声字" + ], + "result": [ + "☺☺", + "жж", + "阿美" + ] + }, + { + "name": "functions, length, array data", + "selector": "$[?length(@.a)>=2]", + "document": [ + { + "a": [ + 1, + 2, + 3 + ] + }, + { + "a": [ + 1 + ] + } + ], + "result": [ + { + "a": [ + 1, + 2, + 3 + ] + } + ] + }, + { + "name": "functions, length, missing data", + "selector": "$[?length(@.a)>=2]", + "document": [ + { + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, length, number arg", + "selector": "$[?length(1)>=2]", + "document": [ + { + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, length, true arg", + "selector": "$[?length(true)>=2]", + "document": [ + { + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, length, false arg", + "selector": "$[?length(false)>=2]", + "document": [ + { + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, length, null arg", + "selector": "$[?length(null)>=2]", + "document": [ + { + "d": "f" + } + ], + "result": [] + }, + { + "name": "functions, length, result must be compared", + "selector": "$[?length(@.a)]", + "invalid_selector": true + }, + { + "name": "functions, length, no params", + "selector": "$[?length()==1]", + "invalid_selector": true + }, + { + "name": "functions, length, too many params", + "selector": "$[?length(@.a,@.b)==1]", + "invalid_selector": true + }, + { + "name": "functions, length, non-singular query arg", + "selector": "$[?length(@.*)<3]", + "invalid_selector": true + }, + { + "name": "functions, length, arg is a function expression", + "selector": "$.values[?length(@.a)==length(value($..c))]", + "document": { + "c": "cd", + "values": [ + { + "a": "ab" + }, + { + "a": "d" + } + ] + }, + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, length, arg is special nothing", + "selector": "$[?length(value(@.a))>0]", + "document": [ + { + "a": "ab" + }, + { + "c": "d" + }, + { + "a": null + } + ], + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, match, found match", + "selector": "$[?match(@.a, 'a.*')]", + "document": [ + { + "a": "ab" + } + ], + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, match, double quotes", + "selector": "$[?match(@.a, \"a.*\")]", + "document": [ + { + "a": "ab" + } + ], + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, match, regex from the document", + "selector": "$.values[?match(@, $.regex)]", + "document": { + "regex": "b.?b", + "values": [ + "abc", + "bcd", + "bab", + "bba", + "bbab", + "b", + true, + [], + {} + ] + }, + "result": [ + "bab" + ] + }, + { + "name": "functions, match, don't select match", + "selector": "$[?!match(@.a, 'a.*')]", + "document": [ + { + "a": "ab" + } + ], + "result": [] + }, + { + "name": "functions, match, not a match", + "selector": "$[?match(@.a, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, match, select non-match", + "selector": "$[?!match(@.a, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [ + { + "a": "bc" + } + ] + }, + { + "name": "functions, match, non-string first arg", + "selector": "$[?match(1, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, match, non-string second arg", + "selector": "$[?match(@.a, 1)]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, match, filter, match function, unicode char class, uppercase", + "selector": "$[?match(@, '\\\\p{Lu}')]", + "document": [ + "ж", + "Ж", + "1", + "жЖ", + true, + [], + {} + ], + "result": [ + "Ж" + ] + }, + { + "name": "functions, match, filter, match function, unicode char class negated, uppercase", + "selector": "$[?match(@, '\\\\P{Lu}')]", + "document": [ + "ж", + "Ж", + "1", + true, + [], + {} + ], + "result": [ + "ж", + "1" + ] + }, + { + "name": "functions, match, filter, match function, unicode, surrogate pair", + "selector": "$[?match(@, 'a.b')]", + "document": [ + "a𐄁b", + "ab", + "1", + true, + [], + {} + ], + "result": [ + "a𐄁b" + ] + }, + { + "name": "functions, match, result cannot be compared", + "selector": "$[?match(@.a, 'a.*')==true]", + "invalid_selector": true + }, + { + "name": "functions, match, too few params", + "selector": "$[?match(@.a)==1]", + "invalid_selector": true + }, + { + "name": "functions, match, too many params", + "selector": "$[?match(@.a,@.b,@.c)==1]", + "invalid_selector": true + }, + { + "name": "functions, match, arg is a function expression", + "selector": "$.values[?match(@.a, value($..['regex']))]", + "document": { + "regex": "a.*", + "values": [ + { + "a": "ab" + }, + { + "a": "ba" + } + ] + }, + "result": [ + { + "a": "ab" + } + ] + }, + { + "name": "functions, search, at the end", + "selector": "$[?search(@.a, 'a.*')]", + "document": [ + { + "a": "the end is ab" + } + ], + "result": [ + { + "a": "the end is ab" + } + ] + }, + { + "name": "functions, search, double quotes", + "selector": "$[?search(@.a, \"a.*\")]", + "document": [ + { + "a": "the end is ab" + } + ], + "result": [ + { + "a": "the end is ab" + } + ] + }, + { + "name": "functions, search, at the start", + "selector": "$[?search(@.a, 'a.*')]", + "document": [ + { + "a": "ab is at the start" + } + ], + "result": [ + { + "a": "ab is at the start" + } + ] + }, + { + "name": "functions, search, in the middle", + "selector": "$[?search(@.a, 'a.*')]", + "document": [ + { + "a": "contains two matches" + } + ], + "result": [ + { + "a": "contains two matches" + } + ] + }, + { + "name": "functions, search, regex from the document", + "selector": "$.values[?search(@, $.regex)]", + "document": { + "regex": "b.?b", + "values": [ + "abc", + "bcd", + "bab", + "bba", + "bbab", + "b", + true, + [], + {} + ] + }, + "result": [ + "bab", + "bba", + "bbab" + ] + }, + { + "name": "functions, search, don't select match", + "selector": "$[?!search(@.a, 'a.*')]", + "document": [ + { + "a": "contains two matches" + } + ], + "result": [] + }, + { + "name": "functions, search, not a match", + "selector": "$[?search(@.a, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, search, select non-match", + "selector": "$[?!search(@.a, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [ + { + "a": "bc" + } + ] + }, + { + "name": "functions, search, non-string first arg", + "selector": "$[?search(1, 'a.*')]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, search, non-string second arg", + "selector": "$[?search(@.a, 1)]", + "document": [ + { + "a": "bc" + } + ], + "result": [] + }, + { + "name": "functions, search, filter, search function, unicode char class, uppercase", + "selector": "$[?search(@, '\\\\p{Lu}')]", + "document": [ + "ж", + "Ж", + "1", + "жЖ", + true, + [], + {} + ], + "result": [ + "Ж", + "жЖ" + ] + }, + { + "name": "functions, search, filter, search function, unicode char class negated, uppercase", + "selector": "$[?search(@, '\\\\P{Lu}')]", + "document": [ + "ж", + "Ж", + "1", + true, + [], + {} + ], + "result": [ + "ж", + "1" + ] + }, + { + "name": "functions, search, filter, search function, unicode, surrogate pair", + "selector": "$[?search(@, 'a.b')]", + "document": [ + "a𐄁bc", + "abc", + "1", + true, + [], + {} + ], + "result": [ + "a𐄁bc" + ] + }, + { + "name": "functions, search, result cannot be compared", + "selector": "$[?search(@.a, 'a.*')==true]", + "invalid_selector": true + }, + { + "name": "functions, search, too few params", + "selector": "$[?search(@.a)]", + "invalid_selector": true + }, + { + "name": "functions, search, too many params", + "selector": "$[?search(@.a,@.b,@.c)]", + "invalid_selector": true + }, + { + "name": "functions, search, arg is a function expression", + "selector": "$.values[?search(@, value($..['regex']))]", + "document": { + "regex": "b.?b", + "values": [ + "abc", + "bcd", + "bab", + "bba", + "bbab", + "b", + true, + [], + {} + ] + }, + "result": [ + "bab", + "bba", + "bbab" + ] + }, + { + "name": "functions, value, single-value nodelist", + "selector": "$[?value(@.*)==4]", + "document": [ + [ + 4 + ], + { + "foo": 4 + }, + [ + 5 + ], + { + "foo": 5 + }, + 4 + ], + "result": [ + [ + 4 + ], + { + "foo": 4 + } + ] + }, + { + "name": "functions, value, multi-value nodelist", + "selector": "$[?value(@.*)==4]", + "document": [ + [ + 4, + 4 + ], + { + "foo": 4, + "bar": 4 + } + ], + "result": [] + }, + { + "name": "functions, value, too few params", + "selector": "$[?value()==4]", + "invalid_selector": true + }, + { + "name": "functions, value, too many params", + "selector": "$[?value(@.a,@.b)==4]", + "invalid_selector": true + }, + { + "name": "functions, value, result must be compared", + "selector": "$[?value(@.a)]", + "invalid_selector": true + }, + { + "name": "whitespace, filter, space between question mark and expression", + "selector": "$[? @.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, newline between question mark and expression", + "selector": "$[?\n@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, tab between question mark and expression", + "selector": "$[?\t@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, return between question mark and expression", + "selector": "$[?\r@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, space between question mark and parenthesized expression", + "selector": "$[? (@.a)]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, newline between question mark and parenthesized expression", + "selector": "$[?\n(@.a)]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, tab between question mark and parenthesized expression", + "selector": "$[?\t(@.a)]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, return between question mark and parenthesized expression", + "selector": "$[?\r(@.a)]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, space between parenthesized expression and bracket", + "selector": "$[?(@.a) ]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, newline between parenthesized expression and bracket", + "selector": "$[?(@.a)\n]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, tab between parenthesized expression and bracket", + "selector": "$[?(@.a)\t]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, return between parenthesized expression and bracket", + "selector": "$[?(@.a)\r]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, space between bracket and question mark", + "selector": "$[ ?@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, newline between bracket and question mark", + "selector": "$[\n?@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, tab between bracket and question mark", + "selector": "$[\t?@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, filter, return between bracket and question mark", + "selector": "$[\r?@.a]", + "document": [ + { + "a": "b", + "d": "e" + }, + { + "b": "c", + "d": "f" + } + ], + "result": [ + { + "a": "b", + "d": "e" + } + ] + }, + { + "name": "whitespace, functions, space between function name and parenthesis", + "selector": "$[?count (@.*)==1]", + "invalid_selector": true + }, + { + "name": "whitespace, functions, newline between function name and parenthesis", + "selector": "$[?count\n(@.*)==1]", + "invalid_selector": true + }, + { + "name": "whitespace, functions, tab between function name and parenthesis", + "selector": "$[?count\t(@.*)==1]", + "invalid_selector": true + }, + { + "name": "whitespace, functions, return between function name and parenthesis", + "selector": "$[?count\r(@.*)==1]", + "invalid_selector": true + }, + { + "name": "whitespace, functions, space between parenthesis and arg", + "selector": "$[?count( @.*)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, newline between parenthesis and arg", + "selector": "$[?count(\n@.*)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, tab between parenthesis and arg", + "selector": "$[?count(\t@.*)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, return between parenthesis and arg", + "selector": "$[?count(\r@.*)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, space between arg and comma", + "selector": "$[?search(@ ,'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, newline between arg and comma", + "selector": "$[?search(@\n,'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, tab between arg and comma", + "selector": "$[?search(@\t,'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, return between arg and comma", + "selector": "$[?search(@\r,'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, space between comma and arg", + "selector": "$[?search(@, '[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, newline between comma and arg", + "selector": "$[?search(@,\n'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, tab between comma and arg", + "selector": "$[?search(@,\t'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, return between comma and arg", + "selector": "$[?search(@,\r'[a-z]+')]", + "document": [ + "foo", + "123" + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, space between arg and parenthesis", + "selector": "$[?count(@.* )==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, newline between arg and parenthesis", + "selector": "$[?count(@.*\n)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, tab between arg and parenthesis", + "selector": "$[?count(@.*\t)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, return between arg and parenthesis", + "selector": "$[?count(@.*\r)==1]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, functions, spaces in a relative singular selector", + "selector": "$[?length(@ .a .b) == 3]", + "document": [ + { + "a": { + "b": "foo" + } + }, + {} + ], + "result": [ + { + "a": { + "b": "foo" + } + } + ] + }, + { + "name": "whitespace, functions, newlines in a relative singular selector", + "selector": "$[?length(@\n.a\n.b) == 3]", + "document": [ + { + "a": { + "b": "foo" + } + }, + {} + ], + "result": [ + { + "a": { + "b": "foo" + } + } + ] + }, + { + "name": "whitespace, functions, tabs in a relative singular selector", + "selector": "$[?length(@\t.a\t.b) == 3]", + "document": [ + { + "a": { + "b": "foo" + } + }, + {} + ], + "result": [ + { + "a": { + "b": "foo" + } + } + ] + }, + { + "name": "whitespace, functions, returns in a relative singular selector", + "selector": "$[?length(@\r.a\r.b) == 3]", + "document": [ + { + "a": { + "b": "foo" + } + }, + {} + ], + "result": [ + { + "a": { + "b": "foo" + } + } + ] + }, + { + "name": "whitespace, functions, spaces in an absolute singular selector", + "selector": "$..[?length(@)==length($ [0] .a)]", + "document": [ + { + "a": "foo" + }, + {} + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, newlines in an absolute singular selector", + "selector": "$..[?length(@)==length($\n[0]\n.a)]", + "document": [ + { + "a": "foo" + }, + {} + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, tabs in an absolute singular selector", + "selector": "$..[?length(@)==length($\t[0]\t.a)]", + "document": [ + { + "a": "foo" + }, + {} + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, functions, returns in an absolute singular selector", + "selector": "$..[?length(@)==length($\r[0]\r.a)]", + "document": [ + { + "a": "foo" + }, + {} + ], + "result": [ + "foo" + ] + }, + { + "name": "whitespace, operators, space before ||", + "selector": "$[?@.a ||@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before ||", + "selector": "$[?@.a\n||@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before ||", + "selector": "$[?@.a\t||@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before ||", + "selector": "$[?@.a\r||@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after ||", + "selector": "$[?@.a|| @.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after ||", + "selector": "$[?@.a||\n@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after ||", + "selector": "$[?@.a||\t@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after ||", + "selector": "$[?@.a||\r@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "c": 3 + } + ], + "result": [ + { + "a": 1 + }, + { + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before &&", + "selector": "$[?@.a &&@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before &&", + "selector": "$[?@.a\n&&@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before &&", + "selector": "$[?@.a\t&&@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before &&", + "selector": "$[?@.a\r&&@.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after &&", + "selector": "$[?@.a&& @.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after &&", + "selector": "$[?@.a&& @.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after &&", + "selector": "$[?@.a&& @.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after &&", + "selector": "$[?@.a&& @.b]", + "document": [ + { + "a": 1 + }, + { + "b": 2 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before ==", + "selector": "$[?@.a ==@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, newline before ==", + "selector": "$[?@.a\n==@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, tab before ==", + "selector": "$[?@.a\t==@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, return before ==", + "selector": "$[?@.a\r==@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, space after ==", + "selector": "$[?@.a== @.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, newline after ==", + "selector": "$[?@.a==\n@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, tab after ==", + "selector": "$[?@.a==\t@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, return after ==", + "selector": "$[?@.a==\r@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 1 + } + ] + }, + { + "name": "whitespace, operators, space before !=", + "selector": "$[?@.a !=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before !=", + "selector": "$[?@.a\n!=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before !=", + "selector": "$[?@.a\t!=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before !=", + "selector": "$[?@.a\r!=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after !=", + "selector": "$[?@.a!= @.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after !=", + "selector": "$[?@.a!=\n@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after !=", + "selector": "$[?@.a!=\t@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after !=", + "selector": "$[?@.a!=\r@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before <", + "selector": "$[?@.a <@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before <", + "selector": "$[?@.a\n<@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before <", + "selector": "$[?@.a\t<@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before <", + "selector": "$[?@.a\r<@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after <", + "selector": "$[?@.a< @.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after <", + "selector": "$[?@.a<\n@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after <", + "selector": "$[?@.a<\t@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after <", + "selector": "$[?@.a<\r@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before >", + "selector": "$[?@.b >@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before >", + "selector": "$[?@.b\n>@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before >", + "selector": "$[?@.b\t>@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before >", + "selector": "$[?@.b\r>@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after >", + "selector": "$[?@.b> @.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after >", + "selector": "$[?@.b>\n@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after >", + "selector": "$[?@.b>\t@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after >", + "selector": "$[?@.b>\r@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ], + "result": [ + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before <=", + "selector": "$[?@.a <=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before <=", + "selector": "$[?@.a\n<=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before <=", + "selector": "$[?@.a\t<=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before <=", + "selector": "$[?@.a\r<=@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after <=", + "selector": "$[?@.a<= @.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after <=", + "selector": "$[?@.a<=\n@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after <=", + "selector": "$[?@.a<=\t@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after <=", + "selector": "$[?@.a<=\r@.b]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space before >=", + "selector": "$[?@.b >=@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline before >=", + "selector": "$[?@.b\n>=@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab before >=", + "selector": "$[?@.b\t>=@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return before >=", + "selector": "$[?@.b\r>=@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space after >=", + "selector": "$[?@.b>= @.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, newline after >=", + "selector": "$[?@.b>=\n@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, tab after >=", + "selector": "$[?@.b>=\t@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, return after >=", + "selector": "$[?@.b>=\r@.a]", + "document": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + }, + { + "a": 2, + "b": 1 + } + ], + "result": [ + { + "a": 1, + "b": 1 + }, + { + "a": 1, + "b": 2 + } + ] + }, + { + "name": "whitespace, operators, space between logical not and test expression", + "selector": "$[?! @.a]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, newline between logical not and test expression", + "selector": "$[?!\n@.a]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, tab between logical not and test expression", + "selector": "$[?!\t@.a]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, return between logical not and test expression", + "selector": "$[?!\r@.a]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, space between logical not and parenthesized expression", + "selector": "$[?! (@.a=='b')]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "a", + "d": "e" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, newline between logical not and parenthesized expression", + "selector": "$[?!\n(@.a=='b')]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "a", + "d": "e" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, tab between logical not and parenthesized expression", + "selector": "$[?!\t(@.a=='b')]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "a", + "d": "e" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "whitespace, operators, return between logical not and parenthesized expression", + "selector": "$[?!\r(@.a=='b')]", + "document": [ + { + "a": "a", + "d": "e" + }, + { + "a": "b", + "d": "f" + }, + { + "a": "d", + "d": "f" + } + ], + "result": [ + { + "a": "a", + "d": "e" + }, + { + "a": "d", + "d": "f" + } + ] + }, + { + "name": "whitespace, selectors, space between root and bracket", + "selector": "$ ['a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, newline between root and bracket", + "selector": "$\n['a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, tab between root and bracket", + "selector": "$\t['a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, return between root and bracket", + "selector": "$\r['a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, space between bracket and bracket", + "selector": "$['a'] ['b']", + "document": { + "a": { + "b": "ab" + } + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, newline between root and bracket", + "selector": "$['a'] \n['b']", + "document": { + "a": { + "b": "ab" + } + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, tab between root and bracket", + "selector": "$['a'] \t['b']", + "document": { + "a": { + "b": "ab" + } + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, return between root and bracket", + "selector": "$['a'] \r['b']", + "document": { + "a": { + "b": "ab" + } + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, space between root and dot", + "selector": "$ .a", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, newline between root and dot", + "selector": "$\n.a", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, tab between root and dot", + "selector": "$\t.a", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, return between root and dot", + "selector": "$\r.a", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, space between dot and name", + "selector": "$. a", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, newline between dot and name", + "selector": "$.\na", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, tab between dot and name", + "selector": "$.\ta", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, return between dot and name", + "selector": "$.\ra", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, space between recursive descent and name", + "selector": "$.. a", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, newline between recursive descent and name", + "selector": "$..\na", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, tab between recursive descent and name", + "selector": "$..\ta", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, return between recursive descent and name", + "selector": "$..\ra", + "invalid_selector": true + }, + { + "name": "whitespace, selectors, space between bracket and selector", + "selector": "$[ 'a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, newline between bracket and selector", + "selector": "$[\n'a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, tab between bracket and selector", + "selector": "$[\t'a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, return between bracket and selector", + "selector": "$[\r'a']", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, space between selector and bracket", + "selector": "$['a' ]", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, newline between selector and bracket", + "selector": "$['a'\n]", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, tab between selector and bracket", + "selector": "$['a'\t]", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, return between selector and bracket", + "selector": "$['a'\r]", + "document": { + "a": "ab" + }, + "result": [ + "ab" + ] + }, + { + "name": "whitespace, selectors, space between selector and comma", + "selector": "$['a' ,'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, newline between selector and comma", + "selector": "$['a'\n,'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, tab between selector and comma", + "selector": "$['a'\t,'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, return between selector and comma", + "selector": "$['a'\r,'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, space between comma and selector", + "selector": "$['a', 'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, newline between comma and selector", + "selector": "$['a',\n'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, tab between comma and selector", + "selector": "$['a',\t'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, selectors, return between comma and selector", + "selector": "$['a',\r'b']", + "document": { + "a": "ab", + "b": "bc" + }, + "result": [ + "ab", + "bc" + ] + }, + { + "name": "whitespace, slice, space between start and colon", + "selector": "$[1 :5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, newline between start and colon", + "selector": "$[1\n:5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, tab between start and colon", + "selector": "$[1\t:5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, return between start and colon", + "selector": "$[1\r:5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, space between colon and end", + "selector": "$[1: 5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, newline between colon and end", + "selector": "$[1:\n5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, tab between colon and end", + "selector": "$[1:\t5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, return between colon and end", + "selector": "$[1:\r5:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, space between end and colon", + "selector": "$[1:5 :2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, newline between end and colon", + "selector": "$[1:5\n:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, tab between end and colon", + "selector": "$[1:5\t:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, return between end and colon", + "selector": "$[1:5\r:2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, space between colon and step", + "selector": "$[1:5: 2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, newline between colon and step", + "selector": "$[1:5:\n2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, tab between colon and step", + "selector": "$[1:5:\t2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + }, + { + "name": "whitespace, slice, return between colon and step", + "selector": "$[1:5:\r2]", + "document": [ + 1, + 2, + 3, + 4, + 5, + 6 + ], + "result": [ + 2, + 4 + ] + } + ] +} diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql index 7fc4b25..7bc45a7 100644 --- a/tests/fixtures/optimizer/merge_subqueries.sql +++ b/tests/fixtures/optimizer/merge_subqueries.sql @@ -226,7 +226,7 @@ FROM t1 WHERE row_num = 1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 WHERE t1.row_num = 1; # title: Test preventing merge of window expressions join clause with t1 as ( @@ -241,7 +241,7 @@ SELECT t1.a, t1.b FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1; # title: Test preventing merge of window expressions agg function with t1 as ( @@ -256,7 +256,7 @@ SELECT SUM(t1.row_num) as total_rows FROM t1; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1 AS t1; # title: Test prevent merging of window if in group by func with t1 as ( @@ -274,7 +274,7 @@ FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY row_num; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 AS t1 GROUP BY t1.row_num ORDER BY row_num; # title: Test prevent merging of window if in order by func with t1 as ( @@ -291,7 +291,7 @@ SELECT FROM t1 ORDER BY t1.row_num, t1.a; -WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a; +WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 AS t1 ORDER BY t1.row_num, t1.a; # title: Test allow merging of window function with t1 as ( diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index ea29e3e..8f4a551 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -79,7 +79,7 @@ WITH "cte" AS ( ) SELECT "cte"."a" AS "a" -FROM "cte"; +FROM "cte" AS "cte"; # title: Chained CTEs WITH cte1 AS ( @@ -103,11 +103,11 @@ WITH "cte1" AS ( ) SELECT "cte1"."a" AS "a" -FROM "cte1" +FROM "cte1" AS "cte1" UNION ALL SELECT "cte1"."a" + 1 AS "a" -FROM "cte1"; +FROM "cte1" AS "cte1"; # title: Correlated subquery SELECT a, SUM(b) AS sum_b @@ -223,14 +223,14 @@ WITH "m" AS ( SELECT "m"."a" AS "a", "m"."b" AS "b" - FROM "m" + FROM "m" AS "m" WHERE "m"."a" = 1 ), "o" AS ( SELECT "m"."a" AS "a", "m"."b" AS "b" - FROM "m" + FROM "m" AS "m" WHERE "m"."a" = 2 ) @@ -238,10 +238,10 @@ SELECT "n"."a" AS "a", "n"."b" AS "b", "o"."b" AS "b" -FROM "n" +FROM "n" AS "n" JOIN "n" AS "n2" ON "n"."a" = "n2"."a" -FULL JOIN "o" +FULL JOIN "o" AS "o" ON "n"."a" = "o"."a" WHERE "o"."b" > 0; @@ -355,7 +355,7 @@ WITH RECURSIVE "base" AS ( "cte1"."a" AS "a", "cte1"."b" AS "b", "cte1"."c" AS "c" - FROM "cte1" + FROM "cte1" AS "cte1" WHERE "cte1"."a" < 1 ) @@ -363,7 +363,7 @@ SELECT "cte1"."a" AS "a", "cte1"."b" AS "b", "cte1"."c" AS "c" -FROM "cte1"; +FROM "cte1" AS "cte1"; # title: right join should not push down to from SELECT x.a, y.b @@ -747,7 +747,7 @@ SELECT `_q_0`.`second_half_sales` AS `second_half_sales` FROM `produce` AS `produce` UNPIVOT((`first_half_sales`, `second_half_sales`) FOR `semesters` IN ((`produce`.`q1`, `produce`.`q2`) AS 'semester_1', (`produce`.`q3`, `produce`.`q4`) AS 'semester_2')) AS `_q_0`; -# title: quoting is maintained +# title: quoting is preserved # dialect: snowflake with cte1("id", foo) as (select 1, 2) select "id" from cte1; WITH "CTE1" AS ( @@ -756,7 +756,7 @@ WITH "CTE1" AS ( ) SELECT "CTE1"."id" AS "id" -FROM "CTE1"; +FROM "CTE1" AS "CTE1"; # title: ensures proper quoting happens after all optimizations # execute: false @@ -1090,7 +1090,7 @@ WITH "m" AS ( ) SELECT COALESCE("m"."a", "foo"."a") AS "a" -FROM "m" +FROM "m" AS "m" JOIN "n" AS "foo"("a") ON "foo"."a" = "m"."a"; @@ -1170,7 +1170,7 @@ SELECT "alias3"."m_od" AS "m_od", COALESCE("alias3"."c_od", 0) AS "c_od" FROM "table1" AS "table1" -LEFT JOIN "alias3" +LEFT JOIN "alias3" AS "alias3" ON "alias3"."cid" = "table1"."cid"; # title: CTE with EXPLODE cannot be merged @@ -1205,7 +1205,7 @@ WITH `T` AS ( ) SELECT `T`.`CoL` AS `CoL` -FROM `T`; +FROM `T` AS `T`; # title: override mysql's settings so it normalizes to lowercase # dialect: mysql, normalization_strategy = lowercase @@ -1217,7 +1217,7 @@ WITH `t` AS ( ) SELECT `t`.`CoL` AS `CoL` -FROM `t`; +FROM `t` AS `t`; # title: top-level query is parenthesized # execute: false @@ -1239,11 +1239,11 @@ WITH "x" AS ( ( SELECT "x"."a" AS "a" - FROM "x" + FROM "x" AS "x" UNION ALL SELECT "x"."a" AS "a" - FROM "x" + FROM "x" AS "x" LIMIT 10 ) LIMIT 10; @@ -1273,3 +1273,41 @@ JOIN "stops" AS "d"("id", "name") AND ( "c"."name" = 'Craiglockhart' OR "d"."name" = 'Tollcross' ); + +# title: avoid dag cycles with unnesting subqueries +# execute: false +# dialect: snowflake +SELECT + A.ACCOUNT_ID, + A.NAME, + C.EMAIL_DOMAIN +FROM ACCOUNTS AS A +LEFT JOIN CONTACTS AS C + ON C.ACCOUNT_ID = A.ACCOUNT_ID + AND C.EMAIL_DOMAIN IN ( + SELECT + D.DOMAIN + FROM DOMAINS D + WHERE + TYPE = 'education' + ); +WITH "_u_0" AS ( + SELECT + "D"."DOMAIN" AS "DOMAIN" + FROM "DOMAINS" AS "D" + WHERE + "D"."TYPE" = 'education' + GROUP BY + "D"."DOMAIN" +) +SELECT + "A"."ACCOUNT_ID" AS "ACCOUNT_ID", + "A"."NAME" AS "NAME", + "C"."EMAIL_DOMAIN" AS "EMAIL_DOMAIN" +FROM "ACCOUNTS" AS "A" +LEFT JOIN "CONTACTS" AS "C" + ON "A"."ACCOUNT_ID" = "C"."ACCOUNT_ID" +LEFT JOIN "_u_0" AS "_u_0" + ON "C"."EMAIL_DOMAIN" = "_u_0"."DOMAIN" +WHERE + NOT "_u_0"."DOMAIN" IS NULL; diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql index 8497c5b..b7103ef 100644 --- a/tests/fixtures/optimizer/pushdown_projections.sql +++ b/tests/fixtures/optimizer/pushdown_projections.sql @@ -23,19 +23,19 @@ SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0; WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1; -WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1; +WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1 AS t1; SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x); SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0; WITH y AS (SELECT * FROM x) SELECT a FROM y; -WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y; +WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y AS y; WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q; -WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q; +WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q; WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z UNION SELECT z.a AS a FROM z AS z; SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a); SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0; @@ -50,7 +50,7 @@ SELECT x FROM UNNEST([1, 2]) AS q(x, y); SELECT q.x AS x FROM UNNEST(ARRAY(1, 2)) AS q(x, y); WITH t1 AS (SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)]) AS "q"("cola", "colb")) SELECT cola FROM t1; -WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1; +WITH t1 AS (SELECT "q".cola AS cola FROM UNNEST(ARRAY(STRUCT(1 AS cola, 'test' AS colb))) AS "q"("cola", "colb")) SELECT t1.cola AS cola FROM t1 AS t1; SELECT x FROM VALUES(1, 2) AS q(x, y); SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y); @@ -59,7 +59,7 @@ SELECT i.a FROM x AS i LEFT JOIN (SELECT a, b FROM (SELECT a, b FROM x)) AS j ON SELECT i.a AS a FROM x AS i LEFT JOIN (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS j ON i.a = j.a; WITH cte AS (SELECT source.a AS a, ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC) AS index FROM source AS source QUALIFY index) SELECT cte.a AS a FROM cte; -WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte; +WITH cte AS (SELECT source.a AS a FROM source AS source QUALIFY ROW_NUMBER() OVER (PARTITION BY source.id, source.timestamp ORDER BY source.a DESC)) SELECT cte.a AS a FROM cte AS cte; WITH cte AS (SELECT 1 AS x, 2 AS y, 3 AS z) SELECT cte.a FROM cte AS cte(a); WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a); @@ -71,13 +71,13 @@ WITH cte(x, y, z) AS (SELECT 1, 2, 3) SELECT a, z FROM (SELECT * FROM cte AS cte WITH cte AS (SELECT 1 AS x, 3 AS z) SELECT cte.a AS a, cte.z AS z FROM (SELECT cte.b AS a, cte.z AS z FROM cte AS cte(b)) AS cte; WITH y AS (SELECT a FROM x) SELECT 1 FROM y; -WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y; WITH y AS (SELECT SUM(a) FROM x) SELECT 1 FROM y; -WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT MAX(1) AS _ FROM x AS x) SELECT 1 AS "1" FROM y AS y; WITH y AS (SELECT a FROM x GROUP BY a) SELECT 1 FROM y; -WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y; +WITH y AS (SELECT 1 AS _ FROM x AS x GROUP BY x.a) SELECT 1 AS "1" FROM y AS y; -------------------------------------- -- Unknown Star Expansion @@ -102,7 +102,7 @@ SELECT a FROM (SELECT * FROM (SELECT * FROM aa)); SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT aa.a AS a FROM aa AS aa) AS _q_0) AS _q_1; with cte1 as (SELECT cola, colb FROM tb UNION ALL SELECT colc, cold FROM tb2) SELECT cola FROM cte1; -WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1; +WITH cte1 AS (SELECT tb.cola AS cola FROM tb AS tb UNION ALL SELECT tb2.colc AS colc FROM tb2 AS tb2) SELECT cte1.cola AS cola FROM cte1 AS cte1; SELECT * FROM ((SELECT c FROM t1) JOIN t2); SELECT * FROM ((SELECT t1.c AS c FROM t1 AS t1) AS _q_0, t2 AS t2); diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index 5641ed4..ad197db 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -227,9 +227,22 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y); SELECT (SELECT c FROM y) FROM x; SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x; +# execute: false +WITH t(c) AS (SELECT 1) SELECT (SELECT c) FROM t; +WITH t AS (SELECT 1 AS c) SELECT (SELECT t.c AS c) AS _col_0 FROM t AS t; + +# execute: false +WITH t1(c1) AS (SELECT 1), t2(c2) AS (SELECT 2) SELECT (SELECT c1 FROM t2) FROM t1; +WITH t1 AS (SELECT 1 AS c1), t2 AS (SELECT 2 AS c2) SELECT (SELECT t1.c1 AS c1 FROM t2 AS t2) AS _col_0 FROM t1 AS t1; + SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y)); SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0); +# dialect: mysql +# execute: false +SELECT * FROM table_a as A WHERE A.col1 IN (SELECT MAX(B.col2) FROM table_b as B UNION ALL SELECT MAX(C.col2) FROM table_b as C); +SELECT * FROM table_a AS `A` WHERE `A`.col1 IN (SELECT MAX(`B`.col2) AS _col_0 FROM table_b AS `B` UNION ALL SELECT MAX(`C`.col2) AS _col_0 FROM table_b AS `C`); + -------------------------------------- -- Correlated subqueries -------------------------------------- @@ -291,28 +304,28 @@ SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b -- CTEs -------------------------------------- WITH z AS (SELECT x.a AS a FROM x) SELECT z.a AS a FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z(a) AS (SELECT a FROM x) SELECT * FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z AS (SELECT a FROM x) SELECT * FROM z as q; WITH z AS (SELECT x.a AS a FROM x AS x) SELECT q.a AS a FROM z AS q; WITH z AS (SELECT a FROM x) SELECT * FROM z; -WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z; +WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z AS z; WITH z AS (SELECT a FROM x), q AS (SELECT * FROM z) SELECT * FROM q; -WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z) SELECT q.a AS a FROM q; +WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z AS z) SELECT q.a AS a FROM q AS q; WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z; -WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z UNION SELECT z.a AS a, z.b AS b FROM z; +WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z AS z UNION SELECT z.a AS a, z.b AS b FROM z AS z; WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q; -WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q; +WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z AS z) SELECT q.b AS b FROM q AS q; WITH z AS ((SELECT b FROM x UNION ALL SELECT b FROM y) ORDER BY b) SELECT * FROM z; -WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z; +WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z AS z; WITH cte(x) AS (SELECT 1) SELECT * FROM cte AS cte(a); WITH cte AS (SELECT 1 AS x) SELECT cte.a AS a FROM cte AS cte(a); @@ -322,7 +335,7 @@ WITH cte AS (SELECT 1 AS x, 2 AS y) SELECT cte.a AS a, cte.y AS y FROM cte AS ct # execute: false WITH player AS (SELECT player.name, player.asset.info FROM players) SELECT * FROM player; -WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player; +WITH player AS (SELECT players.player.name AS name, players.player.asset.info AS info FROM players AS players) SELECT player.name AS name, player.info AS info FROM player AS player; -------------------------------------- -- Except and Replace @@ -346,6 +359,9 @@ SELECT x.b AS b FROM x AS x; SELECT * EXCEPT (a, b) FROM x; SELECT * EXCEPT (x.a, x.b) FROM x AS x; +SELECT COALESCE(t1.a, '') AS a, t2.* EXCEPT (a) FROM x AS t1, x AS t2; +SELECT COALESCE(t1.a, '') AS a, t2.b AS b FROM x AS t1, x AS t2; + -------------------------------------- -- Using -------------------------------------- @@ -354,7 +370,7 @@ SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b; # execute: false WITH cte AS (SELECT a.b.c.d.f.g FROM tbl1) SELECT g FROM (SELECT g FROM tbl2) tbl2 JOIN cte USING(g); -WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte ON tbl2.g = cte.g; +WITH cte AS (SELECT tbl1.a.b.c.d.f.g AS g FROM tbl1 AS tbl1) SELECT COALESCE(tbl2.g, cte.g) AS g FROM (SELECT tbl2.g AS g FROM tbl2 AS tbl2) AS tbl2 JOIN cte AS cte ON tbl2.g = cte.g; SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b); SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b; @@ -408,7 +424,7 @@ SELECT a FROM t1 JOIN t2 USING(a); SELECT COALESCE(t1.a, t2.a) AS a FROM t1 AS t1 JOIN t2 AS t2 ON t1.a = t2.a; WITH m(a) AS (SELECT 1), n(b) AS (SELECT 1) SELECT * FROM m JOIN n AS foo(a) USING (a); -WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m JOIN n AS foo(a) ON m.a = foo.a; +WITH m AS (SELECT 1 AS a), n AS (SELECT 1 AS b) SELECT COALESCE(m.a, foo.a) AS a FROM m AS m JOIN n AS foo(a) ON m.a = foo.a; -------------------------------------- -- Hint with table reference diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql index 87e0f6d..907780b 100644 --- a/tests/fixtures/optimizer/qualify_columns_ddl.sql +++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql @@ -1,6 +1,6 @@ # title: Create with CTE WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte; # title: Create without CTE CREATE TABLE foo AS SELECT a FROM tbl; @@ -8,15 +8,15 @@ CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl; # title: Create with complex CTE with derived table WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte; -WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte; +WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte; # title: Create wtih multiple CTEs WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code) WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1; -WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1; # title: Create with multiple derived tables CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y)); @@ -24,11 +24,11 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A # title: Create with a CTE and a derived table WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte)); -WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte) AS _q_0) AS _q_1; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; # title: Insert with CTE WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; -WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) INSERT INTO s SELECT cte.b AS b FROM cte AS cte; # title: Insert without CTE INSERT INTO foo SELECT a FROM tbl; diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 0ebfcd3..1426aa7 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -19,9 +19,13 @@ SELECT 1 FROM x.y.z AS z; SELECT 1 FROM y.z AS z, z.a; SELECT 1 FROM c.y.z AS z, z.a; -# title: cte can't be qualified +# title: single cte WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a; -WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a; +WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a; + +# title: two ctes that are self-joined +WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a CROSS JOIN a; +WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a AS a CROSS JOIN a AS a; # title: query that yields a single column as projection SELECT (SELECT y.c FROM y AS y) FROM x; @@ -130,3 +134,15 @@ SELECT x FROM c.db.t AS t, LATERAL UNNEST(t.xs) AS _q_0; # title: table with ordinality SELECT * FROM t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json; SELECT * FROM c.db.t AS t CROSS JOIN JSON_ARRAY_ELEMENTS(t.response) WITH ORDINALITY AS kv_json; + +# title: alter table +ALTER TABLE t ADD PRIMARY KEY (id) NOT ENFORCED; +ALTER TABLE c.db.t ADD PRIMARY KEY (id) NOT ENFORCED; + +# title: create statement with cte +CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte); +CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte); + +# title: insert statement with cte +WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; +WITH cte AS (SELECT b FROM c.db.y AS y) INSERT INTO c.db.s SELECT * FROM cte AS cte; diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index d38c3cc..5ea51e0 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -191,7 +191,7 @@ WITH "wscs" AS ( ELSE NULL END ) AS "sat_sales" - FROM "wscs" + FROM "wscs" AS "wscs" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_date_sk" = "wscs"."sold_date_sk" GROUP BY @@ -206,7 +206,7 @@ SELECT ROUND("wswscs"."thu_sales" / "wswscs_2"."thu_sales", 2) AS "_col_5", ROUND("wswscs"."fri_sales" / "wswscs_2"."fri_sales", 2) AS "_col_6", ROUND("wswscs"."sat_sales" / "wswscs_2"."sat_sales", 2) AS "_col_7" -FROM "wswscs" +FROM "wswscs" AS "wswscs" JOIN "date_dim" AS "date_dim" ON "date_dim"."d_week_seq" = "wswscs"."d_week_seq" AND "date_dim"."d_year" = 1998 JOIN "wswscs" AS "wswscs_2" @@ -821,7 +821,7 @@ WITH "salesreturns" AS ( "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" - "ssr"."profit_loss" AS "profit" - FROM "ssr" + FROM "ssr" AS "ssr" UNION ALL SELECT 'catalog channel' AS "channel", @@ -829,7 +829,7 @@ WITH "salesreturns" AS ( "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" - "csr"."profit_loss" AS "profit" - FROM "csr" + FROM "csr" AS "csr" UNION ALL SELECT 'web channel' AS "channel", @@ -837,7 +837,7 @@ WITH "salesreturns" AS ( "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" - "wsr"."profit_loss" AS "profit" - FROM "wsr" + FROM "wsr" AS "wsr" ) SELECT "x"."channel" AS "channel", @@ -2203,7 +2203,7 @@ WITH "item_2" AS ( ), "_u_1" AS ( SELECT "avg_sales"."average_sales" AS "average_sales" - FROM "avg_sales" + FROM "avg_sales" AS "avg_sales" ), "_u_0" AS ( SELECT "item"."i_item_sk" AS "ss_item_sk" @@ -2996,7 +2996,7 @@ WITH "frequent_ss_items" AS ( SELECT "customer"."c_customer_sk" AS "c_customer_sk" FROM "store_sales" AS "store_sales" - CROSS JOIN "max_store_sales" + CROSS JOIN "max_store_sales" AS "max_store_sales" JOIN "customer_2" AS "customer" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" GROUP BY @@ -3006,13 +3006,13 @@ WITH "frequent_ss_items" AS ( ), "_u_1" AS ( SELECT "frequent_ss_items"."item_sk" AS "item_sk" - FROM "frequent_ss_items" + FROM "frequent_ss_items" AS "frequent_ss_items" GROUP BY "frequent_ss_items"."item_sk" ), "_u_2" AS ( SELECT "best_ss_customer"."c_customer_sk" AS "c_customer_sk" - FROM "best_ss_customer" + FROM "best_ss_customer" AS "best_ss_customer" GROUP BY "best_ss_customer"."c_customer_sk" ), "date_dim_4" AS ( @@ -3137,14 +3137,14 @@ WITH "ssales" AS ( ), "_u_0" AS ( SELECT 0.05 * AVG("ssales"."netpaid") AS "_col_0" - FROM "ssales" + FROM "ssales" AS "ssales" ) SELECT "ssales"."c_last_name" AS "c_last_name", "ssales"."c_first_name" AS "c_first_name", "ssales"."s_store_name" AS "s_store_name", SUM("ssales"."netpaid") AS "paid" -FROM "ssales" +FROM "ssales" AS "ssales" CROSS JOIN "_u_0" AS "_u_0" WHERE "ssales"."i_color" = 'papaya' @@ -4095,17 +4095,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_manufact_id" AS "i_manufact_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_manufact_id" AS "i_manufact_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_manufact_id" AS "i_manufact_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_manufact_id" AS "i_manufact_id", @@ -5534,7 +5534,7 @@ SELECT "v1"."sum_sales" AS "sum_sales", "v1_lag"."sum_sales" AS "psum", "v1_lead"."sum_sales" AS "nsum" -FROM "v1" +FROM "v1" AS "v1" JOIN "v1" AS "v1_lag" ON "v1"."i_brand" = "v1_lag"."i_brand" AND "v1"."i_category" = "v1_lag"."i_category" @@ -6455,7 +6455,7 @@ WITH "cs_or_ws_sales" AS ( ), "my_revenue" AS ( SELECT SUM("store_sales"."ss_ext_sales_price") AS "revenue" - FROM "my_customers" + FROM "my_customers" AS "my_customers" JOIN "customer_address" AS "customer_address" ON "customer_address"."ca_address_sk" = "my_customers"."c_current_addr_sk" JOIN "store_sales" AS "store_sales" @@ -6480,7 +6480,7 @@ SELECT CAST(( "my_revenue"."revenue" / 50 ) AS INT) * 50 AS "segment_base" -FROM "my_revenue" +FROM "my_revenue" AS "my_revenue" GROUP BY CAST(( "my_revenue"."revenue" / 50 @@ -6681,17 +6681,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_item_id" AS "i_item_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -6813,7 +6813,7 @@ SELECT "v1"."sum_sales" AS "sum_sales", "v1_lag"."sum_sales" AS "psum", "v1_lead"."sum_sales" AS "nsum" -FROM "v1" +FROM "v1" AS "v1" JOIN "v1" AS "v1_lag" ON "v1"."cc_name" = "v1_lag"."cc_name" AND "v1"."i_brand" = "v1_lag"."i_brand" @@ -7014,14 +7014,14 @@ SELECT ( "ss_items"."ss_item_rev" + "cs_items"."cs_item_rev" + "ws_items"."ws_item_rev" ) / 3 AS "average" -FROM "ss_items" -JOIN "cs_items" +FROM "ss_items" AS "ss_items" +JOIN "cs_items" AS "cs_items" ON "cs_items"."cs_item_rev" <= 1.1 * "ss_items"."ss_item_rev" AND "cs_items"."cs_item_rev" >= 0.9 * "ss_items"."ss_item_rev" AND "cs_items"."item_id" = "ss_items"."item_id" AND "ss_items"."ss_item_rev" <= 1.1 * "cs_items"."cs_item_rev" AND "ss_items"."ss_item_rev" >= 0.9 * "cs_items"."cs_item_rev" -JOIN "ws_items" +JOIN "ws_items" AS "ws_items" ON "cs_items"."cs_item_rev" <= 1.1 * "ws_items"."ws_item_rev" AND "cs_items"."cs_item_rev" >= 0.9 * "ws_items"."ws_item_rev" AND "ss_items"."item_id" = "ws_items"."item_id" @@ -7193,7 +7193,7 @@ WITH "wss" AS ( "wss"."thu_sales" AS "thu_sales2", "wss"."fri_sales" AS "fri_sales2", "wss"."sat_sales" AS "sat_sales2" - FROM "wss" + FROM "wss" AS "wss" JOIN "date_dim" AS "d" ON "d"."d_month_seq" <= 1219 AND "d"."d_month_seq" >= 1208 @@ -7212,7 +7212,7 @@ SELECT "wss"."thu_sales" / "x"."thu_sales2" AS "_col_7", "wss"."fri_sales" / "x"."fri_sales2" AS "_col_8", "wss"."sat_sales" / "x"."sat_sales2" AS "_col_9" -FROM "wss" +FROM "wss" AS "wss" JOIN "date_dim" AS "d" ON "d"."d_month_seq" <= 1207 AND "d"."d_month_seq" >= 1196 @@ -7379,17 +7379,17 @@ WITH "customer_address_2" AS ( SELECT "ss"."i_item_id" AS "i_item_id", "ss"."total_sales" AS "total_sales" - FROM "ss" + FROM "ss" AS "ss" UNION ALL SELECT "cs"."i_item_id" AS "i_item_id", "cs"."total_sales" AS "total_sales" - FROM "cs" + FROM "cs" AS "cs" UNION ALL SELECT "ws"."i_item_id" AS "i_item_id", "ws"."total_sales" AS "total_sales" - FROM "ws" + FROM "ws" AS "ws" ) SELECT "tmp1"."i_item_id" AS "i_item_id", @@ -7921,7 +7921,7 @@ WITH "cs_ui" AS ( ON "ad1"."ca_address_sk" = "store_sales"."ss_addr_sk" JOIN "customer_demographics" AS "cd1" ON "cd1"."cd_demo_sk" = "store_sales"."ss_cdemo_sk" - JOIN "cs_ui" + JOIN "cs_ui" AS "cs_ui" ON "cs_ui"."cs_item_sk" = "store_sales"."ss_item_sk" JOIN "customer" AS "customer" ON "customer"."c_customer_sk" = "store_sales"."ss_customer_sk" @@ -10343,8 +10343,8 @@ WITH "date_dim_2" AS ( "ss"."sales" AS "sales", COALESCE("sr"."returns1", 0) AS "returns1", "ss"."profit" - COALESCE("sr"."profit_loss", 0) AS "profit" - FROM "ss" - LEFT JOIN "sr" + FROM "ss" AS "ss" + LEFT JOIN "sr" AS "sr" ON "sr"."s_store_sk" = "ss"."s_store_sk" UNION ALL SELECT @@ -10353,8 +10353,8 @@ WITH "date_dim_2" AS ( "cs"."sales" AS "sales", "cr"."returns1" AS "returns1", "cs"."profit" - "cr"."profit_loss" AS "profit" - FROM "cs" - CROSS JOIN "cr" + FROM "cs" AS "cs" + CROSS JOIN "cr" AS "cr" UNION ALL SELECT 'web channel' AS "channel", @@ -10362,8 +10362,8 @@ WITH "date_dim_2" AS ( "ws"."sales" AS "sales", COALESCE("wr"."returns1", 0) AS "returns1", "ws"."profit" - COALESCE("wr"."profit_loss", 0) AS "profit" - FROM "ws" - LEFT JOIN "wr" + FROM "ws" AS "ws" + LEFT JOIN "wr" AS "wr" ON "wr"."wp_web_page_sk" = "ws"."wp_web_page_sk" ) SELECT @@ -10546,12 +10546,12 @@ SELECT COALESCE("ws"."ws_qty", 0) + COALESCE("cs"."cs_qty", 0) AS "other_chan_qty", COALESCE("ws"."ws_wc", 0) + COALESCE("cs"."cs_wc", 0) AS "other_chan_wholesale_cost", COALESCE("ws"."ws_sp", 0) + COALESCE("cs"."cs_sp", 0) AS "other_chan_sales_price" -FROM "ss" -LEFT JOIN "cs" +FROM "ss" AS "ss" +LEFT JOIN "cs" AS "cs" ON "cs"."cs_customer_sk" = "ss"."ss_customer_sk" AND "cs"."cs_item_sk" = "cs"."cs_item_sk" AND "cs"."cs_sold_year" = "ss"."ss_sold_year" -LEFT JOIN "ws" +LEFT JOIN "ws" AS "ws" ON "ss"."ss_customer_sk" = "ws"."ws_customer_sk" AND "ss"."ss_item_sk" = "ws"."ws_item_sk" AND "ss"."ss_sold_year" = "ws"."ws_sold_year" @@ -10850,7 +10850,7 @@ WITH "date_dim_2" AS ( "ssr"."sales" AS "sales", "ssr"."returns1" AS "returns1", "ssr"."profit" AS "profit" - FROM "ssr" + FROM "ssr" AS "ssr" UNION ALL SELECT 'catalog channel' AS "channel", @@ -10858,7 +10858,7 @@ WITH "date_dim_2" AS ( "csr"."sales" AS "sales", "csr"."returns1" AS "returns1", "csr"."profit" AS "profit" - FROM "csr" + FROM "csr" AS "csr" UNION ALL SELECT 'web channel' AS "channel", @@ -10866,7 +10866,7 @@ WITH "date_dim_2" AS ( "wsr"."sales" AS "sales", "wsr"."returns1" AS "returns1", "wsr"."profit" AS "profit" - FROM "wsr" + FROM "wsr" AS "wsr" ) SELECT "x"."channel" AS "channel", @@ -11249,10 +11249,10 @@ SELECT ( "sr_items"."sr_item_qty" + "cr_items"."cr_item_qty" + "wr_items"."wr_item_qty" ) / 3.0 AS "average" -FROM "sr_items" -JOIN "cr_items" +FROM "sr_items" AS "sr_items" +JOIN "cr_items" AS "cr_items" ON "cr_items"."item_id" = "sr_items"."item_id" -JOIN "wr_items" +JOIN "wr_items" AS "wr_items" ON "sr_items"."item_id" = "wr_items"."item_id" ORDER BY "sr_items"."item_id", @@ -12312,14 +12312,14 @@ WITH "ws_wh" AS ( ), "_u_0" AS ( SELECT "ws_wh"."ws_order_number" AS "ws_order_number" - FROM "ws_wh" + FROM "ws_wh" AS "ws_wh" GROUP BY "ws_wh"."ws_order_number" ), "_u_1" AS ( SELECT "web_returns"."wr_order_number" AS "wr_order_number" FROM "web_returns" AS "web_returns" - JOIN "ws_wh" + JOIN "ws_wh" AS "ws_wh" ON "web_returns"."wr_order_number" = "ws_wh"."ws_order_number" GROUP BY "web_returns"."wr_order_number" @@ -12475,8 +12475,8 @@ SELECT ELSE 0 END ) AS "store_and_catalog" -FROM "ssci" -FULL JOIN "csci" +FROM "ssci" AS "ssci" +FULL JOIN "csci" AS "csci" ON "csci"."customer_sk" = "ssci"."customer_sk" AND "csci"."item_sk" = "ssci"."item_sk" LIMIT 100; diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql index 36f096c..bf624da 100644 --- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql +++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql @@ -882,7 +882,7 @@ WITH "revenue" AS ( ), "_u_0" AS ( SELECT MAX("revenue"."total_revenue") AS "_col_0" - FROM "revenue" + FROM "revenue" AS "revenue" ) SELECT "supplier"."s_suppkey" AS "s_suppkey", @@ -891,7 +891,7 @@ SELECT "supplier"."s_phone" AS "s_phone", "revenue"."total_revenue" AS "total_revenue" FROM "supplier" AS "supplier" -JOIN "revenue" +JOIN "revenue" AS "revenue" ON "revenue"."supplier_no" = "supplier"."s_suppkey" JOIN "_u_0" AS "_u_0" ON "_u_0"."_col_0" = "revenue"."total_revenue" diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index f83191d..3d1e7ec 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -41,7 +41,7 @@ LEFT JOIN ( GROUP BY y.a ) AS _u_1 - ON x.a = "_u_1"."a" + ON x.a = _u_1.a LEFT JOIN ( SELECT y.b AS b @@ -49,7 +49,7 @@ LEFT JOIN ( GROUP BY y.b ) AS _u_2 - ON x.a = "_u_2"."b" + ON x.a = _u_2.b LEFT JOIN ( SELECT y.a AS a @@ -57,7 +57,7 @@ LEFT JOIN ( GROUP BY y.a ) AS _u_3 - ON x.a = "_u_3"."a" + ON x.a = _u_3.a LEFT JOIN ( SELECT SUM(y.b) AS b, @@ -157,9 +157,9 @@ LEFT JOIN ( ON _u_21._u_22 = x.a WHERE x.a = _u_0.a - AND NOT "_u_1"."a" IS NULL - AND NOT "_u_2"."b" IS NULL - AND NOT "_u_3"."a" IS NULL + AND NOT _u_1.a IS NULL + AND NOT _u_2.b IS NULL + AND NOT _u_3.a IS NULL AND x.a = _u_4.b AND x.a > _u_6.b AND x.a = _u_8.a diff --git a/tests/test_build.py b/tests/test_build.py index 817b609..f0c631f 100644 --- a/tests/test_build.py +++ b/tests/test_build.py @@ -653,6 +653,18 @@ class TestBuild(unittest.TestCase): lambda: exp.cast_unless("CAST(x AS TEXT)", "int", "int"), "CAST(CAST(x AS TEXT) AS INT)", ), + ( + lambda: exp.rename_column("table1", "c1", "c2", True), + "ALTER TABLE table1 RENAME COLUMN IF EXISTS c1 TO c2", + ), + ( + lambda: exp.rename_column("table1", "c1", "c2", False), + "ALTER TABLE table1 RENAME COLUMN c1 TO c2", + ), + ( + lambda: exp.rename_column("table1", "c1", "c2"), + "ALTER TABLE table1 RENAME COLUMN c1 TO c2", + ), ]: with self.subTest(sql): self.assertEqual(expression().sql(dialect[0] if dialect else None), sql) diff --git a/tests/test_jsonpath.py b/tests/test_jsonpath.py new file mode 100644 index 0000000..01cd899 --- /dev/null +++ b/tests/test_jsonpath.py @@ -0,0 +1,140 @@ +import json +import os +import unittest + +from sqlglot import jsonpath +from sqlglot.errors import ParseError, TokenError +from tests.helpers import FIXTURES_DIR + + +class TestJsonpath(unittest.TestCase): + maxDiff = None + + def test_jsonpath(self): + self.assertEqual( + jsonpath.parse("$.*.a[0]['x'][*, 'y', 1].z[?(@.a == 'b'), 1:][1:5][1,?@.a][(@.x)]"), + [ + {"kind": "root"}, + {"kind": "child", "value": "*"}, + {"kind": "child", "value": "a"}, + {"kind": "subscript", "value": 0}, + {"kind": "key", "value": "x"}, + {"kind": "union", "value": [{"kind": "wildcard"}, "y", 1]}, + {"kind": "child", "value": "z"}, + {"kind": "selector", "value": {"kind": "filter", "value": "(@.a == 'b'), 1:"}}, + { + "kind": "subscript", + "value": {"end": 5, "kind": "slice", "start": 1, "step": None}, + }, + {"kind": "union", "value": [1, {"kind": "filter", "value": "@.a"}]}, + {"kind": "selector", "value": {"kind": "script", "value": "@.x)"}}, + ], + ) + + def test_identity(self): + for selector, expected in ( + ("$.select", "$.select"), + ("$[(@.length-1)]", "$[(@.length-1)]"), + ("$[((@.length-1))]", "$[((@.length-1))]"), + ): + with self.subTest(f"{selector} -> {expected}"): + self.assertEqual(jsonpath.generate(jsonpath.parse(selector)), expected) + + def test_cts_file(self): + with open(os.path.join(FIXTURES_DIR, "jsonpath", "cts.json")) as file: + tests = json.load(file)["tests"] + + # sqlglot json path generator rewrites to a normal form + overrides = { + """$['a',1]""": """$["a",1]""", + """$[*,'a']""": """$[*,"a"]""", + """$..['a','d']""": """$..["a","d"]""", + """$[1, ?@.a=='b', 1:]""": """$[1,?@.a=='b', 1:]""", + """$["a"]""": """$.a""", + """$["c"]""": """$.c""", + """$['a']""": """$.a""", + """$['c']""": """$.c""", + """$[' ']""": """$[" "]""", + """$['\\'']""": """$["\'"]""", + """$['\\\\']""": """$["\\\\"]""", + """$['\\/']""": """$["\\/"]""", + """$['\\b']""": """$["\\b"]""", + """$['\\f']""": """$["\\f"]""", + """$['\\n']""": """$["\\n"]""", + """$['\\r']""": """$["\\r"]""", + """$['\\t']""": """$["\\t"]""", + """$['\\u263A']""": """$["\\u263A"]""", + """$['\\u263a']""": """$["\\u263a"]""", + """$['\\uD834\\uDD1E']""": """$["\\uD834\\uDD1E"]""", + """$['\\uD83D\\uDE00']""": """$["\\uD83D\\uDE00"]""", + """$['']""": """$[""]""", + """$[? @.a]""": """$[?@.a]""", + """$[?\n@.a]""": """$[?@.a]""", + """$[?\t@.a]""": """$[?@.a]""", + """$[?\r@.a]""": """$[?@.a]""", + """$[? (@.a)]""": """$[?(@.a)]""", + """$[?\n(@.a)]""": """$[?(@.a)]""", + """$[?\t(@.a)]""": """$[?(@.a)]""", + """$[?\r(@.a)]""": """$[?(@.a)]""", + """$[ ?@.a]""": """$[?@.a]""", + """$[\n?@.a]""": """$[?@.a]""", + """$[\t?@.a]""": """$[?@.a]""", + """$[\r?@.a]""": """$[?@.a]""", + """$ ['a']""": """$.a""", + """$\n['a']""": """$.a""", + """$\t['a']""": """$.a""", + """$\r['a']""": """$.a""", + """$['a'] ['b']""": """$.a.b""", + """$['a'] \n['b']""": """$.a.b""", + """$['a'] \t['b']""": """$.a.b""", + """$['a'] \r['b']""": """$.a.b""", + """$ .a""": """$.a""", + """$\n.a""": """$.a""", + """$\t.a""": """$.a""", + """$\r.a""": """$.a""", + """$[ 'a']""": """$.a""", + """$[\n'a']""": """$.a""", + """$[\t'a']""": """$.a""", + """$[\r'a']""": """$.a""", + """$['a' ]""": """$.a""", + """$['a'\n]""": """$.a""", + """$['a'\t]""": """$.a""", + """$['a'\r]""": """$.a""", + """$['a' ,'b']""": """$["a","b"]""", + """$['a'\n,'b']""": """$["a","b"]""", + """$['a'\t,'b']""": """$["a","b"]""", + """$['a'\r,'b']""": """$["a","b"]""", + """$['a', 'b']""": """$["a","b"]""", + """$['a',\n'b']""": """$["a","b"]""", + """$['a',\t'b']""": """$["a","b"]""", + """$['a',\r'b']""": """$["a","b"]""", + """$[1 :5:2]""": """$[1:5:2]""", + """$[1\n:5:2]""": """$[1:5:2]""", + """$[1\t:5:2]""": """$[1:5:2]""", + """$[1\r:5:2]""": """$[1:5:2]""", + """$[1: 5:2]""": """$[1:5:2]""", + """$[1:\n5:2]""": """$[1:5:2]""", + """$[1:\t5:2]""": """$[1:5:2]""", + """$[1:\r5:2]""": """$[1:5:2]""", + """$[1:5 :2]""": """$[1:5:2]""", + """$[1:5\n:2]""": """$[1:5:2]""", + """$[1:5\t:2]""": """$[1:5:2]""", + """$[1:5\r:2]""": """$[1:5:2]""", + """$[1:5: 2]""": """$[1:5:2]""", + """$[1:5:\n2]""": """$[1:5:2]""", + """$[1:5:\t2]""": """$[1:5:2]""", + """$[1:5:\r2]""": """$[1:5:2]""", + } + + for test in tests: + selector = test["selector"] + + with self.subTest(f"{selector.strip()} /* {test['name']} */"): + if test.get("invalid_selector"): + try: + jsonpath.parse(selector) + except (ParseError, TokenError): + pass + else: + nodes = jsonpath.parse(selector) + self.assertEqual(jsonpath.generate(nodes), overrides.get(selector, selector)) diff --git a/tests/test_lineage.py b/tests/test_lineage.py index 66b8b15..2f3456d 100644 --- a/tests/test_lineage.py +++ b/tests/test_lineage.py @@ -51,7 +51,7 @@ class TestLineage(unittest.TestCase): ) self.assertEqual( node.source.sql(), - "WITH z AS (SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y /* source: y */) SELECT z.a AS a FROM z", + "WITH z AS (SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y /* source: y */) SELECT z.a AS a FROM z AS z", ) self.assertEqual(node.alias, "") @@ -79,14 +79,14 @@ class TestLineage(unittest.TestCase): ) self.assertEqual( node.source.sql(), - "SELECT z.a AS a FROM (WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y) AS z /* source: z */", + "SELECT z.a AS a FROM (WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y AS y) AS z /* source: z */", ) self.assertEqual(node.alias, "") downstream = node.downstream[0] self.assertEqual( downstream.source.sql(), - "WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y", + "WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y AS y", ) self.assertEqual(downstream.alias, "z") @@ -104,7 +104,7 @@ class TestLineage(unittest.TestCase): ) self.assertEqual( node.source.sql(), - "WITH y AS (SELECT * FROM x AS x) SELECT y.a AS a FROM y", + "WITH y AS (SELECT * FROM x AS x) SELECT y.a AS a FROM y AS y", ) self.assertEqual(node.alias, "") @@ -122,7 +122,7 @@ class TestLineage(unittest.TestCase): ) self.assertEqual( node.source.sql(), - "WITH y AS (SELECT * FROM x AS x) SELECT a AS a FROM y JOIN z AS z ON y.uid = z.uid", + "WITH y AS (SELECT * FROM x AS x) SELECT a AS a FROM y AS y JOIN z AS z ON y.uid = z.uid", ) self.assertEqual(node.alias, "") @@ -166,12 +166,12 @@ class TestLineage(unittest.TestCase): self.assertEqual( node.source.sql(), - "WITH t1 AS (SELECT t2.c2 AS c2 FROM a.b.t2 AS t2), inter AS (SELECT t1.c2 AS c2 FROM t1) SELECT inter.c2 AS c2 FROM inter", + "WITH t1 AS (SELECT t2.c2 AS c2 FROM a.b.t2 AS t2), inter AS (SELECT t1.c2 AS c2 FROM t1 AS t1) SELECT inter.c2 AS c2 FROM inter AS inter", ) self.assertEqual(node.alias, "") downstream = node.downstream[0] - self.assertEqual(downstream.source.sql(), "SELECT t1.c2 AS c2 FROM t1") + self.assertEqual(downstream.source.sql(), "SELECT t1.c2 AS c2 FROM t1 AS t1") self.assertEqual(downstream.expression.sql(), "t1.c2 AS c2") self.assertEqual(downstream.alias, "") diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py index 3f96a0e..d0ef8aa 100644 --- a/tests/test_optimizer.py +++ b/tests/test_optimizer.py @@ -262,7 +262,7 @@ class TestOptimizer(unittest.TestCase): ), dialect="bigquery", ).sql(), - 'WITH "x" AS (SELECT "y"."a" AS "a" FROM "DB"."y" AS "y" CROSS JOIN "a"."b"."INFORMATION_SCHEMA"."COLUMNS" AS "COLUMNS") SELECT "x"."a" AS "a" FROM "x"', + 'WITH "x" AS (SELECT "y"."a" AS "a" FROM "DB"."y" AS "y" CROSS JOIN "a"."b"."INFORMATION_SCHEMA"."COLUMNS" AS "COLUMNS") SELECT "x"."a" AS "a" FROM "x" AS "x"', ) self.assertEqual( @@ -550,18 +550,26 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|') def test_struct_type_annotation(self): tests = { - "SELECT STRUCT(1 AS col)": "STRUCT<col INT>", - "SELECT STRUCT(1 AS col, 2.5 AS row)": "STRUCT<col INT, row DOUBLE>", - "SELECT STRUCT(1)": "STRUCT<INT>", - "SELECT STRUCT(1 AS col, 2.5 AS row, struct(3.5 AS inner_col, 4 AS inner_row) AS nested_struct)": "STRUCT<col INT, row DOUBLE, nested_struct STRUCT<inner_col DOUBLE, inner_row INT>>", - "SELECT STRUCT(1 AS col, 2.5, ARRAY[1, 2, 3] AS nested_array, 'foo')": "STRUCT<col INT, DOUBLE, nested_array ARRAY<INT>, VARCHAR>", - "SELECT STRUCT(1, 2.5, 'bar')": "STRUCT<INT, DOUBLE, VARCHAR>", - 'SELECT STRUCT(1 AS "CaseSensitive")': 'STRUCT<"CaseSensitive" INT>', + ("SELECT STRUCT(1 AS col)", "spark"): "STRUCT<col INT>", + ("SELECT STRUCT(1 AS col, 2.5 AS row)", "spark"): "STRUCT<col INT, row DOUBLE>", + ("SELECT STRUCT(1)", "bigquery"): "STRUCT<INT>", + ( + "SELECT STRUCT(1 AS col, 2.5 AS row, struct(3.5 AS inner_col, 4 AS inner_row) AS nested_struct)", + "spark", + ): "STRUCT<col INT, row DOUBLE, nested_struct STRUCT<inner_col DOUBLE, inner_row INT>>", + ( + "SELECT STRUCT(1 AS col, 2.5, ARRAY[1, 2, 3] AS nested_array, 'foo')", + "bigquery", + ): "STRUCT<col INT, DOUBLE, nested_array ARRAY<INT>, VARCHAR>", + ("SELECT STRUCT(1, 2.5, 'bar')", "spark"): "STRUCT<INT, DOUBLE, VARCHAR>", + ('SELECT STRUCT(1 AS "CaseSensitive")', "spark"): 'STRUCT<"CaseSensitive" INT>', + ("SELECT STRUCT_PACK(a := 1, b := 2.5)", "duckdb"): "STRUCT<a INT, b DOUBLE>", + ("SELECT ROW(1, 2.5, 'foo')", "presto"): "STRUCT<INT, DOUBLE, VARCHAR>", } - for sql, target_type in tests.items(): + for (sql, dialect), target_type in tests.items(): with self.subTest(sql): - expression = annotate_types(parse_one(sql)) + expression = annotate_types(parse_one(sql, read=dialect)) assert expression.expressions[0].is_type(target_type) def test_literal_type_annotation(self): diff --git a/tests/test_parser.py b/tests/test_parser.py index bff4f98..91fd4c6 100644 --- a/tests/test_parser.py +++ b/tests/test_parser.py @@ -4,6 +4,7 @@ from unittest.mock import patch from sqlglot import Parser, exp, parse, parse_one from sqlglot.errors import ErrorLevel, ParseError +from sqlglot.parser import logger as parser_logger from tests.helpers import assert_logger_contains @@ -151,11 +152,14 @@ class TestParser(unittest.TestCase): ) def test_command(self): - expressions = parse("SET x = 1; ADD JAR s3://a; SELECT 1", read="hive") - self.assertEqual(len(expressions), 3) - self.assertEqual(expressions[0].sql(), "SET x = 1") - self.assertEqual(expressions[1].sql(), "ADD JAR s3://a") - self.assertEqual(expressions[2].sql(), "SELECT 1") + with self.assertLogs(parser_logger) as cm: + expressions = parse("SET x = 1; ADD JAR s3://a; SELECT 1", read="hive") + self.assertEqual(len(expressions), 3) + self.assertEqual(expressions[0].sql(), "SET x = 1") + self.assertEqual(expressions[1].sql(), "ADD JAR s3://a") + self.assertEqual(expressions[2].sql(), "SELECT 1") + + assert "'ADD JAR s3://a'" in cm.output[0] def test_lambda_struct(self): expression = parse_one("FILTER(a.b, x -> x.id = id)") @@ -272,6 +276,8 @@ class TestParser(unittest.TestCase): with self.assertRaises(ParseError): parse_one("SELECT A[:") + self.assertEqual(parse_one("as as", error_level=ErrorLevel.IGNORE).sql(), "AS as") + def test_space(self): self.assertEqual( parse_one("SELECT ROW() OVER(PARTITION BY x) FROM x GROUP BY y").sql(), @@ -504,7 +510,9 @@ class TestParser(unittest.TestCase): self.assertEqual(set_to.sql(), "SET x = 1") self.assertIsInstance(set_to, exp.Set) - set_as_command = parse_one("SET DEFAULT ROLE ALL TO USER") + with self.assertLogs(parser_logger) as cm: + set_as_command = parse_one("SET DEFAULT ROLE ALL TO USER") + assert "'SET DEFAULT ROLE ALL TO USER'" in cm.output[0] self.assertEqual(set_as_command.sql(), "SET DEFAULT ROLE ALL TO USER") diff --git a/tests/test_serde.py b/tests/test_serde.py index 1043fcf..40d6134 100644 --- a/tests/test_serde.py +++ b/tests/test_serde.py @@ -6,8 +6,7 @@ from sqlglot.optimizer.annotate_types import annotate_types from tests.helpers import load_sql_fixtures -class CustomExpression(exp.Expression): - ... +class CustomExpression(exp.Expression): ... class TestSerDe(unittest.TestCase): diff --git a/tests/test_transpile.py b/tests/test_transpile.py index 51805d9..fdbf2e0 100644 --- a/tests/test_transpile.py +++ b/tests/test_transpile.py @@ -5,6 +5,7 @@ from unittest import mock from sqlglot import parse_one, transpile from sqlglot.errors import ErrorLevel, ParseError, UnsupportedError from sqlglot.helper import logger as helper_logger +from sqlglot.parser import logger as parser_logger from tests.helpers import ( assert_logger_contains, load_sql_fixture_pairs, @@ -736,6 +737,45 @@ FROM base""", with self.subTest(sql): self.assertEqual(transpile(sql)[0], sql.strip()) + def test_command_identity(self): + for sql in ( + "ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER", + "ALTER DOMAIN foo VALIDATE CONSTRAINT bla", + "ALTER ROLE CURRENT_USER WITH REPLICATION", + "ALTER RULE foo ON bla RENAME TO baz", + "ALTER SEQUENCE IF EXISTS baz RESTART WITH boo", + "ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS=3", + "ALTER TABLE integers DROP PRIMARY KEY", + "ALTER TABLE s_ut ADD CONSTRAINT s_ut_uq UNIQUE hajo", + "ALTER TABLE table1 MODIFY COLUMN name1 SET TAG foo='bar'", + "ALTER TABLE table1 RENAME COLUMN c1 AS c2", + "ALTER TABLE table1 RENAME COLUMN c1 TO c2, c2 TO c3", + "ALTER TABLE table1 RENAME COLUMN c1 c2", + "ALTER TYPE electronic_mail RENAME TO email", + "ALTER VIEW foo ALTER COLUMN bla SET DEFAULT 'NOT SET'", + "ALTER schema doo", + "ANALYZE a.y", + "CALL catalog.system.iceberg_procedure_name(named_arg_1 => 'arg_1', named_arg_2 => 'arg_2')", + "COMMENT ON ACCESS METHOD gin IS 'GIN index access method'", + "CREATE OR REPLACE STAGE", + "CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT)", + "EXECUTE statement", + "EXPLAIN SELECT * FROM x", + "GRANT INSERT ON foo TO bla", + "LOAD foo", + "OPTIMIZE TABLE y", + "PREPARE statement", + "SET -v", + "SET @user OFF", + "SHOW TABLES", + "TRUNCATE TABLE x", + "VACUUM FREEZE my_table", + ): + with self.subTest(sql): + with self.assertLogs(parser_logger) as cm: + self.assertEqual(transpile(sql)[0], sql) + assert f"'{sql[:100]}' contains unsupported syntax" in cm.output[0] + def test_normalize_name(self): self.assertEqual( transpile("cardinality(x)", read="presto", write="presto", normalize_functions="lower")[ |