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-01

SELECT DISTINCT

Yes

E051-02

GROUP BY clause

Yes

E051-04

GROUP BY can contain columns not in select-list

No

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-01

DECLARE CURSOR

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

No

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-01

SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

Yes

SERIALIZABLE is the same as CONSISTENT READ.

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

F021-04

TABLE_CONSTRAINTS view

Yes

Named TABLECONSTRAINTS.

F021-05

REFERENTIAL _CONSTRAINTS view

No

F021-06

CHECK_CONSTRAINTS view

No

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-03

LEFT OUTER JOIN

Yes

F041-04

RIGHT OUTER JOIN

No

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 TIME ZONE]. Supports optional [WITHOUT TIME ZONE], which changes the behavior (unlike in the SQL standard where WITHOUT TIME ZONE is the default).

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

No

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 CASE

Yes

F261-02

Searched CASE

Yes

F261-03

NULLIF

Yes

F261-04

COALESCE

Yes

F311

Schema definition statement

Mostly

F311-01

CREATE SCHEMA

Yes

F311-02

CREATE TABLE for persistent base tables

Yes

F311-03

CREATE VIEW

Yes

F311-04

CREATE VIEW: WITH CHECK OPTION

No

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.