SQL Standard Compliance

The following table describes NuoDB compliance with core standard SQL-99 features. This list is not comprehensive and is subject to change.

Feature ID

Feature Name

NuoDB Support

Known Limitations When Using NuoDB

B011 Embedded Ada *) Obsolete  
B012 Embedded C *) Obsolete  
B013 Embedded COBOL *) Obsolete  
B014 Embedded Fortran *) Obsolete  
B015 Embedded MUMPS *) Obsolete  
B016 Embedded Pascal *) Obsolete  
B017 Embedded PL/I 1 *) Obsolete  
E011 Numeric data types Partial NUMERIC type is supported but defined differently from the standard.
E011-01 INTEGER and SMALLINT data types (including all spellings) Yes  
E011-02 REAL, DOUBLE PRECISON, and FLOAT data types Yes REAL and FLOAT are treated as DOUBLE.
E011-03 DECIMAL and NUMERIC data types Yes NuoDB treats NUMERIC the same as DECIMAL according to the standard.
E011-04 Arithmetic operators Yes  
E011-05 Numeric comparison Yes  
E011-06 Implicit casting among the numeric data types Yes  
E021 Character data types Yes  
E021-01 CHARACTER data type (including all its spellings) Yes  
E021-02 CHARACTER VARYING data type (including all its spellings) Yes  
E021-03 Character literals Partial Escape characters are not yet implemented.
E021-04 CHARACTER_LENGTH() function Yes  
E021-05 OCTET_LENGTH() function Yes  
E021-06 SUBSTRING() function Yes  
E021-07 Character concatenation Yes  
E021-08 UPPER() and LOWER() functions Yes  
E021-09 TRIM() function Yes  
E021-10 Implicit casting among the character data types Yes  
E021-11 POSITION() function Yes  
E011-12 Character comparison Yes  
E031 Identifiers Yes SQL identifiers, such as table names, column names, and domain names, are limited to 128 characters.
E031-01 Delimited identifiers Yes  
E031-02 Lowercase identifiers Partial You can define lowercase table names by enclosing them in double quotes. However, names of system metadata tables are case insensitive.
E031-03 Trailing underscore Yes  
E051 Basic query specification Mostly  
E051-02 GROUP BY clause Yes  
E051-04 GROUP BY can contain columns not in select-list Yes  
E051-05 Select list items can be renamed Yes  
E051-06 HAVING clause Yes  
E051-07 Qualified * in select list Yes  
E051-08 Correlation names in the FROM clause Not yet implemented  
E051-09 Rename columns in the FROM clause No  
E061 Basic predicates and search conditions Partial Partially implemented.
E061-01 Comparison predicate Yes  
E061-02 BETWEEN predicate Yes  
E061-03 IN predicate with list of values Yes  
E061-04 LIKE predicate Yes  
E061-05 LIKE predicate: ESCAPE clause Yes  
E061-06 NULL predicate Yes  
E061-07 Quantified comparison predicate No  
E061-08 EXISTS predicate Yes  
E061-09 Subqueries in comparison predicate Yes  
E061-11 Subqueries in IN predicate Yes  
E061-12 Subqueries in quantified comparison predicate No  
E061-13 Correlated subqueries Yes  
E061-14 Search condition Yes  
E071 Basic query expressions Partial  
E071-01 UNION DISTINCT table operator Yes  
E071-02 UNION ALL table operator Yes  
E071-03 EXCEPT DISTINCT table operator No  
E071-05 Columns combined by table operators need not have exactly the same data type Yes  
E071-06 Table operators in subqueries Yes  
E081 Basic privileges Partial GRANT is partially implemented.
E081-01 SELECT privilege at the table level Yes  
E081-02 DELETE privilege Yes  
E081-03 INSERT privilege at the table level Yes  
E081-04 UPDATE privilege at the table level Yes  
E081-05 UPDATE privilege at the column level No  
E081-06 REFERENCES privilege at the table level No  
E081-07 REFERENCES privilege at the column level No  
E081-08 WITH GRANT OPTION Not yet implemented GRANT is assumed by default.
E081-09 USAGE privilege No  
E081-10 EXECUTE privilege Yes  
E091 Set functions Not yet implemented  
E091-01 AVG Yes  
E091-02 COUNT Yes  
E091-03 MAX Yes  
E091-04 MIN Yes  
E091-05 SUM Yes  
E091-06 ALL quantifier Yes  
E091-07 DISTINCT quantifier Yes  
E101 Basic data manipulation Yes  
E101-01 INSERT statement Yes  
E101-03 Searched UPDATE statement Yes  
E101-04 Searched DELETE statement Yes  
E111 Single row SELECT statement Partial INTO is not supported.
E121 Basic cursor support Yes  
E121-02 ORDER BY columns need not be in select list Yes  
E121-03 Value expressions in ORDER BY clause Yes  
E121-04 OPEN statement Yes  
E121-06 Positioned UPDATE statement Yes  
E121-07 Positioned DELETE statement Yes  
E121-08 CLOSE statement Yes  
E121-10 FETCH statement: implicit NEXT Yes  
E121-17 WITH HOLD cursors Yes  
E131 Null value support (nulls in place of values) Yes  
E141 Basic integrity constraints Yes  
E141-01 NOT NULL constraints Yes  
E141-02 UNIQUE constraints of NOT NULL columns Yes  
E141-03 PRIMARY KEY constraints Yes  
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default No You can specify FOREIGN but it is ignored.
E141-06 CHECK constraints Yes  
E141-07 Column defaults Yes  
E141-08 NOT NULL inferred on PRIMARY KEY Yes  
E141-10 Names in a foreign key can be specified in any order No You can specify FOREIGN but it is ignored.
E151 Transaction support Yes  
E151-01 COMMIT statement Yes  
E151-02 ROLLBACK statement Yes  
E152 Basic SET TRANSACTION statement Yes  
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses Yes  
E153 Updatable queries with subqueries Yes  
E161 SQL comments using leading double minus Yes  
E171 SQLSTATE support No  
F021 Basic information schema Yes  
F021-01 COLUMNS view Yes  
F021-02 TABLES view Yes  
F021-03 VIEWS view Yes  
F031 Basic schema manipulation Partial  
F031-01 CREATE TABLE statement to create persistent base tables Yes  
F031-02 CREATE VIEW statement Yes  
F031-03 GRANT statement Yes  
F031-04 ALTER TABLE statement: ADD COLUMN clause Yes  
F031-13 DROP TABLE statement: RESTRICT clause Yes  
F031-16 DROP VIEW statement: RESTRICT clause Yes  
F031-19 REVOKE statement: RESTRICT clause No  
F041 Basic joined table Yes  
F041-01 Inner join (but not necessarily the INNER keyword) Yes  
F041-02 INNER keyword Yes  
F041-05 Outer joins can be nested Partial Parentheses are not allowed.
F041-07 The inner table in a left or right outer join can also be used in an inner join Yes  
F041-08 All comparison operators are supported (rather than just =) Yes  
F051 Basic date and time Yes  
F051-01 DATE data type (including DATE literal) Yes  
F051-02 TIME data type (including TIME literal) with fractional seconds precision of 0 Yes Does not support [WITH[OUT] TIME ZONE].
F051-03 TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6 Yes Does not support [WITH[OUT] TIME ZONE].
F051-04 Comparison predicate on DATE, TIME , and TIMESTAMP data types Yes  
F051-05 Explicit CAST between date and time types and character types Yes  
F051-06 CURRENT_DATE Yes  
F051-07 LOCALTIME No Supported with alternative syntax.
F051-08 LOCALTIMESTAMP No Supported with alternative syntax.
F081 UNION and EXCEPT in views Yes UNION is supported. EXCEPT is not supported.
F131 Grouped operations Yes  
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views Yes  
F131-02 Multiple tables supported in queries with grouped views Yes  
F131-03 Set functions supported in queries with grouped views Yes  
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views Yes  
F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views Yes  
F201 CAST function Yes  
F221 Explicit defaults Yes  
F261 CASE expression Yes  
F261-01 Simple CAS Yes  
F261-02 Searched CASE Yes  
F261-03 NULLIF Yes  
F261-04 COALESCE Yes  
F311 Schema definition statement Mostly  
F311-02 CREATE TABLE for persistent base tables Yes  
F311-03 CREATE VIEW Yes  
F311-05 GRANT statement Mostly  
F471 Scalar subquery values Yes  
F481 Expanded NULL predicate Yes  
F501 Features and conformance views No  
F501-01 SQL_FEATURES view No  
F501-02 SQL_SIZING view No  
F501-03 SQL_LANGUAGES view No  
F812 Basic flagging No  
S011 Distinct data types No CREATE TYPE is not supported.
S011-01 USER_DEFINED_TYPES view No  
T321 Basic SQL-invoked routines No  
T321-01 User defined functions with no overloading No  
T321-02 User defined stored procedures Yes  
T321-03 Function invocation No  
T321-04 CALL statement Yes  
T321-05 RETURN statement Yes  
T321-06 ROUTINES view Yes Supported in system metadata table.
T321-07 PARAMETERS view Yes Supported in system metadata table.