From b0bf1ad1a196585c2d31bc38c554637fc4e2eec9 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 14 Jul 2023 07:42:58 +0200 Subject: Adding 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 +++++++++++++++++++++++++++ 6 files changed, 88 insertions(+), 3 deletions(-) (limited to 'tests/dialects') 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={ -- cgit v1.2.3