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 ClustrixDB, the numeric value 2 is returned instead of NULL.
sql> Insert into t1 value (1), (2); sql> Insert into t2 values (1); sql> Insert into t2 values (NULL); sql> select a from t1 where a not IN (select b from t2); +----+ | 2 | +----+ 1 row in set (0.00 sec)
ClustrixDB is more permissive than MySQL for the COUNT() function. ClustrixDB will allow SQL such as: SELECT COUNT(id1, id2) FROM foo but MySQL will error
ClustrixDB will include trailing NULL values a COUNT(), while MySQL does not
MySQL 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.
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.
ClustrixDB does not support the referential action SET DEFAULT.
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:
sql> create table foo (a int, b int); sql> insert into foo values (2, 0), (4, 0), (6, 0); sql> set @rc := 1; sql> 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.
sql> select @rc; +------+ | @rc | +------+ | 2 | +------+ 1 row in set (0.00 sec)
sql> select * from foo order by a, b; +------+------+ | a | b | +------+------+ | 2 | 0 | | 2 | 2 | | 4 | 0 | | 6 | 0 | +------+------+ 4 rows in set (0.00 sec)
DB supports standard MySQL DDL to CREATE, ALTER, and DROP objects with the following caveats. See also Online Schema Changes
In DB the autoincrement value shows up improperly in SHOW CREATE TABLE output following an unconstrained DELETE. DB resets it properly after one successful INSERT into the table.
DB is more permissive than MySQL in the following cases:
DB accepts a default integer value for a column with ENUM values which is positional. For example:
sql> CREATE TABLE CountryLanguage (id INT, IsOfficial enum('T','F') NOT NULL default 1); sql> INSERT INTO CountryLanguage VALUES (1, default); sql> SELECT * FROM CountryLanguage;
DB will return a default value of T and MySQL errors out in such a situation.
When creating an ENUM column, DB will allow you to specify a default value that is not in the enumeration of possible values. For example:
sql> CREATE TABLE Sunny (Pig ENUM('little', 'big') DEFAULT 'pink');
MySQL would reject this default value and not allow the table to be created.
In DB 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.
sql> CREATE TABLE foo (id int DEFAULT 'abc', name varchar(30));
The above will not error in DB, however a subsequent INSERT statement will insert 0 for the id field.
In DB 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.
sql> create table foo (id int not null default null);
The above will not fail in DB, however insert into foo values(); will fail.
ClustrixDB supports the syntax CREATE TABLE with CHECK CONSTRAINT, 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.
sql> 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 ClustrixDB.
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 will allow syntax for row_format=compressed but not actually perform the compression.
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.
sql> create view bar as select * from foo;
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 will accept the syntax for the ALGORITHM option of ALTER TABLE but does not support the underlying functionality.
ClustrixDB will accept the syntax for the LOCK option of ALTER TABLE but does not support the underlying functionality.