This page describes Xpand's support for data manipulation including read-only SELECT statement.
Xpand supports most SQL syntax including:
as well as
While we do not synchronize our feature development with MySQL releases, we strive to provide the features and functions required by our customers to successfully run production environments. We monitor both our customers' requirements and new MySQL functionality to determine our feature roadmap and use recent shipping releases of MySQL as part of an automated QA process for compatibility.
Xpand does not support:
When using the ANY or IN options in SELECT subqueries, Xpand 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 Xpand, 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)
Xpand uses a parallel evaluation model, which can result in non-deterministic results for inline variable evaluation:
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;
Depending on when the Xpand planner applies the LIMIT, the value for rc may be incremented multiple times:
sql> select @rc; +------+ | @rc | +------+ | 2 | +------+
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)
Xpand allows duplicate handlers for the same SQLSTATE code within a stored procedure block. MySQL errors out during compilation.
Xpand ignores LOCK TABLE syntax. No error is returned, because dump files generated by mysqldump include LOCK TABLE commands and Xpand must be able to process such files.
However, Xpand does support LOCK TABLES ... FOR UPDATE which accepts a list of relations to issue an exclusive table lock on the listed relations. With this lock, inserts by other transactions are still allowed, but UPDATE, DELETE, or SELECT FOR UPDATE will be blocked. Note that for LOCK TABLES ... FOR UPDATE to be effective, it must be issued within the context of an explicit transaction, as the table lock is held until that transaction is committed or rolled back (which will be instantly if in autocommit mode).
It is also possible to have LOCK TABLES ... FOR UPDATE also block inserts. There is a performance penalty associated with having inserts check for this lock, so this behavior is not enabled by default. This behavior is enabled on a per-table basis with ALTER TABLE table_name ENABLE INSERT_LOCK, and can be disabled with ALTER TABLE table_name DISABLE INSERT_LOCK. A table with this behavior enabled will include INSERT_LOCK at the end of SHOW CREATE TABLE output:
sql> show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) ) CHARACTER SET utf8 /*$ REPLICAS=2 SLICES=6 INSERT_LOCK */
Here is an example of the behavior, where the s1> and s2> prompts indicate two separate sessions of mysql client:
s1> alter table foo enable insert_lock; Query OK, 0 rows affected (0.22 sec) s1> begin; Query OK, 0 rows affected (0.00 sec) s1> lock tables foo for update; Query OK, 0 rows affected (0.03 sec) s2> insert into foo values (666); [session blocks] s1> commit; Query OK, 0 rows affected (0.00 sec) [session s2 resumes] Query OK, 1 row affected (2 min 14.29 sec)