[GitHub] [doris] no7dw commented on issue #4839: [Bug] Create view failed

[GitBox <gi...@apache.org>]

no7dw commented on issue #4839:
URL: https://github.com/apache/doris/issues/4839#issuecomment-1180001299

   still got a error here . 
   ver:
   Git : git://4ae1784ec1a5/root/doris-1.0.1@3d4dbaca8229a92525c309ad9211b1cdc750aea0
   
   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 
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org