SELECT '' is NULL rather than empty string

[derekmahar]

Why does SELECT '' result in NULL rather than ''?

$ csvq "SELECT '' AS col1"
+------+
| col1 |
+------+
| NULL |
+------+

For comparison, in both MySQL 5.7 and PostgreSQL 13, SELECT '' produces ''.

[mithrandie]

In csvq, when the dual table is used, an empty string matches the column name in the dual table, so the value of that column in the dual table, null, is displayed as the value of the column whose column name is the empty string.

This behavior is probably rarely a problem, but it might need to be fixed.

[derekmahar]

What is the dual table?

[mithrandie]

The dual table is a table prepared by DBMSs that is used to retrieve pseudo columns. It has one column and one record, so you can get the result you want by processing it like any other SELECT queues.

SELECT 1; is an abbreviated syntax of SELECT 1 FROM DUAL;.

[derekmahar]

Does the null value in the dual table column having empty string as its name also apply if you assign the column a name?

[mithrandie]

I'm not sure I understand your intention, but if you want to give an alias to the column name in the dual table, that operation is not available.

[mithrandie]

This bug has been fixed and the fix is included in the version 1.14.1.

[derekmahar]

I'm not sure I understand your intention, but if you want to give an alias to the column name in the dual table, that operation is not available.

Didn't I assign an alias to the column name in my original example?

[derekmahar]

This bug has been fixed and the fix is included in the version 1.14.1.

I verified that this issue is fixed in version 1.14.1:

$ csvq "SELECT '' AS col1"
+------+
| col1 |
+------+
|      |
+------+
$ csvq --version
csvq version 1.14.1

Thank you!