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 other folks 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 INTEGER)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 aggregate function (with 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 slowetr than
JOIN...ONwhen there are a large number of such joins. So in general use the
ONsyntax (Note that it is on Exasol's roadmap 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.
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 b'with no
ONclause is implicitly a cross join. In Exasol you have to explicitly say
'a CROSS JOIN b'otherwise 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, blank, space whereas Exasol sorts: blank, tab, space
GROUP_CONCAT(DISTINCT a ORDER BY b)Exasol will only collapse identical adjacent values *after* ordering while MySQL collapses all identical values regardless of 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
And I am happy that we decided to lift the 20.000 character Maximum on new Posts... great post Peter!!!