Interface SqlConformance
- All Known Implementing Classes:
- SqlAbstractConformance,- SqlConformanceEnum,- SqlDelegatingConformance
For most purposes, one of the built-in compatibility modes in enum
 SqlConformanceEnum will suffice.
 
If you wish to implement this interface to build your own conformance,
 we strongly recommend that you extend SqlAbstractConformance,
 or use a SqlDelegatingConformance,
 so that you won't be broken by future changes.
- 
Nested Class SummaryNested ClassesModifier and TypeInterfaceDescriptionstatic enumValue describing how to perform lookup for aliases defined in a SELECT list.
- 
Field SummaryFieldsModifier and TypeFieldDescriptionstatic final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.static final SqlConformanceEnumDeprecated.
- 
Method SummaryModifier and TypeMethodDescriptionbooleanWhether directly alias array items in UNNEST.booleanWhether this dialect allows character literals as column aliases.booleanWhether to allow SQL syntax "ROW(expr1, expr2, expr3)".booleanWhether to allow mixing table columns with extended columns inINSERT(orUPSERT).booleanWhether TRIM should support more than one trim character.booleanWhether to allow geo-spatial extensions, including the GEOMETRY type.booleanWhether to allow hyphens in an unquoted table name.booleanWhether to allow lenient type coercions.booleanWhether to allow parentheses to be specified in calls to niladic functions of returned the specific constant value.booleanWhether to allow parentheses to be specified in calls to niladic functions and procedures (that is, functions and procedures with no parameters).booleanWhether interval literals should allow plural time units such as "YEARS" and "DAYS" in interval literals.booleanWhether to allow a qualified common column in a query that has a NATURAL join or a join with a USING clause.booleanWhether the implementation uses checked arithmetic.booleanWhetherCROSS APPLYandOUTER APPLYoperators are allowed in the parser.booleanWhether the bang-equal token != is allowed as an alternative to <> in the parser.booleanWhetherFROMclause is required in aSELECTstatement.booleanWhether to allow aliases from theSELECTclause to be used as column names in theGROUP BYclause.booleanWhetherGROUP BY 2is interpreted to mean 'group by the 2nd column in the select list'.booleanWhether to allow aliases from theSELECTclause to be used as column names in theHAVINGclause.booleanWhether to allowINSERT(orUPSERT) with no column list but fewer values than the target table.booleanWhether this dialect supports features from a wide variety of dialects.booleanWhether to allow the SQL syntax "LIMIT start, count".booleanWhetherMINUSis allowed as an alternative toEXCEPTin the parser.booleanWhether to allow the SQL syntax "OFFSET start LIMIT count" (that is,OFFSETbeforeLIMIT, in addition toLIMITbeforeOFFSETandOFFSETbeforeFETCH).booleanWhether the "%" operator is allowed by the parser as an alternative to themodfunction.Whether to allow aliases from theSELECTclause to be used as column names in the sameSELECTclause.booleanWhether 'ORDER BY x' is interpreted to mean 'sort by the select list item whose alias is x' even if there is a column called x.booleanWhether "empno" is invalid in "select empno as x from emp order by empno" because the alias "x" obscures it.booleanWhether 'ORDER BY 2' is interpreted to mean 'sort by the 2nd column in the select list'.booleanWhether this dialect supports dual table.booleanWhetherVALUEis allowed as an alternative toVALUESin the parser.Controls the behavior of operators that are part of Standard SQL but nevertheless have different behavior in different databases.booleanWhether the least restrictive type of a number of CHAR types of different lengths should be a VARCHAR type.booleanWhether to split a quoted table name.
- 
Field Details- 
DEFAULTDeprecated.Short-cut forSqlConformanceEnum.DEFAULT.
- 
STRICT_92Deprecated.Short-cut forSqlConformanceEnum.STRICT_92.
- 
STRICT_99Deprecated.Short-cut forSqlConformanceEnum.STRICT_99.
- 
PRAGMATIC_99Deprecated.Short-cut forSqlConformanceEnum.PRAGMATIC_99.
- 
ORACLE_10Deprecated.Short-cut forSqlConformanceEnum.ORACLE_10.
- 
STRICT_2003Deprecated.Short-cut forSqlConformanceEnum.STRICT_2003.
- 
PRAGMATIC_2003Deprecated.Short-cut forSqlConformanceEnum.PRAGMATIC_2003.
 
