Repository: tobymao/sqlglot

Issue:
Duckdb - Error tokenizing multiple named parameters 
**Before you file an issue**
- [x] Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- [x] Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- [x] Check if the issue still exists on main

**Fully reproducible code snippet**
I'm writing a transformer that remap duckdb named parameters into a valid type based on the user's inputs.

```python
def resolve_user_params(query: str, params: dict[str, Any], dialect='duckdb') -> str:
    errors: set[str] = set()
    
    def _transform(node: sqlglot.expressions.Expression) -> sqlglot.expressions.Expression:
        if isinstance(node, sqlglot.expressions.Placeholder):
            key = node.name
            if key not in params:
                errors.add(f'Variable ${key} is an unknown user_param')
                return node
            value = params[key]
            match value:
                case bool():
                    return sqlglot.expressions.Boolean(this=value)
                case int():
                    return sqlglot.expressions.Literal(this=value, is_string=False)
                case float():
                    return sqlglot.expressions.Literal(this=value, is_string=False)
                case str():
                    return sqlglot.expressions.Literal(this=value, is_string=True)
                case _:
                    errors.add(f'Variable ${key} is not a valid type, got {type(value)}')
            return node
        return node

    
    parsed_query = sqlglot.parse_one(query, read=dialect)
    # print(repr(parsed_query))
    
    transformed_query = parsed_query.transform(_transform)
    final_query = transformed_query.sql(dialect=dialect, comments=False)

    print('query', query)
    print(' - params', params)
    print(' - errors', errors)
    print(' - final_query', final_query)

    return final_query
```

The function works with a basic query like:

```python
resolve_user_params(
    query="SELECT * FROM taxi_zones WHERE LocationID > $location_id_min AND LocationID IS NOT NULL",
    params={
        "location_id_min": 124,
    },
)

# query SELECT * FROM taxi_zones WHERE LocationID > $location_id_min AND LocationID IS NOT NULL
# - params {'location_id_min': 124}
# - errors set()
# - final_query SELECT * FROM taxi_zones WHERE LocationID > 124 AND NOT LocationID IS NULL
```

but it fails when I pass multiple named parameters:

```python
resolve_user_params(
    query="SELECT * FROM taxi_zones WHERE LocationID > $location_id_min AND Borough = $borough",
    params={
        "location_id_min": 124,
        "borough": 12,
    },
)

Traceback (most recent call last):
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 970, in tokenize
    self._scan()
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 1003, in _scan
    self._scan_keywords()
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 1134, in _scan_keywords
    if self._scan_string(word):
       ^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 1306, in _scan_string
    text = self._extract_string(end, unescape_sequences=token_type != TokenType.RAW_STRING)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 1385, in _extract_string
    raise TokenError(f"Missing {delimiter} from {self._line}:{self._start}")
sqlglot.errors.TokenError: Missing $location_id_min AND Borough = $ from 1:44

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File ".venv/lib/python3.12/site-packages/marimo/_runtime/runner/cell_runner.py", line 302, in run
    return_value = execute_cell(cell, self.glbls)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/marimo/_ast/cell.py", line 460, in execute_cell
    return eval(cell.last_expr, glbls)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/folders/pg/0dm9b2p54yn_wqd89jd7j_wm0000gn/T/marimo_4911/__marimo__cell_lEQa__output.py", line 31, in <module>
  File "/var/folders/pg/0dm9b2p54yn_wqd89jd7j_wm0000gn/T/marimo_4911/__marimo__cell_bkHC_.py", line 26, in resolve_user_params
    parsed_query = sqlglot.parse_one(query, read='duckdb')
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/__init__.py", line 136, in parse_one
    result = dialect.parse(sql, **opts)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/dialects/dialect.py", line 518, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
                                     ^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/dialects/dialect.py", line 535, in tokenize
    return self.tokenizer.tokenize(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.12/site-packages/sqlglot/tokens.py", line 975, in tokenize
    raise TokenError(f"Error tokenizing '{context}'") from e
sqlglot.errors.TokenError: Error tokenizing 'cationID > $location_id_min AND Borough = $boroug'
```

**Official Documentation**
- Python SDK: https://duckdb.org/docs/api/python/dbapi#named-parameters
- Query Syntax: https://duckdb.org/docs/sql/query_syntax/prepared_statements.html#named-parameters-parameter


Discussion:

Patch:
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 3b6fc428..1282e150 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -1273,18 +1273,6 @@ class Tokenizer(metaclass=_Tokenizer):
             elif token_type == TokenType.BIT_STRING:
                 base = 2
             elif token_type == TokenType.HEREDOC_STRING:
