Why concatenation does not work in Dbeaver?

Using Dbeaver, the || concatenation does not work. Had to use CONCAT() instead. Got output error message: Truncated incorrect DOUBLE value. Anyone else had this issue?

Skill level: Beginner
Editor: Dbeaver
OS: Windows 7

Answer

MariaDB expects the PIPES_AS_CONCAT to be set with the sql_mode to use || as a concat operator.

One can check what is set for sql_mode by doing:

SELECT @@sql_mode;

To which output similar to the following may appear:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note that the PIPES_AS_CONCAT is not listed, so this can be appended by doing:

SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));

Then recheck the sql_mode to see:

root@localhost((none))select @@sql_mode G
*************************** 1. row ***************************
@@sql_mode: PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
            NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
            NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

More information about PIPES_AS_CONCAT can be found here.