Package org.apache.calcite.sql

Class SqlDialect

java.lang.Object
org.apache.calcite.sql.SqlDialect
Direct Known Subclasses:
AccessSqlDialect, AnsiSqlDialect, BigQuerySqlDialect, CalciteSqlDialect, ClickHouseSqlDialect, Db2SqlDialect, DerbySqlDialect, DuckDBSqlDialect, ExasolSqlDialect, FirebirdSqlDialect, FireboltSqlDialect, H2SqlDialect, HiveSqlDialect, HsqldbSqlDialect, InfobrightSqlDialect, InformixSqlDialect, IngresSqlDialect, InterbaseSqlDialect, JethroDataSqlDialect, LucidDbSqlDialect, MssqlSqlDialect, MysqlSqlDialect, NeoviewSqlDialect, NetezzaSqlDialect, OracleSqlDialect, ParaccelSqlDialect, PhoenixSqlDialect, PostgresqlSqlDialect, PrestoSqlDialect, RedshiftSqlDialect, SnowflakeSqlDialect, SparkSqlDialect, SqliteSqlDialect, SybaseSqlDialect, TeradataSqlDialect, VerticaSqlDialect
public class SqlDialect extends Object
SqlDialect encapsulates the differences between dialects of SQL.

It is used by classes such as SqlWriter and SqlBuilder.

