Repository: tobymao/sqlglot

Issue:
Windows line endings cause IndexOutOfRange during tokenization
I am parsing view and stored procedure definitions from windows-based mssql.

I am getting failures from IndexOutOfRange and strange parsing contexts where the leading character has been removed.

I think I have identified the problem + solution - are you open to a PR (with unit test)?

The issue is this:

In _advance:
https://github.com/tobymao/sqlglot/blob/a18444dbd7ccfc05b189dcb2005c85a1048cc8de/sqlglot/tokens.py#L1008

If it sees \r\n, then it sets i to 2
_current is increased by i
_char always looks at position _current-1

This can lead to it looking past the end of the source text, as _current-1 > size

I believe the fix is something like changing:
https://github.com/tobymao/sqlglot/blob/a18444dbd7ccfc05b189dcb2005c85a1048cc8de/sqlglot/tokens.py#L1022

to

```
        if self._end:
            self._peek = ""
            self._char = self.sql[self.size - 1]
        else:
            self._char = self.sql[self._current - 1]
            self._peek = self.sql[self._current]
```




Discussion:
mr-miles: It's totally immaterial but I noticed the column gets set to 1 but the line number doesnt change til the next iteration ... how about

```
            if not(self._char == "\r" and self._peek == "\n"):
               self._col = 1
               self._line += 1
```
georgesittas: > It's totally immaterial but I noticed the column gets set to 1 but the line number doesnt change til the next iteration ... how about
> 
> ```
>             if not(self._char == "\r" and self._peek == "\n"):
>                self._col = 1
>                self._line += 1
> ```
> 
> anyhow thanks for the swift attention!

Done! I liked this approach more, I think it simplifies the logic 👍 

Patch:
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index a41b6ea8..70066677 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -171,6 +171,8 @@ class Redshift(Postgres):
             ),
             exp.SortKeyProperty: lambda self,
             e: f"{'COMPOUND ' if e.args['compound'] else ''}SORTKEY({self.format_args(*e.this)})",
+            exp.StartsWith: lambda self,
+            e: f"{self.sql(e.this)} LIKE {self.sql(e.expression)} || '%'",
             exp.TableSample: no_tablesample_sql,
             exp.TsOrDsAdd: date_delta_sql("DATEADD"),
             exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 804df019..3186becf 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -46,9 +46,11 @@ class Generator(metaclass=_Generator):
             'safe': Only quote identifiers that are case insensitive.
         normalize: Whether to normalize identifiers to lowercase.
             Default: False.
-        pad: The pad size in a formatted string.
+        pad: The pad size in a formatted string. For example, this affects the indentation of
+            a projection in a query, relative to its nesting level.
             Default: 2.
-        indent: The indentation size in a formatted string.
+        indent: The indentation size in a formatted string. For example, this affects the
+            indentation of subqueries and filters under a `WHERE` clause.
             Default: 2.
         normalize_functions: How to normalize function names. Possible values are:
             "upper" or True (default): Convert names to uppercase.
@@ -3221,7 +3223,7 @@ class Generator(metaclass=_Generator):
         num_sqls = len(expressions)
 
         # These are calculated once in case we have the leading_comma / pretty option set, correspondingly
-        pad = " " * self.pad
+        pad = " " * len(sep)
         stripped_sep = sep.strip()
 
         result_sqls = []
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 824ebe74..1ba8b2ad 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -565,8 +565,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "~": TokenType.TILDA,
         "?": TokenType.PLACEHOLDER,
         "@": TokenType.PARAMETER,
-        # used for breaking a var like x'y' but nothing else
-        # the token type doesn't matter
+        # Used for breaking a var like x'y' but nothing else the token type doesn't matter
         "'": TokenType.QUOTE,
         "`": TokenType.IDENTIFIER,
         '"': TokenType.IDENTIFIER,
@@ -892,7 +891,7 @@ class Tokenizer(metaclass=_Tokenizer):
 
     COMMAND_PREFIX_TOKENS = {TokenType.SEMICOLON, TokenType.BEGIN}
 
-    # handle numeric literals like in hive (3L = BIGINT)
+    # Handle numeric literals like in hive (3L = BIGINT)
     NUMERIC_LITERALS: t.Dict[str, str] = {}
 
     COMMENTS = ["--", ("/*", "*/")]
@@ -965,8 +964,7 @@ class Tokenizer(metaclass=_Tokenizer):
         while self.size and not self._end:
             current = self._current
 
-            # skip spaces inline rather than iteratively call advance()
-            # for performance reasons
+            # Skip spaces here rather than iteratively calling advance() for performance reasons
             while current < self.size:
                 char = self.sql[current]
 
@@ -975,12 +973,10 @@ class Tokenizer(metaclass=_Tokenizer):
                 else:
                     break
 
