Compatibility Matrix

Beam strives to cover the full breadth of the relevant SQL standards. In general, if there is something in a SQL standard that is not implemented in a generic manner in beam-core, feel free to file an issue requesting support. There are some features that beam purposefully omits because no major RDBMS implements them. For example, database-level assertions are not supported in any of the default beam backends, and thus are not supported by beam-core. If you have a need for these features, feel free to file an issue. Be sure to motivate your use case with examples and a testing strategy.

The relevant SQL standards are SQL-92, SQL:1999, SQL:2003, SQL:2008, and SQL:2011. Because not all the standards are not publicly accessible, I've done my best to piece together features from various documents available online. I believe I've covered most of the common cases, but there may be pieces of functionality that are missing. File an issue if this is the case.

The table below summarizes the features defined in each SQL standard and beam's support for them. FULL means beam supports everything in that feature. NONE means that there is no support for that feature, and none planned. N/A means that the feature only applies to RDBMSs, not the SQL language. WONTFIX means that the feature has been considered and willfully ignored. UNKNOWN means not enough investigation has gone into the feature to make a determination. TODO means the feature has not been implemented yet, but an implementation is planned.

Tip

The 'TODO' items are a great way to contribute to beam!

Feature Status Notes
B011 Embedded Ada NONE
B012 Embedded C NONE
B013 Embedded COBOL NONE
B014 Embedded FORTRAN NONE
B015 Embedded MUMPS NONE
B016 Embedded Pascal NONE
B017 Embedded PL/I NONE
B021 Direct SQL NONE
B031 Basic dynamic SQL NONE
B032 Extended dynamic SQL NONE
B033 Untyped SQL-invoked function arguments NONE
B034 Dynamic specification of cursor attributes NONE
B035 Non-extended descriptor names NONE
B051 Enhanced execution rights NONE
B111 Module language Ada NONE
B112 Module language C NONE
B113 Module language COBOL NONE
B114 Module language Fortran NONE
B115 Module language MUMPS NONE
B116 Module language Pascal NONE
B117 Module language PL/I NONE
B121 Routine language Ada NONE
B122 Routine language C NONE
B123 Routine language COBOL NONE
B124 Routine language Fortran NONE
B125 Routine language MUMPS NONE
B126 Routine language Pascal NONE
B127 Routine language PL/I NONE
B128 Routine language SQL NONE
B211 Module language Ada: VARCHAR and NUMERIC support NONE
B221 Routine language Ada: VARCHAR and NUMERIC support NONE
E011 - Numeric data types
E011-01 INTEGER and SMALLINT data types FULL Use Int32 for INTEGER, Int16 for SMALLINT
E011-02 REAL, DOUBLE PRECISION, FLOAT FULL Use Double and Float
E011-03 DECIMAL and NUMERIC data types FULL Use Scientific. You can provide the database precision using beam-migrate
E011-04 Arithmetic operators FULL Use the Num instance for QGenExpr
E011-05 Numeric comparison FULL Use the . suffixed operators (i.e., ==., /=., <., etc)
E011-06 Implicit casting among numeric data types WONTFIX Beam never implicitly casts. Use cast_
E021 Character string types
E021-01 CHARACTER data type FULL Use Text. Use beam-migrate to specify width
E021-02 CHARACTER VARYING data type FULL Use Text. Use beam-migrate to specify width.
E021-03 Character literals FULL Use val_
E021-04 CHARACTER_LENGTH function FULL Use charLength_
E021-05 OCTET_LENGTH function FULL Use octetLength_
E021-06 SUBSTRING function TODO
E021-07 Character concatenation FULL Use concat_
E021-08 UPPER and LOWER functions FULL Use upper_ and lower_
E021-09 TRIM function PARTIAL Use trim_. Full support may be provided on backends that implement it
E021-10 Implicit casting among string types WONTFIX Beam never implicitly casts. Use cast_
E021-11 POSITION function FULL Use position_
E021-12 Character comparison FULL Use comparison operators (See E011-05)
E031 Identifiers
E031-01 Delimited identifiers TODO Find out more
E021-02 Lower case identifiers TODO
E021-03 Trailing underscore N/A Beam will use whatever column names you specify
E051 Basic query specification
E051-01 SELECT DISTINCT FULL Use nub_
E051-02 GROUP BY clause FULL See aggregate_ or read the section on aggregates
E051-04 GROUP BY can contain columns not in SELECT TODO Unsure how this applies to beam in particular
E051-05 Select list items can be renamed N/A Beam uses this feature internally, the user never needs it
E051-06 HAVING clause FULL guard_ and filter_ are appropriately converted to HAVING when allowed
E051-07 Qualified * in select list N/A Beam handles projections instead
E051-08 Correlation names in FROM TODO Unsure how this applies to beam
E051-09 Rename columns in the FROM clause NONE Beam doesn't need this
E061 Basic predicates and search conditions
E061-01 Comparison predicate FULL Use the comparison operators (see E011-05)
E061-02 BETWEEN predicate FULL Use between_
E061-03 IN predicate with list of values FULL Use in_
E061-04 LIKE predicate FULL Use like_
E061-05 LIKE predicate ESCAPE clause TODO Unsure how this would apply
E061-06 NULL predicate FULL Use isNothing_ and isJust_
E061-07 Quantified comparison predicate FULL Use one of the quantified comparison operators (==*., /=*., <*., >*., <=*., >=*.)
E051-08 EXISTS predicate FULL Use exists_
E061-09 Subqueries in comparison predicate FULL Use subquery_ as usual
E061-11 Subqueries in IN predicate FULL
E061-12 Subqueries in quantified comparison predicate FULL
E061-13 Correlated subqueries FULL Use subquery_
E061-14 Search condition FULL Construct QGenExprs with type Bool
E071 Basic query expressions
E071-01 UNION DISTINCT table operator FULL Use union_
E071-02 UNION ALL table operator FULL Use unionAll_
E071-03 EXCEPT DISTINCT table operator FULL Use except_
E071-05 Columns combined via operators need not have same type WONTFIX Beam is strongly typed
E071-06 Table operators in subqueries FULL Supported for backends that support it
E081 Basic privileges NONE Database security is not beam's focus. beam-migrate may expose this in the future
E091 Set functions
E091-01 AVG FULL Use avg_ or avgOver_
E091-02 COUNT FULL Use countAll_, countAllOver_, count_, or countOver_
E091-03 MAX FULL Use max_ or maxOver_
E091-04 MIN FULL Use min_ or minOver_
E091-05 SUM FULL Use sum_ or sumOver_
E091-06 ALL quantifier FULL Use the *Over_ functions with the allInGroupExplicitly_ quantifier
E091-07 DISTINCT quantifier FULL Use the *Over_ functions with the distinctInGroup_ quantifier
E101 Basic data manipulation
E101-01 INSERT statement FULL Use insert and SqlInsert
E101-03 Searched UPDATE FULL Use update and SqlUpdate
E101-04 Searched DELETE FULL Use delete and SqlDelete
E111 Single row SELECT statement FULL Use select as expected
E121 Basic cursor support NONE Use the backends explicitly
E131 Null value support PARTIAL Use Maybe column types, Nullable, and the just_, nothing_, and maybe_ functions
E141 Basic integrity constraints Implemented in beam-migrate
E141-01 NOT NULL constraints FULL Use notNull_
E141-02 UNIQUE constraints of NOT NULL columns TODO
E141-03 PRIMARY KEY constraints FULL Instantiate Table with the correct PrimaryKey
E141-04 Basic FOREIGN KEY constraints TODO You can embed the PrimaryKey of the relation directly.
E141-06 CHECK constraints TODO
E141-07 Column defaults FULL Use default_ from beam-migrate
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 None Use the backend functions explicitly
E152 SET TRANSACTION statement N/A
E153 Updatable queries with subqueries TODO Not a common feature, but would be trivial to support
E161 SQL comments with double minus N/A
E171 SQLSTATE support N/A
E182 Host language binding N/A
F031 Basic schema manipulation
F031-01 CREATE TABLE for persistent base tables FULL Use createTable_ in beam-migrate
F031-02 CREATE VIEW statement TODO
F031-03 GRANT statement TODO
F031-04 ALTER TABLE statement: ADD COLUMN clause TODO
F031-13 DROP TABLE statement: RESTRICT clause TODO
F031-16 DROP VIEW statement: RESTRICT clause TODO
F031-19 REVOKE statement: RESTRICT clause NONE See note for E081
F032 CASCADE drop behavior TODO Would be in beam-migrate
F033 ALTER TABLE statement: DROP COLUMN clause TODO
F034 Extended REVOKE statement NONE
F041 Basic joined table
F041-01 Inner join FULL Use the monadic join interface
F041-02 INNER keyword N/A The INNER keyword is just syntactic sugar. The regular joins do what you want.
F041-03 LEFT OUTER JOIN FULL Use leftJoin_
F041-04 RIGHT OUTER JOIN PARTIAL Supported in backend syntaxes, not exposed. Can always be written using LEFT OUTER JOIN
F041-05 Outer joins can be nested FULL outerJoin_ can be nested arbitrarily
F041-07 The inner table in outer join can be used in inner join TODO How does this apply to us?
F041-08 All comparison operators in JOIN FULL Arbitrary QGenExprs are supported.
F051 Basic date and time
F051-01 DATE data type FULL Use Day from Data.Time and val_
F051-02 TIME data type FULL Use TimeOfDay from Data.Time and val_
F051-03 TIMESTAMP datatype FULL Use LocalTime from Data.Time and val_. Precision can be specified in beam-migrate
F051-04 Comparison predicate on time types FULL Use comparison operatiors (See E011-05)
F051-05 Explicit cast between date-time types and string TODO
F051-06 CURRENT_DATE TODO
F051-07 LOCALTIME TODO
F051-08 LOCALTIMESTAMP TODO
F081 UNION and EXCEPT in views FULL Views can use any query
F111 Isolation levels other than SERIALIZABLE NONE Use backends
F121 Basic diagnostics mangement NONE Use backends
F122 Extended diagnostics management NONE Use backends
F123 All diagnostics NONE Use backends
F131 Grouped operations TODO Depends on grouped views
F171 Multiple schemas per user N/A Depends on backend
F191 Referential delete actions TODO
F181 Multiple module support N/A
F200 TRUNCATE TABLE statement TODO May be added in the future
F201 CAST function TODO
F202 TRUNCATE TABLE: identity column restart option TODO Depends on F200
F221 Explicit defaults FULL Use default_ and insertExpressions when inserting
F222 INSERT statement: DEFAULT VALUES clause TODO
F251 Domain support PARTIAL Use DomainTypeEntity
F261 CASE expression
F261-01 Simple CASE TODO Use searched case (see F261-02)
F261-02 Searched CASE FULL Use if_, then_, and else_
F261-03 NULLIF FULL Use nullIf_
F261-04 COALESCE FULL Use coalesce_
F262 Extended CASE expression WONTFIX Beam allows any expression in a WHEN condition
F263 Comma-separater predicates in simple CASE expression WONTFIX Unnecessary
F271 Compound character literals N/A This is syntactic sugar
F281 LIKE enhancements FULL Supported in backends that support this
F291 UNIQUE predicate FULL Use unique_
F301 CORRESPONDING in query expressions N/A Beam set functions work based off the query result type, not the column name
F302 INTERSECT table operator FULL Use intersect_
F302-01 INTERSECT DISTINCT table operator FULL Use intersect_
F302-02 INTERSET ALL table operator FULL Use intersectAll_
F304 EXCEPT ALL table operator FULL Use exceptAll_
F311 Schema definition statement TODO Would be in beam-migrate
F312 MERGE statement TODO
F313 Enhanced MERGE statement TODO
F314 MERGE statement with DELETE branch TODO
F321 User authorization N/A
F361 Subprogram support N/A
F381 Extended schema manipulation TODO
F382 Alter column data type TODO
F384 Drop identity property clause TODO
F385 Drop column generation expression clause TODO
F386 Set identity column generation clause TODO
F391 Long identifiers FULL Supported in backends that support it
F392 Unicode escapes in identifiers TODO Unsure how this applies
F393 Unicode escapes in literals TODO Unsure how this applies
F394 Optional normal form specification N/A
F401 Extended joined table FULL Full outer join using outerJoin_. Natural join is not needed. A cross join is generated automatically when there are no join conditions.
F402 Named column joins for LOBs, arrays, and multisets PARTIAL Supported in backends that support it
F403 Partitioned join tables TODO
F411 Time zone specification TODO
F421 National character FULL Supported in beam-migrate as a data type for Text
F431 Read-only scrollable cursors N/A Use the underlying backend
F441 Extended set function support TODO
F442 Mixed column references in set functions TODO Unsure how this would work with beam
F451 Character set definition TODO Likely would go in beam-migrate
F461 Named character sets TODO See F451
F491 Constraint management TODO
F492 Optional table constraint enforcement TODO
F521 Assertions TODO
F531 Temporary tables TODO
F481 Expanded NULL predicate FULL Supported in backends that support it
F555 Enhanced seconds precision TODO
F561 Full value expressions TODO
F571 Truth value tests TODO
F591 Derived tables TODO
F611 Indicator data types TODO
F641 Row and table constructors PARTIAL Use row_ (TODO)
F651 Catalog name qualifiers TODO
F661 Simple tables TODO
F671 Subqueries in CHECK constraints TODO Planned with E141-06
F672 Retrospective CHECK constraints TODO Would require temporal DB support
F690 Collation support PARTIAL beam-migrate supports some collation features
F692 Enhanced collation support TODO
F693 SQL-session and client module collations TODO
F695 Translation support TODO
F701 Referential update actions TODO
F711 ALTER domain TODO
F721 Deferrable constraints PARTIAL The syntax exists in beam-migrate
F731 INSERT column privileges N/A
F741 Referential MATCH type PARTIAL Exists in the syntax in beam-migrate, not exposed yet (TODO)
F751 View CHECK enhancements TODO
F761 Session management TODO
F762 CURRENT_CATALOG TODO
F763 CURRENT_SCHEMA TODO
F812 Basic flagging N/A
F841 LIKE_REGEX predicate TODO Easy
F842 OCCURENCES_REGEX function TODO Easy
F843 POSITION_REGEX function TODO Easy
F844 SUBSTRING_REGEX function TODO Easy
F845 TRANSLATE_REGEX function TODO Easy
F846 Octet support in regular expression operators TODO
F847 Nonconstant regular expression TODO Easy once regex support is added
F850 Top-level in FULL Use orderBy_ as usual. Beam will do the right thing behind the scenes.
F851 in subqueries FULL Works in backends that support it
F852 Top-level in views FULL Views can use any query
F855 Nested in UNKNOWN
F856 Nested in N/A Beam automatically optimizes nested orderBy_ calls
F857 Top-level in FULL limit_ and offset_ are correctly translated to dialect-specific pagination mechanisms
F858 in subqueries FULL
F859 Top-level in subqueries FULL
*F860 dynamic in TODO
*F861 Top-level in FULL See note for F587
F862 in subqueries FULL
F863 Nested in FULL
F864 Top-level in views FULL
F865 dynamic in TODO
F866 FETCH FIRST clause: PERCENT option TODO
F867 FETCH FIRST clause: WITH TIES option TODO
R010 Row pattern recognition: FROM clause TODO
R020 Row pattern recognition: WINDOW clause TODO
R030 Row pattern recognition: full aggregate support TODO
S011 Distinct data types TODO
S023 Basic structured types TODO
S024 Enhanced structured types TODO
S025 Final structured types TODO
S026 Self-referencing structured types TODO
S027 Create method by specific method name TODO
S028 Permutable UDT options list TODO
S041 Basic reference types TODO
S043 Enhanced reference types TODO
S051 Create table of type TODO
S071 SQL paths in function and type name resolution N/A Beam qualifies everything anyway
S081 Subtables PARTIAL You can use them right now, but there's no support for their creation or management in beam-migrate
S091 Basic array support PARTIAL Supported in some backends (beam-postgres for example)
S092 Arrays of user-defined types TODO Depends on user-defined types
S094 Arrays of reference types TODO
S095 Array constructors by query PARTIAL
S096 Optional array bounds PARTIAL Supported in beam-postgres
S097 Array element assignment TODO Not yet, but should be easy enough in beam-postgres
S098 ARRAY_AGG PARTIAL Supported in beam-postgres
S111 ONLY in query expressions TODO
S151 Type predicate TODO
S161 Subtype treatment TODO
S162 Subtype treatment for references TODO
S201 SQL-invoked routines on arrays TODO Would be subsumed by sql-routines (T-321)
S202 SQL-invoked routines on multisets TODO Would be subsumed by sql-routines (T-321)
S211 User-defined cast functions TODO
S231 Structured type locators TODO
S232 Array locators TODO
S233 Multiset locators TODO
S241 Transform functions TODO
S242 Alter transform statement TODO
S251 User-defined orderings TODO
S261 Specific type method TODO
S271 Basic multiset support PARTIAL Supported in beam-postgres
S272 Multisets of user-defined types TODO
S274 Multisets reference types TODO
S275 Advanced multiset support TODO
S281 Nested collection types TODO
S291 Unique constraint on entire row TODO
S301 Enhanced UNNEST TODO
S401 Distinct types based on array types TODO
S402 Distinct types based on distinct types TODO
S403 ARRAY_MAX_CARDINALITY TODO
S404 TRIM_ARRAY TODO
T021 BINARY and VARBINARY data types FULL
T022 Advanced support for BINARY and VARBINARY data types TODO
T023 Compound binary literals N/A Beam handles serialization
T024 Spaces in binary literals N/A Beam handles serialization
T031 Boolean data type FULL
T041 Basic LOB data type support TODO
T042 Extended LOB data type support TODO
T043 Multiplier T TODO
T044 Multiplier P TODO
T051 Row types PARTIAL
T061 UCS support TODO
T071 BIGINT data type FULL
T101 Enhanced nullability detection TODO
T111 Updatable joins, unions, and columns TODO
T121 WITH (excluding recursive) in query expression TODO
T122 WITH (excluding recursive) in subquery TODO
T131 Recursive query TODO
T132 Recursive query in subquery TODO
T141 SIMILAR predicate FULL
T151 DISTINCT predicate FULL
T152 DISTINCT predicate with negation TODO
T171 LIKE clause in table definition TODO
T172 AS subquery clause in table definition TODO
T173 Extended LIKE clause in table definition TODO
T174 Identity columns TODO
T175 Generated columns TODO
T176 Sequence generator support TODO
T177 Sequence generator support: simple restart option TODO
T178 Identity columns: simple restart option TODO
T180 System-versioned tables TODO
T181 Application-time period tables TODO
T191 Referential action RESTART TODO
T201 Comparable data types for referential constraints TODO
T211 Basic trigger capability TODO
T212 Enhanced trigger capability TODO
T213 INSTEAD OF triggers TODO
T231 Sensitive cursors TODO
T241 START TRANSACTION statement WONTFIX Use the backend library
T251 SET TRANSACTION option: LOCAL option WONTFIX Use the backend library
T261 Chained transactions N/A
T271 Savepoints N/A
T272 Enhanced savepoint management N/A
T281 SELECT privilege with column granularity N/A
T285 Enhanced derived column names N/A
T301 Functional dependencies TODO
T312 OVERLAY function TODO
T321 Basic SQL-invoked routines TODO
T323 Explicit security for external routines TODO
T324 Explicit security for SQL routines TODO
T325 Qualified SQL parameter references N/A Beam will likely use the qualified ones by default. Likely not exposed to user
T326 Table functions TODO
T331 Basic roles N/A
T332 Extended roles N/A
T341 Overleading of SQL-invoked functions and procodures WONTFIX Haskell doesn't allow overloading, and this seems complicated and unnecessary
T351 Bracketed comments N/A
T431 Extended grouping capabalities TODO
T432 Nested and concatenated GROUPING SETs TODO
T433 Multiargument GROUPING function TODO
T434 GROUP BY DISTINCT TODO
T441 ABS and MOD functions FULL
T461 Symmetric BETWEEN predicate FULL Beam doesn't check this
T471 Result sets return value TODO
T472 DESCRIBE CURSOR N/A Use the backend library
T491 LATERAL derived table TODO
T495 Combined data change and retrieval TODO
T501 Enhanced EXISTS predicate TODO
T502 Period predicates TODO
T511 Transaction counts TODO
T521 Nested arguments in CALL statement TODO
T522 Default values for IN parameters of SQL-invoked procs TODO
T551 Optional key words for DEFAULT syntax TODO
T561 Holdable locators TODO
T571 Array-returning SQL-invoked functions TODO Will be supported once SQL-invoked functions are
T572 Multiset-returning SQL-invoked functions TODO
T581 Regular expression substring function TODO
T591 UNIQUE constraints of possible NULL columns TODO
T601 Local cursor references N/A
T611 Elementary OLAP operations FULL See withWindow_, window functions
T612 Advanced OLAP operations PARTIAL No exclusions yet. See percentRank_, cumeDist_, and denseRank_
T613 Sampling TODO
T614 NTILE function FULL ntile_
T615 LEAD and LAG function FULL lead1_, lag1_, lead_, lag_, leadWithDefault_, lagWithDefault_
T616 Null treatment for LEAD and LAG functions TODO
T617 FIRST_VALUE and LAST_VALUE function FULL lastValue_ and firstValue_ respectively
T618 NTH_VALUE function FULL nthValue_
T619 Nested window function TODO
T620 WINDOW clause: GROUPS option TODO
T621 Enhanced numeric functions FULL All functions and aggregates in Database.Beam.Query.Extension
T641 Multiple column assignment TODO
T651 SQL-schema statements in SQL routines TODO
T652 SQL-dynamic statements in SQL routines TODO
T653 SQL-schema statements in external routines TODO
T654 SQL-dynamic statements in external routines TODO
T655 Cyclically dependent routines TODO