summaryrefslogtreecommitdiffstats
path: root/sqlglot/dialects
diff options
context:
space:
mode:
Diffstat (limited to 'sqlglot/dialects')
-rw-r--r--sqlglot/dialects/bigquery.py20
-rw-r--r--sqlglot/dialects/clickhouse.py2
-rw-r--r--sqlglot/dialects/databricks.py2
-rw-r--r--sqlglot/dialects/dialect.py5
-rw-r--r--sqlglot/dialects/drill.py5
-rw-r--r--sqlglot/dialects/duckdb.py3
-rw-r--r--sqlglot/dialects/hive.py26
-rw-r--r--sqlglot/dialects/mysql.py16
-rw-r--r--sqlglot/dialects/oracle.py51
-rw-r--r--sqlglot/dialects/postgres.py2
-rw-r--r--sqlglot/dialects/snowflake.py11
-rw-r--r--sqlglot/dialects/sqlite.py5
-rw-r--r--sqlglot/dialects/teradata.py3
-rw-r--r--sqlglot/dialects/tsql.py11
14 files changed, 106 insertions, 56 deletions
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 6a43846..a3f9e6d 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -10,6 +10,7 @@ from sqlglot.dialects.dialect import (
Dialect,
datestrtodate_sql,
inline_array_sql,
+ max_or_greatest,
min_or_least,
no_ilike_sql,
rename_func,
@@ -212,6 +213,9 @@ class BigQuery(Dialect):
),
}
+ LOG_BASE_FIRST = False
+ LOG_DEFAULTS_TO_LN = True
+
class Generator(generator.Generator):
TRANSFORMS = {
**generator.Generator.TRANSFORMS, # type: ignore
@@ -227,6 +231,7 @@ class BigQuery(Dialect):
exp.GroupConcat: rename_func("STRING_AGG"),
exp.ILike: no_ilike_sql,
exp.IntDiv: rename_func("DIV"),
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
exp.Select: transforms.preprocess(
[_unqualify_unnest], transforms.delegate("select_sql")
@@ -253,17 +258,19 @@ class BigQuery(Dialect):
TYPE_MAPPING = {
**generator.Generator.TYPE_MAPPING, # type: ignore
- exp.DataType.Type.TINYINT: "INT64",
- exp.DataType.Type.SMALLINT: "INT64",
- exp.DataType.Type.INT: "INT64",
exp.DataType.Type.BIGINT: "INT64",
+ exp.DataType.Type.BOOLEAN: "BOOL",
+ exp.DataType.Type.CHAR: "STRING",
exp.DataType.Type.DECIMAL: "NUMERIC",
- exp.DataType.Type.FLOAT: "FLOAT64",
exp.DataType.Type.DOUBLE: "FLOAT64",
- exp.DataType.Type.BOOLEAN: "BOOL",
+ exp.DataType.Type.FLOAT: "FLOAT64",
+ exp.DataType.Type.INT: "INT64",
+ exp.DataType.Type.NCHAR: "STRING",
+ exp.DataType.Type.NVARCHAR: "STRING",
+ exp.DataType.Type.SMALLINT: "INT64",
exp.DataType.Type.TEXT: "STRING",
+ exp.DataType.Type.TINYINT: "INT64",
exp.DataType.Type.VARCHAR: "STRING",
- exp.DataType.Type.NVARCHAR: "STRING",
}
PROPERTIES_LOCATION = {
**generator.Generator.PROPERTIES_LOCATION, # type: ignore
@@ -271,6 +278,7 @@ class BigQuery(Dialect):
}
EXPLICIT_UNION = True
+ LIMIT_FETCH = "LIMIT"
def array_sql(self, expression: exp.Array) -> str:
first_arg = seq_get(expression.expressions, 0)
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index b54a77d..89e2296 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -68,6 +68,8 @@ class ClickHouse(Dialect):
TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {TokenType.ANY} # type: ignore
+ LOG_DEFAULTS_TO_LN = True
+
def _parse_in(
self, this: t.Optional[exp.Expression], is_global: bool = False
) -> exp.Expression:
diff --git a/sqlglot/dialects/databricks.py b/sqlglot/dialects/databricks.py
index 4268f1b..2f93ee7 100644
--- a/sqlglot/dialects/databricks.py
+++ b/sqlglot/dialects/databricks.py
@@ -16,6 +16,8 @@ class Databricks(Spark):
"DATEDIFF": parse_date_delta(exp.DateDiff),
}
+ LOG_DEFAULTS_TO_LN = True
+
class Generator(Spark.Generator):
TRANSFORMS = {
**Spark.Generator.TRANSFORMS, # type: ignore
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index b267521..839589d 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -430,6 +430,11 @@ def min_or_least(self: Generator, expression: exp.Min) -> str:
return rename_func(name)(self, expression)
+def max_or_greatest(self: Generator, expression: exp.Max) -> str:
+ name = "GREATEST" if expression.expressions else "MAX"
+ return rename_func(name)(self, expression)
+
+
def count_if_to_sum(self: Generator, expression: exp.CountIf) -> str:
cond = expression.this
diff --git a/sqlglot/dialects/drill.py b/sqlglot/dialects/drill.py
index dc0e519..a33aadc 100644
--- a/sqlglot/dialects/drill.py
+++ b/sqlglot/dialects/drill.py
@@ -1,6 +1,5 @@
from __future__ import annotations
-import re
import typing as t
from sqlglot import exp, generator, parser, tokens
@@ -102,6 +101,8 @@ class Drill(Dialect):
"TO_CHAR": format_time_lambda(exp.TimeToStr, "drill"),
}
+ LOG_DEFAULTS_TO_LN = True
+
class Generator(generator.Generator):
TYPE_MAPPING = {
**generator.Generator.TYPE_MAPPING, # type: ignore
@@ -154,4 +155,4 @@ class Drill(Dialect):
}
def normalize_func(self, name: str) -> str:
- return name if re.match(exp.SAFE_IDENTIFIER_RE, name) else f"`{name}`"
+ return name if exp.SAFE_IDENTIFIER_RE.match(name) else f"`{name}`"
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index f1d2266..c034208 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -80,6 +80,7 @@ class DuckDB(Dialect):
class Tokenizer(tokens.Tokenizer):
KEYWORDS = {
**tokens.Tokenizer.KEYWORDS,
+ "~": TokenType.RLIKE,
":=": TokenType.EQ,
"ATTACH": TokenType.COMMAND,
"BINARY": TokenType.VARBINARY,
@@ -212,5 +213,7 @@ class DuckDB(Dialect):
"except": "EXCLUDE",
}
+ LIMIT_FETCH = "LIMIT"
+
def tablesample_sql(self, expression: exp.TableSample, seed_prefix: str = "SEED") -> str:
return super().tablesample_sql(expression, seed_prefix="REPEATABLE")
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 0110eee..68137ae 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -10,6 +10,7 @@ from sqlglot.dialects.dialect import (
format_time_lambda,
if_sql,
locate_to_strposition,
+ max_or_greatest,
min_or_least,
no_ilike_sql,
no_recursive_cte_sql,
@@ -34,6 +35,13 @@ DATE_DELTA_INTERVAL = {
"DAY": ("DATE_ADD", 1),
}
+TIME_DIFF_FACTOR = {
+ "MILLISECOND": " * 1000",
+ "SECOND": "",
+ "MINUTE": " / 60",
+ "HOUR": " / 3600",
+}
+
DIFF_MONTH_SWITCH = ("YEAR", "QUARTER", "MONTH")
@@ -51,6 +59,14 @@ def _add_date_sql(self: generator.Generator, expression: exp.DateAdd) -> str:
def _date_diff_sql(self: generator.Generator, expression: exp.DateDiff) -> str:
unit = expression.text("unit").upper()
+
+ factor = TIME_DIFF_FACTOR.get(unit)
+ if factor is not None:
+ left = self.sql(expression, "this")
+ right = self.sql(expression, "expression")
+ sec_diff = f"UNIX_TIMESTAMP({left}) - UNIX_TIMESTAMP({right})"
+ return f"({sec_diff}){factor}" if factor else sec_diff
+
sql_func = "MONTHS_BETWEEN" if unit in DIFF_MONTH_SWITCH else "DATEDIFF"
_, multiplier = DATE_DELTA_INTERVAL.get(unit, ("", 1))
multiplier_sql = f" / {multiplier}" if multiplier > 1 else ""
@@ -237,11 +253,6 @@ class Hive(Dialect):
"FROM_UNIXTIME": format_time_lambda(exp.UnixToStr, "hive", True),
"GET_JSON_OBJECT": exp.JSONExtractScalar.from_arg_list,
"LOCATE": locate_to_strposition,
- "LOG": (
- lambda args: exp.Log.from_arg_list(args)
- if len(args) > 1
- else exp.Ln.from_arg_list(args)
- ),
"MAP": parse_var_map,
"MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate.from_arg_list(args)),
"PERCENTILE": exp.Quantile.from_arg_list,
@@ -261,6 +272,8 @@ class Hive(Dialect):
),
}
+ LOG_DEFAULTS_TO_LN = True
+
class Generator(generator.Generator):
TYPE_MAPPING = {
**generator.Generator.TYPE_MAPPING, # type: ignore
@@ -293,6 +306,7 @@ class Hive(Dialect):
exp.JSONExtract: rename_func("GET_JSON_OBJECT"),
exp.JSONExtractScalar: rename_func("GET_JSON_OBJECT"),
exp.Map: var_map_sql,
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
exp.VarMap: var_map_sql,
exp.Create: create_with_partitions_sql,
@@ -338,6 +352,8 @@ class Hive(Dialect):
exp.TableFormatProperty: exp.Properties.Location.POST_SCHEMA,
}
+ LIMIT_FETCH = "LIMIT"
+
def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
return self.func(
"COLLECT_LIST",
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 1e2cfa3..5dfa811 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -3,7 +3,9 @@ from __future__ import annotations
from sqlglot import exp, generator, parser, tokens
from sqlglot.dialects.dialect import (
Dialect,
+ arrow_json_extract_scalar_sql,
locate_to_strposition,
+ max_or_greatest,
min_or_least,
no_ilike_sql,
no_paren_current_date_sql,
@@ -288,6 +290,8 @@ class MySQL(Dialect):
"SWAPS",
}
+ LOG_DEFAULTS_TO_LN = True
+
def _parse_show_mysql(self, this, target=False, full=None, global_=None):
if target:
if isinstance(target, str):
@@ -303,7 +307,13 @@ class MySQL(Dialect):
db = None
else:
position = None
- db = self._parse_id_var() if self._match_text_seq("FROM") else None
+ db = None
+
+ if self._match(TokenType.FROM):
+ db = self._parse_id_var()
+ elif self._match(TokenType.DOT):
+ db = target_id
+ target_id = self._parse_id_var()
channel = self._parse_id_var() if self._match_text_seq("FOR", "CHANNEL") else None
@@ -384,6 +394,8 @@ class MySQL(Dialect):
exp.CurrentDate: no_paren_current_date_sql,
exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
exp.ILike: no_ilike_sql,
+ exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
exp.TableSample: no_tablesample_sql,
exp.TryCast: no_trycast_sql,
@@ -415,6 +427,8 @@ class MySQL(Dialect):
exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
}
+ LIMIT_FETCH = "LIMIT"
+
def show_sql(self, expression):
this = f" {expression.name}"
full = " FULL" if expression.args.get("full") else ""
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 7028a04..fad6c4a 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -4,7 +4,7 @@ import typing as t
from sqlglot import exp, generator, parser, tokens, transforms
from sqlglot.dialects.dialect import Dialect, no_ilike_sql, rename_func, trim_sql
-from sqlglot.helper import csv, seq_get
+from sqlglot.helper import seq_get
from sqlglot.tokens import TokenType
PASSING_TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
@@ -13,10 +13,6 @@ PASSING_TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
}
-def _limit_sql(self, expression):
- return self.fetch_sql(exp.Fetch(direction="FIRST", count=expression.expression))
-
-
def _parse_xml_table(self) -> exp.XMLTable:
this = self._parse_string()
@@ -89,6 +85,20 @@ class Oracle(Dialect):
column.set("join_mark", self._match(TokenType.JOIN_MARKER))
return column
+ def _parse_hint(self) -> t.Optional[exp.Expression]:
+ if self._match(TokenType.HINT):
+ start = self._curr
+ while self._curr and not self._match_pair(TokenType.STAR, TokenType.SLASH):
+ self._advance()
+
+ if not self._curr:
+ self.raise_error("Expected */ after HINT")
+
+ end = self._tokens[self._index - 3]
+ return exp.Hint(expressions=[self._find_sql(start, end)])
+
+ return None
+
class Generator(generator.Generator):
LOCKING_READS_SUPPORTED = True
@@ -110,41 +120,20 @@ class Oracle(Dialect):
TRANSFORMS = {
**generator.Generator.TRANSFORMS, # type: ignore
**transforms.UNALIAS_GROUP, # type: ignore
+ exp.Hint: lambda self, e: f" /*+ {self.expressions(e).strip()} */",
exp.ILike: no_ilike_sql,
- exp.Limit: _limit_sql,
- exp.Trim: trim_sql,
exp.Matches: rename_func("DECODE"),
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=" "),
+ exp.Substring: rename_func("SUBSTR"),
exp.Table: lambda self, e: self.table_sql(e, sep=" "),
exp.TimeToStr: lambda self, e: f"TO_CHAR({self.sql(e, 'this')}, {self.format_time(e)})",
- exp.UnixToTime: lambda self, e: f"TO_DATE('1970-01-01','YYYY-MM-DD') + ({self.sql(e, 'this')} / 86400)",
- exp.Substring: rename_func("SUBSTR"),
exp.ToChar: lambda self, e: self.function_fallback_sql(e),
+ exp.Trim: trim_sql,
+ exp.UnixToTime: lambda self, e: f"TO_DATE('1970-01-01','YYYY-MM-DD') + ({self.sql(e, 'this')} / 86400)",
}
- def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str:
- return csv(
- *sqls,
- *[self.sql(sql) for sql in expression.args.get("joins") or []],
- self.sql(expression, "match"),
- *[self.sql(sql) for sql in expression.args.get("laterals") or []],
- self.sql(expression, "where"),
- self.sql(expression, "group"),
- self.sql(expression, "having"),
- self.sql(expression, "qualify"),
- self.seg("WINDOW ") + self.expressions(expression, "windows", flat=True)
- if expression.args.get("windows")
- else "",
- self.sql(expression, "distribute"),
- self.sql(expression, "sort"),
- self.sql(expression, "cluster"),
- self.sql(expression, "order"),
- self.sql(expression, "offset"), # offset before limit in oracle
- self.sql(expression, "limit"),
- self.sql(expression, "lock"),
- sep="",
- )
+ LIMIT_FETCH = "FETCH"
def offset_sql(self, expression: exp.Offset) -> str:
return f"{super().offset_sql(expression)} ROWS"
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 5f556a5..31b7e45 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -6,6 +6,7 @@ from sqlglot.dialects.dialect import (
arrow_json_extract_scalar_sql,
arrow_json_extract_sql,
format_time_lambda,
+ max_or_greatest,
min_or_least,
no_paren_current_date_sql,
no_tablesample_sql,
@@ -315,6 +316,7 @@ class Postgres(Dialect):
exp.DateDiff: _date_diff_sql,
exp.LogicalOr: rename_func("BOOL_OR"),
exp.LogicalAnd: rename_func("BOOL_AND"),
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
exp.ArrayContains: lambda self, e: self.binary(e, "@>"),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 799e9a6..c50961c 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -9,6 +9,7 @@ from sqlglot.dialects.dialect import (
datestrtodate_sql,
format_time_lambda,
inline_array_sql,
+ max_or_greatest,
min_or_least,
rename_func,
timestamptrunc_sql,
@@ -275,6 +276,9 @@ class Snowflake(Dialect):
exp.ArrayConcat: rename_func("ARRAY_CAT"),
exp.ArrayJoin: rename_func("ARRAY_TO_STRING"),
exp.DateAdd: lambda self, e: self.func("DATEADD", e.text("unit"), e.expression, e.this),
+ exp.DateDiff: lambda self, e: self.func(
+ "DATEDIFF", e.text("unit"), e.expression, e.this
+ ),
exp.DateStrToDate: datestrtodate_sql,
exp.DataType: _datatype_sql,
exp.If: rename_func("IFF"),
@@ -296,6 +300,7 @@ class Snowflake(Dialect):
exp.TsOrDsToDate: ts_or_ds_to_date_sql("snowflake"),
exp.UnixToTime: _unix_to_time_sql,
exp.DayOfWeek: rename_func("DAYOFWEEK"),
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
}
@@ -314,12 +319,6 @@ class Snowflake(Dialect):
exp.SetProperty: exp.Properties.Location.UNSUPPORTED,
}
- def ilikeany_sql(self, expression: exp.ILikeAny) -> str:
- return self.binary(expression, "ILIKE ANY")
-
- def likeany_sql(self, expression: exp.LikeAny) -> str:
- return self.binary(expression, "LIKE ANY")
-
def except_op(self, expression):
if not expression.args.get("distinct", False):
self.unsupported("EXCEPT with All is not supported in Snowflake")
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index ab78b6e..4091dbb 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -82,6 +82,8 @@ class SQLite(Dialect):
exp.TryCast: no_trycast_sql,
}
+ LIMIT_FETCH = "LIMIT"
+
def cast_sql(self, expression: exp.Cast) -> str:
if expression.to.this == exp.DataType.Type.DATE:
return self.func("DATE", expression.this)
@@ -115,9 +117,6 @@ class SQLite(Dialect):
return f"CAST({sql} AS INTEGER)"
- def fetch_sql(self, expression: exp.Fetch) -> str:
- return self.limit_sql(exp.Limit(expression=expression.args.get("count")))
-
# https://www.sqlite.org/lang_aggfunc.html#group_concat
def groupconcat_sql(self, expression):
this = expression.this
diff --git a/sqlglot/dialects/teradata.py b/sqlglot/dialects/teradata.py
index 8bd0a0c..3d43793 100644
--- a/sqlglot/dialects/teradata.py
+++ b/sqlglot/dialects/teradata.py
@@ -1,7 +1,7 @@
from __future__ import annotations
from sqlglot import exp, generator, parser, tokens
-from sqlglot.dialects.dialect import Dialect, min_or_least
+from sqlglot.dialects.dialect import Dialect, max_or_greatest, min_or_least
from sqlglot.tokens import TokenType
@@ -128,6 +128,7 @@ class Teradata(Dialect):
TRANSFORMS = {
**generator.Generator.TRANSFORMS,
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
exp.ToChar: lambda self, e: self.function_fallback_sql(e),
}
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 7b52047..8e9b6c3 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -6,6 +6,7 @@ import typing as t
from sqlglot import exp, generator, parser, tokens
from sqlglot.dialects.dialect import (
Dialect,
+ max_or_greatest,
min_or_least,
parse_date_delta,
rename_func,
@@ -269,7 +270,6 @@ class TSQL(Dialect):
# TSQL allows @, # to appear as a variable/identifier prefix
SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy()
- SINGLE_TOKENS.pop("@")
SINGLE_TOKENS.pop("#")
class Parser(parser.Parser):
@@ -313,6 +313,9 @@ class TSQL(Dialect):
TokenType.END: lambda self: self._parse_command(),
}
+ LOG_BASE_FIRST = False
+ LOG_DEFAULTS_TO_LN = True
+
def _parse_system_time(self) -> t.Optional[exp.Expression]:
if not self._match_text_seq("FOR", "SYSTEM_TIME"):
return None
@@ -435,11 +438,17 @@ class TSQL(Dialect):
exp.NumberToStr: _format_sql,
exp.TimeToStr: _format_sql,
exp.GroupConcat: _string_agg_sql,
+ exp.Max: max_or_greatest,
exp.Min: min_or_least,
}
TRANSFORMS.pop(exp.ReturnsProperty)
+ LIMIT_FETCH = "FETCH"
+
+ def offset_sql(self, expression: exp.Offset) -> str:
+ return f"{super().offset_sql(expression)} ROWS"
+
def systemtime_sql(self, expression: exp.SystemTime) -> str:
kind = expression.args["kind"]
if kind == "ALL":
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-29 09:49:54 +0000