When exporting query results from the query editor, the format of the generated file differs from the one MariaDB uses. The query editor also seems to be missing the terminating newline for the last row of the data which causes MariaDB to treat it as an invalid CSV record.
Exporting the following query as CSV with tabs as separators reproduces the problem:
Here's a hexdump of both the file from MaxScale and the one generated by the server when used with the SELECT ... INTO OUTFILE command. As can be seen, the data generated by the server seems to have the special value \N for SQL null values when exported via SELECT ... INTO OUTFILE.
This also reveals a minor problem where the following query ends up generating a NULL value instead of the string literal \N:
To make things worse, this seems to be affected by the current SQL_MODE of the connection: with NO_BACKSLASH_ESCAPES the null values are exported as NULL whereas without it they are exported as \N.
Here's what happens when the files are loaded into MariaDB. This was tested with MariaDB 10.6.8.
I think the order of importance for these problems is:
- The missing newline
- Export of null values (to either \N or NULL)
- Literal NULL and \N values being confused with SQL null values