'MariaDB: subselect in COALESCE throws ERROR 1064 (42000)

Intention: I want to subselect values within a coalesce function in MariaDB (10.7.1).

Problem: Executing this SQL statement ...

select coalesce(select id from MY_TABLE limit 1);

... throws this error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select id from MY_TABLE limit 1)' at line 1

Workarounds / checks I tried:

  • select id from MY_TABLE limit 1; returns a valid value. So that part of the statement is fine.
  • select coalesce('a'); returns the value a. So executing coalesce with one parameter only is also fine.
sqlmariadb


Solution 1:[1]

You must make the result of select to be an expression. Statement is not an expression

with MY_TABLE as (
      select 1 as id
     )
select coalesce((select id from MY_TABLE limit 1)) c;

Note the result of LIMIT without ORDER BY is an arbitrary row.

Solution 2:[2]

The COALESCE function typically takes 2 or more arguments. The value returned is the first value which is not NULL. You probably intend something like this:

SELECT COALESCE(id, 'missing') AS id
FROM MY_TABLE
ORDER BY <some column>
LIMIT 1;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Serg
Solution 2 Tim Biegeleisen

Related Questions