SQL Conformance

Apache Ignite supports most of the major features of ANSI-99 out-of-the-box. The following table shows Ignite compliance to SQL:1999 (Core).

Feature ID, Name Support

Feature ID, Name	Support
`E011` Numeric data types	Ignite fully supports the following sub-features: `E011–01` INTEGER and SMALLINT data types (including all spellings) `E011–02` REAL, DOUBLE PRECISON, and FLOAT data types `E011–05` Numeric comparison `E011–06` Implicit casting among the numeric data types Ignite provides partial support for the following sub-features: `E011–03` DECIMAL and NUMERIC data types. Fixed <scale> is not supported for DEC and NUMERIC, so there are violations for: 7) If a <scale> is omitted, then a <scale> of 0 (zero) is implicit (6.1 <data type>) 22) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the <precision> and <scale>. 23) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the <scale> and the implementation-defined decimal precision equal to or greater than the value of the specified <precision>. `E011–04` Arithmetic operator. See issue for feature E011–03
`E021` Character string types	Ignite fully supports the following sub-features: `E021-01` `CHARACTER` data type `E021–02` `CHARACTER VARYING` data type `E021–03` Character literals `E021–04` CHARACTER_LENGTH function `E021–05` OCTET_LENGTH function `E021–06` SUBSTRING function `E021–07` Character concatenation `E021–08` UPPER and LOWER functions `E021–09` TRIM function `E021–10` Implicit casting among the fixed-length and variable-length character string types `E021–11` POSITION function `E021–12` Character comparison Ignite provides partial support for the following sub-features: `E021–01` CHARACTER data type (including all its spellings). <character string type> ::= CHARACTER [ <left paren> <length> <right paren> ] \| CHAR [ <left paren> <length> <right paren> ] \| CHARACTER VARYING <left paren> <length> <right paren> \| CHAR VARYING <left paren> <length> <right paren> \| VARCHAR <left paren> <length> <right paren> <length> is not supported for CHARACTER and CHARACTER VARYING data type. `E021–02` CHARACTER VARYING data type (including all its spellings). See issue for feature E021–01
`E031` Identifiers	Ignite fully supports the following sub-features: `E031–01` Delimited identifiers `E031–02` Lower case identifiers `E031–03` Trailing underscore
`E051` Basic query specification	Ignite fully supports the following sub-features: `E051–01` SELECT DISTINCT `E051–04` GROUP BY can contain columns not in <select-list> `E051–05` Select list items can be renamed `E051–06` HAVING clause `E051–07` Qualified * in select list `E051–08` Correlation names in the FROM clause Ignite does not support the following sub-features: `E051–02` GROUP BY clause; No support for ROLLUP, CUBE, GROUPING SETS. `E051–09` Rename columns in the FROM clause. Some information about support from other products is here.
`E061` Basic predicates and search conditions	Ignite fully supports the following sub-features: `E061–01` Comparison predicate `E061–02` BETWEEN predicate `E061–03` IN predicate with list of values `E061–06` NULL predicate `E061–08` EXISTS predicate `E061–09` Subqueries in comparison predicate `E061–11` Subqueries in IN predicate `E061–13` Correlated subqueries `E061–14` Search condition Ignite provides partial support for the following sub-features: `E061–04` LIKE predicate; There is support for <character like predicate>, but <octet like predicate> could not be checked because of this issue. `E061–05` LIKE predicate: ESCAPE clause; There is support for <character like predicate>, but <octet like predicate> could not be checked because of this issue. `E061–07` Quantified comparison predicate; Except ALL (see issue). Ignite does not support the following sub-feature: `E061–12` Subqueries in quantified comparison predicate.
`E071` Basic query expressions	Ignite provides partial support for the following sub-features: `E071–01` UNION DISTINCT table operator `E071–02` UNION ALL table operator `E071–03` EXCEPT DISTINCT table operator `E071–05` Columns combined via table operators need not have exactly the same data type `E071–06` Table operators in subqueries Note that there is no support for non-recursive WITH clause in H2 and Ignite. According to the H2 docs there is support for recursive WITH clause, but it fails in Ignite.
E081 Basic Privileges	Ignite does not support the following sub-feature: `E081–01` SELECT privilege at the table level `E081–02` DELETE privilege `E081–03` INSERT privilege at the table level `E081–04` UPDATE privilege at the table level `E081–05` UPDATE privilege at the column level `E081–06` REFERENCES privilege at the table `E081–07` REFERENCES privilege at the column `E081–08` WITH GRANT OPTION `E081–09` USAGE privilege `E081–10` EXECUTE privilege
`E091` Set functions	Ignite provides partial support for the following sub-features: `E091–01` AVG `E091–02` COUNT `E091–03` MAX `E091–04` MIN `E091–05` SUM `E091–06` ALL quantifier `E091–07` DISTINCT quantifier Note that there is no support for: GROUPING and ANY (both in H2 and Ignite). EVERY and SOME functions. There is support in H2, but fails in Ignite.
`E101` Basic data manipulation	Ignite fully supports the following sub-features: `E101–03` Searched UPDATE statement `E101–04` Searched DELETE statement Ignite provides partial support for the following sub-features: `E101–01` INSERT statement. No support for DEFAULT values in Ignite. Works in H2.
`E111` Single row SELECT statement	Ignite does not support this feature.
`E121` Basic cursor support	Ignite does not support the following sub-features `E121–01` DECLARE CURSOR `E121–02` ORDER BY columns need not be in select list `E121–03` Value expressions in ORDER BY clause `E121–04` OPEN statement `E121–06` Positioned UPDATE statement `E121–07` Positioned DELETE statement `E121–08` CLOSE statement `E121–10` FETCH statement: implicit NEXT `E121–17` WITH HOLD cursors
`E131` Null value support (nulls in lieu of values)	Ignite fully supports this feature.
`E141` Basic integrity constraints	Ignite fully supports the following sub-feature: `E141–01` NOT NULL constraints YES Ignite provides partial support for the following sub-features: `E141–03` PRIMARY KEY constraints. See IGNITE-7479 `E141–08` NOT NULL inferred on PRIMARY KEY. See IGNITE-7479 Ignite does not support the following sub-features: `E141–02` UNIQUE constraints of NOT NULL columns `E141–04` Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action `E141–06` CHECK constraints `E141–07` Column defaults `E141–10` Names in a foreign key can be specified in any order
`E151` Transaction support	Ignite does not support the following sub-features: `E151–01` COMMIT statement `E151–02` ROLLBACK statement
`E152` Basic SET TRANSACTION statement	Ignite does not support the following sub-features: `E152–01` SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause `E152–02` SET TRANSACTION statement: READ ONLY and READ WRITE clauses
`E153` Updatable queries with subqueries	Ignite fully supports this feature.
`E161` SQL comments using leading double minus	Ignite fully supports this feature.
`E171` SQLSTATE support	Ignite provides partial support for this feature implementing a subset of standard error codes and introducing custom ones. A full list of errors supported by Ignite can be found here: JDBC Error Codes ODBC Error Codes
`E182` Host language Binding (previously "Module Language")	Ignite does not support this feature.
`F021` Basic information schema	Ignite does not support the following sub-features: `F021–01` COLUMNS view `F021–02` TABLES view `F021–03` VIEWS view `F021–04` TABLE_CONSTRAINTS `F021–05` REFERENTIAL_CONSTRAINTS view `F021–06` CHECK_CONSTRAINTS view
`F031` Basic schema manipulation	Ignite fully supports the following feature: `F031–04` ALTER TABLE statement: ADD COLUMN clause Ignite provides partial support for the following sub-feature: `F031–01` CREATE TABLE statement to create persistent base tables. Basic syntax is supported. 'AS' is supported in H2 but not in Ignite. No support for privileges (INSERT, SELECT, UPDATE, DELETE). Ignite does not support the following sub-features: `F031–02` CREATE VIEW statement `F031–03` GRANT statement `F031–13` DROP TABLE statement: RESTRICT clause `F031–16` DROP VIEW statement: RESTRICT clause `F031–19REVOKE` statement: RESTRICT clause A DDL is being actively developed; more features will be supported in upcoming releases.
`F041` Basic joined table	Ignite fully supports the following sub-features: `F041–01` Inner join (but not necessarily the INNER keyword) `F041–02` INNER keyword `F041–03` LEFT OUTER JOIN `F041–04` RIGHT OUTER JOIN `F041–05` Outer joins can be nested `F041–07` The inner table in a left or right outer join can also be used in an inner join `F041–08` All comparison operators are supported (rather than just =)
`F051` Basic date and time	Ignite fully supports the following sub-features: `F051–04` Comparison predicate on DATE, TIME, and TIMESTAMP data types `F051–05` Explicit CAST between datetime types and character string types `F051–06` CURRENT_DATE `F051–07` LOCALTIME `F051–08` LOCALTIMESTAMP Ignite provides partial support for the following sub-features: `F051–01` DATE data type (including support of DATE literal). See IGNITE-7360. `F051–02` TIME data type (including support of TIME literal) with fractional seconds precision of at least 0. <precision> is not supported correctly for TIME data type. Also see IGNITE-7360. `F051–03` TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6. <precision> is not supported correctly for TIME data type. Also see IGNITE-7360.
`F081` UNION and EXCEPT in views	Ignite does not support this feature.
`F131` Grouped operations	Ignite does not support the following sub-features: `F131–01` WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views `F131–02` Multiple tables supported in queries with grouped views `F131–03` Set functions supported in queries with grouped views `F131–04` Subqueries with GROUP BY and HAVING clauses and grouped views `F131–05` Single row SELECT with GROUP BY and HAVING clauses and grouped views
`F181` Multiple module support	Ignite does not support this feature.
`F201` CAST function	Ignite fully supports this feature.
`F221` Explicit defaults	Ignite fully supports this feature.
`F261` CASE expression	Ignite fully supports the following sub-features: `F261–01` Simple CASE `F261–02` Searched CASE `F261–03` NULLIF `F261–04` COALESCE
`F311` Schema definition statement	Ignite does not support the following sub-features: `F311–01` CREATE SCHEMA `F311–02` CREATE TABLE for persistent base tables `F311–03` CREATE VIEW `F311–04` CREATE VIEW: WITH CHECK OPTION `F311–05` GRANT statement
`F471` Scalar subquery values	Ignite fully supports this feature.
`F481` Expanded NULL predicate	Ignite fully supports this feature.
`F501` Features and conformance views	Ignite does not support the following sub-features: `F501–01` SQL_FEATURES view `F501–02` SQL_SIZING view `F501–03` SQL_LANGUAGES view
`F641` Row and table constructors	Ignite does not support this feature. Only single values are supported after SQL logical operations.
`F812` Basic flagging	Ignite does not support this feature. `S011` Distinct data types Ignite does not support the following sub-feature: `S011–01` USER_DEFINED_TYPES view
`T321` Basic SQL-invoked routines	Ignite does not support the following sub-features: `T321–01` User-defined functions with no overloading `T321–02` User-defined stored procedures with no overloading `T321–03` Function invocation `T321–04` CALL statement `T321–05` RETURN statement `T321–06` ROUTINES view `T321–07` PARAMETERS view