To add a new SqlDialect sub-class, extends this class to hold 2 public final static member:

  • DEFAULT_CONTEXT: a default SqlDialect.Context instance, which can be used to customize or extending the dialect if the DEFAULT instance does not meet the requests
  • DEFAULT: the default SqlDialect instance with context properties defined with DEFAULT_CONTEXT

  • Field Details

    • LOGGER

      protected static final org.slf4j.Logger LOGGER

    • EMPTY_CONTEXT

      public static final SqlDialect.Context EMPTY_CONTEXT
      Empty context.

    • BUILT_IN_OPERATORS_LIST

      protected static final Set<SqlOperator> BUILT_IN_OPERATORS_LIST
      Built-in scalar functions and operators common for every dialect.

    • identifierQuoteString

      protected final @Nullable String identifierQuoteString

    • identifierEndQuoteString

      protected final @Nullable String identifierEndQuoteString

    • identifierEscapedQuote

      protected final @Nullable String identifierEscapedQuote

    • literalQuoteString

      protected final String literalQuoteString

    • literalEndQuoteString

      protected final String literalEndQuoteString

    • literalEscapedQuote

      protected final String literalEscapedQuote

    • nullCollation

      protected final NullCollation nullCollation

  • Constructor Details

    • SqlDialect

      @Deprecated public SqlDialect(SqlDialect.DatabaseProduct databaseProduct, String databaseProductName, String identifierQuoteString)
      Deprecated.

    • SqlDialect

      @Deprecated public SqlDialect(SqlDialect.DatabaseProduct databaseProduct, String databaseProductName, String identifierQuoteString, NullCollation nullCollation)
      Deprecated.
      Use SqlDialect(Context)
      Creates a SqlDialect.
      Parameters:
      databaseProduct - Database product; may be UNKNOWN, never null
      databaseProductName - Database product name from JDBC driver
      identifierQuoteString - String to quote identifiers. Null if quoting is not supported. If "[", close quote is deemed to be "]".
      nullCollation - Whether NULL values appear first or last

    • SqlDialect

      public SqlDialect(SqlDialect.Context context)
      Creates a SqlDialect.
      Parameters:
      context - All the information necessary to create a dialect

  • Method Details

    • create

      @Deprecated public static SqlDialect create(DatabaseMetaData databaseMetaData)
      Deprecated.
      Replaced by SqlDialectFactory
      Creates a SqlDialect from a DatabaseMetaData.

      Does not maintain a reference to the DatabaseMetaData -- or, more importantly, to its Connection -- after this call has returned.

      Parameters:
      databaseMetaData - used to determine which dialect of SQL to generate

    • getProduct

      @Deprecated public static SqlDialect.DatabaseProduct getProduct(String productName, String productVersion)
      Deprecated.
      Converts a product name and version (per the JDBC driver) into a product enumeration.
      Parameters:
      productName - Product name
      productVersion - Product version
      Returns:
      database product

    • getTypeSystem

      public RelDataTypeSystem getTypeSystem()
      Returns the type system implementation for this dialect.

    • quoteIdentifier

      public String quoteIdentifier(String val)
      Encloses an identifier in quotation marks appropriate for the current SQL dialect.

      For example, quoteIdentifier("emp") yields a string containing "emp" in Oracle, and a string containing [emp] in Access.

      Parameters:
      val - Identifier to quote
      Returns:
      Quoted identifier

    • quoteIdentifier

      public StringBuilder quoteIdentifier(StringBuilder buf, String val)
      Encloses an identifier in quotation marks appropriate for the current SQL dialect, writing the result to a StringBuilder.

      For example, quoteIdentifier("emp") yields a string containing "emp" in Oracle, and a string containing [emp] in Access.

      Parameters:
      buf - Buffer
      val - Identifier to quote
      Returns:
      The buffer

    • quoteIdentifier

      public StringBuilder quoteIdentifier(StringBuilder buf, List<String> identifiers)
      Quotes a multi-part identifier.
      Parameters:
      buf - Buffer
      identifiers - List of parts of the identifier to quote
      Returns:
      The buffer

    • identifierNeedsQuote

      protected boolean identifierNeedsQuote(String val)
      Returns whether to quote an identifier. By default, all identifiers are quoted.

    • quoteStringLiteral

      public final String quoteStringLiteral(String val)
      Converts a string into a string literal.

      For example, "can't run" becomes "'can''t run'".

    • quoteStringLiteral

      public void quoteStringLiteral(StringBuilder buf, @Nullable String charsetName, String val)
      Appends a string literal to a buffer.
      Parameters:
      buf - Buffer
      charsetName - Character set name, e.g. "utf16", or null
      val - String value

    • unparseCall

      public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec)

    • unparseBoolLiteral

      public void unparseBoolLiteral(SqlWriter writer, SqlLiteral literal, int leftPrec, int rightPrec)

    • unparseDateTimeLiteral

      public void unparseDateTimeLiteral(SqlWriter writer, SqlAbstractDateTimeLiteral literal, int leftPrec, int rightPrec)

    • unparseNumericLiteral

      public void unparseNumericLiteral(SqlWriter writer, String value, int leftPrec, int rightPrec)

    • unparseSqlDatetimeArithmetic

      public void unparseSqlDatetimeArithmetic(SqlWriter writer, SqlCall call, SqlKind sqlKind, int leftPrec, int rightPrec)

    • unparseSqlIntervalQualifier

      public void unparseSqlIntervalQualifier(SqlWriter writer, SqlIntervalQualifier qualifier, RelDataTypeSystem typeSystem)
      Converts an interval qualifier to a SQL string. The default implementation returns strings such as INTERVAL '1 2:3:4' DAY(4) TO SECOND(4).

    • unparseSqlIntervalLiteral

      public void unparseSqlIntervalLiteral(SqlWriter writer, SqlIntervalLiteral literal, int leftPrec, int rightPrec)
      Converts an interval literal to a SQL string. The default implementation returns strings such as INTERVAL '1 2:3:4' DAY(4) TO SECOND(4).

    • unparseTableScanHints

      public void unparseTableScanHints(SqlWriter writer, SqlNodeList hints, int leftPrec, int rightPrec)
      Converts table scan hints. The default implementation suppresses all hints.

    • containsNonAscii

      protected static boolean containsNonAscii(String s)
      Returns whether the string contains any characters outside the comfortable 7-bit ASCII range (32 through 127, plus linefeed (10) and carriage return (13)).

      Such characters can be used unquoted in SQL character literals.

      Parameters:
      s - String
      Returns:
      Whether string contains any non-7-bit-ASCII characters

    • quoteStringLiteralUnicode

      public void quoteStringLiteralUnicode(StringBuilder buf, String val)
      Converts a string into a unicode string literal. For example, can't{tab}run\ becomes u'can''t\0009run\\'.

    • unquoteStringLiteral

      public @Nullable String unquoteStringLiteral(@Nullable String val)
      Converts a string literal back into a string. For example, 'can''t run' becomes can't run.

    • allowsAs

      protected boolean allowsAs()

    • requiresAliasForFromItems

      public boolean requiresAliasForFromItems()
      Whether a sub-query in the FROM clause must have an alias.

      For example, in PostgreSQL, this query is legal:

      SELECT * FROM (SELECT * FROM Emp) As e

      but remove the alias e and it is not:

      SELECT * FROM (SELECT * FROM Emp)

      In Oracle, both queries are legal.

    • hasImplicitTableAlias

      public boolean hasImplicitTableAlias()
      Returns whether a qualified table in the FROM clause has an implicit alias which consists of just the table name.

      For example, in SqlDialect.DatabaseProduct.ORACLE

      SELECT * FROM sales.emp

      is equivalent to

      SELECT * FROM sales.emp AS emp

      and therefore

      SELECT emp.empno FROM sales.emp

      is valid. But SqlDialect.DatabaseProduct.DB2 does not have an implicit alias, so the previous query it not valid; you need to write

      SELECT sales.emp.empno FROM sales.emp

      Returns true for all databases except DB2.

    • quoteTimestampLiteral

      public String quoteTimestampLiteral(Timestamp timestamp)
      Converts a timestamp to a SQL timestamp literal, e.g. TIMESTAMP '2009-12-17 12:34:56'.

      Timestamp values do not have a time zone. We therefore interpret them as the number of milliseconds after the UTC epoch, and the formatted value is that time in UTC.

      In particular,

      quoteTimestampLiteral(new Timestamp(0));

      returns TIMESTAMP '1970-01-01 00:00:00', regardless of the JVM's time zone.

      Parameters:
      timestamp - Timestamp
      Returns:
      SQL timestamp literal

    • getDatabaseProduct

      @Deprecated public SqlDialect.DatabaseProduct getDatabaseProduct()
      Deprecated.
      To be removed without replacement
      Returns the database this dialect belongs to, SqlDialect.DatabaseProduct.UNKNOWN if not known, never null.

      Please be judicious in how you use this method. If you wish to determine whether a dialect has a particular capability or behavior, it is usually better to add a method to SqlDialect and override that method in particular sub-classes of SqlDialect.

      Returns:
      Database product

    • supportsCharSet

      @Pure public boolean supportsCharSet()
      Returns whether the dialect supports character set names as part of a data type, for instance VARCHAR(30) CHARACTER SET `ISO-8859-1`.

    • supportsGroupByLiteral

      public boolean supportsGroupByLiteral()
      Returns whether the dialect supports GROUP BY literals.

      For instance, in SqlDialect.DatabaseProduct.REDSHIFT, the following queries are illegal: 

      
 select avg(salary)
 from emp
 group by true

 select avg(salary)
 from emp
 group by 'a', DATE '2022-01-01'

    • supportsAggregateFunction

      public boolean supportsAggregateFunction(SqlKind kind)

    • supportsApproxCountDistinct

      public boolean supportsApproxCountDistinct()
      Returns whether this dialect supports APPROX_COUNT_DISTINCT functions.

    • supportsTimestampPrecision

      public boolean supportsTimestampPrecision()
      Returns whether this dialect supports TIMESTAMP with precision.

    • supportsAggregateFunctionFilter

      public boolean supportsAggregateFunctionFilter()
      Returns whether this dialect supports the use of FILTER clauses for aggregate functions. e.g. COUNT(*) FILTER (WHERE a = 2).

    • supportsWindowFunctions

      public boolean supportsWindowFunctions()
      Returns whether this dialect supports window functions (OVER clause).

    • supportBooleanCaseWhen

      public boolean supportBooleanCaseWhen()
      Returns whether this dialect supports case when return boolean type.

    • prepareUnparse

      public RexNode prepareUnparse(RexNode rexNode)
      Converts RexNode expression to RexNode expression before unparse.

    • supportsFunction

      public boolean supportsFunction(SqlOperator operator, RelDataType type, List<RelDataType> paramTypes)
      Returns whether this dialect supports a given function or operator. It only applies to built-in scalar functions and operators, since user-defined functions and procedures should be read by JdbcSchema.

    • getCalendarPolicy

      public SqlDialect.CalendarPolicy getCalendarPolicy()

    • supportsDataType

      public boolean supportsDataType(RelDataType type)
      Returns whether this dialect supports a given type.

    • getCastSpec

      public @Nullable SqlNode getCastSpec(RelDataType type)
      Returns SqlNode for type in "cast(column as type)", which might be different between databases by type name, precision etc.

      If this method returns null, the cast will be omitted. In the default implementation, this is the case for the NULL type, and therefore CAST(NULL AS <nulltype>) is rendered as NULL.

    • rewriteSingleValueExpr

      public SqlNode rewriteSingleValueExpr(SqlNode aggCall, RelDataType relDataType)
      Rewrites SINGLE_VALUE into expression based on database variants E.g. HSQLDB, MYSQL, ORACLE, etc.

    • rewriteMaxMinExpr

      public SqlNode rewriteMaxMinExpr(SqlNode aggCall, RelDataType relDataType)
      Rewrites MAX(x)/MIN(x) as BOOL_OR(x)/BOOL_AND(x) for certain database variants (Postgres and Redshift, currently).
      See Also:

    • rewriteMaxMin

      protected static SqlNode rewriteMaxMin(SqlNode aggCall, RelDataType relDataType)
      Helper for rewrites of MAX/MIN. Some dialects (e.g. Postgres and Redshift), rewrite as BOOL_OR/BOOL_AND if the return type is BOOLEAN.

    • emulateNullDirection

      public @Nullable SqlNode emulateNullDirection(SqlNode node, boolean nullsFirst, boolean desc)
      Returns the SqlNode for emulating the null direction for the given field or null if no emulation needs to be done.
      Parameters:
      node - The SqlNode representing the expression
      nullsFirst - Whether nulls should come first
      desc - Whether the sort direction is RelFieldCollation.Direction.DESCENDING or RelFieldCollation.Direction.STRICTLY_DESCENDING
      Returns:
      A SqlNode for null direction emulation or null if not required

    • emulateJoinTypeForCrossJoin

      public JoinType emulateJoinTypeForCrossJoin()

    • emulateNullDirectionWithIsNull

      protected @Nullable SqlNode emulateNullDirectionWithIsNull(SqlNode node, boolean nullsFirst, boolean desc)

    • supportsOffsetFetch

      @Deprecated public boolean supportsOffsetFetch()
      Deprecated.
      This method is no longer used. To change how the dialect unparses offset/fetch, override the unparseOffsetFetch(org.apache.calcite.sql.SqlWriter, org.apache.calcite.sql.SqlNode, org.apache.calcite.sql.SqlNode) method.
      Returns whether the dialect supports OFFSET/FETCH clauses introduced by SQL:2008, for instance OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY. If false, we assume that the dialect supports the alternative syntax LIMIT 20 OFFSET 10.

    • unparseOffsetFetch

      public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch)
      Converts an offset and fetch into SQL.

      At least one of offset and fetch must be provided.

      Common options:

      • OFFSET offset ROWS FETCH NEXT fetch ROWS ONLY (ANSI standard SQL, Oracle, PostgreSQL, and the default)
      • LIMIT fetch OFFSET offset (Apache Hive, MySQL, Redshift)
      Parameters:
      writer - Writer
      offset - Number of rows to skip before emitting, or null
      fetch - Number of rows to fetch, or null
      See Also:

    • unparseTopN

      public void unparseTopN(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch)
      Converts a fetch into a "SELECT TOP(fetch)".

      A dialect that uses "TOP" syntax should override this method to print "TOP(fetch)", and override unparseOffsetFetch(org.apache.calcite.sql.SqlWriter, org.apache.calcite.sql.SqlNode, org.apache.calcite.sql.SqlNode) to no-op.

      The default implementation of this method is no-op.

      Parameters:
      writer - Writer
      offset - Number of rows to skip before emitting, or null
      fetch - Number of rows to fetch, or null

    • unparseSqlSetOption

      public void unparseSqlSetOption(SqlWriter writer, int leftPrec, int rightPrec, SqlSetOption option)

    • unparseFetchUsingAnsi

      protected static void unparseFetchUsingAnsi(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch)
      Unparses offset/fetch using ANSI standard "OFFSET offset ROWS FETCH NEXT fetch ROWS ONLY" syntax.

    • unparseFetchUsingLimit

      protected static void unparseFetchUsingLimit(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch)
      Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax.

    • unparseLimit

      protected static void unparseLimit(SqlWriter writer, @Nullable SqlNode fetch)

    • unparseOffset

      protected static void unparseOffset(SqlWriter writer, @Nullable SqlNode offset)

    • getFormatModel

      public FormatModel getFormatModel()
      Returns a description of the format string used by functions in this dialect.

      Dialects may need to override this element mapping if they differ from Oracle's format elements. By default, this returns FormatModels.DEFAULT.

    • supportsNestedAggregations

      public boolean supportsNestedAggregations()
      Returns whether the dialect supports nested aggregations, for instance SELECT SUM(SUM(1)) .

    • supportsGroupByWithRollup

      public boolean supportsGroupByWithRollup()
      Returns whether this dialect supports "WITH ROLLUP" in the "GROUP BY" clause.

      For instance, in MySQL version 5,

      SELECT deptno, job, COUNT(*) AS c FROM emp GROUP BY deptno, job WITH ROLLUP

      is equivalent to standard SQL

      SELECT deptno, job, COUNT(*) AS c FROM emp GROUP BY ROLLUP(deptno, job) ORDER BY deptno, job

      The "WITH ROLLUP" clause was introduced in MySQL and is not standard SQL.

      See also supportsAggregateFunction(SqlKind) applied to SqlKind.ROLLUP, which returns true in MySQL 8 and higher.

    • supportsGroupByWithCube

      public boolean supportsGroupByWithCube()
      Returns whether this dialect supports "WITH CUBE" in "GROUP BY" clause.

    • supportsJoinType

      public boolean supportsJoinType(JoinRelType joinType)
      Returns whether this dialect support the specified type of join.

    • getNullCollation

      public NullCollation getNullCollation()
      Returns how NULL values are sorted if an ORDER BY item does not contain NULLS ASCENDING or NULLS DESCENDING.

    • defaultNullDirection

      public RelFieldCollation.NullDirection defaultNullDirection(RelFieldCollation.Direction direction)
      Returns whether NULL values are sorted first or last, in this dialect, in an ORDER BY item of a given direction.

    • supportsAliasedValues

      public boolean supportsAliasedValues()
      Returns whether the dialect supports VALUES in a sub-query with and an "AS t(column, ...)" values to define column names.

      Currently, only Oracle does not. For this, we generate "SELECT v0 AS c0, v1 AS c1 ... UNION ALL ...". We may need to refactor this method when we support VALUES for other dialects.

    • supportsImplicitTypeCoercion

      public boolean supportsImplicitTypeCoercion(RexCall call)
      Returns whether the dialect supports implicit type coercion.

      Most of the sql dialects support implicit type coercion, so we make this method default return true. For instance, "cast('10' as integer) > 5" can be simplified to "'10' > 5" if the dialect supports implicit type coercion for VARCHAR and INTEGER comparison.

      For sql dialect that does not support implicit type coercion, such as the BigQuery, we can not convert '10' into INT64 implicitly.

      Now this method is used for some auxiliary decision when translating some RexCalls, see SqlImplementor#stripCastFromString for details.

      Parameters:
      call - the call to make decision

    • getSingleRowTableName

      public @Nullable List<String> getSingleRowTableName()
      Returns the name of the system table that has precisely one row. If there is no such table, returns null, and we will generate SELECT with no FROM clause.

      For VALUES 1, Oracle returns ["DUAL"] and we generate "SELECT 1 FROM DUAL"; MySQL returns null and we generate "SELECT 1".

    • configureParser

      public SqlParser.Config configureParser(SqlParser.Config config)
      Copies settings from this dialect into a parser configuration.

      SqlDialect, SqlParser.Config and SqlConformance cover different aspects of the same thing - the dialect of SQL spoken by a database - and this method helps to bridge between them. (The aspects are, respectively, generating SQL to send to a source database, parsing SQL sent to Calcite, and validating queries sent to Calcite. It makes sense to keep them as separate interfaces because they are used by different modules.)

      The settings copied may differ among dialects, and may change over time, but currently include the following:

      Parameters:
      config - Parser configuration builder
      Returns:
      The configuration builder

    • configureParser

      @Deprecated public SqlParser.ConfigBuilder configureParser(SqlParser.ConfigBuilder configBuilder)
      Deprecated.

    • getConformance

      public SqlConformance getConformance()
      Returns the SqlConformance that matches this dialect.

      The base implementation returns its best guess, based upon databaseProduct; sub-classes may override.

    • getQuoting

      protected @Nullable org.apache.calcite.avatica.util.Quoting getQuoting()
      Returns the quoting scheme, or null if the combination of identifierQuoteString and identifierEndQuoteString does not correspond to any known quoting scheme.

    • getUnquotedCasing

      public org.apache.calcite.avatica.util.Casing getUnquotedCasing()
      Returns how unquoted identifiers are stored.

    • getQuotedCasing

      public org.apache.calcite.avatica.util.Casing getQuotedCasing()
      Returns how quoted identifiers are stored.

    • isCaseSensitive

      public boolean isCaseSensitive()
      Returns whether matching of identifiers is case-sensitive.