This is documentation for a previous version of ClustrixDB. Documentation for the latest version can be found here

Skip to end of metadata
Go to start of metadata

See Also:

SQL Statements and Functions

  • ClustrixDB speaks SQL and provides full support for standard SELECT...WHERE MySQL syntax with support for JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN
  • ClustrixDB provides support for GROUP BY, ORDER BY, and aggregate functions AVG(), COUNT(), GROUP_CONCAT, MAX(), MIN(), STD(), SUM()
  • Support for SHOW statements to describe tables, indexes, and databases as well as some support for the MySQL INFORMATION_SCHEMA 
  • Support for EXPLAIN to describe how the ClustrixDB Planner costs and plans query execution

Caveats for SQL Support

SELECT subqueries: When using the ANY or IN options in SELECT subqueries, ClustrixDB will return a numeric value instead of a “NULL” value when the subquery resolves to NULL. However, in MySQL when the subquery resolves to NULL, MySQL returns a “NULL” value. For example, assume numeric values are inserted into the following tables t1 and t2 and then NULL is inserted into t2. When the subquery below is run in Clustrix, the numeric value "2" is returned instead of "NULL".

	Insert into t1 value (1), (2);
	Insert into t2 values (1);
	Insert into t2 values (NULL);

	ClustrixDB> select a from t1 where a not IN (select b from t2);
	| 2  |
	1 row in set (0.00 sec)

Unsupported SQL

  • ClustrixDB does not support ASC or DESC qualifier for GROUP BY; instead, for ordered output, ORDER BY must be explicit.  For example: 
    select a,count(*) from foo group by 1 desc; -- will give a syntax error
    select a,count(*) from foo group by 1 order by desc; -- OK
  • ClustrixDB does not support ROLLUP
  • COLLATE in SELECT statements is not supporrted
  • ClustrixDB does not support the IGNORE keyword as part of a DELETE statement 
  • ClustrixDB does not support use of NOT in conjunction with user-defined variables that reference a function, e.g.
     SET @a = not foo();
  • ClustrixDB does not support  ZEROFILL
  • ClustrixDB does not support the LOCK TABLES statement

Caveats for MySQL Function Support

  • COUNT(): 

    • Clustrix is more permissive than MySQL for the COUNT() function. Clustrix will allow SQL such as: SELECT COUNT(id1, id2) FROM foo but MySQL will error

    • Clustrix will include trailing NULL values a COUNT(), while MySQL does not

  • GROUP_CONCAT(): Multiple GROUP_CONCAT statements are supported, but only one ORDER BY is allowed per select statement
  • LOCATE(): In ClustrixDB, LOCATE() returns NULL when position is provided as NULL. MySQL returns 0 in such instances.
  • MICROSECOND(): Clustrix will accept calls to MICROSECOND, but does not implement this function and will always return 0
  • PERIOD_ADDThe results of the  function do not match MySQL. Specifically, this affects when year boundaries are being crossed or approached.
  • RAND(): Does not accept an integer argument as seed.
  • ROW_COUNT() (support added in v5.2-10223) 
    • When updating a row, if the values are not being changed (e.g. (1,1) --> (1,1)) mysql reports 0, ClustrixDB returns 1
    • When replacing a row with an identical row, mysql reports 1, ClustrixDB returns 2
    • On Clustrix, ROW_COUNT does not reflect the number of rows affected by a stored procedure 
    • ROW_COUNT does not match mysql behavior for DDL (e.g. CREATE TABLE then select ROW_COUNT)
    • When committing an explicit transaction, ClustrixDB reports the row_count value for the most recent statement before the COMMIT. In other words, row_count behaves as if there was no COMMIT. 
  • SYSDATE(): Does not reflect time zone settings; always returns UTC.

Feature Difference on Unique Foreign Keys

MySQL (InnoDB) allows foreign keys to reference non-unique parent keys, but ClustrixDB has opted to disallow foreign keys with non-unique parents with referential actions CASCADE or SET NULL. Non-unique parents are permitted as long as CASCADE and SET NULL are not also in use (RESTRICT and NO ACTION are permitted).

This change was made as of v5.0-8987 

Prior to those releases, the behavior for foreign keys to non-unique parents could result in errors for "Container key not found" in the case of concurrent actions to the multiple parent rows. 

To enforce this requirement, when a schema with a foreign key referencing a non-unique parent is created or altered and uses CASCADE or SET NULL, ClustrixDB will provide an error message:

Invalid foreign key specification encountered in DDL statement: FOREIGN KEY <name> requires UNIQUE index on 
<name> if using the CASCADE or SET NULL referential action.

The referential action SET DEFAULT is recognized by the ClustrixDB parser, but like  MySQL (innoDB), ClustrixDB does not accept table definitions that contain the ON DELETE SET DEFAULT and ON UPDATE SET DEFAULT clauses.

Clustrix Support can assist with identifying whether your schema contains constraints that are not permitted.


By default, ClustrixDB imposes no order on result sets, whereas MySQL results are often ordered as a side effect of implementation (for example, when a query reads an index or includes a GROUP BY clause). To ensure that query results are returned in the desired sequence, specify the ORDER BY clause. Alternatively, you can set the global variable consistent_order to true. This will have an additional compile time overhead that is mitigated by the query cache. See Consistent Order for more details

Inline Variable Evaluation

ClustrixDB has limited support for inline variable evaluation. MySQL semantics depend very heavily on serial execution of queries, which makes it straightforward to enforce strict ordering throughout the evaluation of a query. ClustrixDB leverages a parallel evaluation model to provide distributed scale, so such serialization would have an adverse impact on performance.

