What's Working Already?

Here's a laundry list of SQL features which are already working in Farrago as of release 0.9. If a sub-feature isn't mentioned (e.g. CHECK clause in CREATE TABLE), it isn't available yet. Everything is supposed to work according to the SQL standard, so if something doesn't, that's either a bug or an incomplete feature. Which standard? Normal procedure is to consult the SQL:2003 standard and cross-check it with SQL-92 and SQL:1999 to see if there are any incompatibilities.

DDL

All identifiers may be up to 128 characters. Identifiers may be quoted (with case-sensitivity) or unquoted (with implicit uppercasing before both storage and lookup). The compound identifier "catalog_name"."schema_name"."object_name" fully qualifies a schema element.
SET CATALOG: the system currently starts out with several catalogs predefined (SYS_BOOT, SYS_MOF, SYS_CWM, SYS_FEM, and LOCALDB). Additional catalogs can be created implicitly via the SQL/MED CREATE SERVER command. The SYS_BOOT catalog contains internal tables for metadata storage, along with things like views for implementing JDBC metadata. The SYS_MOF catalog is a virtual catalog exposing the highest-level metamodel. The SYS_CWM catalog is a virtual catalog exposing the contents of the CWM-based repository. SYS_FEM is similar, but for Farrago extensions to CWM. The LOCALDB catalog stores user metadata about both local and foreign tables. The default catalog starts out as LOCALDB; SET CATALOG can be used to change this per-session.
SET SCHEMA: user schemas are created in the LOCALDB catalog. SET SCHEMA can be used to change the default schema within the current catalog. The SYS_CWM catalog defines one schema per CWM package (e.g. "Core", "Relational", or "KeysIndexes"); the tables in the CWM schemas correspond to classes (e.g. schema SYS_CWM."Core" contains "ModelElement" and "Feature"; schema SYS_CWM."Relational" contains "Table" and "View"). Likewise for SYS_FEM.
CREATE SCHEMA: a schema can be created by itself, or together with all of its contents in the same statement. The AUTHORIZATION clause is currently parsed but ignored. The PATH clause is supported for UDR/UDT lookup.
CREATE TABLE: for the default row-store implementation, every table must have a primary key. Supported column datatypes are BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL (alias NUMERIC), REAL, DOUBLE (alias DOUBLE PRECISION), VARCHAR (alias CHARACTER VARYING), VARBINARY, CHAR (alias CHARACTER), BINARY, DATE, TIME, TIMESTAMP, and user-defined type. Decimal precision is limited to 19 digits. Time precision is currently limited to seconds, but will eventually be enhanced to microseconds (which should be the default for TIMESTAMP according to the SQL standard). Timezones are not yet supported. Support for standard multisets and time intervals is currently under development. Autoincrement columns are supported.
NOT NULL: can be specified explicitly on any column; is also implied for columns which participate in the table's primary key (or clustered index for row-store tables).
DEFAULT: can be specified for any column; otherwise default value is NULL. Currently, default values can only be literals of the appropriate datatype (not arbitrary expressions).
PRIMARY KEY/UNIQUE: constraints can be specified at either the column level or the table level. Constraints can be named, and are enforced via implicitly created indexes.
CREATE CLUSTERED INDEX: if specified, this must be at the end of the CREATE TABLE statement, not in a separate statement. Example:
```
create table t(i int not null primary key,j int not null,k int)
create clustered index t_x on t(j);
create index t_y on t(k);
```
If not specified, the primary key is used implicitly for clustering. For the default row-store, every table has exactly one clustered index.
CREATE INDEX: unclustered indexes can be created either at the end of the table definition or in separate statements. Indexes are considered to be schema elements but are always created in the same schema as their containing table.
CREATE GLOBAL TEMPORARY TABLE: supports ON COMMIT {PRESERVE|DELETE} [ROWS] option. Note that the "GLOBAL TEMPORARY" name in the standard is confusing: this is really an instantiate-on-reference session-local temporary table with a permanent shared metadata definition in the catalog. Any indexes on a temporary table must be specified together with the CREATE TABLE statement; indexes may not be subsequently added or dropped (if you need that, drop and recreate the table definition). Beyond that, temporary tables support the same features as permanent tables (constraints, defaults, etc.). However, see the "What's Broken" section below for some known issues.
CREATE VIEW: all views are currently read-only.
SQL/MED support. CREATE/DROP FOREIGN DATA WRAPPER, CREATE/DROP SERVER, and CREATE/DROP FOREIGN TABLE are working, along with IMPORT FOREIGN SCHEMA. Implementations for JDBC, flatfiles and MDR are available (the MDR implementation is already being used for exposing the catalog contents via SQL). The system starts out with foreign data wrappers SYS_MDR, SYS_MOCK_FOREIGN, SYS_JDBC, and SYS_FILE_WRAPPER predefined. Foreign data server SYS_MOCK_FOREIGN_DATA_SERVER and HSQLDB_DEMO can be used for test purposes.
Pluggable storage engine support via CREATE/DROP LOCAL DATA WRAPPER. The system starts out with local data wrappers SYS_FTRS (row-store) and SYS_COLUMN_STORE (LucidDB column-store), as well as SYS_MOCK. A corresponding local data server (SYS_FTRS_DATA_SERVER, SYS_COLUMN_STORE_DATA_SERVER, or SYS_MOCK_DATA_SERVER) can be specified in the SERVER clause for CREATE TABLE when creating a local table (default is SYS_FTRS_DATA_SERVER for vanilla Farrago session personality).
CREATE FUNCTION/PROCEDURE: supports functions with SQL-defined bodies consisting of a single RETURN statement, and Java-defined external functions and procedures with IN parameters only. For external functions, parameters must be of builtin type. Procedure result sets are not supported, but see the UDX spec for user-defined transformation functions which take cursors as input and return tables as output. External routines may call back into JDBC via standard jdbc:default:connection.



