summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_dialect.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-30 17:08:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-30 17:08:33 +0000
commit75d158890b303b701c51f12b34c422fb823ba9aa (patch)
tree5f10a4a1eb612918ea94a934cfc9b9893ea19442 /tests/dialects/test_dialect.py
parentAdding upstream version 10.5.6. (diff)
downloadsqlglot-75d158890b303b701c51f12b34c422fb823ba9aa.tar.xz
sqlglot-75d158890b303b701c51f12b34c422fb823ba9aa.zip
Adding upstream version 10.5.10.upstream/10.5.10
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_dialect.py')
-rw-r--r--tests/dialects/test_dialect.py66
1 files changed, 41 insertions, 25 deletions
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index b2f4676..f1144ce 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -950,40 +950,40 @@ class TestDialect(Validator):
},
)
self.validate_all(
- "POSITION(' ' in x)",
+ "POSITION(needle in haystack)",
write={
- "drill": "STRPOS(x, ' ')",
- "duckdb": "STRPOS(x, ' ')",
- "postgres": "STRPOS(x, ' ')",
- "presto": "STRPOS(x, ' ')",
- "spark": "LOCATE(' ', x)",
- "clickhouse": "position(x, ' ')",
- "snowflake": "POSITION(' ', x)",
- "mysql": "LOCATE(' ', x)",
+ "drill": "STRPOS(haystack, needle)",
+ "duckdb": "STRPOS(haystack, needle)",
+ "postgres": "STRPOS(haystack, needle)",
+ "presto": "STRPOS(haystack, needle)",
+ "spark": "LOCATE(needle, haystack)",
+ "clickhouse": "position(haystack, needle)",
+ "snowflake": "POSITION(needle, haystack)",
+ "mysql": "LOCATE(needle, haystack)",
},
)
self.validate_all(
- "STR_POSITION(x, 'a')",
+ "STR_POSITION(haystack, needle)",
write={
- "drill": "STRPOS(x, 'a')",
- "duckdb": "STRPOS(x, 'a')",
- "postgres": "STRPOS(x, 'a')",
- "presto": "STRPOS(x, 'a')",
- "spark": "LOCATE('a', x)",
- "clickhouse": "position(x, 'a')",
- "snowflake": "POSITION('a', x)",
- "mysql": "LOCATE('a', x)",
+ "drill": "STRPOS(haystack, needle)",
+ "duckdb": "STRPOS(haystack, needle)",
+ "postgres": "STRPOS(haystack, needle)",
+ "presto": "STRPOS(haystack, needle)",
+ "spark": "LOCATE(needle, haystack)",
+ "clickhouse": "position(haystack, needle)",
+ "snowflake": "POSITION(needle, haystack)",
+ "mysql": "LOCATE(needle, haystack)",
},
)
self.validate_all(
- "POSITION('a', x, 3)",
+ "POSITION(needle, haystack, pos)",
write={
- "drill": "STRPOS(SUBSTR(x, 3), 'a') + 3 - 1",
- "presto": "STRPOS(x, 'a', 3)",
- "spark": "LOCATE('a', x, 3)",
- "clickhouse": "position(x, 'a', 3)",
- "snowflake": "POSITION('a', x, 3)",
- "mysql": "LOCATE('a', x, 3)",
+ "drill": "STRPOS(SUBSTR(haystack, pos), needle) + pos - 1",
+ "presto": "STRPOS(haystack, needle, pos)",
+ "spark": "LOCATE(needle, haystack, pos)",
+ "clickhouse": "position(haystack, needle, pos)",
+ "snowflake": "POSITION(needle, haystack, pos)",
+ "mysql": "LOCATE(needle, haystack, pos)",
},
)
self.validate_all(
@@ -1365,3 +1365,19 @@ SELECT
"spark": "MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE * WHEN NOT MATCHED THEN INSERT *",
},
)
+ self.validate_all(
+ """
+ MERGE a b USING c d ON b.id = d.id
+ WHEN MATCHED AND EXISTS (
+ SELECT b.name
+ EXCEPT
+ SELECT d.name
+ )
+ THEN UPDATE SET b.name = d.name
+ """,
+ write={
+ "bigquery": "MERGE INTO a AS b USING c AS d ON b.id = d.id WHEN MATCHED AND EXISTS(SELECT b.name EXCEPT DISTINCT SELECT d.name) THEN UPDATE SET b.name = d.name",
+ "snowflake": "MERGE INTO a AS b USING c AS d ON b.id = d.id WHEN MATCHED AND EXISTS(SELECT b.name EXCEPT SELECT d.name) THEN UPDATE SET b.name = d.name",
+ "spark": "MERGE INTO a AS b USING c AS d ON b.id = d.id WHEN MATCHED AND EXISTS(SELECT b.name EXCEPT SELECT d.name) THEN UPDATE SET b.name = d.name",
+ },
+ )