- 
- 
Method Details- 
isLiberalboolean isLiberal()Whether this dialect supports features from a wide variety of dialects. This is enabled for the Babel parser, disabled otherwise.
- 
allowCharLiteralAliasboolean allowCharLiteralAlias()Whether this dialect allows character literals as column aliases.For example, SELECT empno, sal + comm AS 'remuneration' FROM Emp Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.BIG_QUERY,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.SQL_SERVER_2008; false otherwise.
- 
isGroupByAliasboolean isGroupByAlias()Whether to allow aliases from theSELECTclause to be used as column names in theGROUP BYclause.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.BIG_QUERY,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5; false otherwise.
- 
isSelectAliasSqlConformance.SelectAliasLookup isSelectAlias()Whether to allow aliases from theSELECTclause to be used as column names in the sameSELECTclause. E.g., SELECT 1 as X, X+1 as Y; Name lookup considers an identifier in the same SELECT only if other lookups failed. Supported by Spark, Snowflake, Redshift, DuckDB.Note: this usually requires isGroupByAlias()to also return true.
- 
isGroupByOrdinalboolean isGroupByOrdinal()WhetherGROUP BY 2is interpreted to mean 'group by the 2nd column in the select list'.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.BIG_QUERY,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.PRESTO; false otherwise.
- 
isHavingAliasboolean isHavingAlias()Whether to allow aliases from theSELECTclause to be used as column names in theHAVINGclause.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.BIG_QUERY,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5; false otherwise.
- 
isSortByOrdinalboolean isSortByOrdinal()Whether 'ORDER BY 2' is interpreted to mean 'sort by the 2nd column in the select list'.Among the built-in conformance levels, true in SqlConformanceEnum.DEFAULT,SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.PRAGMATIC_99,SqlConformanceEnum.PRAGMATIC_2003,SqlConformanceEnum.PRESTO,SqlConformanceEnum.SQL_SERVER_2008,SqlConformanceEnum.STRICT_92; false otherwise.
- 
isSortByAliasboolean isSortByAlias()Whether 'ORDER BY x' is interpreted to mean 'sort by the select list item whose alias is x' even if there is a column called x.Among the built-in conformance levels, true in SqlConformanceEnum.DEFAULT,SqlConformanceEnum.BABEL,SqlConformanceEnum.BIG_QUERY,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.SQL_SERVER_2008,SqlConformanceEnum.STRICT_92; false otherwise.
- 
isSortByAliasObscuresboolean isSortByAliasObscures()Whether "empno" is invalid in "select empno as x from emp order by empno" because the alias "x" obscures it.Among the built-in conformance levels, true in SqlConformanceEnum.STRICT_92; false otherwise.
- 
isSupportedDualTableboolean isSupportedDualTable()Whether this dialect supports dual table.For example, SELECT 1 + 1 FROM DUAL Among the built-in conformance levels, true in SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12, false otherwise.
- 
isFromRequiredboolean isFromRequired()WhetherFROMclause is required in aSELECTstatement.Among the built-in conformance levels, true in SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.STRICT_92,SqlConformanceEnum.STRICT_99,SqlConformanceEnum.STRICT_2003; false otherwise.
- 
splitQuotedTableNameboolean splitQuotedTableName()Whether to split a quoted table name. If true,`x.y.z`is parsed as if the user had written`x`.`y`.`z`.Among the built-in conformance levels, true in SqlConformanceEnum.BIG_QUERY; false otherwise.
- 
allowHyphenInUnquotedTableNameboolean allowHyphenInUnquotedTableName()Whether to allow hyphens in an unquoted table name.If true, SELECT * FROM foo-bar.baz-buzzis valid, and is parsed as if the user had writtenSELECT * FROM `foo-bar`.`baz-buzz`.Among the built-in conformance levels, true in SqlConformanceEnum.BIG_QUERY; false otherwise.
- 
isBangEqualAllowedboolean isBangEqualAllowed()Whether the bang-equal token != is allowed as an alternative to <> in the parser.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.PRESTO; false otherwise.
- 
isPercentRemainderAllowedboolean isPercentRemainderAllowed()Whether the "%" operator is allowed by the parser as an alternative to themodfunction.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.PRESTO; false otherwise.
- 
isMinusAllowedboolean isMinusAllowed()WhetherMINUSis allowed as an alternative toEXCEPTin the parser.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12; false otherwise.Note: MySQL does not support MINUSorEXCEPT(as of version 5.5).
- 
isApplyAllowedboolean isApplyAllowed()WhetherCROSS APPLYandOUTER APPLYoperators are allowed in the parser.APPLYinvokes a table-valued function for each row returned by a table expression. It is syntactic sugar:- SELECT * FROM emp CROSS APPLY TABLE(promote(empno)
 is equivalent to
 - SELECT * FROM emp CROSS JOIN LATERAL TABLE(promote(empno)
- SELECT * FROM emp OUTER APPLY TABLE(promote(empno)
 is equivalent to
 - SELECT * FROM emp LEFT JOIN LATERAL TABLE(promote(empno)ON true
 Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.SQL_SERVER_2008; false otherwise.
- 
isInsertSubsetColumnsAllowedboolean isInsertSubsetColumnsAllowed()Whether to allowINSERT(orUPSERT) with no column list but fewer values than the target table.The N values provided are assumed to match the first N columns of the table, and for each of the remaining columns, the default value of the column is used. It is an error if any of these columns has no default value. The default value of a column is specified by the DEFAULTclause in theCREATE TABLEstatement, or isNULLif the column is not declaredNOT NULL.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.PRAGMATIC_99,SqlConformanceEnum.PRAGMATIC_2003; false otherwise.
- 
allowAliasUnnestItemsboolean allowAliasUnnestItems()Whether directly alias array items in UNNEST.E.g. in UNNEST(a_array, b_array) AS T(a, b), a and b will be aliases of elements in a_array and b_array respectively. Without this flag set, T will be the alias of the element in a_array and a, b will be the top level fields of T if T is a STRUCT type. Among the built-in conformance levels, true in SqlConformanceEnum.PRESTO; false otherwise.
- 
allowNiladicParenthesesboolean allowNiladicParentheses()Whether to allow parentheses to be specified in calls to niladic functions and procedures (that is, functions and procedures with no parameters).For example, CURRENT_DATEis a niladic system function. In standard SQL it must be invoked without parentheses:VALUES CURRENT_DATEIf allowNiladicParentheses, the following syntax is also valid:VALUES CURRENT_DATE()Of the popular databases, MySQL, Apache Phoenix and VoltDB allow this behavior; Apache Hive, HSQLDB, IBM DB2, Microsoft SQL Server, Oracle, PostgreSQL do not. Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5; false otherwise.
- 
allowNiladicConstantWithoutParenthesesboolean allowNiladicConstantWithoutParentheses()Whether to allow parentheses to be specified in calls to niladic functions of returned the specific constant value.For example, PIis a niladic function and return specific constant values pi. In standard SQL it must be invoked with parentheses:VALUES PI()If allowNiladicConstantWithoutParentheses, the following syntax is also valid:VALUES PIThe same function include E which result is Euler's constant. Among the built-in conformance levels, true in SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.DEFAULT;SqlConformanceEnum.LENIENT; false otherwise.
- 
allowExplicitRowValueConstructorboolean allowExplicitRowValueConstructor()Whether to allow SQL syntax "ROW(expr1, expr2, expr3)".The equivalent syntax in standard SQL is " (expr1, expr2, expr3)".Standard SQL does not allow this because the type is not well-defined. However, PostgreSQL allows this behavior. Standard SQL allows row expressions in other contexts, for instance inside VALUESclause.Among the built-in conformance levels, true in SqlConformanceEnum.DEFAULT,SqlConformanceEnum.LENIENT,SqlConformanceEnum.PRESTO; false otherwise.
- 
allowExtendboolean allowExtend()Whether to allow mixing table columns with extended columns inINSERT(orUPSERT).For example, suppose that the declaration of table Thas columnsAandB, and you want to insert data of columnC INTEGERnot present in the table declaration as an extended column. You can specify the columns in anINSERTstatement as follows:INSERT INTO T (A, B, C INTEGER) VALUES (1, 2, 3)Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT; false otherwise.
- 
isLimitStartCountAllowedboolean isLimitStartCountAllowed()Whether to allow the SQL syntax "LIMIT start, count".The equivalent syntax in standard SQL is " OFFSET start ROW FETCH FIRST count ROWS ONLY", and in PostgreSQL "LIMIT count OFFSET start".MySQL and CUBRID allow this behavior. Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5; false otherwise.
- 
isOffsetLimitAllowedboolean isOffsetLimitAllowed()Whether to allow the SQL syntax "OFFSET start LIMIT count" (that is,OFFSETbeforeLIMIT, in addition toLIMITbeforeOFFSETandOFFSETbeforeFETCH).The equivalent syntax in standard SQL is " OFFSET start ROW FETCH FIRST count ROWS ONLY".Trino allows this behavior. Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT; false otherwise.
- 
allowGeometryboolean allowGeometry()Whether to allow geo-spatial extensions, including the GEOMETRY type.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.PRESTO,SqlConformanceEnum.SQL_SERVER_2008; false otherwise.
- 
shouldConvertRaggedUnionTypesToVaryingboolean shouldConvertRaggedUnionTypesToVarying()Whether the least restrictive type of a number of CHAR types of different lengths should be a VARCHAR type. And similarly BINARY to VARBINARY.For example, consider the query SELECT 'abcde' UNION SELECT 'xyz' The input columns have types CHAR(5)andCHAR(3), and we need a result type that is large enough for both:- Under strict SQL:2003 behavior, its column has type CHAR(5), and the value in the second row will have trailing spaces.
- With lenient behavior, its column has type VARCHAR(5), and the values have no trailing spaces.
 Among the built-in conformance levels, true in SqlConformanceEnum.PRAGMATIC_99,SqlConformanceEnum.PRAGMATIC_2003,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.PRESTO,SqlConformanceEnum.SQL_SERVER_2008; false otherwise.
- Under strict SQL:2003 behavior, its column has type 
- 
allowExtendedTrimboolean allowExtendedTrim()Whether TRIM should support more than one trim character.For example, consider the query SELECT TRIM('eh' FROM 'hehe__hehe')Under strict behavior, if the length of trim character is not 1, TRIM throws an exception, and the query fails. However many implementations (in databases such as MySQL and SQL Server) trim all the characters, resulting in a return value of '__'. Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5,SqlConformanceEnum.SQL_SERVER_2008; false otherwise.
- 
allowPluralTimeUnitsboolean allowPluralTimeUnits()Whether interval literals should allow plural time units such as "YEARS" and "DAYS" in interval literals.Under strict behavior, INTERVAL '2' DAYis valid andINTERVAL '2' DAYSis invalid; PostgreSQL allows both; Oracle only allows singular time units.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT; false otherwise.
- 
allowQualifyingCommonColumnboolean allowQualifyingCommonColumn()Whether to allow a qualified common column in a query that has a NATURAL join or a join with a USING clause.For example, in the query SELECT emp.deptno FROM emp JOIN dept USING (deptno) deptnois the common column. A qualified common column such asemp.deptnois not allowed in Oracle, but is allowed in PostgreSQL.Among the built-in conformance levels, false in SqlConformanceEnum.ORACLE_10,SqlConformanceEnum.ORACLE_12,SqlConformanceEnum.PRESTO,SqlConformanceEnum.STRICT_92,SqlConformanceEnum.STRICT_99,SqlConformanceEnum.STRICT_2003; true otherwise.
- 
isValueAllowedboolean isValueAllowed()WhetherVALUEis allowed as an alternative toVALUESin the parser.Among the built-in conformance levels, true in SqlConformanceEnum.BABEL,SqlConformanceEnum.LENIENT,SqlConformanceEnum.MYSQL_5; false otherwise.
- 
semanticsSqlLibrary semantics()Controls the behavior of operators that are part of Standard SQL but nevertheless have different behavior in different databases.Consider the SUBSTRINGoperator. In ISO standard SQL, negative start indexes are converted to 1; in Google BigQuery, negative start indexes are treated as offsets from the end of the string. For example,SUBSTRING('abcde' FROM -3 FOR 2)returns'ab'in standard SQL and 'cd' in BigQuery.If you specify conformance=BIG_QUERYin your connection parameters,SUBSTRINGwill give the BigQuery behavior. Similarly MySQL and Oracle.Among the built-in conformance levels: - SqlConformanceEnum.BIG_QUERYreturns- SqlLibrary.BIG_QUERY;
- SqlConformanceEnum.MYSQL_5returns- SqlLibrary.MYSQL;
- SqlConformanceEnum.ORACLE_10and- SqlConformanceEnum.ORACLE_12return- SqlLibrary.ORACLE;
- otherwise returns SqlLibrary.STANDARD.
 
- 
allowLenientCoercionboolean allowLenientCoercion()Whether to allow lenient type coercions.Coercions include: - Coercion of string literal to array literal. For example,
 SELECT ARRAY[0,1,2] == '{0,1,2}'
- Casting BOOLEANvalues to one of the following numeric types:TINYINT,SMALLINT,INTEGER,BIGINT.
 Among the built-in conformance levels, true in SqlConformanceEnum.BABEL, false otherwise.
- Coercion of string literal to array literal. For example,
 
- 
checkedArithmeticboolean checkedArithmetic()Whether the implementation uses checked arithmetic. Most SQL dialects use checked arithmetic at runtime: they terminate with a fatal error on overflow.
 
-