SQL Language Limitations

SQLFire has limitations and restrictions for SQL statements, clauses, and expressions.

ALTER TABLE Limitations

This release of SQLFire has the following restrictions for ALTER TABLE. SQLFire throws a SQLException “Feature not implemented�? with SQLState “0A000�? if any of these actions are attempted:
  • Adding or dropping a column when the table has data, or when the table had data at some point after creation.
  • Dropping a primary key column with or without data.
  • Adding or dropping a primary key constraint when the table has data, or when the table had data at some point after creation.

In addition, the ALTER COLUMN clause as in the SQL-92 standard is not implemented and SQLFire will throw an SQLException with state “0A000�? though it is not treated as a syntactical error.

Auto-Generated Columns

This release of SQLFire supports auto-generated IDENTITY columns, but has the following limitations:
  • Only INT and BIGINT column types can be marked as auto-generated IDENTITY columns.
  • The START WITH and INCREMENT BY clauses are supported only for GENERATED BY DEFAULT identity columns.
  • If the maximum permissible value for the type is reached in any insert, then SQLFire throws an overflow exception (SQLState: “42Z24�?). This does not necessarily mean that all possible values of that type have been used up, because it is possible that some values remain unused.

Applications should not depend on identity values being incremental across the distributed system, because SQLFire provides no ordering guarantee for concurrent inserts from multiple members. However, inserts from a single member will have the generated values in ascending order and applications can use that for ordering purposes.

LONG/LOB Column Restrictions

SQLFire does not support using columns of the following data types in indexes, ORDER BY clauses, GROUP BY clauses, DISTINCT clauses, UNION clauses, or other set operations:
  • BLOB
  • CLOB
  • LONG VARCHAR FOR BIT DATA

Columns of type LONG VARCHAR are supported in these cases.

Bulk Update Limitations

If a SQL statement performs a bulk update operation on multiple SQLFire members, any exception that occurs during the bulk update can leave some rows updated while other rows are not updated. Use transactions with bulk update statements to ensure that all updates succeed or roll back as a whole. See Atomicity for Bulk Updates.

Cascade DELETE Not Supported

SQLFire does not support cascade delete operations.

Locking Prioritizes DML over DDL

The SQLFire locking behavior prioritizes DML execution over DDL statements. DDL statments may receive a lock timeout exception (SQLState: 40XL1) if your system is processing numerous concurrent DML and DDL statements. You can configure the maximum amount of time that DDL statements wait for locks using sqlfire.max-lock-wait.

Expiration and Eviction Limitations

EXPIRE ENTRY WITH IDLETIME works only when a primary key based query is fired. Otherwise the system will not modify its accessed time when table scans or index scans happen and it gets destroyed.

EXPIRATION or EVICTION with action as DESTROY should not be set on a parent table having child tables with foreign key reference to it. This is due to a lack of cascade delete support in SQLFire. If an attempt is made to create a child table having foreign key reference to a table with such a policy then a SQLException is thrown (SQLState: "X0Y99").

INSERT with subselect

SQLFire has a limited support for INSERT statements that use a subselect statement. Nested selects and selects having aggregates are not supported; these queries throw a feature not implemented exception (SQLSTATE 0A000).

LOCK TABLE

The LOCK TABLE statement is not supported in this release of SQLFire.

Procedure Invocation (Data-Aware and Non-Data-Aware Procedures)

When you use the ON TABLE extension in a CALL statement, the WHERE clause is mandatory. If you need to route a data-aware procedure to all members that host the table (without any pruning), then you must specify some extraneous condition that always evaluates to true (such as WHERE 1=1).

A server can only handle Java procedure definitions that exactly match the JDBC parameter types in a CREATE PROCEDURE statement. If a procedure specifies parameter types that use the base class of a corresponding java type (for example, if a procedure uses java.util.Date instead of java.sql.Date) then the invocation from the client side fails.

RENAME

SQLFire does not support the RENAME statement as specified by the SQL-92 standard. A SQLException with state “0A000�? is thrown, although the statement is not treated as a syntactical error.

Updatable Result Sets

SQLFire supports updatable result sets only when the result set is obtained from a JDBC peer client connection using a SQL query that includes the FOR UPDATE clause. For example, you cannot use the following JDBC commands to work with an updatable result set:

Statement s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
String sql = "select * from trade.networth where cid > " + lowCid + " and cid <= " + highCid;

ResultSet updatableRs = s.executeQuery(sql);

SQLFire does not support the WHERE CURRENT OF clause with an UPDATE or DELETE statement to perform positioned updates with updatable cursors.

SELECT … FOR UPDATE does not provide updatable result sets for thin client connections. Any attempt to use the result set to update rows results in an exception or undefined behavior. However the SELECT will obtain appropriate locks on the affected rows, and afterward an explicit update statement can be executed to update the rows. Use the JDBC peer client driver instead.

The SELECT … FOR UPDATE statement does not lock any rows if the statement is not part of a transaction. If you use this statement outside of a transactional context (isolation level Connection.TRANSACTION_NONE) then a SQLWarning is raised and logged. This occurs because in a non-transactional context, appropriate row locks are not obtained during the select phase, and the update phase might see a modified row.

In a transaction, SQLFire locks all rows returned in a SELECT ... FOR UPDATE statement. It releases them only after the result set is closed (with ResultSet.close() or because of a transaction commit or rollback).

SQLFire does not support holdable result sets (ResultSet.HOLD_CURSORS_OVER_COMMIT).

Transaction Isolation Levels

This release of SQLFire supports the READ_COMMITTED, READ_UNCOMMITTED, and REPEATABLE_READ transaction isolation levels. Any transaction that uses the READ_UNCOMMITTED level is implicitly upgraded to be READ_COMMITTED. When nothing is specified, then JDBC operations use TRANSACTION_NONE isolation which denotes the absence of a transaction. However, SQLFire still offers certain data consistency and atomicity guarantees as described in Understanding the Data Consistency Model.

Using Distributed Transactions in Your Applications provides an overview of the distributed transaction implementation and semantics.

Trigger Limitations

This release of SQLFire does not support statement triggers.

UNION, INTERSECT, and EXCEPT Operators

SQLFire does not support any query that has either nested set operators or a set operator with either a join, function expression, SQL procedure, view, or sub-query. There is no explicit support provided for ORDER BY, GROUP BY, or complex filters in the WHERE clause in either child of a query that uses a set operator. Also, transactions and high availability features are not supported for queries that use a set operator.

In this context, a set operator includes any of these operators: UNION DISTINCT, UNION, UNION ALL, INTERSECT DISTINCT, INTERSECT, INTERSECT ALL, EXCEPT DISTINCT, EXCEPT, or EXCEPT ALL.

VIEW Limitations

SQLFire does not support views that involve grouping, aggregate, distinct, or join operations on a partitioned table.