MariaDB: subselect in COALESCE throws ERROR 1064 (42000)-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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;