CREATE
TYPE/CONSTRUCTOR/ORDERING: supports SQL-defined types (both
structured and distinct, but not domains and not SQL/JRT).  Neither
type inheritance nor typed tables are supported.  Constructor methods
may only consist of a list of SET attribute statements followed by
RETURN SELF.  Observers are supported but mutators are not.
User-defined casts and transformations are not supported, but
user-defined orderings are.  Directly querying distinct type values via
JDBC is supported; this is currently unreliable for structured types.


CALL SQLJ.INSTALL_JAR/REMOVE_JAR: standard system-defined
procedures for jar management.  Also supported are non-standard
CREATE/DROP JAR statements; these are equivalent and used internally
to implement the standard SQLJ procedures.  Deployment descriptors and
jar paths are not yet supported.

SET PATH: defines the lookup scope for user-defined routine and
type references.

DROP: both RESTRICT and CASCADE are supported where relevant.

DROP INDEX:  only unclustered indexes may be dropped.

TRUNCATE TABLE "tbl":  delete all rows from tbl without logging them
individually.

ALTER
TABLE "tbl" REBUILD: physically reorganize table contents, purging
deleted entries and rebalancing indexes where supported.

ALTER
TABLE "tbl" ADD COLUMN: add a new column to an existing table.

ANALYZE TABLE
"tbl" {ESTIMATE|COMPUTE} STATISTICS FOR { ALL COLUMNS | COLUMNS (a, b, c...)
}: collect statistics on stored data distribution for use by the
optimizer.

ALTER SYSTEM SET "parameterName" = value; change a system
parameter (see documentation
for all parameters).

CHECKPOINT:  force an immediate checkpoint.

COMMIT:  commit current transaction.

ROLLBACK:  rollback current transaction.

SAVEPOINT "x":  create a savepoint with name x.

ROLLBACK TO SAVEPOINT "x": partially rollback current transaction
to savepoint x.


ALTER SYSTEM ADD CATALOG JAR and ALTER SESSION IMPLEMENTATION {SET|ADD} JAR:
Farrago-specific mechanisms for system extensibility


ALTER SYSTEM REPLACE CATALOG:
Farrago-specific catalog upgrade mechanism


CREATE OR REPLACE [ RENAME TO "new-name" ] is supported for all
objects except local tables and indexes.  This is a Farrago-specific
extension.  The command succeeds as long as the replacement definition
does not invalidate any dependencies.


CREATE ROLE "role-name" [ WITH ADMIN "admin-auth-id" ]


CREATE USER "user-name" [ AUTHORIZATION 'auth-string' ] 
[ DEFAULT {CATALOG|SCHEMA} "default-qualifier" ]:  this is a
Farrago-specific extension (SQL:2003 stops at role level.)


GRANT role/privilege


Sample
datasets: This is a Farrago-specific extension.


CREATE
LABEL: This is a Farrago-specific extension.



Queries



SELECT [DISTINCT] ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY

UNION [ALL], INTERSECT, EXCEPT

