Page tree
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
  • Support for INSERT, UPDATE, DELETE, REPLACE 
  • 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 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)

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 supported
  • 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(): 

    • 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

  • GET_LOCK(): Use of GET_LOCK with Statement-based Replication is considered unsafe, though ClustrixDB does not raise an error. MySQL raises an error. 
  • 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.
  • PERIOD_ADD: The 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() 
    • 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 ClustrixDB, 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.

Foreign Keys

Unique Foreign Key references

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.

Referential Actions

ClustrixDB does not support the referential action SET DEFAULT. 

Ordering

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:

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)

DDL

DB supports standard MySQL DDL to CREATE, ALTER, and DROP objects with the following caveats. See also Online Schema Changes

CREATE

Autoincrement Value in SHOW CREATE TABLE Output

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.

Default Values for ENUM Columns

DB is more permissive than MySQL in the following cases: 

  1. 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.

  2. 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.  

CREATE TABLE and Quoted Strings as Default INT Field Value

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.

CREATE TABLE and NULL as Default NOT NULL Field Value

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.

CREATE TABLE with CHECK CONSTRAINT

ClustrixDB supports the syntax CREATE TABLE with CHECK CONSTRAINT, however constraint checking is not performed.

CREATE TABLE VARCHAR with no size

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);

CREATE TABLE UNIQUE KEY (column, ....)

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.

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

CREATE TABLE with ROW_FORMAT=COMPRESSED

ClustrixDB will allow syntax for row_format=compressed but not actually perform the compression. 

CREATE VIEW

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;
  • If you fully qualify column names, then the behavior will match MySQL.

DROP

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.

ALTER

ALTER TABLE ALGORITHM

ClustrixDB will accept the syntax for the ALGORITHM option of ALTER TABLE but does not support the underlying functionality. 

ALTER TABLE LOCK

ClustrixDB will accept the syntax for the LOCK option of ALTER TABLE but does not support the underlying functionality. 

Views

  • When using DESCRIBE on a view, if the underlying column has a default value of 0, ClustrixDB 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.

 

  • No labels