-                if (
-                    self.HEREDOC_TAG_IS_IDENTIFIER
-                    and not self._peek.isidentifier()
-                    and not self._peek == end
-                ):
-                    if self.HEREDOC_STRING_ALTERNATIVE != token_type.VAR:
-                        self._add(self.HEREDOC_STRING_ALTERNATIVE)
-                    else:
-                        self._scan_var()
-
-                    return True
-
                 self._advance()
 
                 if self._char == end:
@@ -1296,7 +1284,10 @@ class Tokenizer(metaclass=_Tokenizer):
                         raise_unmatched=not self.HEREDOC_TAG_IS_IDENTIFIER,
                     )
 
-                if self._end and tag and self.HEREDOC_TAG_IS_IDENTIFIER:
+                if tag and self.HEREDOC_TAG_IS_IDENTIFIER and (self._end or not tag.isidentifier()):
+                    if not self._end:
+                        self._advance(-1)
+
                     self._advance(-len(tag))
                     self._add(self.HEREDOC_STRING_ALTERNATIVE)
                     return True
diff --git a/sqlglotrs/src/tokenizer.rs b/sqlglotrs/src/tokenizer.rs
index e79d0e90..45bbe778 100644
--- a/sqlglotrs/src/tokenizer.rs
+++ b/sqlglotrs/src/tokenizer.rs
@@ -405,19 +405,6 @@ impl<'a> TokenizerState<'a> {
             } else if *token_type == self.token_types.bit_string {
                 (Some(2), *token_type, end.clone())
             } else if *token_type == self.token_types.heredoc_string {
-                if self.settings.heredoc_tag_is_identifier
-                    && !self.is_identifier(self.peek_char)
-                    && self.peek_char.to_string() != *end
-                {
-                    if self.token_types.heredoc_string_alternative != self.token_types.var {
-                        self.add(self.token_types.heredoc_string_alternative, None)?
-                    } else {
-                        self.scan_var()?
-                    };
-
-                    return Ok(true)
-                };
-
                 self.advance(1)?;
 
                 let tag = if self.current_char.to_string() == *end {
@@ -426,7 +413,14 @@ impl<'a> TokenizerState<'a> {
                     self.extract_string(end, false, false, !self.settings.heredoc_tag_is_identifier)?
                 };
 
-                if self.is_end && !tag.is_empty() && self.settings.heredoc_tag_is_identifier {
+                if !tag.is_empty()
+                    && self.settings.heredoc_tag_is_identifier
+                    && (self.is_end || !self.is_identifier(&tag))
+                {
+                    if !self.is_end {
+                        self.advance(-1)?;
+                    }
+
                     self.advance(-(tag.len() as isize))?;
                     self.add(self.token_types.heredoc_string_alternative, None)?;
                     return Ok(true)
@@ -494,7 +488,7 @@ impl<'a> TokenizerState<'a> {
             } else if self.peek_char.to_ascii_uppercase() == 'E' && scientific == 0 {
                 scientific += 1;
                 self.advance(1)?;
-            } else if self.is_identifier(self.peek_char) {
+            } else if self.is_alphabetic_or_underscore(self.peek_char) {
                 let number_text = self.text();
                 let mut literal = String::from("");
 
@@ -676,10 +670,18 @@ impl<'a> TokenizerState<'a> {
         Ok(text)
     }
 
-    fn is_identifier(&mut self, name: char) -> bool {
+    fn is_alphabetic_or_underscore(&mut self, name: char) -> bool {
         name.is_alphabetic() || name == '_'
     }
 
+    fn is_identifier(&mut self, s: &str) -> bool {
+        s.chars().enumerate().all(
+            |(i, c)|
+            if i == 0 { self.is_alphabetic_or_underscore(c) }
+            else { self.is_alphabetic_or_underscore(c) || c.is_digit(10) }
+        )
+    }
+
     fn extract_value(&mut self) -> Result<String, TokenizerError> {
         loop {
             if !self.peek_char.is_whitespace()


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 7bc493ac..08a65091 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -285,6 +285,7 @@ class TestDuckDB(Validator):
         self.validate_identity("FROM tbl", "SELECT * FROM tbl")
         self.validate_identity("x -> '$.family'")
         self.validate_identity("CREATE TABLE color (name ENUM('RED', 'GREEN', 'BLUE'))")
+        self.validate_identity("SELECT * FROM foo WHERE bar > $baz AND bla = $bob")
         self.validate_identity(
             "SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
         )