Repository: tobymao/sqlglot

Issue:
Show doesn't yield any metadata
```
from sqlglot import parse_one, exp

tables = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', read="snowflake", dialect="snowflake").find_all(exp.Table)
```


Expected output:

List of tables, in this case it should be `customers`

Instead receiving an empty result

Official documentation:
https://docs.snowflake.com/en/sql-reference/sql/show-primary-keys

Discussion:

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 27c1f62b..279ed314 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -133,7 +133,6 @@ class Oracle(Dialect):
             ),
             exp.Group: transforms.preprocess([transforms.unalias_group]),
             exp.ILike: no_ilike_sql,
-            exp.Coalesce: rename_func("NVL"),
             exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
             exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})",
             exp.Subquery: lambda self, e: self.subquery_sql(e, sep=" "),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 934e320a..8d8183c3 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -190,6 +190,13 @@ def _parse_regexp_replace(args: t.List) -> exp.RegexpReplace:
     return regexp_replace
 
 
+def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[Snowflake.Parser], exp.Show]:
+    def _parse(self: Snowflake.Parser) -> exp.Show:
+        return self._parse_show_snowflake(*args, **kwargs)
+
+    return _parse
+
+
 class Snowflake(Dialect):
     # https://docs.snowflake.com/en/sql-reference/identifiers-syntax
     RESOLVES_IDENTIFIERS_AS_UPPERCASE = True
@@ -289,6 +296,16 @@ class Snowflake(Dialect):
             ),
         }
 
+        STATEMENT_PARSERS = {
+            **parser.Parser.STATEMENT_PARSERS,
+            TokenType.SHOW: lambda self: self._parse_show(),
+        }
+
+        SHOW_PARSERS = {
+            "PRIMARY KEYS": _show_parser("PRIMARY KEYS"),
+            "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"),
+        }
+
         def _parse_id_var(
             self,
             any_token: bool = True,
@@ -304,6 +321,23 @@ class Snowflake(Dialect):
 
             return super()._parse_id_var(any_token=any_token, tokens=tokens)
 
+        def _parse_show_snowflake(self, this: str) -> exp.Show:
+            scope = None
+            scope_kind = None
+
+            if self._match(TokenType.IN):
+                if self._match_text_seq("ACCOUNT"):
+                    scope_kind = "ACCOUNT"
+                elif self._match_set(self.DB_CREATABLES):
+                    scope_kind = self._prev.text
+                    if self._curr:
+                        scope = self._parse_table()
+                elif self._curr:
+                    scope_kind = "TABLE"
+                    scope = self._parse_table()
+
+            return self.expression(exp.Show, this=this, scope=scope, scope_kind=scope_kind)
+
     class Tokenizer(tokens.Tokenizer):
         STRING_ESCAPES = ["\\", "'"]
         HEX_STRINGS = [("x'", "'"), ("X'", "'")]
@@ -338,6 +372,8 @@ class Snowflake(Dialect):
 
         VAR_SINGLE_TOKENS = {"$"}
 
+        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
+
     class Generator(generator.Generator):
         PARAMETER_TOKEN = "$"
         MATCHED_BY_SOURCE = False
@@ -413,6 +449,16 @@ class Snowflake(Dialect):
             exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
 
+        def show_sql(self, expression: exp.Show) -> str:
+            scope = self.sql(expression, "scope")
+            scope = f" {scope}" if scope else ""
+
+            scope_kind = self.sql(expression, "scope_kind")
+            if scope_kind:
+                scope_kind = f" IN {scope_kind}"
+
+            return f"SHOW {expression.name}{scope_kind}{scope}"
+
         def regexpextract_sql(self, expression: exp.RegexpExtract) -> str:
             # Other dialects don't support all of the following parameters, so we need to
             # generate default values as necessary to ensure the transpilation is correct
diff --git a/sqlglot/dialects/teradata.py b/sqlglot/dialects/teradata.py
index 2be1a625..163cc137 100644
--- a/sqlglot/dialects/teradata.py
+++ b/sqlglot/dialects/teradata.py
@@ -95,6 +95,9 @@ class Teradata(Dialect):
 
         STATEMENT_PARSERS = {
             **parser.Parser.STATEMENT_PARSERS,
+            TokenType.DATABASE: lambda self: self.expression(
+                exp.Use, this=self._parse_table(schema=False)
+            ),
             TokenType.REPLACE: lambda self: self._parse_create(),
         }
 
@@ -165,6 +168,7 @@ class Teradata(Dialect):
             exp.Select: transforms.preprocess([transforms.eliminate_distinct_on]),
             exp.StrToDate: lambda self, e: f"CAST({self.sql(e, 'this')} AS DATE FORMAT {self.format_time(e)})",
             exp.ToChar: lambda self, e: self.function_fallback_sql(e),
+            exp.Use: lambda self, e: f"DATABASE {self.sql(e, 'this')}",
         }
 
         def partitionedbyproperty_sql(self, expression: exp.PartitionedByProperty) -> str:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 7103b9c8..0479da09 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1071,6 +1071,8 @@ class Show(Expression):
         "like": False,
         "where": False,
         "db": False,
