[Bug] Create view failed

[morningman]

Describe the bug
Create view failed with error: "failed to init view stmt"

To Reproduce
create a view with some complex select stmt such as union and join and group by.

Why
This is because the toSql() method of SlotRef will return like "<slot 20> col as alias", if that SlotRef is analyzed.

[no7dw]

Still got an error here.
Version:
Git : git://4ae1784ec1a5/root/doris-1.0.1@3d4dbac

Version : 1.1.0-preview2

when using union all query statement

if not create view, the following run without error

SELECT   
hash,  block_timestamp ,  from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10, 50
union all
SELECT   
hash,  block_timestamp ,   from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10

create view without union all run successfully

create view gaia_data__gaia.bsc_transactio_view_wade (
hash,  block_timestamp ,   from_address , to_address , value
) as 
(
SELECT   
hash,  block_timestamp ,   from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10 )

however , the following query will run fail:

create view gaia_data__gaia.bsc_transactio_view_wade (
hash,  block_timestamp ,   from_address , to_address , value
) as 
(
SELECT   
hash,  block_timestamp ,  from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10, 50
union all
SELECT   
hash,  block_timestamp ,   from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10 )
 

FE log show a bit weird missing columns after union all
2022-07-11 05:07:32,376 INFO (qtp565841203-97|97) [StmtExecutionAction.executeSQL():97] stmt: -- drop view bsc_transactio_view_wade
create view gaia_data__gaia.bsc_transactio_view_wade (
hash, block_timestamp , from_address , to_address , value
) as
(
SELECT hash, block_timestamp , from_address , to_address , value
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc limit 10, 50
union all SELECT hash, block_timestamp , from_address , to_address , value
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc limit 10 )
2022-07-11 05:07:32,381 INFO (doris-mysql-nio-pool-1218|4352) [View.init():172] stmt is SELECT hash AS hash, block_timestamp AS block_timestamp, from_address AS from_address, to_address AS to_address, value AS value FROM default_cluster:gaia_data__gaia.bsc_transactions WHERE block_timestamp > '2022-03-30' ORDER BY block_timestamp LIMIT 10, 50 UNION ALL SELECT FROM gaia_data__gaia.bsc_transactions WHERE block_timestamp > '2022-03-30' ORDER BY block_timestamp LIMIT 10
2022-07-11 05:07:32,381 INFO (doris-mysql-nio-pool-1218|4352) [View.init():173] exception because:
org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = Syntax error

query has columns ,but missing column after UNION ALL SELECT in var inLineViewDef

[jackwener]

Thanks @no7dw .

I will open a new issue for this problems.

[jackwener]

@no7dw In this case, you can use temporary sql (remove ( hash, block_timestamp , from_address , to_address , value ) )

create view gaia_data__gaia.bsc_transactio_view_wade
(
SELECT   
hash,  block_timestamp ,  from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10, 50
union all
SELECT   
hash,  block_timestamp ,   from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10 )

[no7dw]

yep , I know. thanks. but the purpose for my scenario is to create a view for user to query

[no7dw]

work around : add
select * from between crate view xxx and xx union all yy
example

create view gaia_data__gaia.bsc_transactio_view_wade_2 as  
select 
*  
from(
SELECT   
hash,  block_timestamp ,  from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10, 50
union all
SELECT   
hash,  block_timestamp ,   from_address , to_address , value 
FROM gaia_data__gaia.bsc_transactions where block_timestamp > '2022-03-30' order by block_timestamp desc   limit  10  
)   bsc_u