Repository: tobymao/sqlglot

Issue:
[Postgres/Redshift] - Treat optimizer hints as comments in Postgres/Redshift
**Issue**:
Postgres doesn't have any support for Optimizer hints. All seemingly-optimizer-hints should be treated as comments.
Currently if a comment starts with a `+`, that is assumed as a hint placed out of position and an error is thrown.

```python
from sqlglot import parse_one
sample_pseudo_hint_sql = "/*+ some comment*/ select  b.foo, b.bar from baz b"
parse_one(sample_pseudo_hint_sql, read="postgres") #Throws error
```
Expected:
Should parse `/*+ some comment*/` as a comment on the statement.

Actual:
`sqlglot.errors.ParseError: Invalid expression / Unexpected token.`

**Official Documentation**
This is the best I could find: https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion which describes postgres's stance on optimizer hints.

[Mysql](https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html)
[Doris](https://doris.apache.org/docs/query/join-optimization/join-hint)
[StarRocks](https://docs.starrocks.io/docs/administration/Query_planning/#query-hint)
[Oracle](https://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm)
[spark sql](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html)
[databricks sql](https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-hints.html)
[Tsql](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16)

are the databases that I could identify to have support for Optimizer hints.


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 3fd17e4e..4f557e7b 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -317,6 +317,7 @@ class BigQuery(Dialect):
         }
         KEYWORDS.pop("DIV")
         KEYWORDS.pop("VALUES")
+        KEYWORDS.pop("/*+")
 
     class Parser(parser.Parser):
         PREFIXED_PIVOT_COLUMNS = True
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index df1b9dba..209a2894 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -147,6 +147,7 @@ class ClickHouse(Dialect):
             "SYSTEM": TokenType.COMMAND,
             "PREWHERE": TokenType.PREWHERE,
         }
+        KEYWORDS.pop("/*+")
 
         SINGLE_TOKENS = {
             **tokens.Tokenizer.SINGLE_TOKENS,
diff --git a/sqlglot/dialects/drill.py b/sqlglot/dialects/drill.py
index 06f49d5e..9d90a84f 100644
--- a/sqlglot/dialects/drill.py
+++ b/sqlglot/dialects/drill.py
@@ -70,6 +70,9 @@ class Drill(Dialect):
         IDENTIFIERS = ["`"]
         STRING_ESCAPES = ["\\"]
 
+        KEYWORDS = tokens.Tokenizer.KEYWORDS.copy()
+        KEYWORDS.pop("/*+")
+
     class Parser(parser.Parser):
         STRICT_CAST = False
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 4db30b14..296405d3 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -220,6 +220,7 @@ class DuckDB(Dialect):
             "TIMESTAMP_US": TokenType.TIMESTAMP,
             "VARCHAR": TokenType.TEXT,
         }
+        KEYWORDS.pop("/*+")
 
         SINGLE_TOKENS = {
             **tokens.Tokenizer.SINGLE_TOKENS,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 526cb880..a4baf92c 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -331,6 +331,7 @@ class Postgres(Dialect):
             "REGTYPE": TokenType.OBJECT_IDENTIFIER,
             "FLOAT": TokenType.DOUBLE,
         }
+        KEYWORDS.pop("/*+")
         KEYWORDS.pop("DIV")
 
         SINGLE_TOKENS = {
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index e4c1edf7..fd4750f6 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -227,7 +227,7 @@ class Presto(Dialect):
             "TDIGEST": TokenType.TDIGEST,
             "HYPERLOGLOG": TokenType.HLLSKETCH,
         }
-
+        KEYWORDS.pop("/*+")
         KEYWORDS.pop("QUALIFY")
 
     class Parser(parser.Parser):
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index d8b03055..8bd1f773 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -685,6 +685,7 @@ class Snowflake(Dialect):
             "WAREHOUSE": TokenType.WAREHOUSE,
             "STREAMLIT": TokenType.STREAMLIT,
         }
+        KEYWORDS.pop("/*+")
 
         SINGLE_TOKENS = {
             **tokens.Tokenizer.SINGLE_TOKENS,
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index f9e510cb..5b1ed95c 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -106,6 +106,9 @@ class SQLite(Dialect):
         IDENTIFIERS = ['"', ("[", "]"), "`"]
         HEX_STRINGS = [("x'", "'"), ("X'", "'"), ("0x", ""), ("0X", "")]
 
+        KEYWORDS = tokens.Tokenizer.KEYWORDS.copy()
+        KEYWORDS.pop("/*+")
+
     class Parser(parser.Parser):
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
diff --git a/sqlglot/dialects/teradata.py b/sqlglot/dialects/teradata.py
index 3aee3cd0..1203e25f 100644
--- a/sqlglot/dialects/teradata.py
+++ b/sqlglot/dialects/teradata.py
@@ -96,6 +96,7 @@ class Teradata(Dialect):
             "TOP": TokenType.TOP,
             "UPD": TokenType.UPDATE,
         }
+        KEYWORDS.pop("/*+")
 
         # Teradata does not support % as a modulo operator
         SINGLE_TOKENS = {**tokens.Tokenizer.SINGLE_TOKENS}
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index f81e7172..74fef24c 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -477,6 +477,7 @@ class TSQL(Dialect):
             "UPDATE STATISTICS": TokenType.COMMAND,
             "XML": TokenType.XML,
         }
+        KEYWORDS.pop("/*+")
 
         COMMANDS = {*tokens.Tokenizer.COMMANDS, TokenType.END}
 


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index d9d56754..816a283e 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -75,10 +75,6 @@ class TestPostgres(Validator):
         self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True)
         self.validate_identity("SELECT CURRENT_USER")
         self.validate_identity("SELECT * FROM ONLY t1")
-        self.validate_identity(
-            "SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]",
-            "SELECT ARRAY[1, 2] @> ARRAY[1, 2, 3]",
-        )
         self.validate_identity(
             """UPDATE "x" SET "y" = CAST('0 days 60.000000 seconds' AS INTERVAL) WHERE "x"."id" IN (2, 3)"""
         )
@@ -135,6 +131,14 @@ class TestPostgres(Validator):
             "pg_catalog.PG_TABLE_IS_VISIBLE(c.oid) "
             "ORDER BY 2, 3"
         )
+        self.validate_identity(
+            "/*+ some comment*/ SELECT b.foo, b.bar FROM baz AS b",
+            "/* + some comment */ SELECT b.foo, b.bar FROM baz AS b",
+        )
+        self.validate_identity(
+            "SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]",
+            "SELECT ARRAY[1, 2] @> ARRAY[1, 2, 3]",
+        )
         self.validate_identity(
             "SELECT ARRAY[]::INT[] AS foo",
             "SELECT CAST(ARRAY[] AS INT[]) AS foo",