Here is an example of how ClustrixDB provides only partial support for inline variables:

create table foo (a int, b int);
insert into foo values (2, 0), (4, 0), (6, 0);
set @rc := 1;
insert into foo select @rc := @rc + 1, a from foo limit 1;

Here, depending on when the ClustrixDB planner applies the limit, the value for rc may be incremented multiple times. The results show that indeed, the inline variable handling incremented rc twice.

ClustrixDB> select @rc;
| @rc  |
| 2    |
1 row in set (0.00 sec)

ClustrixDB> select * from foo order by a, b;
| a    | b    |
|    2 |    0 |
|    2 |    2 |
|    4 |    0 |
|    6 |    0 |
4 rows in set (0.00 sec)


ClustrixDB supports standard MySQL DDL to Create, Alter, and Drop objects with the following caveats. See also Online Schema Changes


Autoincrement Value in SHOW CREATE TABLE Output

In ClustrixDB the autoincrement value shows up improperly in SHOW CREATE TABLE output following an unconstrained delete. ClustrixDB resets it properly after one successful insert into the table.

Default Values for ENUM Columns

ClustrixDB is more permissive than MySQL in the following cases: 

  1. ClustrixDB accepts a default integer value for a column with ENUM values which is positional. For example:

    CREATE TABLE CountryLanguage (id INT, IsOfficial enum('T','F') NOT NULL default 1);
    INSERT INTO CountryLanguage VALUES (1, default);
    SELECT * FROM CountryLanguage;

    Clustrix will return a default value of 'T' and MySQL errors out in such a situation.

  2. When creating an ENUM column, ClustrixDB will allow you to specify a default value that is not in the enumeration of possible values. For example:

    CREATE TABLE Sunny (Pig ENUM('little', 'big') DEFAULT 'pink');

    MySQL would reject this default value and not allow the table to be created.  

CREATE TABLE and Quoted Strings as Default INT Field Value

In ClustrixDB CREATE TABLE does not raise an error when the default value for an INT type field is set to a quoted string; subsequent inserts with default value will however set the field value to 0. MySQL errors out in the CREATE TABLE statement itself.

CREATE TABLE foo (id int DEFAULT 'abc', name varchar(30));

The above will not error in ClustrixDB, however a subsequent insert statement will insert 0 for the id field.

CREATE TABLE and NULL as Default NOT NULL Field Value

In ClustrixDB CREATE TABLE does not raise an error when the default value for a NOT NULL column is set to NULL; subsequent inserts with no default value specified fails. MySQL errors out in CREATE TABLE statement itself.

create table foo (id int not null default null);

The above will not fail in ClustrixDB, however insert into foo values(); will fail.


ClustrixDB supports the syntax CREATE TABLE with CHECK CONSTRAINT using the IN operator; however constraint checking is not performed.


ClustrixDB permits table definitions that use the VARCHAR data type with no max length, and supply a default max length of 21845. MySQL does not permit this. 

create table foo (bar varchar);


ClustrixDB allows a UNIQUE key length that exceeds the MySQL maximum length. Keep this in mind when importing or replicating a database to MySQL from Clustrix.

CREATE TABLE with multiple TIMESTAMP  columns

MySQL only permits a single column that can be auto-initialized (DEFAULT CURRENT_TIMESTAMP) and or automatically set to the current timestamp (UPDATE CURRENT_TIMESTAMP). On MySQL, of these behaviors are applied to the first timestamp column created, but not to subsequent timestamp columns.

ClustrixDB differs from MySQL in that:

  • ClustrixDB permits multiple timestamp columns with DEFAULT CURRENT_TIMESTAMP and UPDATE CURRENT_TIMESTAMP specified
  • Both of these behaviors are applied by default to all timestamp columns, not just the first timestamp column created


When creating a view, if creating a table using a SELECT statement:

  • If you do not fully qualify column names the column names and use a SELECT *, then the view definition will change if the underlying table definition changes. This differs from MySQL behavior.

    create view bar as select * from foo;
  • If you fully qualify column names, then the behavior will match MySQL.


Multi-Table DROP Statements

A ClustrixDB slave errors out or stops on a multi-table DROP statement being replicated where only a subset of tables exist, whereas a MySQL slave does not.

DROP TABLE Command from a Different Session

ClustrixDB allows you to drop a table created and rows inserted from another session with autocommit set to 0 explicitly. MySQL hangs on a DROP TABLE command from a different session until the session that created the table, inserted rows, and set autocommit to 0 issues an explicit COMMIT.



ClustrixDB has added the new-option described below to the available ALTER DATABASE syntax:

ALTER DATABASE [name] <mysql-option>
ALTER DATABASE [name] SET <mysql-option | new-option> [ , ... ]


<mysql-option> ::= [DEFAULT] COLLATE [=] <collation-name> 
			| [DEFAULT] CHARACTER SET [=] <charset-name>

<new-option> ::= <mysql-option>

new-option (HIDDEN) is only for ClustrixDB-specific/non-MySQL arguments. For MySQL arguments (charset/collate), use the mysql-option so it works over replication.


  • When using DESCRIBE on a view, if the underlying column has a default value of "0", Clustrix will display NULL instead. 

Other caveats for DDL

  • When creating a column of type bit(0), MySQL will automatically create a bit(1). ClustrixDB will not allow you to create a column with type bit(0) and will provide an error message.
  • ClustrixDB does not permit ALTER statements that rename a table to be prefixed with a period, e.g. alter table foo rename .foo