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".
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
SET @a = not foo();
ClustrixDB does not support the LOCK TABLES statement
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
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:
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
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:
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 supports standard MySQL DDL to Create, Alter, and Drop objects with the following caveats. See also Online Schema Changes
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.
ClustrixDB is more permissive than MySQL in the following cases:
ClustrixDB accepts a default integer value for a column with ENUM values which is positional. For example:
Clustrix will return a default value of 'T' and MySQL errors out in such a situation.
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:
MySQL would reject this default value and not allow the table to be created.
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.
The above will not error in ClustrixDB, however a subsequent insert statement will insert 0 for the id field.
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.
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.
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.
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:
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.
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.
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:
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.