Here is a list of lessons we learned while migrating our reporting tools from MySQL (5.6 with strict mode off) to Exasol.
Hopefully this will be useful for others making the same transition:
#for indicating SQL comments. Exasol only supports
'\n'in MySQL strings is interpreted as a newline. In Exasol it's treated as a literal slash + n so instead you need to use a literal newline character
`...`while in Exasol it is
'...'while in Exasol it is only
PATHis a keyword in Exasol but not in MySQL. You can get a list of Exasol keywords via
SELECT keyword FROM exa_sql_keywords WHERE reserved = TRUE;
REGEXP -> REGEXP_LIKE, DATE -> TO_DATE, TRIM, DAYOFMONTH -> DAY, UNIX_TIMESTAMP -> POSIX_TIME, DATABASE() -> CURRENT_SCHEMA, DATETIME -> TIMESTAMP, SYSTEM -> SESSIONTIMEZONEetc.
CONCAT_WS, FIELD, DAYOFWEEK,etc
SELECTcolumns which are not part of the
GROUP-BYmust be enclosed in an aggregation of some sort (
COUNT, SUM, MAX, FIRST_VALUE, etc) or will otherwise generate an error. MySQL randomly chooses a value if no aggregate function is specified.
SELECT a FROM b GROUP BY c;
SELECT FIRST_VALUE(a) FROM b GROUP BY c; -- Note this generates an error in MySQL
SELECT NOW() + INTERVAL 1 DAY
SELECT NOW() + INTERVAL '1' DAY
DATEif it's a string.
SELECT '2001-02-03' + INTERVAL '1' DAY
SELECT DATE('2001-02-03') + INTERVAL '1' DAY
ORDER-BY...ASCare sorted last by default while in MySQL they are sorted first (and vice versa for
DESC). You can specify
ORDER BY...ASC NULLS FIRSTin Exasol to mimic MySQL behavior.
UPPER() / LOWER()(however note that this may cause performance degradation)
RTRIM()(may also cause performance degradation)
''is treated as
NULLin Exasol . It doesn't distinguish between the two. So any MySQL behavior which treats
NULLdifferently will behave differently in Exasol. There are many such behavior differences. Unfortunately it is not possible to make Exasol mimic MySQL in this regard. e.g.
'' = ''is true and
'abc' = ''is false in MySQL while both are
NULLwhen sorting while Exasol does not
NULLif any of the inputs are
NULLand so in Exasol they will return
NULLif passed in
''while in MySQL they will not
UNIONcolumn datatypes to match across unions.
string + 0doesn't convert a string into a number. Exasol optimizes away the
+ 0. You need to use a
CAST(string AS INT)or
string + 1 - 1
SELECTcolumn must only return one row and it can't do that via
LIMIT 1(as can be done in MySQL). Instead you have to use an single-group aggregate function (no
SELECT (SELECT user_id FROM users WHERE roles.role_id = users.role_id LIMIT 1) FROM roles;
SELECT (SELECT FIRST_VALUE(user_id) FROM users WHERE roles.role_id = users.role_id) FROM roles;
SELECTcolumn which is part of a
UNIONquery where the column in other
NULLwill generate an error. At least one of the
NULLsneeds to be cast.
SELECT (SELECT MAX(user_id) FROM users WHERE roles.role_id = users.role_id) FROM roles UNION SELECT NULL;
SELECT (SELECT MAX(user_id) FROM users WHERE roles.role_id = users.role_id) FROM roles UNION SELECT CAST(NULL AS INTEGER);
JOIN...USING()join syntax can be significantly slower than
JOIN...ONwhen there are a large number of such joins. So in general use the
ONsyntax (Note that it is on Exasol's road-map to address this issue). In MySQL there is no performance difference between the two syntaxes.
JOIN-USINGswith the same column name generates a duplicate column error.
SELECT * FROM a JOIN b USING(id) JOIN c USING(id);
SELECT * FROM a JOIN b ON a.id = b.id JOIN c ON a.id = c.id;
USING()cannot include the table name. In MySQL it can.
SELECT a.id FROM a JOIN b USING(id)
SELECT id FROM a JOIN b USING(id)
a JOIN bwith no
ONclause is implicitly a cross join. In Exasol you have to explicitly say
a CROSS JOIN botherwise you get a syntax error.
JOINcriteria. You have to rewrite the sub-query as a table and
... JOIN a ON c1 = c2 AND c3 = (SELECT c4 ...)
... JOIN a ON c1 = c2 JOIN (SELECT c4 ...) AS t ON c3 = c4
1/0are considered boolean in comparisons (e.g.
TRUE = 1is TRUE) they are not considered boolean as operands of
AND/OR. In MySQL they are.
...WHERE 1 = TRUEor
...WHERE CAST(1 AS BOOLEAN)
GROUP BY. In MySQL you can.
WHERE/HAVINGclause cannot directly reference
SELECTcolumn aliases. The criteria needs to reference underlying column SQL or use
SELECT t.col AS a FROM ... WHERE a = 1
SELECT t.col AS a FROM ... WHERE local.a = 1
SELECT t.col AS a FROM ... WHERE t.col = 1
(...) UNION (...) ORDER BY ...construct is a syntax error in Exasol (in MySQL it is not). It needs to be written as
SELECT * FROM (...) UNION (...) AS a ORDER BY ...
ORDER-BYsorts in this order: tab, empty-string, space whereas Exasol sorts: empty-string, tab, space
GROUP_CONCAT(DISTINCT a ORDER BY b)Exasol collapses identical adjacent values of
aafter ordering by
bwhile MySQL collapses all identical values of
aregardless of the order.
REGEXPsin Exasol assume a leading
$while MySQL's don't. So you need an explicit
'.*'in Exasol to emulate that.
SQL_CALC_FOUND_ROWS + FOUND_ROWS()construct. Instead you can just include a column which does
COUNT(*) OVER () AS num_rows
Of course the greatest lesson learned was that Exasol is incredibly fast at handling large datasets compared to MySQL! 🙂