E011 Numeric data types

Ignite fully supports the following sub-features:

E011–01 INTEGER and SMALLINT data types (including all spellings)

E011–02 REAL, DOUBLE PRECISON, and FLOAT data types

E011–05 Numeric comparison

E011–06 Implicit casting among the numeric data types

Ignite provides partial support for the following sub-features:

E011–03 DECIMAL and NUMERIC data types. Fixed <scale> is not supported for DEC and NUMERIC, so there are violations for:

7) If a <scale> is omitted, then a <scale> of 0 (zero) is implicit (6.1 <data type>)

22) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the <precision> and <scale>.

23) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the <scale> and the implementation-defined decimal precision equal to or greater than the value of the specified <precision>.

E011–04 Arithmetic operator. See issue for feature E011–03

E021 Character string types

Ignite fully supports the following sub-features:

E021-01 CHARACTER data type

E021–02 CHARACTER VARYING data type

E021–03 Character literals

E021–04 CHARACTER_LENGTH function

E021–05 OCTET_LENGTH function

E021–06 SUBSTRING function

E021–07 Character concatenation

E021–08 UPPER and LOWER functions

E021–09 TRIM function

E021–10 Implicit casting among the fixed-length and variable-length character string types

E021–11 POSITION function

E021–12 Character comparison

