[#MXS-4267] NULL values are exported as empty strings when using CSV format

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:

SELECT NULL, 0, '' INTO OUTFILE 'data-server.csv';

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.

[markusjm@monolith Downloads]$ hexdump -C data-maxscale.csv

00000000  09 30 09                                          |.0.|

00000003

[markusjm@monolith Downloads]$ hexdump -C data-server.csv

00000000  5c 4e 09 30 09 0a                                 |\N.0..|

00000006

This also reveals a minor problem where the following query ends up generating a NULL value instead of the string literal \N:

SELECT '\\N' INTO OUTFILE 'data-server.csv';

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.

MariaDB [test]> create or replace table loading(a varchar(20), b varchar(20), c varchar(20));

Query OK, 0 rows affected (0.072 sec)

MariaDB [test]> load data local infile 'data-maxscale.csv' into table loading;

Query OK, 1 row affected, 1 warning (0.013 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [test]> show warnings;

+---------+------+--------------------------------------------+

| Level   | Code | Message                                    |

+---------+------+--------------------------------------------+

| Warning | 1261 | Row 1 doesn't contain data for all columns |

+---------+------+--------------------------------------------+

1 row in set (0.000 sec)

MariaDB [test]> load data local infile 'data-server.csv' into table loading;

Query OK, 1 row affected (0.012 sec)

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> select * from loading;

+------+------+------+

| a    | b    | c    |

+------+------+------+

|      | 0    | NULL |

| NULL | 0    |      |

+------+------+------+

2 rows in set (0.001 sec)

[MXS-4267] NULL values are exported as empty strings when using CSV format Created: 2022-08-30 Updated: 2022-09-20 Resolved: 2022-09-20
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	maxgui
Affects Version/s:	6.4.2
Fix Version/s:	6.4.3

[MXS-4267] NULL values are exported as empty strings when using CSV format Created: 2022-08-30 Updated: 2022-09-20 Resolved: 2022-09-20