Repository: tobymao/sqlglot

Issue:
split with `\\Q` is invalid on databricks
![image](https://github.com/user-attachments/assets/25f375b9-019a-4ba6-8f98-a0894540bf58)

Discussion:
@georgesittas: Hi Phillip 👋 
Can you provide some more context on this?@cpcloud: Sure!

Sorry, I was being a bit lazy 😅 

Here's the sqlglot that produced the query:

```
In [1]: import sqlglot as sg

In [2]: sg.parse_one("select split(a, '/')", read="duckdb").sql("databricks")
Out[2]: "SELECT SPLIT(a, CONCAT('\\\\Q', '/'))"

In [3]: sg.__version__
Out[3]: '25.22.0'
```@georgesittas: I see, this conversion is [pretty old](https://github.com/tobymao/sqlglot/commit/c2706ed4d9e64e156b9c4bfb1d29f715d5f3624), so most likely a bug. @VaggelisD could you take a look?@cpcloud: FWIW this is working as expected with Spark SQL running through PySpark, it's **just** Databricks that exhibits the problem.@VaggelisD: From a quick search I can't find any context for that transformation, still looking..

@cpcloud Small clarification regarding Spark, do you mean that `CONCAT` is working as expected because it's required for the split pattern or that it's just producing a proper result? Would the simple `SPLIT(a, '/')` work too?@cpcloud: > that it's just producing a proper result?

this

> Would the simple SPLIT(a, '/') work too?

Not sure, lemme try.@cpcloud: `SPLIT(a, '/')` seems to do the job on vanilla pyspark 🎉 @VaggelisD: I think this transformation is more nuanced than expected, and it might be needed in the end. From what I could piece together:

- In [Presto](https://prestodb.io/docs/current/functions/string.html#split), `SPLIT` is done on a delimiter / string literal
- In [Databricks](https://docs.databricks.com/en/sql/language-manual/functions/split.html), `SPLIT` is done on a regex

So to transpile from a literal-based split (`exp.Split`) to a regex-based split (`exp.RegexpSplit`) we must make sure that the string is not treated as a pattern. Afaict, one way to achieve this is by enclosing it in `\Q \E`, as is [showcased here](https://stackoverflow.com/questions/30776860/regular-expression-q-e).

I also tried a few queries on Databricks, this pattern didn't seem to bother execution:

```
SELECT SPLIT('ab/c', CONCAT('\\Q', '/'));

split(ab/c, concat(\Q, /), -1)
["ab","c"]
```

And 

```
SELECT SPLIT('ab/c', '\\Q/');

split(ab/c, \Q/, -1)
["ab","c"]
```

Do you have more context on why this breaks at your end?

Side note: If that's the case, I'm not sure why `\E` is not added by SQLGlot and not required (?) in Databricks, maybe it's optional or `\Q` on it's own is a no-op.






@cpcloud: Note the `\\\\Q` in the generated code, which turns into 

```
SELECT SPLIT('ab/c', CONCAT('\\Q', '/'));
```

You have to write it **exactly** like that to reproduce the problem.@cpcloud: Hm, I am confused now, because that is running for me now. Let me go back to the drawing board.@georgesittas: Yep, I can verify that this indeed works in databricks, so not sure if there's an issue here.@cpcloud: When I run this with a column and not a literal, then things start to fail.@georgesittas: Can't repro this using a CTE:

<img width="360" alt="Screenshot 2024-09-25 at 7 46 37 PM" src="https://github.com/user-attachments/assets/8471c01a-7117-47bd-a4f7-7ccf5a1f89c9">
@cpcloud: This fails for me

```sql
CREATE OR REPLACE TABLE x AS SELECT '11/10/01' as date_string_col;

SELECT SPLIT(date_string_col, CONCAT('\\Q', '/'))
FROM x;
```

![image](https://github.com/user-attachments/assets/f8e4c859-9ceb-425a-81e0-7babaf83840e)
@cpcloud: If I inline the value, it works. No clue what's going on.

![image](https://github.com/user-attachments/assets/c1f694cc-1548-4e3e-a7d3-74765430b4a2)
@georgesittas: Yep, this does look weird. I wonder if it's a databricks bug?@cpcloud: Slapping `'\\E'` on the end "fixes" it.

![image](https://github.com/user-attachments/assets/4afcfb81-208d-481e-b61f-c41cddeb4b5c)
@georgesittas: Yep, I can repro this after all:

```
-- CREATE OR REPLACE TABLE george.default.x AS SELECT '11/10/01' as date_string_col;

-- The given pattern is invalid for SPLIT.
SELECT SPLIT(date_string_col, CONCAT('\\Q', '/')) FROM george.default.x;
```@VaggelisD: I think adding `\\\\E` to the `CONCAT` transformation then makes sense, both to satisfy the regex specs and to fix this edge case

Solution patch:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index d59e3a84c2..9616dbb206 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -546,7 +546,7 @@ class Generator(generator.Generator):
             exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
             exp.ArrayUniqueAgg: rename_func("COLLECT_SET"),
             exp.Split: lambda self, e: self.func(
-                "SPLIT", e.this, self.func("CONCAT", "'\\\\Q'", e.expression)
+                "SPLIT", e.this, self.func("CONCAT", "'\\\\Q'", e.expression, "'\\\\E'")
             ),
             exp.Select: transforms.preprocess(
                 [


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 17808a339c..e7ddfd1cef 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -535,8 +535,8 @@ def test_duckdb(self):
             write={
                 "duckdb": "STR_SPLIT(x, 'a')",
                 "presto": "SPLIT(x, 'a')",
-                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
-                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
+                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
+                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
             },
         )
         self.validate_all(
@@ -544,8 +544,8 @@ def test_duckdb(self):
             write={
                 "duckdb": "STR_SPLIT(x, 'a')",
                 "presto": "SPLIT(x, 'a')",
-                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
-                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a'))",
+                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
+                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a', '\\\\E'))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 311a789373..3d5fbfe807 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -158,8 +158,8 @@ def test_regex(self):
             write={
                 "duckdb": "STR_SPLIT(x, 'a.')",
                 "presto": "SPLIT(x, 'a.')",
-                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a.'))",
-                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a.'))",
+                "hive": "SPLIT(x, CONCAT('\\\\Q', 'a.', '\\\\E'))",
+                "spark": "SPLIT(x, CONCAT('\\\\Q', 'a.', '\\\\E'))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py
index ee4dc90239..44c54a6ee9 100644
--- a/tests/dialects/test_starrocks.py
+++ b/tests/dialects/test_starrocks.py
@@ -85,8 +85,8 @@ def test_unnest(self):
             r"""SELECT id, t.type, t.scores FROM example_table, unnest(split(type, ";"), scores) AS t(type,scores)""",
             write={
                 "postgres": "SELECT id, t.type, t.scores FROM example_table, UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)",
-                "spark": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
-                "databricks": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
+                "spark": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
+                "databricks": r"""SELECT id, t.type, t.scores FROM example_table LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
                 "starrocks": r"""SELECT id, t.type, t.scores FROM example_table, UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)""",
                 "hive": UnsupportedError,
             },
@@ -95,7 +95,7 @@ def test_unnest(self):
         self.validate_all(
             r"""SELECT id, t.type, t.scores FROM example_table_2 CROSS JOIN LATERAL unnest(split(type, ";"), scores) AS t(type,scores)""",
             write={
-                "spark": r"""SELECT id, t.type, t.scores FROM example_table_2 LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';')), scores)) t AS type, scores""",
+                "spark": r"""SELECT id, t.type, t.scores FROM example_table_2 LATERAL VIEW INLINE(ARRAYS_ZIP(SPLIT(type, CONCAT('\\Q', ';', '\\E')), scores)) t AS type, scores""",
                 "starrocks": r"""SELECT id, t.type, t.scores FROM example_table_2 CROSS JOIN LATERAL UNNEST(SPLIT(type, ';'), scores) AS t(type, scores)""",
                 "hive": UnsupportedError,
             },