From c8d4d4ead5df1c84966431eec8b88e974414dafc Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 14 Jul 2023 07:43:02 +0200 Subject: Merging upstream version 17.4.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_dialect.py | 2 +- tests/dialects/test_mysql.py | 2 + tests/dialects/test_oracle.py | 14 +++- tests/dialects/test_postgres.py | 1 + tests/dialects/test_snowflake.py | 43 +++++++++++ tests/dialects/test_tsql.py | 29 ++++++++ tests/fixtures/identity.sql | 17 +++-- tests/fixtures/optimizer/optimizer.sql | 105 +++++++++++++++++++++++++++ tests/fixtures/optimizer/qualify_columns.sql | 4 + tests/fixtures/optimizer/qualify_tables.sql | 50 ++++++------- tests/test_expressions.py | 2 + tests/test_parser.py | 57 +-------------- 12 files changed, 237 insertions(+), 89 deletions(-) (limited to 'tests') diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 05738cf..618b1b4 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1356,7 +1356,7 @@ class TestDialect(Validator): write={ "duckdb": "CREATE TABLE t (c TEXT, nc TEXT, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)", "hive": "CREATE TABLE t (c CHAR, nc CHAR, v1 STRING, v2 STRING, nv STRING, nv2 STRING)", - "oracle": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR2, v2 VARCHAR2, nv NVARCHAR2, nv2 NVARCHAR2)", + "oracle": "CREATE TABLE t (c CHAR, nc NCHAR, v1 VARCHAR2, v2 VARCHAR2, nv NVARCHAR2, nv2 NVARCHAR2)", "postgres": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR, v2 VARCHAR, nv VARCHAR, nv2 VARCHAR)", "sqlite": "CREATE TABLE t (c TEXT, nc TEXT, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)", }, diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 3539ad0..70ffcd9 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -69,6 +69,8 @@ class TestMySQL(Validator): ) def test_identity(self): + 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") self.validate_identity("SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt") self.validate_identity("SELECT /*+ INDEX(t, i) */ c1 FROM t WHERE c2 = 'value'") diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index 2c67805..f30b38f 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -1,3 +1,4 @@ +from sqlglot.errors import UnsupportedError from tests.dialects.test_dialect import Validator @@ -57,8 +58,17 @@ class TestOracle(Validator): def test_join_marker(self): self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y") - self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y = e2.y (+)") - self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y (+)") + + self.validate_all( + "SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y = e2.y (+)", write={"": UnsupportedError} + ) + self.validate_all( + "SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y = e2.y (+)", + write={ + "": "SELECT e1.x, e2.x FROM e AS e1, e AS e2 WHERE e1.y = e2.y", + "oracle": "SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y = e2.y (+)", + }, + ) def test_hints(self): self.validate_identity("SELECT /*+ USE_NL(A B) */ A.COL_TEST FROM TABLE_A A, TABLE_B B") diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index 605dfff..9fc18b7 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -9,6 +9,7 @@ class TestPostgres(Validator): dialect = "postgres" def test_ddl(self): + 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)") diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index f7bab4d..e20045b 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -1,3 +1,5 @@ +from unittest import mock + from sqlglot import UnsupportedError, exp, parse_one from tests.dialects.test_dialect import Validator @@ -309,6 +311,7 @@ class TestSnowflake(Validator): "SELECT IFF(TRUE, 'true', 'false')", write={ "snowflake": "SELECT IFF(TRUE, 'true', 'false')", + "spark": "SELECT IF(TRUE, 'true', 'false')", }, ) self.validate_all( @@ -870,6 +873,46 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA self.assertIsInstance(ilike, exp.ILikeAny) like.sql() # check that this doesn't raise + @mock.patch("sqlglot.generator.logger") + def test_regexp_substr(self, logger): + self.validate_all( + "REGEXP_SUBSTR(subject, pattern, pos, occ, params, group)", + write={ + "bigquery": "REGEXP_EXTRACT(subject, pattern, pos, occ)", + "hive": "REGEXP_EXTRACT(subject, pattern, group)", + "presto": "REGEXP_EXTRACT(subject, pattern, group)", + "snowflake": "REGEXP_SUBSTR(subject, pattern, pos, occ, params, group)", + "spark": "REGEXP_EXTRACT(subject, pattern, group)", + }, + ) + self.validate_all( + "REGEXP_SUBSTR(subject, pattern)", + read={ + "bigquery": "REGEXP_EXTRACT(subject, pattern)", + "hive": "REGEXP_EXTRACT(subject, pattern)", + "presto": "REGEXP_EXTRACT(subject, pattern)", + "spark": "REGEXP_EXTRACT(subject, pattern)", + }, + write={ + "bigquery": "REGEXP_EXTRACT(subject, pattern)", + "hive": "REGEXP_EXTRACT(subject, pattern)", + "presto": "REGEXP_EXTRACT(subject, pattern)", + "snowflake": "REGEXP_SUBSTR(subject, pattern)", + "spark": "REGEXP_EXTRACT(subject, pattern)", + }, + ) + self.validate_all( + "REGEXP_SUBSTR(subject, pattern, 1, 1, 'c', group)", + read={ + "bigquery": "REGEXP_SUBSTR(subject, pattern, 1, 1, 'c', group)", + "duckdb": "REGEXP_EXTRACT(subject, pattern, group)", + "hive": "REGEXP_EXTRACT(subject, pattern, group)", + "presto": "REGEXP_EXTRACT(subject, pattern, group)", + "snowflake": "REGEXP_SUBSTR(subject, pattern, 1, 1, 'c', group)", + "spark": "REGEXP_EXTRACT(subject, pattern, group)", + }, + ) + def test_match_recognize(self): for row in ( "ONE ROW PER MATCH", diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py index 5426859..065cdd0 100644 --- a/tests/dialects/test_tsql.py +++ b/tests/dialects/test_tsql.py @@ -6,6 +6,11 @@ class TestTSQL(Validator): dialect = "tsql" def test_tsql(self): + 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") + self.validate_identity("INSERT INTO x (y) OUTPUT x.a, x.b SELECT * FROM z") + self.validate_identity("DELETE x OUTPUT x.a FROM z") self.validate_identity("SELECT * FROM t WITH (TABLOCK, INDEX(myindex))") self.validate_identity("SELECT * FROM t WITH (NOWAIT)") self.validate_identity("SELECT CASE WHEN a > 1 THEN b END") @@ -205,6 +210,30 @@ class TestTSQL(Validator): }, ) + self.validate_all( + "CAST(x as FLOAT(32))", + write={"tsql": "CAST(x AS FLOAT(32))", "hive": "CAST(x AS FLOAT)"}, + ) + + self.validate_all( + "CAST(x as FLOAT(64))", + write={"tsql": "CAST(x AS FLOAT(64))", "spark": "CAST(x AS DOUBLE)"}, + ) + + self.validate_all( + "CAST(x as FLOAT(6))", write={"tsql": "CAST(x AS FLOAT(6))", "hive": "CAST(x AS FLOAT)"} + ) + + self.validate_all( + "CAST(x as FLOAT(36))", + write={"tsql": "CAST(x AS FLOAT(36))", "hive": "CAST(x AS DOUBLE)"}, + ) + + self.validate_all( + "CAST(x as FLOAT(99))", + write={"tsql": "CAST(x AS FLOAT(99))", "hive": "CAST(x AS DOUBLE)"}, + ) + self.validate_all( "CAST(x as DOUBLE)", write={ diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 63631c4..5b2c29c 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -423,6 +423,8 @@ SELECT 1 INTERSECT SELECT 2 SELECT 1 INTERSECT SELECT 2 SELECT 1 AS delete, 2 AS alter SELECT * FROM (x) +SELECT * FROM ((x)) +SELECT * FROM (((x))) SELECT * FROM ((SELECT 1)) SELECT * FROM (x CROSS JOIN foo LATERAL VIEW EXPLODE(y)) SELECT * FROM (SELECT 1) AS x @@ -432,6 +434,14 @@ SELECT * FROM (SELECT 1 UNION ALL SELECT 2) SELECT * FROM ((SELECT 1) AS a UNION ALL (SELECT 2) AS b) SELECT * FROM ((SELECT 1) AS a(b)) SELECT * FROM ((SELECT 1) UNION (SELECT 2) UNION (SELECT 3)) +SELECT * FROM (table1 AS t1 LEFT JOIN table2 AS t2 ON 1 = 1) +SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1) +SELECT * FROM (tbl1, tbl2 JOIN tbl3 ON TRUE) +SELECT * FROM (tbl1 CROSS JOIN tbl2) +SELECT * FROM (tbl1 CROSS JOIN tbl2) AS t +SELECT * FROM (tbl AS tbl) AS t +SELECT * FROM (tbl1 JOIN (tbl2 CROSS JOIN tbl3) ON bla = foo) +SELECT * FROM (tbl1, LATERAL (SELECT * FROM bla) AS tbl) SELECT * FROM x AS y(a, b) SELECT * EXCEPT (a, b) SELECT * EXCEPT (a, b) FROM y @@ -607,6 +617,7 @@ CREATE FUNCTION a() LANGUAGE sql RETURNS INT CREATE FUNCTION a.b(x INT) RETURNS INT AS RETURN x + 1 CREATE FUNCTION a.b.c() CREATE INDEX abc ON t (a) +CREATE INDEX "abc" ON t (a) CREATE INDEX abc ON t (a, b, b) CREATE INDEX abc ON t (a NULLS LAST) CREATE INDEX pointloc ON points USING GIST(BOX(location, location)) @@ -700,6 +711,7 @@ UPDATE tbl_name SET foo = 123 UPDATE tbl_name SET foo = 123, bar = 345 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 TRUNCATE TABLE x OPTIMIZE TABLE y VACUUM FREEZE my_table @@ -721,11 +733,6 @@ WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 1 AS b)) SELECT * FROM a SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z SELECT ((SELECT 1) + 1) SELECT * FROM project.dataset.INFORMATION_SCHEMA.TABLES -SELECT * FROM (table1 AS t1 LEFT JOIN table2 AS t2 ON 1 = 1) -SELECT * FROM (tbl1 LEFT JOIN tbl2 ON 1 = 1) -SELECT * FROM (tbl1, tbl2 JOIN tbl3 ON TRUE) -SELECT * FROM (tbl1 JOIN (tbl2 CROSS JOIN tbl3) ON bla = foo) -SELECT * FROM (tbl1, LATERAL (SELECT * FROM bla) AS tbl) SELECT CAST(x AS INT) /* comment */ FROM foo SELECT a /* x */, b /* x */ SELECT a /* x */ /* y */ /* z */, b /* k */ /* m */ diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 38e64d7..4b58ea5 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -701,3 +701,108 @@ SELECT "x"."a" * "x"."b" - "x"."b" ) AS "f" FROM "x" AS "x"; + +# title: wrapped table without alias +# execute: false +SELECT * FROM (tbl); +SELECT + * +FROM ( + "tbl" AS "tbl" +); + +# title: wrapped table with alias +# execute: false +SELECT * FROM (tbl AS tbl); +SELECT + * +FROM ( + "tbl" AS "tbl" +); + +# title: wrapped join of tables without alias +SELECT a, c FROM (x LEFT JOIN y ON a = c); +SELECT + "x"."a" AS "a", + "y"."c" AS "c" +FROM ( + "x" AS "x" + LEFT JOIN "y" AS "y" + ON "x"."a" = "y"."c" +); + +# title: wrapped join of tables with alias +# execute: false +SELECT a, c FROM (x LEFT JOIN y ON a = c) AS t; +SELECT + "x"."a" AS "a", + "y"."c" AS "c" +FROM "x" AS "x" +LEFT JOIN "y" AS "y" + ON "x"."a" = "y"."c"; + +# title: chained wrapped joins without aliases +# execute: false +SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); +SELECT + * +FROM ( + ( + "a" AS "a" + CROSS JOIN ( + ( + "b" AS "b" + CROSS JOIN "c" AS "c" + ) + CROSS JOIN ( + "d" AS "d" + CROSS JOIN "e" AS "e" + ) + ) + ) +); + +# title: chained wrapped joins with aliases +# execute: false +SELECT * FROM ((a AS foo CROSS JOIN b AS bar) CROSS JOIN c AS baz); +SELECT + * +FROM ( + ( + "a" AS "foo" + CROSS JOIN "b" AS "bar" + ) + CROSS JOIN "c" AS "baz" +); + +# title: table joined with join construct +SELECT x.a, y.b, z.c FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b; +SELECT + "x"."a" AS "a", + "y"."b" AS "b", + "z"."c" AS "c" +FROM "x" AS "x" +LEFT JOIN ( + "y" AS "y" + JOIN "z" AS "z" + ON "y"."c" = "z"."c" +) + ON "x"."b" = "y"."b"; + +# title: select * from table joined with join construct +# execute: false +SELECT * FROM x LEFT JOIN (y INNER JOIN z ON y.c = z.c) ON x.b = y.b; +SELECT + "y"."b" AS "b", + "y"."c" AS "c", + "z"."a" AS "a", + "z"."c" AS "c", + "x"."a" AS "a", + "x"."b" AS "b" +FROM "x" AS "x" +LEFT JOIN ( + "y" AS "y" + JOIN "z" AS "z" + ON "y"."c" = "z"."c" +) + ON "x"."b" = "y"."b"; diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index f7ece5b..487a831 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -430,6 +430,10 @@ SELECT x.a AS a, x.b AS b FROM x AS x QUALIFY COUNT(x.a) OVER (PARTITION BY x.b) -- Expand laterals -------------------------------------- +# execute: false +select 2 AS d, d + 1 FROM x WHERE d = 2 GROUP BY d; +SELECT 2 AS d, 2 + 1 AS _col_1 FROM x AS x WHERE 2 = 2 GROUP BY 1; + # title: expand alias reference SELECT x.a + 1 AS i, diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index d8ce2b0..cd749a0 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -26,45 +26,49 @@ SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x; SELECT * FROM x PIVOT (SUM(a) FOR b IN ('a', 'b')); SELECT * FROM c.db.x AS x PIVOT(SUM(a) FOR b IN ('a', 'b')) AS _q_0; ------------------------------------------------------------ ---- Unnest wrapped tables / joins, expand join constructs ------------------------------------------------------------ - # title: wrapped table without alias SELECT * FROM (tbl); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM (c.db.tbl AS tbl); # title: wrapped table with alias SELECT * FROM (tbl AS tbl); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM (c.db.tbl AS tbl); -# title: wrapped table with alias and multiple redundant parentheses +# title: wrapped table with alias using multiple (redundant) parentheses SELECT * FROM ((((tbl AS tbl)))); -SELECT * FROM c.db.tbl AS tbl; +SELECT * FROM ((((c.db.tbl AS tbl)))); + +# title: wrapped join of tables without alias +SELECT * FROM (t1 CROSS JOIN t2); +SELECT * FROM (c.db.t1 AS t1 CROSS JOIN c.db.t2 AS t2); + +# title: wrapped join of tables with alias, expansion of join construct +SELECT * FROM (t1 CROSS JOIN t2) AS t; +SELECT * FROM (SELECT * FROM c.db.t1 AS t1 CROSS JOIN c.db.t2 AS t2) AS t; # title: chained wrapped joins without aliases (1) SELECT * FROM ((a CROSS JOIN b) CROSS JOIN c); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; +SELECT * FROM ((c.db.a AS a CROSS JOIN c.db.b AS b) CROSS JOIN c.db.c AS c); # title: chained wrapped joins without aliases (2) SELECT * FROM (a CROSS JOIN (b CROSS JOIN c)); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c; +SELECT * FROM (c.db.a AS a CROSS JOIN (c.db.b AS b CROSS JOIN c.db.c AS c)); # title: chained wrapped joins without aliases (3) SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN d))); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d; +SELECT * FROM ((c.db.a AS a CROSS JOIN ((c.db.b AS b CROSS JOIN c.db.c AS c) CROSS JOIN c.db.d AS d))); # title: chained wrapped joins without aliases (4) SELECT * FROM ((a CROSS JOIN ((b CROSS JOIN c) CROSS JOIN (d CROSS JOIN e)))); -SELECT * FROM c.db.a AS a CROSS JOIN c.db.b AS b CROSS JOIN c.db.c AS c CROSS JOIN c.db.d AS d CROSS JOIN c.db.e AS e; +SELECT * FROM ((c.db.a AS a CROSS JOIN ((c.db.b AS b CROSS JOIN c.db.c AS c) CROSS JOIN (c.db.d AS d CROSS JOIN c.db.e AS e)))); # title: chained wrapped joins with aliases SELECT * FROM ((a AS foo CROSS JOIN b AS bar) CROSS JOIN c AS baz); -SELECT * FROM c.db.a AS foo CROSS JOIN c.db.b AS bar CROSS JOIN c.db.c AS baz; +SELECT * FROM ((c.db.a AS foo CROSS JOIN c.db.b AS bar) CROSS JOIN c.db.c AS baz); # title: wrapped join with subquery without alias SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1); -SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1; +SELECT * FROM (c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tbl2 AS tbl2) AS t1); # title: wrapped join with subquery with alias, parentheses can't be omitted because of alias SELECT * FROM (tbl1 CROSS JOIN (SELECT * FROM tbl2) AS t1) AS t2; @@ -72,32 +76,28 @@ SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 CROSS JOIN (SELECT * FROM c.db.tb # title: join construct as the right operand of a left join SELECT * FROM a LEFT JOIN (b INNER JOIN c ON c.id = b.id) ON b.id = a.id; -SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; +SELECT * FROM c.db.a AS a LEFT JOIN (c.db.b AS b INNER JOIN c.db.c AS c ON c.id = b.id) ON b.id = a.id; -# title: nested joins converted to canonical form +# title: nested joins SELECT * FROM a LEFT JOIN b INNER JOIN c ON c.id = b.id ON b.id = a.id; -SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b ON b.id = a.id INNER JOIN c.db.c AS c ON c.id = b.id; +SELECT * FROM c.db.a AS a LEFT JOIN c.db.b AS b INNER JOIN c.db.c AS c ON c.id = b.id ON b.id = a.id; # title: parentheses can't be omitted because alias shadows inner table names SELECT t.a FROM (tbl AS tbl) AS t; SELECT t.a FROM (SELECT * FROM c.db.tbl AS tbl) AS t; -# title: outermost set of parentheses can't be omitted due to shadowing (1) -SELECT * FROM ((tbl AS tbl)) AS _q_0; -SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; - -# title: outermost set of parentheses can't be omitted due to shadowing (2) +# title: wrapped aliased table with outer alias SELECT * FROM ((((tbl AS tbl)))) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl AS tbl) AS _q_0; -# title: join construct with three tables in canonical form +# title: join construct with three tables SELECT * FROM (tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; -# title: join construct with three tables in canonical form and redundant set of parentheses +# title: join construct with three tables and redundant set of parentheses SELECT * FROM ((tbl1 AS tbl1 JOIN tbl2 AS tbl2 ON id1 = id2 JOIN tbl3 AS tbl3 ON id1 = id3)) AS _q_0; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN c.db.tbl2 AS tbl2 ON id1 = id2 JOIN c.db.tbl3 AS tbl3 ON id1 = id3) AS _q_0; -# title: nested join construct in canonical form +# title: join construct within join construct SELECT * FROM (tbl1 AS tbl1 JOIN (tbl2 AS tbl2 JOIN tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; SELECT * FROM (SELECT * FROM c.db.tbl1 AS tbl1 JOIN (SELECT * FROM c.db.tbl2 AS tbl2 JOIN c.db.tbl3 AS tbl3 ON id2 = id3) AS _q_0 ON id1 = id3) AS _q_1; diff --git a/tests/test_expressions.py b/tests/test_expressions.py index 277bec1..989220e 100644 --- a/tests/test_expressions.py +++ b/tests/test_expressions.py @@ -183,6 +183,8 @@ class TestExpressions(unittest.TestCase): exp.table_name(parse_one("foo.`{bar,er}`", read="databricks"), dialect="databricks"), "foo.`{bar,er}`", ) + self.assertEqual(exp.table_name(exp.to_table("a-1.b.c", dialect="bigquery")), '"a-1".b.c') + self.assertEqual(exp.table_name(exp.to_table("a.b.c.d.e", dialect="bigquery")), "a.b.c.d.e") def test_table(self): self.assertEqual(exp.table_("a", alias="b"), parse_one("select * from a b").find(exp.Table)) diff --git a/tests/test_parser.py b/tests/test_parser.py index 891dcef..07686af 100644 --- a/tests/test_parser.py +++ b/tests/test_parser.py @@ -571,7 +571,7 @@ class TestParser(unittest.TestCase): """ ) self.assertIsNotNone(query) - self.assertLessEqual(time.time() - now, 0.1) + self.assertLessEqual(time.time() - now, 0.2) def test_parse_properties(self): self.assertEqual( @@ -580,58 +580,3 @@ class TestParser(unittest.TestCase): def test_parse_floats(self): self.assertTrue(parse_one("1. ").is_number) - - def test_parse_wrapped_tables(self): - expr = parse_one("select * from (table)") - self.assertIsInstance(expr.args["from"].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["wrapped"]) - - expr = parse_one("select * from (((table)))") - self.assertIsInstance(expr.args["from"].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["wrapped"]) - - self.assertEqual(expr.sql(), "SELECT * FROM (table)") - - expr = parse_one("select * from (tbl1 join tbl2)") - self.assertIsInstance(expr.args["from"].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["wrapped"]) - self.assertEqual(len(expr.args["from"].this.args["joins"]), 1) - - expr = parse_one("select * from (tbl1 join tbl2) t") - self.assertIsInstance(expr.args["from"].this, exp.Subquery) - self.assertIsInstance(expr.args["from"].this.this, exp.Select) - self.assertEqual(expr.sql(), "SELECT * FROM (SELECT * FROM tbl1, tbl2) AS t") - - expr = parse_one("select * from (tbl as tbl) t") - self.assertEqual(expr.sql(), "SELECT * FROM (SELECT * FROM tbl AS tbl) AS t") - - expr = parse_one("select * from ((a cross join b) cross join c)") - self.assertIsInstance(expr.args["from"].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["wrapped"]) - self.assertEqual(len(expr.args["from"].this.args["joins"]), 2) - self.assertEqual(expr.sql(), "SELECT * FROM (a CROSS JOIN b CROSS JOIN c)") - - expr = parse_one("select * from ((a cross join b) cross join c) t") - self.assertIsInstance(expr.args["from"].this, exp.Subquery) - self.assertEqual(len(expr.args["from"].this.this.args["joins"]), 2) - self.assertEqual( - expr.sql(), "SELECT * FROM (SELECT * FROM a CROSS JOIN b CROSS JOIN c) AS t" - ) - - expr = parse_one("select * from (a cross join (b cross join c))") - self.assertIsInstance(expr.args["from"].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["wrapped"]) - self.assertEqual(len(expr.args["from"].this.args["joins"]), 1) - self.assertIsInstance(expr.args["from"].this.args["joins"][0].this, exp.Table) - self.assertTrue(expr.args["from"].this.args["joins"][0].this.args["wrapped"]) - self.assertEqual(expr.sql(), "SELECT * FROM (a CROSS JOIN (b CROSS JOIN c))") - - expr = parse_one("select * from ((a cross join ((b cross join c) cross join d)))") - self.assertEqual(expr.sql(), "SELECT * FROM (a CROSS JOIN (b CROSS JOIN c CROSS JOIN d))") - - expr = parse_one( - "select * from ((a cross join ((b cross join c) cross join (d cross join e))))" - ) - self.assertEqual( - expr.sql(), "SELECT * FROM (a CROSS JOIN (b CROSS JOIN c CROSS JOIN (d CROSS JOIN e)))" - ) -- cgit v1.2.3