Reading Data with MariaDB Xpand

Overview

This page describes Xpand's support for SQL statements that read data.

Supported Statements

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 IN

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

Unsupported Statements

Xpand does not support:

  • ASC or DESC qualifier for GROUP BY; instead, for ordered output, ORDER BY must 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;

  • COLLATE in SELECT

  • CUBE

  • EXCEPT

  • INTERSECT

  • LOW PRIORITY or HIGH PRIORITY

  • NOT in conjunction with user-defined variables that reference a function, e.g. SET @a = not foo();

  • ROLLUP

  • SELECT INTO ... OUTFILE option. Use mysql -e "SELECT ..." > file_name instead.

  • Subqueries with ALL or SOME

Caveats for SQL Support

SELECT subqueries

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)

Duplicate Handlers for SQLSTATE Code

Xpand allows duplicate handlers for the same SQLSTATE code within a stored procedure block. MySQL errors out during compilation.