diff options
Diffstat (limited to 'tests/dialects/test_dialect.py')
| -rw-r--r-- | tests/dialects/test_dialect.py | 66 |
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", + }, + ) |