ANSI SQL-92 Conformity
Note
Conformance is a work in progress and this information represents the current state in order to provide transparent information about progress and capability.
For a system to attest to supporting SQL it should have good conformance to the ANSI SQL-92 standard. This standard is also known as ISO/IEC 9075:1992.
Opteryx is not a DBMS, so only aims for confirmity to the subset of SQL-92 for to featureset it supports - this is primarily related to SELECT
statements.
Function | Description | Support |
---|---|---|
E011 | Numeric data types | partial |
E011-01 | INTEGER and SMALLINT data types |
partial |
E011-02 | REAL , DOUBLE PRECISION , and FLOAT data types |
partial |
E011-03 | DECIMAL and NUMERIC data |
yes |
E011-04 | Arithmetic | yes |
E011-05 | Numeric comparison | yes |
E011-06 | Implicit casting among the numeric data types | yes |
E021 | Character string types | unknown |
E021-01 | CHARACTER data type |
unknown |
E021-02 | CHARACTER VARYING data type |
unknown |
E021-03 | Character literals | unknown |
E021-04 | CHARACTER_LENGTH function |
no |
E021-05 | OCTET_LENGTH |
no |
E021-06 | SUBSTRING function |
partial |
E021-07 | Character concatenation | yes |
E021-08 | UPPER and LOWER functions |
yes |
E021-09 | TRIM function |
partial |
E021-10 | Implicit casting among the fixed-length and variable-length character string types | unknown |
E021-11 | POSITION function |
partial |
E021-12 | Character comparison | yes |
E031 | Identifiers | unknown |
E031-01 | Delimited identifiers | yes |
E031-02 | Lower case identifiers | unknown |
E031-03 | Trailing underscore | unknown |
E051 | Basic query specification | unknown |
E051-01 | SELECT DISTINCT |
yes |
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 |
unknown |
E051-09 | Rename columns in the FROM clause |
unknown |
E061 | Basic predicates and search conditions | unknown |
E061-01 | Comparison predicate | unknown |
E061-02 | BETWEEN predicate |
unknown |
E061-03 | IN predicate with list of values |
unknown |
E061-04 | LIKE predicate |
yes |
E061-05 | LIKE predicate: ESCAPE clause |
unknown |
E061-06 | NULL predicate |
unknown |
E061-07 | Quantified comparison predicate | unknown |
E061-08 | EXISTS predicate |
no |
E061-09 | Subqueries in comparison predicate | unknown |
E061-11 | Subqueries in IN predicate |
unknown |
E061-12 | Subqueries in quantified comparison predicate | unknown |
E061-13 | Correlated subqueries | unknown |
E061-14 | Search condition | unknown |
E071 | Basic query expressions | unknown |
E071-01 | UNION DISTINCT table operator |
no |
E071-02 | UNION ALL table operator |
partial |
E071-03 | EXCEPT DISTINCT table operator |
no |
E071-05 | Columns combined via table operators need not have exactly the same data type | partial |
E071-06 | Table operators in subqueries | unknown |
E081 | Basic Privileges | unknown |
E081-01 | SELECT privilege at the table level |
unknown |
E081-02 | DELETE privilege |
n/a |
E081-03 | INSERT privilege at the table level |
n/a |
E081-04 | UPDATE privilege at the table level |
n/a |
E081-05 | UPDATE privilege at the column level |
n/a |
E081-06 | REFERENCES privilege at the table level |
n/a |
E081-07 | REFERENCES privilege at the column level |
n/a |
E081-08 | WITH GRANT OPTION |
n/a |
E081-09 | USAGE privilege |
n/a |
E081-10 | EXECUTE privilege |
n/a |
E091 | Set functions | partial |
E091-01 | AVG |
yes |
E091-02 | COUNT |
yes |
E091-03 | MAX |
yes |
E091-04 | MIN |
yes |
E091-05 | SUM |
yes |
E091-06 | ALL quantifier |
unknown |
E091-07 | DISTINCT quantifier |
yes |
E101 | Basic data manipulation | n/a |
E101-01 | INSERT statement |
n/a |
E101-03 | Searched UPDATE statement |
n/a |
E101-04 | Searched DELETE statement |
n/a |
E111 | Single row SELECT statement | unknown |
E121 | Basic cursor support | unknown |
E121-01 | DECLARE CURSOR |
no |
E121-02 | ORDER BY columns need not be in select |
yes |
E121-03 | Value expressions in ORDER BY clause |
unknown |
E121-04 | OPEN statement |
no |
E121-06 | Positioned UPDATE statement |
no |
E121-07 | Positioned DELETE statement |
no |
E121-08 | CLOSE statement |
no |
E121-10 | FETCH statement: implicit NEXT |
no |
E121-17 | WITH HOLD cursors |
no |
E131 | Null value support | yes |
E141 | Basic integrity constraints | unknown |
E141-01 | NOT NULL constraints |
n/a |
E141-02 | UNIQUE constraints of NOT NULL |
n/a |
E141-03 | PRIMARY KEY constraints |
n/a |
E141-04 | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action |
n/a |
E141-06 | CHECK constraints |
n/a |
E141-07 | Column defaults | unknown |
E141-08 | NOT NULL inferred on PRIMARY KEY |
n/a |
E141-10 | Names in a foreign key can be specified in any order | n/a |
E151 | Transaction support | n/a |
E151-01 | COMMIT statement |
n/a |
E151-02 | ROLLBACK statement |
n/a |
E152 | Basic SET TRANSACTION statement |
n/a |
E152-01 | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause |
n/a |
E152-02 | SET TRANSACTION statement: READ ONLY and READ WRITE clauses |
n/a |
E+ | Other | unknown |
E153 | Updatable queries with subqueries | unknown |
E161 | SQL comments using leading double minus | yes |
E171 | SQLSTATE support |
unknown |
E182 | Host language binding | unknown |
F021 | Basic information schema | unknown |
F021-01 | COLUMNS view |
unknown |
F021-02 | TABLES view |
unknown |
F021-03 | VIEWS view |
unknown |
F021-04 | TABLE_CONSTRAINTS view |
unknown |
F021-05 | REFERENTIAL_CONSTRAINTS view |
unknown |
F021-06 | CHECK_CONSTRAINTS view |
unknown |
F031 | Basic schema manipulation | unknown |
F031-01 | CREATE TABLE statement to create persistent base tables |
n/a |
F031-02 | CREATE VIEW statement |
n/a |
F031-03 | GRANT statement |
unknown |
F031-04 | ALTER TABLE statement: ADD COLUMN clause |
n/a |
F031-13 | DROP TABLE statement: RESTRICT clause |
n/a |
F031-16 | DROP VIEW statement: RESTRICT clause |
n/a |
F031-19 | REVOKE statement: RESTRICT clause |
unknown |
F041 | Basic joined table | unknown |
F041-01 | Inner join (but not necessarily the INNER keyword) |
unknown |
F041-02 | INNER keyword |
unknown |
F041-03 | LEFT OUTER JOIN |
unknown |
F041-04 | RIGHT OUTER JOIN |
unknown |
F041-05 | Outer joins can be nested | unknown |
F041-07 | The inner table in a left or right outer join can also be used in an inner join | unknown |
F041-08 | All comparison operators are supported (rather than just = ) |
unknown |
F051 | Basic date and time | yes |
F051-01 | DATE data type (including support of DATE literal) |
unknown |
F051-02 | TIME data type (including support of TIME literal) with fractional seconds |
unknown |
F051-03 | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 |
yes |
F051-04 | Comparison predicate on DATE , TIME , and TIMESTAMP data types |
partial |
F051-05 | Explicit CAST between datetime types and character string types |
unknown |
F051-06 | CURRENT_DATE |
yes |
F051-07 | LOCALTIME |
yes |
F051-08 | LOCALTIMESTAMP |
unknown |
F081 | UNION and EXCEPT in views |
unknown |
F131 | Grouped operations | unknown |
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 | unknown |
F131-04 | Subqueries with GROUP BY and HAVING clauses and grouped views |
unknown |
F131-05 | Single row SELECT with GROUP BY and HAVING clauses and grouped views |
unknown |
F181 | Multiple module support | unknown |
F201 | CAST function |
yes |
F221 | Explicit defaults | unknown |
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 | n/a |
F311-01 | CREATE SCHEMA |
n/a |
F311-02 | CREATE TABLE |
n/a |
F311-03 | CREATE VIEW |
n/a |
F311-04 | CREATE VIEW : WITH CHECK OPTION |
n/a |
F311-05 | GRANT statement |
n/a |
F471 | Scalar subquery values | unknown |
F481 | Expanded NULL predicate |
unknown |
F501 | Features and conformance views | unknown |
F501-01 | SQL_FEATURES view |
unknown |
F501-02 | SQL_SIZING view |
unknown |
F501-03 | SQL_LANGUAGES view |
unknown |
F812 | Basic flagging | unknown |
S011 | Distinct data types | unknown |
S011-01 | USER_DEFINED_TYPES view |
no |
T321 | Basic SQL-invoked routines | unknown |
T321-01 | User-defined functions with no overloading | unknown |
T321-02 | User-defined stored procedures with no overloading | unknown |
T321-03 | Function invocation | unknown |
T321-04 | CALL statement |
unknown |
T321-05 | RETURN statement |
unknown |
T321-06 | ROUTINES view |
unknown |
T321-07 | PARAMETERS view |
unknown |
T631 | IN predicate with one list element |
unknown |
Support statuses in this table:
- yes The feature is supported and conformance is part of the test suite.
- no The feature is not supported.
- partial Some features are supported.
- n/a The feature relates to a feature not supported by Opteryx.
- unknown No test exists to confirm conformance.