Ignite provides partial support for the following sub-features:

E021–01 CHARACTER data type (including all its spellings).

<character string type> ::=
CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]
| CHARACTER VARYING <left paren> <length> <right paren>
| CHAR VARYING <left paren> <length> <right paren>
| VARCHAR <left paren> <length> <right paren>

<length> is not supported for CHARACTER and CHARACTER VARYING data type.

E021–02 CHARACTER VARYING data type (including all its spellings). See issue for feature E021–01

E031 Identifiers

Ignite fully supports the following sub-features:

E031–01 Delimited identifiers

E031–02 Lower case identifiers

E031–03 Trailing underscore

E051 Basic query specification

Ignite fully supports the following sub-features:

E051–01 SELECT DISTINCT

E051–04 GROUP BY can contain columns not in <select-list>

E051–05 Select list items can be renamed

E051–06 HAVING clause

E051–07 Qualified * in select list

E051–08 Correlation names in the FROM clause

Ignite does not support the following sub-features:

E051–02 GROUP BY clause; No support for ROLLUP, CUBE, GROUPING SETS.

E051–09 Rename columns in the FROM clause. Some information about support from other products is here.

E061 Basic predicates and search conditions

Ignite fully supports the following sub-features:

E061–01 Comparison predicate

E061–02 BETWEEN predicate