+        "scope": False,
+        "scope_kind": False,
         "full": False,
         "mutex": False,
         "query": False,
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 214998c7..f8690d5d 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3154,12 +3154,12 @@ class Parser(metaclass=_Parser):
 
             if len(parts) == 2:
                 if unit:
-                    # this is not actually a unit, it's something else
+                    # This is not actually a unit, it's something else (e.g. a "window side")
                     unit = None
                     self._retreat(self._index - 1)
-                else:
-                    this = exp.Literal.string(parts[0])
-                    unit = self.expression(exp.Var, this=parts[1])
+
+                this = exp.Literal.string(parts[0])
+                unit = self.expression(exp.Var, this=parts[1])
 
         return self.expression(exp.Interval, this=this, unit=unit)
 
@@ -4151,7 +4151,7 @@ class Parser(metaclass=_Parser):
         key = self._parse_column()
         self._match_set((TokenType.COLON, TokenType.COMMA))
         self._match_text_seq("VALUE")
-        value = self._parse_column()
+        value = self._parse_bitwise()
 
         if not key and not value:
             return None
@@ -4852,8 +4852,7 @@ class Parser(metaclass=_Parser):
         parser = self._find_parser(self.SHOW_PARSERS, self.SHOW_TRIE)
         if parser:
             return parser(self)
-        self._advance()
-        return self.expression(exp.Show, this=self._prev.text.upper())
+        return self._parse_as_command(self._prev)
 
     def _parse_set_item_assignment(
         self, kind: t.Optional[str] = None


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 7d9ab900..01a9ca3a 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -6,6 +6,8 @@ class TestOracle(Validator):
     dialect = "oracle"
 
     def test_oracle(self):
+        self.validate_identity("SELECT JSON_OBJECT('name': first_name || ' ' || last_name) FROM t")
+        self.validate_identity("COALESCE(c1, c2, c3)")
         self.validate_identity("SELECT * FROM TABLE(foo)")
         self.validate_identity("SELECT a$x#b")
         self.validate_identity("SELECT :OBJECT")
@@ -43,7 +45,7 @@ class TestOracle(Validator):
             "NVL(NULL, 1)",
             write={
                 "": "COALESCE(NULL, 1)",
-                "oracle": "NVL(NULL, 1)",
+                "oracle": "COALESCE(NULL, 1)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 05da654d..30a1f037 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1034,3 +1034,33 @@ MATCH_RECOGNIZE (
 )""",
                     pretty=True,
                 )
+
+    def test_show(self):
+        # Parsed as Command
+        self.validate_identity("SHOW COLUMNS IN TABLE dt_test")
+        self.validate_identity("SHOW TABLES LIKE 'line%' IN tpch.public")
+
+        ast = parse_one("SHOW TABLES HISTORY IN tpch.public")
+        self.assertIsInstance(ast, exp.Command)
+
+        # Parsed as Show
+        self.validate_identity("SHOW PRIMARY KEYS")
+        self.validate_identity("SHOW PRIMARY KEYS IN ACCOUNT")
+        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE")
+        self.validate_identity("SHOW PRIMARY KEYS IN DATABASE foo")
+        self.validate_identity("SHOW PRIMARY KEYS IN TABLE")
+        self.validate_identity("SHOW PRIMARY KEYS IN TABLE foo")
+        self.validate_identity(
+            'SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"',
+            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"',
+        )
+        self.validate_identity(
+            'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."customers"',
+            'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"',
+        )
+
+        ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', read="snowflake")
+        table = ast.find(exp.Table)
+
+        self.assertIsNotNone(table)
+        self.assertEqual(table.sql(dialect="snowflake"), '"TEST"."PUBLIC"."customers"')
diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py
index 4d322419..32bdc719 100644
--- a/tests/dialects/test_teradata.py
+++ b/tests/dialects/test_teradata.py
@@ -4,6 +4,18 @@ from tests.dialects.test_dialect import Validator
 class TestTeradata(Validator):
     dialect = "teradata"
 
+    def test_teradata(self):
+        self.validate_all(
+            "DATABASE tduser",
+            read={
+                "databricks": "USE tduser",
+            },
+            write={
+                "databricks": "USE tduser",
+                "teradata": "DATABASE tduser",
+            },
+        )
+
     def test_translate(self):
         self.validate_all(
             "TRANSLATE(x USING LATIN_TO_UNICODE)",
diff --git a/tests/test_parser.py b/tests/test_parser.py
index a853a757..7135dd89 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -711,3 +711,11 @@ class TestParser(unittest.TestCase):
             parse_one("SELECT a, b ?? c ?? 'No Data' FROM z").sql(),
             "SELECT a, COALESCE(COALESCE(b, c), 'No Data') FROM z",
         )
+
+    def test_parse_intervals(self):
+        ast = parse_one(
+            "SELECT a FROM tbl WHERE a <= DATE '1998-12-01' - INTERVAL '71 days' GROUP BY b"
+        )
+
+        self.assertEqual(ast.find(exp.Interval).this.sql(), "'71'")
+        self.assertEqual(ast.find(exp.Interval).unit.assert_is(exp.Var).sql(), "days")