FROM clause may contain tables, views, multi-row VALUES,
LEFT/RIGHT/INNER/CROSS JOIN, old-style comma list, nested queries,
UDX invocations, and explicit TABLE references.


EXPLAIN PLAN [ { EXCLUDING | INCLUDING [ ALL ] } ATTRIBUTES ]
[{WITH|WITHOUT} IMPLEMENTATION] [AS XML] FOR
query-or-DML-statement; shows optimizer plan.  WITHOUT
IMPLEMENTATION yields unoptimized abstract relational plan.  Default
is WITH IMPLEMENTATION, which yields optimized plan with all physical
operators.  AS XML yields detailed output in element-oriented XML.
EXCLUDING ATTRIBUTES shows relational operator names only.  
INCLUDING ATTRIBUTES is the default.  INCLUDING ALL ATTRIBUTES provides
additional attributes such as cost.

TABLESAMPLE
with BERNOULLI and SYSTEM options

The LucidDB SQL reference has the most up-to-date list of
supported row
expressions



DML



INSERT

UPDATE

DELETE

MERGE (FTRS support not yet available)

CALL



JDBC API



prepared statements with dynamic parameters 
(e.g. select * from emps where name=?)

DatabaseMetaData:  all except getTablePrivileges, getColumnPrivileges,
getBestRowIdentifier, getVersionColumns, getImportedKeys, getExportedKeys,
getCrossReference

savepoint API

autocommit or manual commit (either via API or via SQL)

implicit query plan cache



Optimizer



hash join/agg and nested loop joins for inner and outer joins

cartesian product join (for inner joins and degenerate outer joins)

index join (for LEFT, RIGHT, or INNER join;
single-column equijoin only)

single-column index search; only one index at a time

hybrid Java/Fennel plans



Executor



Row expressions are implemented via a mix of Java code generation
and Fennel calculator programs.  In cases where both implementations
are available for the same value expression, the optimizer decides
(currently always in favor of Fennel; eventually this should be
properly costed).  System parameter "calcVirtualMachine" governs this
behavior (default setting CALCVM_AUTO uses mixed mode; CALCVM_FENNEL
forces Fennel only; CALCVM_JAVA forces Java only, used implicitly when
Fennel support is not available).

Generated Java code is compiled by the Janino compiler (and then
possibly to native code JIT).

All other execution objects are implemented by Fennel, except for
foreign table access (such as MDR or JDBC).

Each DML statement runs in its own implicit subtransaction as required
by the standard.



Management API




A variety of management views and procedures are available.



Runtime Modes



Embedded engine:  everything runs in a single process; JDBC calls
go into the engine directly.

Client/server: implemented using VJDBC.  By default, the
server listens for RMI connections on port 5433, and uses multiple
dynamically assigned ports for individual RMI objects.  This can be
controlled via configuration parameters;
serverRmiRegistryPort controls the connection port, and
serverSingleListenerPort forces all RMI objects to
multiplex over a single statically assigned port (default value of
-1 means use dynamically assigned ports for this
instead).



What's Broken?

Besides the absence of important standard stuff like referential
integrity, it's worth mentioning some serious limitations in the
features listed above:



REVOKE is not yet implemented, and authorization checks are limited to
table access only (authorization checks for DDL statements is not yet
implemented).  No authentication method is implemented yet.  There are
a number of known security problems in routine invocation, system
routine accessibility, and SQL/MED.

Type inference is missing for some dynamic parameter contexts.

CHARACTER
SET support is limited to LATIN1 (ISO-8859-1) and UTF16 (UNICODE);
collations other than case-sensitive are not supported at all yet.

Bulk load for multi-level trees is implemented internally, but not
yet hooked into CREATE INDEX.

There's no lock manager in the vanilla Farrago session
personality, so concurrency control defaults to dirty read, with the
potential for recovery failures due to write-on-write conflicts.

Updates to unique keys are not ordered correctly, so spurious
uniqueness violations result for a statement like update t set pk=pk+1
(where pk is a primary key).  This probably won't be fixed until
triggers are implemented.

INSERT/UPDATE do not support the explicit DEFAULT keyword.

The SET SCHEMA command cannot be used to set a schema in a
virtual catalog based on a SQL/MED server (including the predefined
SYS_CWM and SYS_FEM catalogs).

There are a number of problems with global temporary table support;
see FRG-337
and FRG-298.



Feedback on anything inaccurate
or missing from these lists is always apppreciated.