E061–03 IN predicate with list of values

E061–06 NULL predicate

E061–08 EXISTS predicate

E061–09 Subqueries in comparison predicate

E061–11 Subqueries in IN predicate

E061–13 Correlated subqueries

E061–14 Search condition

Ignite provides partial support for the following sub-features:

E061–04 LIKE predicate; There is support for <character like predicate>, but <octet like predicate> could not be checked because of this issue.

E061–05 LIKE predicate: ESCAPE clause; There is support for <character like predicate>, but <octet like predicate> could not be checked because of this issue.

E061–07 Quantified comparison predicate; Except ALL (see issue).

Ignite does not support the following sub-feature:

E061–12 Subqueries in quantified comparison predicate.

E071 Basic query expressions

Ignite provides partial support for the following sub-features:

E071–01 UNION DISTINCT table operator

E071–02 UNION ALL table operator

E071–03 EXCEPT DISTINCT table operator

E071–05 Columns combined via table operators need not have exactly the same data type

E071–06 Table operators in subqueries

Note that there is no support for non-recursive WITH clause in H2 and Ignite. According to the H2 docs there is support for recursive WITH clause, but it fails in Ignite.

E081 Basic Privileges

Ignite does not support the following sub-feature:

E081–01 SELECT privilege at the table level

E081–02 DELETE privilege

E081–03 INSERT privilege at the table level

E081–04 UPDATE privilege at the table level

E081–05 UPDATE privilege at the column level

E081–06 REFERENCES privilege at the table

E081–07 REFERENCES privilege at the column

E081–08 WITH GRANT OPTION

E081–09 USAGE privilege

E081–10 EXECUTE privilege

E091 Set functions

Ignite provides partial support for the following sub-features:

E091–01 AVG

E091–02 COUNT

E091–03 MAX

E091–04 MIN

E091–05 SUM

E091–06 ALL quantifier

E091–07 DISTINCT quantifier

Note that there is no support for:

GROUPING and ANY (both in H2 and Ignite).
EVERY and SOME functions. There is support in H2, but fails in Ignite.

E101 Basic data manipulation

Ignite fully supports the following sub-features:

E101–03 Searched UPDATE statement

E101–04 Searched DELETE statement

Ignite provides partial support for the following sub-features:

E101–01 INSERT statement. No support for DEFAULT values in Ignite. Works in H2.

E111 Single row SELECT statement

Ignite does not support this feature.

E121 Basic cursor support

Ignite does not support the following sub-features

E121–01 DECLARE CURSOR

E121–02 ORDER BY columns need not be in select list

E121–03 Value expressions in ORDER BY clause

E121–04 OPEN statement

E121–06 Positioned UPDATE statement

E121–07 Positioned DELETE statement

E121–08 CLOSE statement

E121–10 FETCH statement: implicit NEXT

E121–17 WITH HOLD cursors

E131 Null value support (nulls in lieu of values)

Ignite fully supports this feature.

E141 Basic integrity constraints

Ignite fully supports the following sub-feature:

E141–01 NOT NULL constraints YES

Ignite provides partial support for the following sub-features:

E141–03 PRIMARY KEY constraints. See IGNITE-7479

E141–08 NOT NULL inferred on PRIMARY KEY. See IGNITE-7479

Ignite does not support the following sub-features:

E141–02 UNIQUE constraints of NOT NULL columns

E141–04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action

E141–06 CHECK constraints

E141–07 Column defaults

E141–10 Names in a foreign key can be specified in any order

E151 Transaction support

Ignite does not support the following sub-features:

E151–01 COMMIT statement

E151–02 ROLLBACK statement

E152 Basic SET TRANSACTION statement

Ignite does not support the following sub-features:

E152–01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

E152–02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses

E153 Updatable queries with subqueries

Ignite fully supports this feature.

