|GemFire XD Reference / SQL Language Reference|
GemFire XD has limitations and restrictions for SQL statements, clauses, and expressions.
The following table describes basic limits associated with database objects and statements.
|Maximum columns in a table||1,012|
|Maximum columns in a view||5,000|
|Maximum number of parameters in a stored procedure||90|
|Maximum indexes on a table||32,767 or storage capacity|
|Maximum tables referenced in an SQL statement or a view||storage capacity|
|Maximum elements in a select list||1,012|
|Maximum predicates in a WHERE or HAVING clause||storage capacity|
|Maximum number of columns in a GROUP BY clause||32,677|
|Maximum number of columns in an ORDER BY clause||1,012|
|Maximum number of prepared statements||storage capacity|
|Maximum declared cursors in a program||storage capacity|
|Maximum number of cursors opened at one time||storage capacity|
|Maximum number of constraints on a table||storage capacity|
|Maximum level of subquery nesting||storage capacity|
|Maximum number of subqueries in a single statement||storage capacity|
|Maximum number of rows changed in a unit of work||storage capacity|
|Maximum constants in a statement||storage capacity|
|Maximum depth of cascaded triggers||16|
Converseley, attempting to add a foreign key constraint on unique key columns of a master table fails with an exception if the foreign key table columns are not colocated with the master table.
In addition, the ALTER COLUMN clause as described the SQL-92 standard is not implemented. GemFire XD only supports the ALTER COLUMN clause for changing a non-identity column to a GENERATED ALWAYS AS IDENTITY column.
GemFire XD does not support executing DDL statements in the body of a procedure or function.
Applications should not depend on identity values being incremental across the distributed system, because GemFire XD 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.
Columns of type LONG VARCHAR are supported in these cases.
If a SQL statement performs a bulk update operation on multiple GemFire XD 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.
GemFire XD does not support cascade delete operations.
The GemFire XD 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 gemfirexd.max-lock-wait.
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 GemFire XD. 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").
GemFire XD 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).
The LOCK TABLE statement is not supported in this release of GemFire XD.
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.
GemFire XD 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.
GemFire XD 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);
GemFire XD 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, GemFire XD 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).
GemFire XD does not support holdable result sets (ResultSet.HOLD_CURSORS_OVER_COMMIT).
This release of GemFire XD 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, GemFire XD 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.
This release of GemFire XD does not support statement triggers.
GemFire XD 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.
GemFire XD does not support views that involve grouping, aggregate, distinct, or join operations on a partitioned table.