Skip to end of metadata
Go to start of metadata

All DDL is performed online. See also Online Schema Changes

Supported DDL

Xpand supports standard MySQL DDL to CREATE, ALTER, and DROP objects, always with online schema changes. See below list of caveats. 

Xpand-specific DDL

Xpand also supports the following Xpand-specific DDL:

Executable Comments

Xpand executes comments using the format:

/*$ xpand-specific comment */ 

These comments are ignored by other databases. Xpand does not execute any other format of comments. 

Unsupported DDL

Xpand does not support:

  • ALTER TABLE...CONVERT
  • CREATE SEQUENCE
  • CREATE VIEW ... WITH CHECK OPTION 

Caveats to DDL Support

CREATE

  • Xpand accepts the syntax for CREATE TABLE with CHECK CONSTRAINT, but no constraint checking is performed. 
  • ALTER TABLE does not support the CHECK CONSTRAINT syntax. 
  • Xpand accepts the syntax for ROW_FORMAT=COMPRESSED but does not support compression. 
  • Xpand allows longer unique keys than MySQL.
  • Xpand allows specifying the position of a default ENUM value, e.g. 
    CREATE TABLE CountryLanguage (id INT, IsOfficial enum('T','F') NOT NULL default 1);

    results in a default value of 'T'. 

  • Xpand allows specifying a default value for ENUM that is not in the list of possible values, e.g.  CREATE TABLE Sunny (Pig ENUM('little', 'big') DEFAULT 'pink');

  • Xpand does not trim trailing whitespaces in ENUM values. 

  • Xpand does not raise an error if a NOT NULL column specifies NULL as the default value but treats the columns as NOT NULL. 

  • Xpand does not recommend using the FLOAT data type for primary keys. 

  • Xpand does not error if the length for DECIMAL exceeds the maximum precision and will silently truncate, even if STRICT_TRANS_TABLES is enabled. 
  • CREATE TABLE does not raise an error when the default value for an INT type field is set to a quoted string. However, inserts with the default value will be cast to 0
  • The % wild card is not supported in database names.

SHOW 

  • Xpand does not support WHERE clauses as part of SHOW COLUMNS or SHOW FIELDS 
  • After an unconstrained DELETE, the auto_increment value for a table will appear to be incorrect when viewed via SHOW CREATE TABLE. The value will be properly reset once the next auto_increment value is used. 

Caveats for Views

  • If you create a view using SELECT *, the view definition will change if the underlying table definition changes. 

  • Xpand will accept the syntax for the ALGORITHM option for views, but does not apply any algorithms. 
  • When using DESCRIBE on a view, if the underlying column has a default value of 0, Xpand will display NULL instead. 

DROP

  • DROP TABLE will not wait for other sessions to commit transactions before proceeding with dropping the table. 
  • Xpand does not support dropping primary keys via DROP INDEX `PRIMARY`
  • To drop a primary key, specify LOCK=SHARED as part of the ALTER statement:

    sql> ALTER TABLE T1 DROP PRIMARY KEY, LOCK=SHARED; 

Other caveats for DDL

  • % wild cards are not supported in database names.

See also:

  • No labels