Reading Data with MariaDB Xpand
This page describes Xpand's support for SQL statements that read data.
Xpand supports most SQL syntax including:
SELECT, DISTINCT, [LEFT | RIGHT | OUTER] JOIN, STRAIGHT_JOIN
UNION, HAVING, GROUP BY, LIMIT, ASC, DESC, ORDER BY, FOR UPDATE
Subqueries, including with
EXISTS, NOT EXISTS
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:
GROUP BY;instead, for ordered output,
ORDER BYmust be explicit. For example, this gives a syntax error:
SELECT a, COUNT(*) FROM foo GROUP BY 1 DESC;
This is OK:
SELECT a, COUNT(*) FROM foo GROUP BY 1 ORDER BY DESC;
NOTin conjunction with user-defined variables that reference a function, e.g.
SET @a = not foo();
SELECT INTO ... OUTFILEoption. Use
mysql -e "SELECT ..." > file_nameinstead.
Caveats for SQL Support
When using the
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
t2 and then
NULL is inserted into
t2. When the subquery below is run in Xpand, the numeric value
2 is returned instead of
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)
Duplicate Handlers for SQLSTATE Code
Xpand allows duplicate handlers for the same
SQLSTATE code within a stored procedure block. MySQL errors out during compilation.