Contact Us | Site Map
Firebird is used by approximately 1 million of software developers worldwide. High compatibility with industry standards on many fronts makes Firebird the obvious choice for developing interoperable applications for homogeneous and hybrid environments.
Join Firebird!
Join Firebird Foundation to support Firebird SQL development and receive multiple bonuses
Follow Us
Select your media preference
Google+ Twitter Facebook RSS
Newsletter
Subscribe to Firebird’s Newsletter to receive the latest news
SQL Conformance
Assembled by Dmitry Yemanov. Last updated: February 11, 2006.

This document outlines how much Firebird conforms to the current SQL standard. Please note that the following information is not a full statement of conformance, but just information for those interested in the subject.

The formal name of the SQL standard is ISO/IEC 9075 "Database Language SQL". The version this document refers to is ISO/IEC 9075:2003, or simply SQL:2003. The versions prior to that were SQL:1999 and SQL-92. Each version supersedes the previous one, so claims of conformance to earlier versions have no official standing.

Starting with SQL:1999, the SQL standard defines a large set of individual features rather than the three levels (Entry, Intermediate and Full) declared in SQL-92. A large subset of these features represents the "Core" (mandatory) features, SQL implementation must supply in order to claim conformance. The rest of the features are purely optional.

In the following sections, we provide a list of all SQL:2003 features with an indication of whether it is supported by the given Firebird version.

Every feature consists of an unique identifier and a name. Feature identifiers containing a hyphen are subfeatures. If a particular subfeature is not supported, the main feature is listed as partly supported. Comments are provided where necessary.

Legend:
  • (+) : feature is supported entirely
  • (−) : feature is not supported
  • (*) : feature is supported partly or it does not conform to the standard completely
  • (?) : we have no clear opinion about conformance (to be reviewed)

The document is subject to periodic updates, so please feed back if you disagree with any marks of conformance.

Mandatory features

Feature ID Feature Name v1.5 v2.0 Comments
E011 Numeric data types * *  
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 * * NUMERIC is not compliant and represents almost the same semantics as DECIMAL
E011-04 Arithmetic operators + +  
E011-05 Numeric comparison + +  
E011-06 Implicit casting among the numeric data types + +  
E021 Character string types * *  
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 * + In Firebird 1.5, SUBSTRING does not accept value expressions as its arguments
E021-07 Character concatenation + +  
E021-08 UPPER and LOWER functions * + In Firebird 1.5, LOWER is not supported
E021-09 TRIM function +  
E021-10 Implicit casting among the fixed-length and variable-length character string types + +  
E021-11 POSITION function  
E021-12 Character comparison + +  
E031 Identifiers + +  
E031-01 Delimited identifiers + +  
E031-02 Lower case identifiers + +  
E031-03 Trailing underscore + +  
E051 Basic query specification * *  
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 + + In Firebird 1.5, keyword AS is not supported
E051-09 Rename columns in the FROM clause  
E061 Basic predicates and search conditions + +  
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 * *  
E071-01 UNION DISTINCT table operator * + In Firebird 1.5, keyword DISTINCT is not supported, but the default behaviour conforms
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 + +  
E081-07 REFERENCES privilege at the column level + +  
E081-08 WITH GRANT OPTION + +  
E081-09 USAGE privilege  
E081-10 EXECUTE privilege + +  
E091 Set functions + +  
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 + +  
E101-01 INSERT statement + +  
E101-03 Searched UPDATE statement + +  
E101-04 Searched DELETE statement + +  
E111 Single row SELECT statement + +  
E121 Basic cursor support * *  
E121-01 DECLARE CURSOR +  
E121-02 ORDER BY columns need not be in select list + +  
E121-03 Value expressions in ORDER BY clause + +  
E121-04 OPEN statement +  
E121-06 Positioned UPDATE statement * + In Firebird 1.5, only non-standard FOR SELECT cursors support positioned updates/deletes
E121-07 Positioned DELETE statement * + (same as above)
E121-08 CLOSE statement +  
E121-10 FETCH statement: implicit NEXT +  
E121-17 WITH HOLD cursors  
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 for both referential delete action and referential update action + +  
E141-06 CHECK constraints + +  
E141-07 Column defaults + +  
E141-08 NOT NULL inferred on PRIMARY KEY +  
E141-10 Names in a foreign key can be specified in any order  
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
 
