summaryrefslogtreecommitdiffstats
path: root/tests/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects')
-rw-r--r--tests/dialects/test_dialect.py2
-rw-r--r--tests/dialects/test_mysql.py2
-rw-r--r--tests/dialects/test_oracle.py14
-rw-r--r--tests/dialects/test_postgres.py1
-rw-r--r--tests/dialects/test_snowflake.py43
-rw-r--r--tests/dialects/test_tsql.py29
6 files changed, 88 insertions, 3 deletions
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")
@@ -206,6 +211,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={
"spark": "CAST(x AS DOUBLE)",
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-24 10:05:01 +0000