> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/gridgain/gridgain-8/reference/sql/sql-conformance.md). # SQL Conformance GridGain and Apache Ignite conformance to the ANSI SQL:1999 (Core) standard, broken down by feature and sub-feature support. Apache Ignite supports most of the major features of ANSI-99 out-of-the-box. The following sections show Ignite compliance to [SQL:1999 (Core)](https://en.wikipedia.org/wiki/SQL:1999). ## `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 PRECISION, 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 is not supported for DEC and NUMERIC, so there are violations for: 7. If a is omitted, then a of 0 (zero) is implicit (6.1 ) 8. NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the and . 9. DECIMAL specifies the data type exact numeric, with the decimal scale specified by the and the implementation-defined decimal precision equal to or greater than the value of the specified . `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 [ ] | CHAR [ ] | CHARACTER VARYING | CHAR VARYING | VARCHAR ``` 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 `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](http://modern-sql.com/feature/table-column-aliases). ## `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 , but could not be checked because of [this issue](https://issues.apache.org/jira/browse/IGNITE-7480). `E061–05` LIKE predicate: ESCAPE clause; There is support for , but could not be checked because of [this issue](https://issues.apache.org/jira/browse/IGNITE-7480). `E061–07` Quantified comparison predicate; Except ALL (see [issue](https://issues.apache.org/jira/browse/IGNITE-5749)). 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](http://www.h2database.com/html/grammar.html#with) 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 GridGain 8 provides partial support for the following sub-features: `E141–03` PRIMARY KEY constraints. See [IGNITE-7479](https://issues.apache.org/jira/browse/IGNITE-7479) `E141–08` NOT NULL inferred on PRIMARY KEY. See [IGNITE-7479](https://issues.apache.org/jira/browse/IGNITE-7479) GridGain 8 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](/docs/connectors/gridgain-8/sql/jdbc/jdbc-driver.md#error-codes) [ODBC Error Codes](/docs/connectors/gridgain-8/sql/odbc/error-codes.md) ## `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 For the DDL statements GridGain supports, see [DDL](/docs/gridgain/gridgain-8/reference/sql/ddl.md). ## `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](https://issues.apache.org/jira/browse/IGNITE-7360). `F051–02` TIME data type (including support of TIME literal) with fractional seconds precision of at least 0. is not supported correctly for TIME data type. Also see [IGNITE-7360](https://issues.apache.org/jira/browse/IGNITE-7360). `F051–03` TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6. is not supported correctly for TIME data type. Also see [IGNITE-7360](https://issues.apache.org/jira/browse/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