E152-02 SET TRANSACTION statement:
READ ONLY and READ WRITE clauses
+ +  
E153 Updatable queries with subqueries + +  
E161 SQL comments using leading double minus + +  
E171 SQLSTATE support * * Available in PSQL via SQLCODE and GDSCODE, but uses non-standard errors encoding
E182 Module language  
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
* * RESTRICT keyword is not supported, but the default behaviour conforms
F031-19 REVOKE statement:
RESTRICT clause
* * (same as above)
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 support of DATE literal) + +  
F051-02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 + +  
F051-03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 + +  
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types + +  
F051-05 Explicit CAST between datetime types and character string types + +  
F051-06 CURRENT_DATE + +  
F051-07 LOCALTIME * * Supported as CURRENT_TIME instead
F051-08 LOCALTIMESTAMP * * Supported as CURRENT_TIMESTAMP instead
F081 UNION and EXCEPT in views * EXCEPT is not supported
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  
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-04 CREATE VIEW: WITH CHECK OPTION  
F311-05 GRANT statement  
F471 Scalar subquery values + +  
F481 Expanded NULL predicate + +  
F812 Basic flagging  
S011 Distinct data types  
T321 Basic SQL-invoked routines * *  
T321-01 User-defined functions with no overloading * * Only external (non-SQL) functions are supported
T321-02 User-defined stored procedures with no overloading * * Only SQL procedures are supported
T321-03 Function invocation + +  
T321-04 CALL statement * * Available as EXECUTE PROCEDURE instead
T321-05 RETURN statement * * Available as EXIT in SQL procedures
T631 IN predicate with one list element + +  

Optional features

