Feature overview

Feature overview mk Sat, 03/27/2010 - 23:29

The SQL language standard has grown from a modest SQL'92 document of less then a 100 pages to the latest  SQL:2008 >4000 page description comprised of the following volumes:

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)

The MonetDB SQL implementation is based on SQL 2003 with a limited extension to features found elsewhere. The tables below  illustrates the (un) supported features in the current distribution. Beware, some of the features are technically impossible to support due to the design of MonetDB or would cause excessive performance consequences.

Migration from your favored database system may require adjustment of the schema and query formulation. A comparison table for most actively used features is given in the SQL compatibility wikibook.

 

SQL compatibility

SQL compatibility mk Sun, 10/13/2013 - 23:31

Every SQL implementation differs in minor / major areas from the SQL standard. This calls for using middleware to translate queries, or even manually rewrite them according to the DBMS specifics.

A synopsis of the builtin functionality and comparison with related products, such as Postgresql, MySQL, and several commerical systems, can be found in the SQL wikibook.

Supported

Supported mk Thu, 06/02/2011 - 20:14
Feature ID Feature name
E011 Numeric data types (FULL support)
E011-01 INTEGER and SMALLINT data types (including all spellings)
E011-02 REAL, DOUBLE PRECISON, and FLOAT data types
E011-03 DECIMAL and NUMERIC data types
E011-04 Arithmetic operators
E011-05 Numeric comparison
E011-06 Implicit casting among the numeric data types
E021 Character data types (PARTIAL support)
E021-01 CHARACTER data type (including all its spellings)
E021-02 CHARACTER VARYING data type (including all its spellings)
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 character data types
E021-11 POSITION function
E021-12 Character comparison
E031 Identifiers (FULL support)
E031-01 Delimited identifiers
E031-02 Lower case identifiers
E031-03 Trailing underscore
E051 Basic query specification (FULL support)
E051-01 SELECT DISTINCT
E051-02 GROUP BY clause
E051-04 GROUP BY can contain columns not in select-list
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
E051-09 Rename columns in the FROM clause
E061 Basic predicates and search conditions (FULL support)
E061-01 Comparison predicate
E061-02 BETWEEN predicate
E061-03 IN predicate with list of values
E061-04 LIKE predicate
E061-05 LIKE predicate: ESCAPE clause
E061-06 NULL predicate
E061-07 Quantified comparison predicate
E061-08 EXISTS predicate
E061-09 Subqueries in comparison predicate
E061-11 Subqueries in IN predicate
E061-12 Subqueries in quantified comparison predicate
E061-13 Correlated subqueries
E061-14 Search condition
E071 Basic query expressions (FULL support)
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
E081 Basic Privileges
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 level  (SELECT privilege is used)
E081-07 REFERENCES privilege at the column level (SELECT privilege is used)
E081-08 WITH GRANT OPTION
E091 Set functions (FULL support)
E091-01 AVG
E091-02 COUNT
E091-03 MAX
E091-04 MIN
E091-05 SUM
E091-06 ALL quantifier
E091-07 DISTINCT quantifier
E101 Basic data manipulation (FULL support)
E101-01 INSERT statement
E101-03 Searched UPDATE statement
E101-04 Searched DELETE statement
E111 Single row SELECT statement
E121-01 DECLARE CURSOR
E121-02 ORDER BY columns need not be in select list
E131 Null value support (nulls in lieu of values)
E141 Basic integrity constraints
E141-01 NOT NULL constraints
E141-02 UNIQUE constraints of NOT NULL columns
E141-03 PRIMARY KEY constraints
E141-04 Basic FOREIGN KEY constraint with the NO ACTION default
E141-07 Column defaults
E141-08 NOT NULL inferred on PRIMARY KEY
E151 Transaction support
E151-01 COMMIT statement
E151-02 ROLLBACK statement
E152 Basic SET TRANSACTION statement
E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause
E153 Updatable queries with subqueries
E161 SQL comments using leading double minus
E182 Module language
F021-01 COLUMNS view
F021-02 TABLES view
F021-03 VIEWS view
F021-04 TABLE_CONSTRAINTS view
F021-05 REFERENTIAL_CONSTRAINTS view
F021-06 CHECK_CONSTRAINTS view
F031 Basic schema manipulation
F031-01 CREATE TABLE statement to create persistent base tables
F031-02 CREATE VIEW statement
F031-03 GRANT statement
F031-04 ALTER TABLE statement: ADD COLUMN clause
F031-13 DROP TABLE statement: RESTRICT clause
F031-16 DROP VIEW statement: RESTRICT clause
F031-19 REVOKE statement: RESTRICT clause
F041 Basic joined table
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
F051-01 DATE data type (including DATE literal)
F051-02 TIME data type (including TIME literal) with fractional seconds precision of 0
F051-03 TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types
F051-05 Explicit CAST between datetime types and character types
F051-06 CURRENT_DATE
F051-07 LOCALTIME
F051-08 LOCALTIMESTAMP
F081 UNION and EXCEPT in views
F131 Grouped operations
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 (LIMITED support)
F201 CAST function
F221 Explicit defaults
F261 CASE expression
F261-01 Simple CASE
F261-02 Searched CASE
F261_03 NULLIF
F261-04 COALESCE
F311 Schema definition statement
F311-01 CREATE SCHEMA
F311-02 CREATE TABLE for persistent base tables
F311-03 CREATE VIEW
F311-05 GRANT statement
F471 Scalar subquery values
F481 Expanded NULL predicate
F501-01 SQL_FEATURES view
F501-02 SQL_SIZING view
F501-03 SQL_LANGUAGES view
F521 Assertions
S011 Distinct data types
T321 Basic SQL-invoked routines
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