-            n = current - self._current
-            self._start = current
-            self._advance(n if n > 1 else 1)
+            offset = current - self._current if current > self._current else 1
 
-            if self._char is None:
-                break
+            self._start = current
+            self._advance(offset)
 
             if not self._char.isspace():
                 if self._char.isdigit():
@@ -1008,12 +1004,9 @@ class Tokenizer(metaclass=_Tokenizer):
     def _advance(self, i: int = 1, alnum: bool = False) -> None:
         if self.WHITE_SPACE.get(self._char) is TokenType.BREAK:
             # Ensures we don't count an extra line if we get a \r\n line break sequence
-            if self._char == "\r" and self._peek == "\n":
-                i = 2
-                self._start += 1
-
-            self._col = 1
-            self._line += 1
+            if not (self._char == "\r" and self._peek == "\n"):
+                self._col = 1
+                self._line += 1
         else:
             self._col += i
 
diff --git a/sqlglotrs/src/tokenizer.rs b/sqlglotrs/src/tokenizer.rs
index 2c90a650..881417e5 100644
--- a/sqlglotrs/src/tokenizer.rs
+++ b/sqlglotrs/src/tokenizer.rs
@@ -118,8 +118,27 @@ impl<'a> TokenizerState<'a> {
 
     fn scan(&mut self, until_peek_char: Option<char>) -> Result<(), TokenizerError> {
         while self.size > 0 && !self.is_end {
-            self.start = self.current;
-            self.advance(1)?;
+            let mut current = self.current;
+
+            // Skip spaces here rather than iteratively calling advance() for performance reasons
+            while current < self.size {
+                let ch = self.char_at(current)?;
+
+                if ch == ' ' || ch == '\t' {
+                    current += 1;
+                } else {
+                    break;
+                }
+            }
+
+            let offset = if current > self.current {
+                current - self.current
+            } else {
+                1
+            };
+
+            self.start = current;
+            self.advance(offset as isize)?;
 
             if self.current_char == '\0' {
                 break;
@@ -153,16 +172,12 @@ impl<'a> TokenizerState<'a> {
     }
 
     fn advance(&mut self, i: isize) -> Result<(), TokenizerError> {
-        let mut i = i;
         if Some(&self.token_types.break_) == self.settings.white_space.get(&self.current_char) {
             // Ensures we don't count an extra line if we get a \r\n line break sequence.
-            if self.current_char == '\r' && self.peek_char == '\n' {
-                i = 2;
-                self.start += 1;
+            if ! (self.current_char == '\r' && self.peek_char == '\n') {
+                self.column = 1;
+                self.line += 1;
             }
-
-            self.column = 1;
-            self.line += 1;
         } else {
             self.column = self.column.wrapping_add_signed(i);
         }


Test patch:
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 896ee451..7affe31f 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -139,6 +139,15 @@ class TestRedshift(Validator):
                 "presto": "LENGTH(x)",
             },
         )
+        self.validate_all(
+            "x LIKE 'abc' || '%'",
+            read={
+                "duckdb": "STARTS_WITH(x, 'abc')",
+            },
+            write={
+                "redshift": "x LIKE 'abc' || '%'",
+            },
+        )
 
         self.validate_all(
             "SELECT SYSDATE",
diff --git a/tests/test_tokens.py b/tests/test_tokens.py
index 970c1ac2..29ef5b61 100644
--- a/tests/test_tokens.py
+++ b/tests/test_tokens.py
@@ -85,6 +85,18 @@ x"""
             ],
         )
 
+        for simple_query in ("SELECT 1\r\n", "\r\nSELECT 1"):
+            tokens = Tokenizer().tokenize(simple_query)
+            tokens = [(token.token_type, token.text) for token in tokens]
+
+            self.assertEqual(
+                tokens,
+                [
+                    (TokenType.SELECT, "SELECT"),
+                    (TokenType.NUMBER, "1"),
+                ],
+            )
+
     def test_command(self):
         tokens = Tokenizer().tokenize("SHOW;")
         self.assertEqual(tokens[0].token_type, TokenType.SHOW)
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 0170e230..f6fd2f9a 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -66,6 +66,24 @@ class TestTranspile(unittest.TestCase):
         )
 
     def test_leading_comma(self):
+        self.validate(
+            "SELECT a, b, c FROM (SELECT a, b, c FROM t)",
+            "SELECT\n"
+            "      a\n"
+            "    , b\n"
+            "    , c\n"
+            "FROM (\n"
+            "    SELECT\n"
+            "          a\n"
+            "        , b\n"
+            "        , c\n"
+            "    FROM t\n"
+            ")",
+            leading_comma=True,
+            pretty=True,
+            pad=4,
+            indent=4,
+        )
         self.validate(
             "SELECT FOO, BAR, BAZ",
             "SELECT\n    FOO\n  , BAR\n  , BAZ",