Adding upstream version 17.4.1.upstream/17.4.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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)",