`Function::Coalesce` not support the coexistence of `SimpleExpr` and `QueryStatement`

[bingryan]

Test language=MySQL

Description

I found it difficult to handle the logic that Function::Coalesce supports the coexistence of SimpleExpr and QueryStatement

The following logic is correct

create table bakery (
	id bigint,
	name varchar(255),
	profit_margin	bigint
)


create table cake{
	id bigint,
	name varchar(255),
	price bigint,
	bakery_id bigint,
	gluten_free bigint,
	serial bigint
}

mysql query:

select
	name,
	bakery,
	EXISTS ( SELECT 1 FROM `cake` WHERE `id` = 1 ) AS `exist`,
	-- Coalesce not only support SimpleExpr, logic is correct
	COALESCE (
		-- QueryStatement
		( SELECT count(*) FROM `cake` WHERE `bakery_id` = 27 ), 
		-- SimpleExpr, select or const 
		(SELECT 0),
		0 
	) AS `count` 
from bakery
where id = 27

but the existing logic does not support the coexistence of SimpleExpr and QueryStatement

https://github.com/SeaQL/sea-query/blob/28cf151b0cc92318fa701e31ec115c52c05dcace/src/func.rs#L430-L435

[billy1624]

Hey @bingryan, thanks for the suggestions!! I think we can make Func::coalesce takes iterator of Into<SimpleExpr>.

impl Func {
    pub fn coalesce<I, T>(args: I) -> FunctionCall
    where
        I: IntoIterator<Item = T>,
        T: Into<SimpleExpr>,
    {
        FunctionCall::new(Function::Coalesce).args(args)
    }
}

Which also requires changing

impl FunctionCall {
    pub fn args<I, T>(mut self, args: I) -> Self
    where
        I: IntoIterator<Item = T>,
        T: Into<SimpleExpr>,
    {
        self.args = args.into_iter().map(Into::into).collect();
        self
    }
}

And implementing From<SelectStatement> for SimpleExpr

impl From<SelectStatement> for SimpleExpr {
    fn from(sel: SelectStatement) -> Self {
        SimpleExpr::SubQuery(
            None,
            Box::new(sel.into_sub_query_statement()),
        )
    }
}

Sounds like a good plan? @ikrivosheev

[ikrivosheev]

@billy1624 I think we need only last one:

impl From<SelectStatement> for SimpleExpr {
    fn from(sel: SelectStatement) -> Self {
        SimpleExpr::SubQuery(
            None,
            Box::new(sel.into_sub_query_statement()),
        )
    }
}

And should work. We cannot do:

impl Func {
    pub fn coalesce<I, T>(args: I) -> FunctionCall
    where
        I: IntoIterator<Item = T>,
        T: Into<SimpleExpr>,
    {
        FunctionCall::new(Function::Coalesce).args(args)
    }
}

because after this we cannot do: Func::coalesce(Query::select(), 1).

[billy1624]

Right. User still need Into::into anyways

Func::coalesce(vec![Query::select().into(), 1.into()])