Class SqlDialect

    • Field Detail

      • LOGGER

        protected static final org.slf4j.Logger LOGGER

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

        protected final String identifierQuoteString
      • identifierEndQuoteString

        protected final String identifierEndQuoteString
      • identifierEscapedQuote

        protected final String identifierEscapedQuote
      • literalQuoteString

        protected final String literalQuoteString
      • literalEndQuoteString

        protected final String literalEndQuoteString
      • literalEscapedQuote

        protected final String literalEscapedQuote
    • Constructor Detail

      • SqlDialect

        public SqlDialect​(SqlDialect.DatabaseProduct databaseProduct,
                          String databaseProductName,
                          String identifierQuoteString,
                          NullCollation nullCollation)
        Creates a SqlDialect.
        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.
        context - All the information necessary to create a dialect
    • Method Detail

      • create

        public static SqlDialect create​(DatabaseMetaData databaseMetaData)
        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.

        databaseMetaData - used to determine which dialect of SQL to generate
      • getProduct

        public static SqlDialect.DatabaseProduct getProduct​(String productName,
                                                            String productVersion)
        Converts a product name and version (per the JDBC driver) into a product enumeration.
        productName - Product name
        productVersion - Product version
        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.

        val - Identifier to quote
        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.

        buf - Buffer
        val - Identifier to quote
        The buffer
      • quoteIdentifier

        public StringBuilder quoteIdentifier​(StringBuilder buf,
                                             List<String> identifiers)
        Quotes a multi-part identifier.
        buf - Buffer
        identifiers - List of parts of the identifier to quote
        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,
                                       String charsetName,
                                       String val)
        Appends a string literal to a buffer.
        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)
      • 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).
      • 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.

        s - String
        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 String unquoteStringLiteral​(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:


        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.

        timestamp - Timestamp
        SQL timestamp literal
      • getDatabaseProduct

        public SqlDialect.DatabaseProduct getDatabaseProduct()
        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.

        Database product
      • supportsCharSet

        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`.
      • supportsAggregateFunction

        public boolean supportsAggregateFunction​(SqlKind kind)
      • supportsWindowFunctions

        public boolean supportsWindowFunctions()
        Returns whether this dialect supports window functions (OVER clause).
      • 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.
      • supportsDataType

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

        public 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)
        Rewrite SINGLE_VALUE into expression based on database variants E.g. HSQLDB, MYSQL, ORACLE, etc
      • emulateNullDirection

        public 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.
        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
        A SqlNode for null direction emulation or null if not required
      • emulateJoinTypeForCrossJoin

        public JoinType emulateJoinTypeForCrossJoin()
      • emulateNullDirectionWithIsNull

        protected SqlNode emulateNullDirectionWithIsNull​(SqlNode node,
                                                         boolean nullsFirst,
                                                         boolean desc)
      • unparseFetchUsingAnsi

        protected final void unparseFetchUsingAnsi​(SqlWriter writer,
                                                   SqlNode offset,
                                                   SqlNode fetch)
        Unparses offset/fetch using ANSI standard "OFFSET offset ROWS FETCH NEXT fetch ROWS ONLY" syntax.
      • unparseFetchUsingLimit

        protected final void unparseFetchUsingLimit​(SqlWriter writer,
                                                    SqlNode offset,
                                                    SqlNode fetch)
        Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax.
      • unparseLimit

        protected final void unparseLimit​(SqlWriter writer,
                                          SqlNode fetch)
      • unparseOffset

        protected final void unparseOffset​(SqlWriter writer,
                                           SqlNode offset)
      • 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.
      • getNullCollation

        public NullCollation getNullCollation()
        Returns how NULL values are sorted if an ORDER BY item does not contain NULLS ASCENDING or NULLS DESCENDING.
      • 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.

        call - the call to make decision
      • getSingleRowTableName

        public 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.ConfigBuilder configureParser​(SqlParser.ConfigBuilder configBuilder)
        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:

        configBuilder - Parser configuration builder
        The configuration builder
      • getQuoting

        protected 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.