SQL Conformance
This chapter attempts to outline to what extent QHB conforms to the current SQL standard. The following information is not a full statement of conformance, but it presents the main topics in as much detail as is both reasonable and useful for users.
The formal name of the SQL standard is ISO/IEC 9075 “Database Language SQL”. A revised version of the standard is released from time to time; the most recent update appearing in 2023. The 2023 version is referred to as ISO/IEC 9075:2023, or simply as SQL:2023. The versions prior to that were SQL:2016, SQL:2011, SQL:2008, SQL:2006, SQL:2003, SQL:1999, and SQL-92. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. QHB development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. Many of the features required by the SQL standard are supported, though sometimes with slightly differing syntax or function. Further moves towards conformance can be expected over time.
SQL-92 defined three feature sets for conformance: Entry, Intermediate, and Full. Most database management systems claiming SQL standard conformance were conforming at only the Entry level, since the entire set of features in the Intermediate and Full levels was either too voluminous or in conflict with legacy behaviors.
Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the ineffectively broad three levels found in SQL-92. A large subset of these features represents the “Core” features, which every conforming SQL implementation must supply. The rest of the features are purely optional.
The standard is split into a number of parts also known by a shorthand name.
-
ISO/IEC 9075-1 Framework (SQL/Framework)
-
ISO/IEC 9075-2 Foundation (SQL/Foundation)
-
ISO/IEC 9075-3 Call Level Interface (SQL/CLI)
-
ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM)
-
ISO/IEC 9075-9 Management of External Data (SQL/MED)
-
ISO/IEC 9075-10 Object Language Bindings (SQL/OLB)
-
ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata)
-
ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT)
-
ISO/IEC 9075-14 XML-related specifications (SQL/XML)
-
ISO/IEC 9075-15 Multi-dimensional arrays (SQL/MDA)
-
ISO/IEC 9075-16 Property Graph Queries (SQL/PGQ)
Note that some part numbers are not (or no longer) used.
The QHB core covers parts 1, 2, 9, 11, and 14. Part 3 is covered by the ODBC driver, and part 13 is covered by the PL/Java plug-in, but exact conformance is currently not being verified for these components. There are currently no implementations of parts 4, 10, 15, and 16 for QHB.
QHB supports most of the major features of SQL:2023. Out of 177 mandatory features required for full Core conformance, QHB conforms to at least 170. In addition, there is a long list of supported optional features. It might be worth noting that at the time of writing, no current version of any database management system claims full conformance to Core SQL:2023.
In the following two sections, we provide a list of those features that QHB supports, followed by a list of the features defined in SQL:2023 which are not yet supported in QHB. Both of these lists are approximate: There might be minor details that are nonconforming for a feature that is listed as supported, and large parts of an unsupported feature might in fact be implemented. The main body of the documentation always contains the most accurate information about what does and does not work.
Note
Feature codes containing a hyphen are subfeatures. Therefore, if a particular subfeature is not supported, the main feature is listed as unsupported even if some other subfeatures are supported.
Supported Features
| Identifier | Core? | Description | Comment |
|---|---|---|---|
| B012 | Embedded C | ||
| B021 | Direct SQL | ||
| B128 | Routine language SQL | ||
| E011 | Core | Numeric data types | |
| E011-01 | Core | INTEGER and SMALLINT data types | |
| E011-02 | Core | REAL, DOUBLE PRECISION, and FLOAT data types | |
| E011-03 | Core | DECIMAL and NUMERIC data types | |
| E011-04 | Core | Arithmetic operators | |
| E011-05 | Core | Numeric comparison | |
| E011-06 | Core | Implicit casting among the numeric data types | |
| E021 | Core | Character data types | |
| E021-01 | Core | CHARACTER data type | |
| E021-02 | Core | CHARACTER VARYING data type | |
| E021-03 | Core | Character literals | |
| E021-04 | Core | CHARACTER_LENGTH function | trims trailing spaces from CHARACTER values before counting |
| E021-05 | Core | OCTET_LENGTH function | |
| E021-06 | Core | SUBSTRING function | |
| E021-07 | Core | Character concatenation | |
| E021-08 | Core | UPPER and LOWER functions | |
| E021-09 | Core | TRIM function | |
| E021-10 | Core | Implicit casting among the character string types | |
| E021-11 | Core | POSITION function | |
| E021-12 | Core | Character comparison | |
| E031 | Core | Identifiers | |
| E031-01 | Core | Delimited identifiers | |
| E031-02 | Core | Lower case identifiers | |
| E031-03 | Core | Trailing underscore | |
| E051 | Core | Basic query specification | |
| E051-01 | Core | SELECT DISTINCT | |
| E051-02 | Core | GROUP BY clause | |
| E051-04 | Core | GROUP BY can contain columns not in <select list> | |
| E051-05 | Core | Select list items can be renamed | |
| E051-06 | Core | HAVING clause | |
| E051-07 | Core | Qualified * in select list | |
| E051-08 | Core | Correlation names in the FROM clause | |
| E051-09 | Core | Rename columns in the FROM clause | |
| E061 | Core | Basic predicates and search conditions | |
| E061-01 | Core | Comparison predicate | |
| E061-02 | Core | BETWEEN predicate | |
| E061-03 | Core | IN predicate with list of values | |
| E061-04 | Core | LIKE predicate | |
| E061-05 | Core | LIKE predicate ESCAPE clause | |
| E061-06 | Core | NULL predicate | |
| E061-07 | Core | Quantified comparison predicate | |
| E061-08 | Core | EXISTS predicate | |
| E061-09 | Core | Subqueries in comparison predicate | |
| E061-11 | Core | Subqueries in IN predicate | |
| E061-12 | Core | Subqueries in quantified comparison predicate | |
| E061-13 | Core | Correlated subqueries | |
| E061-14 | Core | Search condition | |
| E071 | Core | Basic query expressions | |
| E071-01 | Core | UNION DISTINCT table operator | |
| E071-02 | Core | UNION ALL table operator | |
| E071-03 | Core | EXCEPT DISTINCT table operator | |
| E071-05 | Core | Columns combined via table operators need not have exactly the same data type | |
| E071-06 | Core | Table operators in subqueries | |
| E081 | Core | Basic Privileges | |
| E081-01 | Core | SELECT privilege | |
| E081-02 | Core | DELETE privilege | |
| E081-03 | Core | INSERT privilege at the table level | |
| E081-04 | Core | UPDATE privilege at the table level | |
| E081-05 | Core | UPDATE privilege at the column level | |
| E081-06 | Core | REFERENCES privilege at the table level | |
| E081-07 | Core | REFERENCES privilege at the column level | |
| E081-08 | Core | WITH GRANT OPTION | |
| E081-09 | Core | USAGE privilege | |
| E081-10 | Core | EXECUTE privilege | |
| E091 | Core | Set functions | |
| E091-01 | Core | AVG | |
| E091-02 | Core | COUNT | |
| E091-03 | Core | MAX | |
| E091-04 | Core | MIN | |
| E091-05 | Core | SUM | |
| E091-06 | Core | ALL quantifier | |
| E091-07 | Core | DISTINCT quantifier | |
| E101 | Core | Basic data manipulation | |
| E101-01 | Core | INSERT statement | |
| E101-03 | Core | Searched UPDATE statement | |
| E101-04 | Core | Searched DELETE statement | |
| E111 | Core | Single row SELECT statement | |
| E121 | Core | Basic cursor support | |
| E121-01 | Core | DECLARE CURSOR | |
| E121-02 | Core | ORDER BY columns need not be in select list | |
| E121-03 | Core | Value expressions in ORDER BY clause | |
| E121-04 | Core | OPEN statement | |
| E121-06 | Core | Positioned UPDATE statement | |
| E121-07 | Core | Positioned DELETE statement | |
| E121-08 | Core | CLOSE statement | |
| E121-10 | Core | FETCH statement implicit NEXT | |
| E121-17 | Core | WITH HOLD cursors | |
| E131 | Core | Null value support (nulls in lieu of values) | |
| E141 | Core | Basic integrity constraints | |
| E141-01 | Core | NOT NULL constraints | |
| E141-02 | Core | UNIQUE constraints of NOT NULL columns | |
| E141-03 | Core | PRIMARY KEY constraints | |
| E141-04 | Core | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action | |
| E141-06 | Core | CHECK constraints | |
| E141-07 | Core | Column defaults | |
| E141-08 | Core | NOT NULL inferred on PRIMARY KEY | |
| E141-10 | Core | Names in a foreign key can be specified in any order | |
| E151 | Core | Transaction support | |
| E151-01 | Core | COMMIT statement | |
| E151-02 | Core | ROLLBACK statement | |
| E152 | Core | Basic SET TRANSACTION statement | |
| E152-01 | Core | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause | |
| E152-02 | Core | SET TRANSACTION statement: READ ONLY and READ WRITE clauses | |
| E153 | Core | Updatable queries with subqueries | |
| E161 | Core | SQL comments using leading double minus | |
| E171 | Core | SQLSTATE support | |
| E182 | Core | Host language binding | |
| F021 | Core | Basic information schema | |
| F021-01 | Core | COLUMNS view | |
| F021-02 | Core | TABLES view | |
| F021-03 | Core | VIEWS view | |
| F021-04 | Core | TABLE_CONSTRAINTS view | |
| F021-05 | Core | REFERENTIAL_CONSTRAINTS view | |
| F021-06 | Core | CHECK_CONSTRAINTS view | |
| F031 | Core | Basic schema manipulation | |
| F031-01 | Core | CREATE TABLE statement to create persistent base tables | |
| F031-02 | Core | CREATE VIEW statement | |
| F031-03 | Core | GRANT statement | |
| F031-04 | Core | ALTER TABLE statement: ADD COLUMN clause | |
| F031-13 | Core | DROP TABLE statement: RESTRICT clause | |
| F031-16 | Core | DROP VIEW statement: RESTRICT clause | |
| F031-19 | Core | REVOKE statement: RESTRICT clause | |
| F032 | CASCADE drop behavior | ||
| F033 | ALTER TABLE statement: DROP COLUMN clause | ||
| F034 | Extended REVOKE statement | ||
| F034-01 | REVOKE statement performed by other than the owner of a schema object | ||
| F034-02 | REVOKE statement: GRANT OPTION FOR clause | ||
| F034-03 | REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION | ||
| F041 | Core | Basic joined table | |
| F041-01 | Core | Inner join (but not necessarily the INNER keyword) | |
| F041-02 | Core | INNER keyword | |
| F041-03 | Core | LEFT OUTER JOIN | |
| F041-04 | Core | RIGHT OUTER JOIN | |
| F041-05 | Core | Outer joins can be nested | |
| F041-07 | Core | The inner table in a left or right outer join can also be used in an inner join | |
| F041-08 | Core | All comparison operators are supported (rather than just =) | |
| F051 | Core | Basic date and time | |
| F051-01 | Core | DATE data type (including support of DATE literal) | |
| F051-02 | Core | TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 | |
| F051-03 | Core | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 | |
| F051-04 | Core | Comparison predicate on DATE, TIME, and TIMESTAMP data types | |
| F051-05 | Core | Explicit CAST between datetime types and character string types | |
| F051-06 | Core | CURRENT_DATE | |
| F051-07 | Core | LOCALTIME | |
| F051-08 | Core | LOCALTIMESTAMP | |
| F052 | Intervals and datetime arithmetic | ||
| F053 | OVERLAPS predicate | ||
| F081 | Core | UNION and EXCEPT in views | |
| F111 | Isolation levels other than SERIALIZABLE | ||
| F111-01 | READ UNCOMMITTED isolation level | ||
| F111-02 | READ COMMITTED isolation level | ||
| F111-03 | REPEATABLE READ isolation level | ||
| F131 | Core | Grouped operations | |
| F131-01 | Core | WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views | |
| F131-02 | Core | Multiple tables supported in queries with grouped views | |
| F131-03 | Core | Set functions supported in queries with grouped views | |
| F131-04 | Core | Subqueries with GROUP BY and HAVING clauses and grouped views | |
| F131-05 | Core | Single row SELECT with GROUP BY and HAVING clauses and grouped views | |
| F171 | Multiple schemas per user | ||
| F181 | Core | Multiple module support | |
| F191 | Referential delete actions | ||
| F200 | TRUNCATE TABLE statement | ||
| F201 | Core | CAST function | |
| F202 | TRUNCATE TABLE: identity column restart option | ||
| F221 | Core | Explicit defaults | |
| F222 | INSERT statement: DEFAULT VALUES clause | ||
| F231 | Privilege tables | ||
| F231-01 | TABLE_PRIVILEGES view | ||
| F231-02 | COLUMN_PRIVILEGES view | ||
| F231-03 | USAGE_PRIVILEGES view | ||
| F251 | Domain support | ||
| F261 | Core | CASE expression | |
| F261-01 | Core | Simple CASE | |
| F261-02 | Core | Searched CASE | |
| F261-03 | Core | NULLIF | |
| F261-04 | Core | COALESCE | |
| F262 | Extended CASE expression | ||
| F271 | Compound character literals | ||
| F281 | LIKE enhancements | ||
| F292 | UNIQUE null treatment | SQL:202x draft | |
| F302 | INTERSECT table operator | ||
| F302-01 | INTERSECT DISTINCT table operator | ||
| F302-02 | INTERSECT ALL table operator | ||
| F304 | EXCEPT ALL table operator | ||
| F311 | Core | Schema definition statement | |
| F311-01 | Core | CREATE SCHEMA | |
| F311-02 | Core | CREATE TABLE for persistent base tables | |
| F311-03 | Core | CREATE VIEW | |
| F311-04 | Core | CREATE VIEW: WITH CHECK OPTION | |
| F311-05 | Core | GRANT statement | |
| F312 | MERGE statement | ||
| F313 | Enhanced MERGE statement | ||
| F314 | MERGE statement with DELETE branch | ||
| F321 | User authorization | ||
| F341 | Usage tables | ||
| F361 | Subprogram support | ||
| F381 | Extended schema manipulation | ||
| F381-01 | ALTER TABLE statement: ALTER COLUMN clause | ||
| F381-02 | ALTER TABLE statement: ADD CONSTRAINT clause | ||
| F381-03 | ALTER TABLE statement: DROP CONSTRAINT clause | ||
| F382 | Alter column data type | ||
| F383 | Set column not null clause | ||
| F384 | Drop identity property clause | ||
| F385 | Drop column generation expression clause | ||
| F386 | Set identity column generation clause | ||
| F391 | Long identifiers | ||
| F392 | Unicode escapes in identifiers | ||
| F393 | Unicode escapes in literals | ||
| F394 | Optional normal form specification | ||
| F401 | Extended joined table | ||
| F401-01 | NATURAL JOIN | ||
| F401-02 | FULL OUTER JOIN | ||
| F401-04 | CROSS JOIN | ||
| F402 | Named column joins for LOBs, arrays, and multisets | ||
| F404 | Range variable for common column names | ||
| F411 | Time zone specification | differences regarding literal interpretation | |
| F421 | National character | ||
| F431 | Read-only scrollable cursors | ||
| F431-01 | FETCH with explicit NEXT | ||
| F431-02 | FETCH FIRST | ||
| F431-03 | FETCH LAST | ||
| F431-04 | FETCH PRIOR | ||
| F431-05 | FETCH ABSOLUTE | ||
| F431-06 | FETCH RELATIVE | ||
| F441 | Extended set function support | ||
| F442 | Mixed column references in set functions | ||
| F471 | Core | Scalar subquery values | |
| F481 | Core | Expanded NULL predicate | |
| F491 | Constraint management | ||
| F501 | Core | Features and conformance views | |
| F501-01 | Core | SQL_FEATURES view | |
| F501-02 | Core | SQL_SIZING view | |
| F502 | Enhanced documentation tables | ||
| F531 | Temporary tables | ||
| F555 | Enhanced seconds precision | ||
| F561 | Full value expressions | ||
| F571 | Truth value tests | ||
| F591 | Derived tables | ||
| F611 | Indicator data types | ||
| F641 | Row and table constructors | ||
| F651 | Catalog name qualifiers | ||
| F661 | Simple tables | ||
| F672 | Retrospective check constraints | ||
| F690 | Collation support | but no character set support | |
| F692 | Extended collation support | ||
| F701 | Referential update actions | ||
| F711 | ALTER domain | ||
| F731 | INSERT column privileges | ||
| F751 | View CHECK enhancements | ||
| F761 | Session management | ||
| F762 | CURRENT_CATALOG | ||
| F763 | CURRENT_SCHEMA | ||
| F771 | Connection management | ||
| F781 | Self-referencing operations | ||
| F791 | Insensitive cursors | ||
| F801 | Full set function | ||
| F850 | Top-level <order by clause> in <query expression> | ||
| F851 | <order by clause> in subqueries | ||
| F852 | Top-level <order by clause> in views | ||
| F855 | Nested <order by clause> in <query expression> | ||
| F856 | Nested <fetch first clause> in <query expression> | ||
| F857 | Top-level <fetch first clause> in <query expression> | ||
| F858 | <fetch first clause> in subqueries | ||
| F859 | Top-level <fetch first clause> in views | ||
| F860 | <fetch first row count> in <fetch first clause> | ||
| F861 | Top-level <result offset clause> in <query expression> | ||
| F862 | <result offset clause> in subqueries | ||
| F863 | Nested <result offset clause> in <query expression> | ||
| F864 | Top-level <result offset clause> in views | ||
| F865 | <offset row count> in <result offset clause> | ||
| F867 | FETCH FIRST clause: WITH TIES option | ||
| S071 | SQL paths in function and type name resolution | ||
| S091-01 | Arrays of built-in data types | ||
| S091-03 | Array expressions | ||
| S092 | Arrays of user-defined types | ||
| S095 | Array constructors by query | ||
| S096 | Optional array bounds | ||
| S098 | ARRAY_AGG | ||
| S111 | ONLY in query expressions | ||
| S201 | SQL-invoked routines on arrays | ||
| S201-01 | Array parameters | ||
| S201-02 | Array as result type of functions | ||
| S211 | User-defined cast functions | ||
| S301 | Enhanced UNNEST | ||
| S404 | TRIM_ARRAY | ||
| T031 | BOOLEAN data type | ||
| T071 | BIGINT data type | ||
| T121 | WITH (excluding RECURSIVE) in query expression | ||
| T122 | WITH (excluding RECURSIVE) in subquery | ||
| T131 | Recursive query | ||
| T132 | Recursive query in subquery | ||
| T133 | Enhanced cycle mark values | SQL:202x draft | |
| T141 | SIMILAR predicate | ||
| T151 | DISTINCT predicate | ||
| T152 | DISTINCT predicate with negation | ||
| T171 | LIKE clause in table definition | ||
| T172 | AS subquery clause in table definition | ||
| T173 | Extended LIKE clause in table definition | ||
| T174 | Identity columns | ||
| T177 | Sequence generator support: simple restart option | ||
| T178 | Identity columns: simple restart option | ||
| T191 | Referential action RESTRICT | ||
| T201 | Comparable data types for referential constraints | ||
| T211-01 | Triggers activated on UPDATE, INSERT, or DELETE of one base table | ||
| T211-02 | BEFORE triggers | ||
| T211-03 | AFTER triggers | ||
| T211-04 | FOR EACH ROW triggers | ||
| T211-05 | Ability to specify a search condition that must be true before the trigger is invoked | ||
| T211-07 | TRIGGER privilege | ||
| T212 | Enhanced trigger capability | ||
| T213 | INSTEAD OF triggers | ||
| T241 | START TRANSACTION statement | ||
| T261 | Chained transactions | ||
| T271 | Savepoints | ||
| T281 | SELECT privilege with column granularity | ||
| T285 | Enhanced derived column names | ||
| T312 | OVERLAY function | ||
| T321-01 | Core | User-defined functions with no overloading | |
| T321-02 | Core | User-defined stored procedures with no overloading | |
| T321-03 | Core | Function invocation | |
| T321-04 | Core | CALL statement | |
| T321-05 | Core | RETURN statement | |
| T321-06 | Core | ROUTINES view | |
| T321-07 | Core | PARAMETERS view | |
| T323 | Explicit security for external routines | ||
| T325 | Qualified SQL parameter references | ||
| T331 | Basic roles | ||
| T332 | Extended roles | ||
| T341 | Overloading of SQL-invoked functions and procedures | ||
| T351 | Bracketed SQL comments (/*...*/ comments) | ||
| T431 | Extended grouping capabilities | ||
| T432 | Nested and concatenated GROUPING SETS | ||
| T433 | Multiargument GROUPING function | ||
| T434 | GROUP BY DISTINCT | ||
| T441 | ABS and MOD functions | ||
| T461 | Symmetric BETWEEN predicate | ||
| T491 | LATERAL derived table | ||
| T501 | Enhanced EXISTS predicate | ||
| T521 | Named arguments in CALL statement | ||
| T523 | Default values for INOUT parameters of SQL-invoked procedures | ||
| T524 | Named arguments in routine invocations other than a CALL statement | ||
| T525 | Default values for parameters of SQL-invoked functions | ||
| T551 | Optional key words for default syntax | ||
| T581 | Regular expression substring function | ||
| T591 | UNIQUE constraints of possibly null columns | ||
| T611 | Elementary OLAP operations | ||
| T612 | Advanced OLAP operations | ||
| T613 | Sampling | ||
| T614 | NTILE function | ||
| T615 | LEAD and LAG functions | ||
| T617 | FIRST_VALUE and LAST_VALUE function | ||
| T620 | WINDOW clause: GROUPS option | ||
| T621 | Enhanced numeric functions | ||
| T622 | Trigonometric functions | ||
| T623 | General logarithm functions | ||
| T624 | Common logarithm functions | ||
| T631 | Core | IN predicate with one list element | |
| T651 | SQL-schema statements in SQL routines | ||
| T653 | SQL-schema statements in external routines | ||
| T655 | Cyclically dependent routines | ||
| T831 | SQL/JSON path language: strict mode | ||
| T832 | SQL/JSON path language: item method | ||
| T833 | SQL/JSON path language: multiple subscripts | ||
| T834 | SQL/JSON path language: wildcard member accessor | ||
| T835 | SQL/JSON path language: filter expressions | ||
| T836 | SQL/JSON path language: starts with predicate | ||
| T837 | SQL/JSON path language: regex_like predicate | ||
| X010 | XML type | ||
| X011 | Arrays of XML type | ||
| X014 | Attributes of XML type | ||
| X016 | Persistent XML values | ||
| X020 | XMLConcat | ||
| X031 | XMLElement | ||
| X032 | XMLForest | ||
| X034 | XMLAgg | ||
| X035 | XMLAgg: ORDER BY option | ||
| X036 | XMLComment | ||
| X037 | XMLPI | ||
| X040 | Basic table mapping | ||
| X041 | Basic table mapping: nulls absent | ||
| X042 | Basic table mapping: null as nil | ||
| X043 | Basic table mapping: table as forest | ||
| X044 | Basic table mapping: table as element | ||
| X045 | Basic table mapping: with target namespace | ||
| X046 | Basic table mapping: data mapping | ||
| X047 | Basic table mapping: metadata mapping | ||
| X048 | Basic table mapping: base64 encoding of binary strings | ||
| X049 | Basic table mapping: hex encoding of binary strings | ||
| X050 | Advanced table mapping | ||
| X051 | Advanced table mapping: nulls absent | ||
| X052 | Advanced table mapping: null as nil | ||
| X053 | Advanced table mapping: table as forest | ||
| X054 | Advanced table mapping: table as element | ||
| X055 | Advanced table mapping: with target namespace | ||
| X056 | Advanced table mapping: data mapping | ||
| X057 | Advanced table mapping: metadata mapping | ||
| X058 | Advanced table mapping: base64 encoding of binary strings | ||
| X059 | Advanced table mapping: hex encoding of binary strings | ||
| X060 | XMLParse: character string input and CONTENT option | ||
| X061 | XMLParse: character string input and DOCUMENT option | ||
| X070 | XMLSerialize: character string serialization and CONTENT option | ||
| X071 | XMLSerialize: character string serialization and DOCUMENT option | ||
| X072 | XMLSerialize: character string serialization | ||
| X090 | XML document predicate | ||
| X120 | XML parameters in SQL routines | ||
| X121 | XML parameters in external routines | ||
| X221 | XML passing mechanism BY VALUE | ||
| X301 | XMLTable: derived column list option | ||
| X302 | XMLTable: ordinality column option | ||
| X303 | XMLTable: column default option | ||
| X304 | XMLTable: passing a context item | must be XML DOCUMENT | |
| X400 | Name and identifier mapping | ||
| X410 | Alter column data type: XML type |
Unsupported Features
The following features defined in SQL:2023 are not implemented in this release of QHB. In a few cases, equivalent functionality is available.
| Identifier | Core? | Description | Comment |
|---|---|---|---|
| B011 | Embedded Ada | ||
| B013 | Embedded COBOL | ||
| B014 | Embedded Fortran | ||
| B015 | Embedded MUMPS | ||
| B016 | Embedded Pascal | ||
| B017 | Embedded PL/I | ||
| B031 | Basic dynamic SQL | ||
| B032 | Extended dynamic SQL | ||
| B032-01 | <describe input statement> | ||
| B033 | Untyped SQL-invoked function arguments | ||
| B034 | Dynamic specification of cursor attributes | ||
| B035 | Non-extended descriptor names | ||
| B041 | Extensions to embedded SQL exception declarations | ||
| B051 | Enhanced execution rights | ||
| B111 | Module language Ada | ||
| B112 | Module language C | ||
| B113 | Module language COBOL | ||
| B114 | Module language Fortran | ||
| B115 | Module language MUMPS | ||
| B116 | Module language Pascal | ||
| B117 | Module language PL/I | ||
| B121 | Routine language Ada | ||
| B122 | Routine language C | ||
| B123 | Routine language COBOL | ||
| B124 | Routine language Fortran | ||
| B125 | Routine language MUMPS | ||
| B126 | Routine language Pascal | ||
| B127 | Routine language PL/I | ||
| B200 | Polymorphic table functions (PTF) | ||
| B201 | More than one PTF generic table parameter | ||
| B202 | PTF Copartitioning | ||
| B203 | More than one copartition specification | ||
| B204 | PRUNE WHEN EMPTY | ||
| B205 | Pass-through columns | ||
| B206 | PTF descriptor parameters | ||
| B207 | Cross products of partitionings | ||
| B208 | PTF component procedure interface | ||
| B209 | PTF extended names | ||
| B211 | Module language Ada: VARCHAR and NUMERIC support | ||
| B221 | Routine language Ada: VARCHAR and NUMERIC support | ||
| F054 | TIMESTAMP in DATE type precedence list | ||
| F121 | Basic diagnostics management | ||
| F121-01 | GET DIAGNOSTICS statement | ||
| F121-02 | SET TRANSACTION statement: DIAGNOSTICS SIZE clause | ||
| F122 | Enhanced diagnostics management | ||
| F123 | All diagnostics | ||
| F263 | Comma-separated predicates in simple CASE expression | ||
| F291 | UNIQUE predicate | ||
| F301 | CORRESPONDING in query expressions | ||
| F403 | Partitioned joined tables | ||
| F451 | Character set definition | ||
| F461 | Named character sets | ||
| F492 | Optional table constraint enforcement | ||
| F521 | Assertions | ||
| F671 | Subqueries in CHECK | intentionally omitted | |
| F673 | Reads SQL-data routine invocations in CHECK constraints | ||
| F693 | SQL-session and client module collations | ||
| F695 | Translation support | ||
| F696 | Additional translation documentation | ||
| F721 | Deferrable constraints | foreign and unique keys only | |
| F741 | Referential MATCH types | no partial match yet | |
| F812 | Core | Basic flagging | |
| F813 | Extended flagging | ||
| F821 | Local table references | ||
| F831 | Full cursor update | ||
| F831-01 | Updatable scrollable cursors | ||
| F831-02 | Updatable ordered cursors | ||
| F841 | LIKE_REGEX predicate | consider regexp_like() | |
| F842 | OCCURRENCES_REGEX function | consider regexp_matches() | |
| F843 | POSITION_REGEX function | consider regexp_instr() | |
| F844 | SUBSTRING_REGEX function | consider regexp_substr() | |
| F845 | TRANSLATE_REGEX function | consider regexp_replace() | |
| F846 | Octet support in regular expression operators | ||
| F847 | Nonconstant regular expressions | ||
| F866 | FETCH FIRST clause: PERCENT option | ||
| R010 | Row pattern recognition: FROM clause | ||
| R020 | Row pattern recognition: WINDOW clause | ||
| R030 | Row pattern recognition: full aggregate support | ||
| S011 | Core | Distinct data types | |
| S011-01 | Core | USER_DEFINED_TYPES view | |
| S023 | Basic structured types | ||
| S024 | Enhanced structured types | ||
| S025 | Final structured types | ||
| S026 | Self-referencing structured types | ||
| S027 | Create method by specific method name | ||
| S028 | Permutable UDT options list | ||
| S041 | Basic reference types | ||
| S043 | Enhanced reference types | ||
| S051 | Create table of type | partially supported | |
| S081 | Subtables | ||
| S091 | Basic array support | partially supported | |
| S091-02 | Arrays of distinct types | ||
| S094 | Arrays of reference types | ||
| S097 | Array element assignment | ||
| S151 | Type predicate | see pg_typeof() | |
| S161 | Subtype treatment | ||
| S162 | Subtype treatment for references | ||
| S202 | SQL-invoked routines on multisets | ||
| S231 | Structured type locators | ||
| S232 | Array locators | ||
| S233 | Multiset locators | ||
| S241 | Transform functions | ||
| S242 | Alter transform statement | ||
| S251 | User-defined orderings | ||
| S261 | Specific type method | ||
| S271 | Basic multiset support | ||
| S272 | Multisets of user-defined types | ||
| S274 | Multisets of reference types | ||
| S275 | Advanced multiset support | ||
| S281 | Nested collection types | ||
| S291 | Unique constraint on entire row | ||
| S401 | Distinct types based on array types | ||
| S402 | Distinct types based on distinct types | ||
| S403 | ARRAY_MAX_CARDINALITY | ||
| T011 | Timestamp in Information Schema | ||
| T021 | BINARY and VARBINARY data types | ||
| T022 | Advanced support for BINARY and VARBINARY data types | ||
| T023 | Compound binary literal | ||
| T024 | Spaces in binary literals | ||
| T041 | Basic LOB data type support | ||
| T041-01 | BLOB data type | ||
| T041-02 | CLOB data type | ||
| T041-03 | POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types | ||
| T041-04 | Concatenation of LOB data types | ||
| T041-05 | LOB locator: non-holdable | ||
| T042 | Extended LOB data type support | ||
| T043 | Multiplier T | ||
| T044 | Multiplier P | ||
| T051 | Row types | ||
| T053 | Explicit aliases for all-fields reference | ||
| T061 | UCS support | ||
| T076 | DECFLOAT data type | ||
| T101 | Enhanced nullability determination | ||
| T111 | Updatable joins, unions, and columns | ||
| T175 | Generated columns | mostly supported | |
| T176 | Sequence generator support | supported except for NEXT VALUE FOR | |
| T180 | System-versioned tables | ||
| T181 | Application-time period tables | ||
| T211 | Basic trigger capability | ||
| T211-06 | Support for run-time rules for the interaction of triggers and constraints | ||
| T211-08 | Multiple triggers for the same event are executed in the order in which they were created in the catalog | intentionally omitted | |
| T231 | Sensitive cursors | ||
| T251 | SET TRANSACTION statement: LOCAL option | ||
| T272 | Enhanced savepoint management | ||
| T301 | Functional dependencies | partially supported | |
| T321 | Core | Basic SQL-invoked routines | partially supported |
| T322 | Declared data type attributes | ||
| T324 | Explicit security for SQL routines | ||
| T326 | Table functions | ||
| T471 | Result sets return value | ||
| T472 | DESCRIBE CURSOR | ||
| T495 | Combined data change and retrieval | different syntax | |
| T502 | Period predicates | ||
| T511 | Transaction counts | ||
| T522 | Default values for IN parameters of SQL-invoked procedures | supported except DEFAULT key word in invocation | |
| T561 | Holdable locators | ||
| T571 | Array-returning external SQL-invoked functions | ||
| T572 | Multiset-returning external SQL-invoked functions | ||
| T601 | Local cursor references | ||
| T616 | Null treatment option for LEAD and LAG functions | ||
| T618 | NTH_VALUE function | function exists, but some options missing | |
| T619 | Nested window functions | ||
| T625 | LISTAGG | ||
| T641 | Multiple column assignment | only some syntax variants supported | |
| T652 | SQL-dynamic statements in SQL routines | ||
| T654 | SQL-dynamic statements in external routines | ||
| T811 | Basic SQL/JSON constructor functions | ||
| T812 | SQL/JSON: JSON_OBJECTAGG | ||
| T813 | SQL/JSON: JSON_ARRAYAGG with ORDER BY | ||
| T814 | Colon in JSON_OBJECT or JSON_OBJECTAGG | ||
| T821 | Basic SQL/JSON query operators | ||
| T822 | SQL/JSON: IS JSON WITH UNIQUE KEYS predicate | ||
| T823 | SQL/JSON: PASSING clause | ||
| T824 | JSON_TABLE: specific PLAN clause | ||
| T825 | SQL/JSON: ON EMPTY and ON ERROR clauses | ||
| T826 | General value expression in ON ERROR or ON EMPTY clauses | ||
| T827 | JSON_TABLE: sibling NESTED COLUMNS clauses | ||
| T828 | JSON_QUERY | ||
| T829 | JSON_QUERY: array wrapper options | ||
| T830 | Enforcing unique keys in SQL/JSON constructor functions | ||
| T838 | JSON_TABLE: PLAN DEFAULT clause | ||
| T839 | Formatted cast of datetimes to/from character strings | ||
| M001 | Datalinks | ||
| M002 | Datalinks via SQL/CLI | ||
| M003 | Datalinks via Embedded SQL | ||
| M004 | Foreign data support | partially supported | |
| M005 | Foreign schema support | ||
| M006 | GetSQLString routine | ||
| M007 | TransmitRequest | ||
| M009 | GetOpts and GetStatistics routines | ||
| M010 | Foreign data wrapper support | different API | |
| M011 | Datalinks via Ada | ||
| M012 | Datalinks via C | ||
| M013 | Datalinks via COBOL | ||
| M014 | Datalinks via Fortran | ||
| M015 | Datalinks via MUMPS | ||
| M016 | Datalinks via Pascal | ||
| M017 | Datalinks via PL/I | ||
| M018 | Foreign data wrapper interface routines in Ada | ||
| M019 | Foreign data wrapper interface routines in C | different API | |
| M020 | Foreign data wrapper interface routines in COBOL | ||
| M021 | Foreign data wrapper interface routines in Fortran | ||
| M022 | Foreign data wrapper interface routines in MUMPS | ||
| M023 | Foreign data wrapper interface routines in Pascal | ||
| M024 | Foreign data wrapper interface routines in PL/I | ||
| M030 | SQL-server foreign data support | ||
| M031 | Foreign data wrapper general routines | ||
| X012 | Multisets of XML type | ||
| X013 | Distinct types of XML type | ||
| X015 | Fields of XML type | ||
| X025 | XMLCast | ||
| X030 | XMLDocument | ||
| X038 | XMLText | ||
| X065 | XMLParse: BLOB input and CONTENT option | ||
| X066 | XMLParse: BLOB input and DOCUMENT option | ||
| X068 | XMLSerialize: BOM | ||
| X069 | XMLSerialize: INDENT | ||
| X073 | XMLSerialize: BLOB serialization and CONTENT option | ||
| X074 | XMLSerialize: BLOB serialization and DOCUMENT option | ||
| X075 | XMLSerialize: BLOB serialization | ||
| X076 | XMLSerialize: VERSION | ||
| X077 | XMLSerialize: explicit ENCODING option | ||
| X078 | XMLSerialize: explicit XML declaration | ||
| X080 | Namespaces in XML publishing | ||
| X081 | Query-level XML namespace declarations | ||
| X082 | XML namespace declarations in DML | ||
| X083 | XML namespace declarations in DDL | ||
| X084 | XML namespace declarations in compound statements | ||
| X085 | Predefined namespace prefixes | ||
| X086 | XML namespace declarations in XMLTable | ||
| X091 | XML content predicate | ||
| X096 | XMLExists | XPath 1.0 only | |
| X100 | Host language support for XML: CONTENT option | ||
| X101 | Host language support for XML: DOCUMENT option | ||
| X110 | Host language support for XML: VARCHAR mapping | ||
| X111 | Host language support for XML: CLOB mapping | ||
| X112 | Host language support for XML: BLOB mapping | ||
| X113 | Host language support for XML: STRIP WHITESPACE option | ||
| X114 | Host language support for XML: PRESERVE WHITESPACE option | ||
| X131 | Query-level XMLBINARY clause | ||
| X132 | XMLBINARY clause in DML | ||
| X133 | XMLBINARY clause in DDL | ||
| X134 | XMLBINARY clause in compound statements | ||
| X135 | XMLBINARY clause in subqueries | ||
| X141 | IS VALID predicate: data-driven case | ||
| X142 | IS VALID predicate: ACCORDING TO clause | ||
| X143 | IS VALID predicate: ELEMENT clause | ||
| X144 | IS VALID predicate: schema location | ||
| X145 | IS VALID predicate outside check constraints | ||
| X151 | IS VALID predicate with DOCUMENT option | ||
| X152 | IS VALID predicate with CONTENT option | ||
| X153 | IS VALID predicate with SEQUENCE option | ||
| X155 | IS VALID predicate: NAMESPACE without ELEMENT clause | ||
| X157 | IS VALID predicate: NO NAMESPACE with ELEMENT clause | ||
| X160 | Basic Information Schema for registered XML Schemas | ||
| X161 | Advanced Information Schema for registered XML Schemas | ||
| X170 | XML null handling options | ||
| X171 | NIL ON NO CONTENT option | ||
| X181 | XML(DOCUMENT (UNTYPED)) type | ||
| X182 | XML(DOCUMENT(ANY)) type | ||
| X190 | XML(SEQUENCE) type | ||
| X191 | XML(DOCUMENT(XMLSCHEMA)) type | ||
| X192 | XML(CONTENT(XMLSCHEMA)) type | ||
| X200 | XMLQuery | ||
| X201 | XMLQuery: RETURNING CONTENT | ||
| X202 | XMLQuery: RETURNING SEQUENCE | ||
| X203 | XMLQuery: passing a context item | ||
| X204 | XMLQuery: initializing an XQuery variable | ||
| X205 | XMLQuery: EMPTY ON EMPTY option | ||
| X206 | XMLQuery: NULL ON EMPTY option | ||
| X211 | XML 1.1 support | ||
| X222 | XML passing mechanism BY REF | parser accepts BY REF but ignores it; passing is always BY VALUE | |
| X231 | XML(CONTENT (UNTYPED)) type | ||
| X232 | XML(CONTENT (ANY)) type | ||
| X241 | RETURNING CONTENT in XML publishing | ||
| X242 | RETURNING SEQUENCE in XML publishing | ||
| X251 | Persistent XML values of XML(DOCUMENT (UNTYPED)) type | ||
| X252 | Persistent XML values of XML(DOCUMENT (ANY)) type | ||
| X253 | Persistent XML values of XML(CONTENT (UNTYPED)) type | ||
| X254 | Persistent XML values of XML(CONTENT (ANY)) type | ||
| X255 | Persistent XML values of XML(SEQUENCE) type | ||
| X256 | Persistent XML values of XML(DOCUMENT (XMLSCHEMA)) type | ||
| X257 | Persistent XML values of XML(CONTENT (XMLSCHEMA)) type | ||
| X260 | XML type: ELEMENT clause | ||
| X261 | XML type: NAMESPACE without ELEMENT clause | ||
| X263 | XML type: NO NAMESPACE with ELEMENT clause | ||
| X264 | XML type: schema location | ||
| X271 | XMLValidate: data-driven case | ||
| X272 | XMLValidate: ACCORDING TO clause | ||
| X273 | XMLValidate: ELEMENT clause | ||
| X274 | XMLValidate: schema location | ||
| X281 | XMLValidate with DOCUMENT option | ||
| X282 | XMLValidate with CONTENT option | ||
| X283 | XMLValidate with SEQUENCE option | ||
| X284 | XMLValidate: NAMESPACE without ELEMENT clause | ||
| X286 | XMLValidate: NO NAMESPACE with ELEMENT clause | ||
| X300 | XMLTable | XPath 1.0 only | |
| X305 | XMLTable: initializing an XQuery variable |
XML Limits and Conformance to SQL/XML
Significant revisions to the XML-related specifications in ISO/IEC 9075-14 (SQL/XML) were introduced with SQL:2006. QHB's implementation of the XML data type and related functions largely follows the earlier 2003 edition, with some borrowing from later editions. In particular:
-
Where the current standard provides a family of XML data types to hold “document” or “content” in untyped or XML Schema-typed variants, and a type XML(SEQUENCE) to hold arbitrary pieces of XML content, QHB provides the single xml type, which can hold “document” or “content”. There is no equivalent of the standard's “sequence” type.
-
QHB provides two functions introduced in SQL:2006, but in variants that use the XPath 1.0 language, rather than XML Query as specified for them in the standard.
This section presents some of the resulting differences you may encounter.
Queries Are Restricted to XPath 1.0
The QHB-specific functions xpath() and xpath_exists() query XML documents using the XPath language. QHB also provides XPath-only variants of the standard functions XMLEXISTS and XMLTABLE, which officially use the XQuery language. For all of these functions, QHB relies on the libxml2 library, which provides only XPath 1.0.
There is a strong connection between the XQuery language and XPath versions 2.0 and later: any expression that is syntactically valid and executes successfully in both produces the same result (with a minor exception for expressions containing numeric character references or predefined entity references, which XQuery replaces with the corresponding character while XPath leaves them alone). But there is no such connection between these languages and XPath 1.0; it was an earlier language and differs in many respects.
There are two categories of limitation to keep in mind: the restriction from XQuery to XPath for the functions specified in the SQL standard, and the restriction of XPath to version 1.0 for both the standard and the QHB-specific functions.
Restriction of XQuery to XPath
Features of XQuery beyond those of XPath include:
-
XQuery expressions can construct and return new XML nodes, in addition to all possible XPath values. XPath can create and return values of the atomic types (numbers, strings, and so on) but can only return XML nodes that were already present in documents supplied as input to the expression.
-
XQuery has control constructs for iteration, sorting, and grouping.
-
XQuery allows declaration and use of local functions.
Recent XPath versions begin to offer capabilities overlapping with these (such as functional-style for-each and sort, anonymous functions, and parse-xml to create a node from a string), but such features were not available before XPath 3.0.
Restriction of XPath to 1.0
For developers familiar with XQuery and XPath 2.0 or later, XPath 1.0 presents a number of differences to contend with:
-
The fundamental type of an XQuery/XPath expression, the sequence, which can contain XML nodes, atomic values, or both, does not exist in XPath 1.0. A 1.0 expression can only produce a node-set (containing zero or more XML nodes), or a single atomic value.
-
Unlike an XQuery/XPath sequence, which can contain any desired items in any desired order, an XPath 1.0 node-set has no guaranteed order and, like any set, does not allow multiple appearances of the same item.
Note
The libxml2 library does seem to always return node-sets to QHB with their members in the same relative order they had in the input document. Its documentation does not commit to this behavior, and an XPath 1.0 expression cannot control it. -
While XQuery/XPath provides all of the types defined in XML Schema and many operators and functions over those types, XPath 1.0 has only node-sets and the three atomic types boolean, double, and string.
-
XPath 1.0 has no conditional operator. An XQuery/XPath expression such as
if ( hat ) then hat/@size else "no hat"has no XPath 1.0 equivalent. -
XPath 1.0 has no ordering comparison operator for strings. Both
"cat" < "dog"and"cat" > "dog"are false, because each is a numeric comparison of two NaNs. In contrast,=and!=do compare the strings as strings. -
XPath 1.0 blurs the distinction between value comparisons and general comparisons as XQuery/XPath define them. Both
sale/@hatsize = 7andsale/@customer = "alice"are existentially quantified comparisons, true if there is any sale with the given value for the attribute, butsale/@taxable = false()is a value comparison to the effective boolean value of a whole node-set. It is true only if no sale has a taxable attribute at all. -
In the XQuery/XPath data model, a document node can have either document form (i.e., exactly one top-level element, with only comments and processing instructions outside of it) or content form (with those constraints relaxed). Its equivalent in XPath 1.0, the root node, can only be in document form. This is part of the reason an xml value passed as the context item to any QHB XPath-based function must be in document form.
The differences highlighted here are not all of them. In XQuery and the 2.0 and later versions of XPath, there is an XPath 1.0 compatibility mode, and the W3C lists of function library changes and language changes applied in that mode offer a more complete (but still not exhaustive) account of the differences. The compatibility mode cannot make the later languages exactly equivalent to XPath 1.0.
Mappings between SQL and XML Data Types and Values
In SQL:2006 and later, both directions of conversion between standard SQL data types and the XML Schema types are specified precisely. However, the rules are expressed using the types and semantics of XQuery/XPath, and have no direct application to the different data model of XPath 1.0.
When QHB maps SQL data values to XML (as in xmlelement), or XML to SQL (as in the output columns of xmltable), except for a few cases treated specially, QHB simply assumes that the XML data type's XPath 1.0 string form will be valid as the text-input form of the SQL datatype, and conversely. This rule has the virtue of simplicity while producing, for many data types, results similar to the mappings specified in the standard.
Where interoperability with other systems is a concern, for some data types, it may be necessary to use data type formatting functions (such as those in Section Data Type Formatting Functions) explicitly to produce the standard mappings.
Incidental Limits of the Implementation
This section concerns limits that are not inherent in the libxml2 library, but apply to the current implementation in QHB.
Only BY VALUE Passing Mechanism Is Supported
The SQL standard defines two passing mechanisms that apply when passing an XML argument from SQL to an XML function or receiving a result: BY REF, in which a particular XML value retains its node identity, and BY VALUE, in which the content of the XML is passed but node identity is not preserved. A mechanism can be specified before a list of parameters, as the default mechanism for all of them, or after any parameter, to override the default.
To illustrate the difference, if x is an XML value, these two queries in an SQL:2006 environment would produce true and false, respectively:
SELECT XMLQUERY(’$a is $b’ PASSING BY REF x AS a, x AS b NULL ON EMPTY);
SELECT XMLQUERY(’$a is $b’ PASSING BY VALUE x AS a, x AS b NULL ON EMPTY);
QHB will accept BY VALUE or BY REF in an XMLEXISTS or XMLTABLE construct, but it ignores them. The xml data type holds a character- string serialized representation, so there is no node identity to preserve, and passing is always effectively BY VALUE.
Cannot Pass Named Parameters to Queries
The XPath-based functions support passing one parameter to serve as the XPath expression's context item, but do not support passing additional values to be available to the expression as named parameters.
No XML(SEQUENCE) Type
The QHB xml data type can only hold a value in DOCUMENT or CONTENT form. An XQuery/XPath expression context item must be a single XML node or atomic value, but XPath 1.0 further restricts it to be only an XML node, and has no node type allowing CONTENT. The upshot is that a well-formed DOCUMENT is the only form of XML value that QHB can supply as an XPath context item.