Repository: tobymao/sqlglot

Issue:
failed to parse mysql string literals
sqlglot treats both "'" and '\\' as string escapes in mysql dialect, and it failed to parse such sqls:

`select "'abc'";` 

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
```
import sqlglot
sqlglot.transpile('select "\'abc\'";', read='mysql')
```

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/robert/workspace/sqlglot/.venv/lib/python3.9/site-packages/sqlglot/__init__.py", line 125, in parse_one
    result = dialect.parse(sql, **opts)
  File "/Users/robert/workspace/sqlglot/.venv/lib/python3.9/site-packages/sqlglot/dialects/dialect.py", line 288, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
  File "/Users/robert/workspace/sqlglot/.venv/lib/python3.9/site-packages/sqlglot/dialects/dialect.py", line 302, in tokenize
    return self.tokenizer.tokenize(sql)
  File "/Users/robert/workspace/sqlglot/.venv/lib/python3.9/site-packages/sqlglot/tokens.py", line 827, in tokenize
    raise TokenError(f"Error tokenizing '{context}'") from e
sqlglot.errors.TokenError: Error tokenizing 'select "'abc'"'
```

while in mysql:

```
mysql> select "'abc'";
+-------+
| 'abc' |
+-------+
| 'abc' |
+-------+
1 row in set (0.00 sec)
```

**Official Documentation**
according to official doc, single quote seems much more a special syntax other than general string escape char:

- A ' inside a string quoted with ' may be written as ''.
- A " inside a string quoted with " may be written as "".

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html


Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 190a3769..46e3f193 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -119,7 +119,7 @@ class MySQL(Dialect):
         QUOTES = ["'", '"']
         COMMENTS = ["--", "#", ("/*", "*/")]
         IDENTIFIERS = ["`"]
-        STRING_ESCAPES = ["'", "\\"]
+        STRING_ESCAPES = ["'", '"', "\\"]
         BIT_STRINGS = [("b'", "'"), ("B'", "'"), ("0b", "")]
         HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", "")]
 
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 7a2409e6..ce255c34 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -1168,7 +1168,11 @@ class Tokenizer(metaclass=_Tokenizer):
         escapes = self._STRING_ESCAPES if escapes is None else escapes
 
         while True:
-            if self._char in escapes and (self._peek == delimiter or self._peek in escapes):
+            if (
+                self._char in escapes
+                and (self._peek == delimiter or self._peek in escapes)
+                and (self._char not in self._QUOTES or self._char == self._peek)
+            ):
                 if self._peek == delimiter:
                     text += self._peek
                 else:


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index c4cb4b75..a0fc16da 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -243,6 +243,15 @@ class TestMySQL(Validator):
         )
 
     def test_escape(self):
+        self.validate_identity("""'"abc"'""")
+        self.validate_identity(
+            r"'\'a'",
+            "'''a'",
+        )
+        self.validate_identity(
+            '''"'abc'"''',
+            "'''abc'''",
+        )
         self.validate_all(
             r"'a \' b '' '",
             write={