Feature ID Feature Name Firebird 1.5 Firebird 2.0 Comments
B011 Embedded Ada + +  
B012 Embedded C + +  
B013 Embedded COBOL + +  
B014 Embedded Fortran + +  
B015 Embedded MUMPS  
B016 Embedded Pascal + +  
B017 Embedded PL/I  
B021 Direct SQL + +  
B031 Basic dynamic SQL * * Not all standard features are supported
B032 Extended dynamic SQL  
B032-01 <describe input statement>  
B033 Untyped SQL-invoked function arguments  
B034 Dynamic specification of cursor attributes  
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  
B128 Routine language SQL + +  
F032 CASCADE drop behavior  
F033 ALTER TABLE statement:
DROP COLUMN clause
+ +  
F034 Extended REVOKE statement * * REVOKE CASCADE is not supported
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 ? ?  
F052 Intervals and datetime arithmetic * * Intervals are not supported
F053 OVERLAPS predicate  
F111 Isolation levels other than SERIALIZABLE * *  
F111-01 READ UNCOMMITTED isolation level  
F111-02 READ COMMITTED isolation level + +  
F111-03 REPEATABLE READ isolation level + + Available as SNAPSHOT
F121 Basic diagnostics management  
F121-01 GET DIAGNOSTICS statement  
F121-02 SET TRANSACTION statement: DIAGNOSTICS SIZE clause  
F171 Multiple schemas per user  
F191 Referential delete actions + +  
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 + +  
F262 Extended CASE expression  
F263 Comma-separated predicates in simple CASE expression  
F271 Compound character literals  
F281 LIKE enhancements + +  
F291 UNIQUE predicate  
F301 CORRESPONDING in query expressions  
F302 INTERSECT table operator  
F302-01 INTERSECT DISTINCT table operator  
F302-02 INTERSECT ALL table operator  
F304 EXCEPT ALL table operator  
F312 MERGE statement  
F321 User authorization * * Only CURRENT_USER is supported
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
+ +  
F391 Long identifiers * * Maximum identifier length is equal to 31
F392 Unicode escapes in identifiers  
F393 Unicode escapes in literals  
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  
F411 Time zone specification  
F421 National character * * Neither national string literals nor NCHAR VARYING datatype are supported
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 + +  
F451 Character set definition  
F461 Named character sets + +  
F491 Constraint management + +  
F502 Enhanced documentation tables  
F502-01 SQL_SIZING_PROFILES view  
F502-02 SQL_IMPLEMENTATION_INFO view  
F502-03 SQL_PACKAGES view  
F521 Assertions  
F531 Temporary tables  
F555 Enhanced seconds precision * * Maximum supported precision is equal to 3, precision in datatype declarations are not supported
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  
F671 Subqueries in CHECK + +  
F672 Retrospective check constraints + +  
F691 Collation and translation * * CREATE/DROP COLLATION is not supported
F692 Enhanced collation support + +  
F693 SQL-session and client module collations  
F695 Translation support  
F696 Additional translation documentation  
F701 Referential update actions + +  
F711 ALTER domain + +  
F721 Deferrable constraints  
F731 INSERT column privileges  
F741 Referential MATCH types  
F751 View CHECK enhancements  
F761 Session management  
F771 Connection management  
F781 Self-referencing operations + +  
F791 Insensitive cursors  
F801 Full set function  
F813 Extended flagging  
F821 Local table references  
F831 Full cursor update  
F831-01 Updateable scrollable cursors  
F831-02 Updateable ordered cursors  
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  
S071 SQL paths in function and type name resolution  
S081 Subtables  
S091 Basic array support * *  
S091-01 Arrays of built-in data types * * Implementation is non-standard and limited to DSQL only
S091-02 Arrays of distinct types  
S091-03 Array expressions  
S092 Arrays of user-defined types  
S094 Arrays of reference types  
S095 Array constructors by query  
S096 Optional array bounds  
S097 Array element assignment  
S111 ONLY in query expressions  
S151 Type predicate  
S161 Subtype treatment  
S162 Subtype treatment for references  
S201 SQL-invoked routines on arrays  
S201-01 Array parameters  
S201-02 Array as result type of functions  
S202 SQL-invoked routines on multisets  
S211 User-defined cast functions  
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  
T011 Timestamp in Information Schema  
T031 BOOLEAN data type  
T041 Basic LOB data type support * *  
T041-01 BLOB data type + +  
T041-02 CLOB data type * * Available as BLOB SUB_TYPE TEXT
T041-03 POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types * * Partially supported
T041-04 Concatenation of LOB data types  
T041-05 LOB locator: non-holdable  
T042 Extended LOB data type support  
T051 Row types  
T052 MAX and MIN for row types  
T053 Explicit aliases for all-fields reference  
T061 UCS support  
T071 BIGINT data type + +  
T111 Updatable joins, unions, and columns  
T121 WITH (excluding RECURSIVE) in query expression  
T122 WITH (excluding RECURSIVE) in subquery  
T131 Recursive query  
T132 Recursive query in subquery  
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  
T175 Generated columns  
T176 Sequence generator support + +  
T191 Referential action RESTRICT + +  
T201 Comparable data types for referential constraints * * Numerics, timestamps and strings are not considered comparable
T211 Basic trigger capability * *  
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 shall be True before the trigger is invoked  
T211-06 Support for run-time rules for the interaction of triggers and constraints ? ?  
T211-07 TRIGGER privilege  
T211-08 Multiple triggers for the same event are executed in the order in which they were
created in the catalog
* * Execution order is specified by the developer
T212 Enhanced trigger capability  
T231 Sensitive cursors  
T241 START TRANSACTION statement + + Available as SET TRANSACTION
T251 SET TRANSACTION statement: LOCAL option  
T261 Chained transactions  
T271 Savepoints + +  
T272 Enhanced savepoint management  
T281 SELECT privilege with column granularity  
T301 Functional dependencies + +  
T312 OVERLAY function  
T322 Overloading of SQL-invoked functions and procedures  
T323 Explicit security for external routines  
T324 Explicit security for SQL routines  
T325 Qualified SQL parameter references  
T326 Table functions  
T331 Basic roles * * SET ROLE is not supported
T332 Extended roles * * GRANTED BY CURRENT_ROLE is not supported
T351 Bracketed SQL comments (/*...*/ comments) + +  
T431 Extended grouping capabilities  
T432 Nested and concatenated GROUPING SETS  
T433 Multi-argument GROUPING function  
T434 GROUP BY DISTINCT  
T441 ABS and MOD functions  
T461 Symmetric BETWEEN predicate  
T471 Result sets return value  
T491 LATERAL derived table  
T501 Enhanced EXISTS predicate + +  
T511 Transaction counts  
T551 Optional keywords for default syntax + +  
T561 Holdable locators  
T571 Array-returning external SQL-invoked functions  
T572 Multiset-returning external SQL-invoked functions  
T581 Regular expression substring function  
T591 UNIQUE constraints of possibly null columns + +  
T601 Local cursor references  
T611 Elementary OLAP operations  
T612 Advanced OLAP operations  
T613 Sampling  
T621 Enhanced numeric functions  
T641 Multiple column assignment  
T651 SQL-schema statements in SQL routines  
T652 SQL-dynamic statements in SQL routines + +  
T653 SQL-schema statements in external routines  
T654 SQL-dynamic statements in external routines  
T655  Cyclically dependent routines + +  

Home About Firebird Documentation Downloads Community Support Development Case Studies Back to Top
This site and the pages contained within are Copyright © 2000-2014, Firebird Project. Firebird® is a registered trademark of Firebird Foundation Incorporated. Developed by DQ Team.