E161 SQL comments using leading double minus

Ignite fully supports this feature.

E171 SQLSTATE support

Ignite provides partial support for this feature implementing a subset of standard error codes and introducing custom ones. A full list of errors supported by Ignite can be found here:

JDBC Error Codes

ODBC Error Codes

E182 Host language Binding (previously "Module Language")

Ignite does not support this feature.

F021 Basic information schema

Ignite does not support the following sub-features:

F021–01 COLUMNS view

F021–02 TABLES view

F021–03 VIEWS view

F021–04 TABLE_CONSTRAINTS

F021–05 REFERENTIAL_CONSTRAINTS view

F021–06 CHECK_CONSTRAINTS view

F031 Basic schema manipulation

Ignite fully supports the following feature:

F031–04 ALTER TABLE statement: ADD COLUMN clause

Ignite provides partial support for the following sub-feature:

F031–01 CREATE TABLE statement to create persistent base tables.

Basic syntax is supported. 'AS' is supported in H2 but not in Ignite. No support for privileges (INSERT, SELECT, UPDATE, DELETE).

Ignite does not support the following sub-features:

F031–02 CREATE VIEW statement

F031–03 GRANT statement

F031–13 DROP TABLE statement: RESTRICT clause

F031–16 DROP VIEW statement: RESTRICT clause

F031–19REVOKE statement: RESTRICT clause

A DDL is being actively developed; more features will be supported in upcoming releases.

F041 Basic joined table

Ignite fully supports the following sub-features:

F041–01 Inner join (but not necessarily the INNER keyword) F041–02 INNER keyword

F041–03 LEFT OUTER JOIN

F041–04 RIGHT OUTER JOIN

F041–05 Outer joins can be nested

F041–07 The inner table in a left or right outer join can also be used in an inner join

F041–08 All comparison operators are supported (rather than just =)

F051 Basic date and time

Ignite fully supports the following sub-features:

F051–04 Comparison predicate on DATE, TIME, and TIMESTAMP data types

F051–05 Explicit CAST between datetime types and character string types

F051–06 CURRENT_DATE

F051–07 LOCALTIME

F051–08 LOCALTIMESTAMP

Ignite provides partial support for the following sub-features:

F051–01 DATE data type (including support of DATE literal). See IGNITE-7360.

F051–02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0. <precision> is not supported correctly for TIME data type. Also see IGNITE-7360.

F051–03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6. <precision> is not supported correctly for TIME data type. Also see IGNITE-7360.

F081 UNION and EXCEPT in views

Ignite does not support this feature.

F131 Grouped operations

Ignite does not support the following sub-features:

F131–01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views

F131–02 Multiple tables supported in queries with grouped views

F131–03 Set functions supported in queries with grouped views

F131–04 Subqueries with GROUP BY and HAVING clauses and grouped views

F131–05 Single row SELECT with GROUP BY and HAVING clauses and grouped views

F181 Multiple module support

Ignite does not support this feature.

F201 CAST function

Ignite fully supports this feature.

F221 Explicit defaults

Ignite fully supports this feature.

F261 CASE expression

Ignite fully supports the following sub-features:

F261–01 Simple CASE

F261–02 Searched CASE

F261–03 NULLIF

F261–04 COALESCE

F311 Schema definition statement

Ignite does not support the following sub-features:

F311–01 CREATE SCHEMA

F311–02 CREATE TABLE for persistent base tables

F311–03 CREATE VIEW

F311–04 CREATE VIEW: WITH CHECK OPTION

F311–05 GRANT statement

F471 Scalar subquery values

Ignite fully supports this feature.

F481 Expanded NULL predicate

Ignite fully supports this feature.

F501 Features and conformance views

Ignite does not support the following sub-features:

F501–01 SQL_FEATURES view

F501–02 SQL_SIZING view

F501–03 SQL_LANGUAGES view

F641 Row and table constructors

Ignite does not support this feature. Only single values are supported after SQL logical operations.

F812 Basic flagging

Ignite does not support this feature.

S011 Distinct data types

Ignite does not support the following sub-feature:

S011–01 USER_DEFINED_TYPES view

T321 Basic SQL-invoked routines

Ignite does not support the following sub-features:

T321–01 User-defined functions with no overloading

T321–02 User-defined stored procedures with no overloading

T321–03 Function invocation

T321–04 CALL statement

T321–05 RETURN statement

T321–06 ROUTINES view

T321–07 PARAMETERS view

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 20, 2024