The database option is not supported with the CTE statement

[BabySid]

Describe the bug
In our system, the generated SQL will automatically use the form like db.table , but in CTE it do NOT works.
On the other hand, create table/view support grammar like db.table or db.view.
We hope that CTE can also support this feature

The case is shown as below

MySQL [(none)]> with tmp as (select dt, app, chan, count(distinct uids) as uv FROM db.mytale WHERE dt = 20200913 group by dt, app, chan)
    -> select dt,app, 
    -> sum(uv) as uv, 
    -> sum(distinct case when chan= 1 then uv else 0 end) as new_uv
    -> from tmp
    -> group by dt, app;
ERROR 1046 (3D000): errCode = 2, detailMessage = No database selected
MySQL [(none)]> 

Also, SQL statements like with db.tmp (....) cannot be run

To Reproduce

Expected behavior
support statement like

with database.tmp (xxx)
select f1 from database.tmp ...

Additional context
Add any other context about the problem here.

[Astralidea]

Doris support CET statement.
https://github.com/apache/incubator-doris/wiki/Sql-Reference#with%E5%AD%90%E5%8F%A5
Your Error looks like not select database when connect the doris, try to use [database]; to select a db, and then try CTE statement again.

[BabySid]

Doris support CET statement.
https://github.com/apache/incubator-doris/wiki/Sql-Reference#with%E5%AD%90%E5%8F%A5
Your Error looks like not select database when connect the doris, try to use [database]; to select a db, and then try CTE statement again.

Yeah. use [database] can resolve this problem.
However, I can use 'database.table' in the create table or select command, e.g. 'create db.table or select ... from db.table'
and i also hope doris can support this kind of syntax in with statement.

In out production env, we provide a restful-interface to run sql against doris.
Therefore, the advantage of doing so is to unify a syntax or semantics.

[Astralidea]

Thanks for you replay. your needs are reasonable.

[BabySid]

@Astralidea thanks for your pr. it run ok if the sql is simple. e.g. with tmp as (..) select xx from tmp

but i found a problem in my production-env if i run a sql with join/union, it will run failed as below

MySQL [(none)]> with tmp as (SELECT dt, app, bitmap_union(cuids) as uids
    ->     FROM db.mytable
    ->     WHERE dt = 20200925
    ->         AND app IN ('xxx')
    ->     GROUP BY dt, app)
    -> 
    -> select a.dt, b.app , a.uv from 
    -> (select dt, count(distinct uids) as uv from tmp group by dt) a
    -> left join (select dt, app from tmp) b on a.dt = b.dt;
ERROR 1046 (3D000): errCode = 2, detailMessage = No database selected

btw: Is the with syntax a simple macro replacement or a result set reuse at run time?

[Astralidea]

@Astralidea thanks for your pr. it run ok if the sql is simple. e.g. with tmp as (..) select xx from tmp

but i found a problem in my production-env if i run a sql with join/union, it will run failed as below

MySQL [(none)]> with tmp as (SELECT dt, app, bitmap_union(cuids) as uids
    ->     FROM db.mytable
    ->     WHERE dt = 20200925
    ->         AND app IN ('xxx')
    ->     GROUP BY dt, app)
    -> 
    -> select a.dt, b.app , a.uv from 
    -> (select dt, count(distinct uids) as uv from tmp group by dt) a
    -> left join (select dt, app from tmp) b on a.dt = b.dt;
ERROR 1046 (3D000): errCode = 2, detailMessage = No database selected

btw: Is the with syntax a simple macro replacement or a result set reuse at run time?

I'll see why Doris can't do this case recently

[Astralidea]

@Astralidea thanks for your pr. it run ok if the sql is simple. e.g. with tmp as (..) select xx from tmp

but i found a problem in my production-env if i run a sql with join/union, it will run failed as below

MySQL [(none)]> with tmp as (SELECT dt, app, bitmap_union(cuids) as uids
    ->     FROM db.mytable
    ->     WHERE dt = 20200925
    ->         AND app IN ('xxx')
    ->     GROUP BY dt, app)
    -> 
    -> select a.dt, b.app , a.uv from 
    -> (select dt, count(distinct uids) as uv from tmp group by dt) a
    -> left join (select dt, app from tmp) b on a.dt = b.dt;
ERROR 1046 (3D000): errCode = 2, detailMessage = No database selected

btw: Is the with syntax a simple macro replacement or a result set reuse at run time?

This is because when select nested select, with does not push down the analysis.
I will submit a PR later.