Unsupported

Unsupported mk Thu, 06/02/2011 - 20:12
Feature ID Feature name
B011-B017 Embedded Language support. Core SQL:1999 says that at least one of Embedded Ada, Embedded C, Embedded Cobol, Embedded Fortran, Embedded MUMPS, Embedded Pascal or Embedded PL/I 1 should be supported.
B111-B117 Module language ADA, COBOL, Fortran MUMPS, Pascal, PL/I
B121-B127 Routine language ADA, COBOL, Fortran MUMPS, Pascal, PL/I
E081-09 USAGE privilege
E081-10 EXECUTE privilege
E153 Updatable queries with subqueries
E121 Basic cursor support (CURSORS are not supported)
E121-01 DECLARE CURSOR
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
E141-06 CHECK constraints
E141-10 Names in a foreign key can be specified in any order (columns should be in the proper order)
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses
E171 SQLSTATE support
F021 Basic information schema
F021-01 COLUMNS view
F021-02 TABLES view
F021-03 VIEWS view
F021-04 TABLE_CONSTRAINTS view
F021-05 REFERENTIAL_CONSTRAINTS view
F021-06 CHECK_CONSTRAINTS view
F121 Basic diagnostic management
F121-01 GET DIAGNOSTICS statement
F121-02 SET TRANSACTION statement: DIAGNOSTICS SIZE clause
F122 Enhanced diagnostics statement
F123 All diagnostics
F181 Multiple module support
F202 TRUNCATE TABLE: identify column restart option
F263 Comma-separated predicates in simple CASE expression
F291 UNIQUE predicate
F301 CORRESPONDING in query expressions
F311-04 CREATE VIEW: WITH CHECK OPTION
F312 MERGE statement
F313 Enhanced MERGE statement
F341 Usage tables (ROUTINE_*_USAGE)
F394 Optional normal form specification
F403 Partitioned joined tables
F451 Character set definition
F461 Named character sets
F501 Features and conformance views
F501-01 SQL_FEATURES view
F501-02 SQL_SIZING view
F501-03 SQL_LANGUAGES view
F671 Subqueries in CHECK
F693 SQL-session and client module collations
F695 Translation support
F696 Additional translation documentation
F721 Deferrable constraints
F751 View CHECK enhancements
F812 Basic flagging
F831 - 831-02 Cursor update, scroll, and order
F841 LIKE_REGEX
F842 OCCURRENCES_REGEX function
F843 POSITION_REGEX function
F844 SUBSRING_REGEX function
F845 TRANSLATE_REGEX function
F846 Octet support in regular expression
F847 Nonconstant regular expression
S011-01 USER_DEFINED_TYPES view
S023 Basic structured types
S024 Enhanced structure types
S025 Final structured types
S026 Self-reference structured types
S027 Create method by specific method name
S028 Permutable UDT options list
S051 Create table of type
S081 Subtables
S091 Basic array support
S091-01 Arrays of built-in data types
S091-02 Arrays of distinct types
S091-03 Array expressions
S094 Arrays of reference types
S097 Array element assignment
S151  - S404 Object support features
T321-06 ROUTINES view
T321-07 PARAMETERS view
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
T052 MAX and MIN for row types
T053 Explicit aliases for all-fields reference
T061 UCS support
T101 Enhanced nullability determination
T111 Updatable joins, unions, and columns
T174 Identity columns
T175 Generated columns
T176 Sequence generator support
T177 Sequence generator support: simple restart option
T178 Identity columns: simple restart option
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
T251 SET TRANSACTION statement: LOCAL option
T261 Chained transactions
T272 Enhanced savepoint management
T285 Enhanced derived column names
T301 Functional dependencies partially supported
T321 Basic SQL-invoked routines
T321-02 User-defined stored procedures with no overloading
T321-04 CALL statement
T321-05 RETURN statement
T324 Explicit security for SQL routines
T325 Qualified SQL parameter references
T326 Table functions
T332 Extended roles mostly supported
T431 Extended grouping capabilities
T432 Nested and concatenated GROUPING SETS
T433 Multiargument GROUPING function
T434 GROUP BY DISTINCT
T471 Result sets return value
T491 LATERAL derived table
T511 Transaction counts
T541 Updatable table references
T561 Holdable locators
T571 Array-returning external SQL-invoked functions
T572 Multiset-returning external SQL-invoked functions
T601 Local cursor references
T611 Elementary OLAP operations most forms supported
T612 Advanced operations some forms supported
T613 Sampling
T616 Null treatment option for LEAD and LAG functions
T618 NTH_VALUE function function exists, but some options missing
T641 Multiple column assignment only some syntax variants supported
T652 SQL-dynamic statements in SQL routines
T653 SQL-schema statements in external routines
T654 SQL-dynamic statements in external routines
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
M011 Datalinks via Ada, C, COBOL, Fortran, MUMPS, Pascal, PL/I
M018 - M024 Foreign data wrapper interface routines in Ada, C, COBOL, Fortran,MUMPS,Pascal,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
X014 Attributes 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
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
X221 XML passing mechanism BY VALUE
X222 XML passing mechanism BY REF
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
X301 XMLTable: derived column list option
X302 XMLTable: ordinality column option
X303 XMLTable: column default option
X304 XMLTable: passing a context item
X305 XMLTable: initializing an XQuery variable