Xuxiaotuan opened a new issue, #31156: URL: https://github.com/apache/doris/issues/31156
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version 2.0.3 ### What's Wrong? When a view is created, the type of the scan column is determined, but the type of the group by column is not determined ### What You Expected? scan and group by type judgments are handled ### How to Reproduce? init data ```sql CREATE TABLE `ods1` ( `65F695F4519253F7_06a28` varchar(*) NULL ) ENGINE=OLAP COMMENT 'OLAP' DISTRIBUTED BY RANDOM BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" ); INSERT INTO ods1 (`65F695F4519253F7_06a28`) VALUES ('2023-12-01'); INSERT INTO ods1 (`65F695F4519253F7_06a28`) VALUES ('2023-10-21'); INSERT INTO ods1 (`65F695F4519253F7_06a28`) VALUES ('2023-08-21'); ``` query sql ```sql select CASE WHEN quarter(65F695F4519253F7_06a28) = '1' THEN concat( year(`65F695F4519253F7_06a28`), '-01-01') WHEN quarter(`65F695F4519253F7_06a28`) = '2' THEN concat( year(`65F695F4519253F7_06a28`), '-04-01') WHEN quarter(`65F695F4519253F7_06a28`) = '3' THEN concat( year(`65F695F4519253F7_06a28`), '-07-01') WHEN quarter(`65F695F4519253F7_06a28`) = '4' THEN concat( year(`65F695F4519253F7_06a28`), '-10-01') ELSE NULL END as ttt from ods1 group by CASE WHEN quarter(65F695F4519253F7_06a28) = '1' THEN concat( year(`65F695F4519253F7_06a28`), '-01-01') WHEN quarter(`65F695F4519253F7_06a28`) = '2' THEN concat( year(`65F695F4519253F7_06a28`), '-04-01') WHEN quarter(`65F695F4519253F7_06a28`) = '3' THEN concat( year(`65F695F4519253F7_06a28`), '-07-01') WHEN quarter(`65F695F4519253F7_06a28`) = '4' THEN concat( year(`65F695F4519253F7_06a28`), '-10-01') ELSE NULL END ``` result： ```sql 2023-07-01 2023-10-01 ``` Then create the view based on the query sql ```sql CREATE VIEW `dwd_ods1` COMMENT 'VIEW_ods1' AS ( select CASE WHEN quarter(65F695F4519253F7_06a28) = '1' THEN concat( year(`65F695F4519253F7_06a28`), '-01-01') WHEN quarter(`65F695F4519253F7_06a28`) = '2' THEN concat( year(`65F695F4519253F7_06a28`), '-04-01') WHEN quarter(`65F695F4519253F7_06a28`) = '3' THEN concat( year(`65F695F4519253F7_06a28`), '-07-01') WHEN quarter(`65F695F4519253F7_06a28`) = '4' THEN concat( year(`65F695F4519253F7_06a28`), '-10-01') ELSE NULL END as ttt from ods1 group by CASE WHEN quarter(65F695F4519253F7_06a28) = '1' THEN concat( year(`65F695F4519253F7_06a28`), '-01-01') WHEN quarter(`65F695F4519253F7_06a28`) = '2' THEN concat( year(`65F695F4519253F7_06a28`), '-04-01') WHEN quarter(`65F695F4519253F7_06a28`) = '3' THEN concat( year(`65F695F4519253F7_06a28`), '-07-01') WHEN quarter(`65F695F4519253F7_06a28`) = '4' THEN concat( year(`65F695F4519253F7_06a28`), '-10-01') ELSE NULL END ) ``` use query sql ```sql select * from `dwd_ods1` ``` result : > [HY000][1105] errCode = 2, detailMessage = select list expression not produced by aggregation output (missing from GROUP BY clause?): Then through query sql `show create view` ```sql show create view `dwd_ods1` ``` result: ```sql SELECT CASE WHEN CAST(quarter(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS BIGINT) = 1 THEN concat( CAST(year(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS VARCHAR(65533)), '-01-01') WHEN CAST(quarter(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS BIGINT) = 2 THEN concat( CAST(year(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS VARCHAR(65533)), '-04-01') WHEN CAST(quarter(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS BIGINT) = 3 THEN concat( CAST(year(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS VARCHAR(65533)), '-07-01') WHEN CAST(quarter(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS BIGINT) = 4 THEN concat( CAST(year(CAST(`65F695F4519253F7_06a28` AS DATETIME)) AS VARCHAR(65533)), '-10-01') ELSE NULL END AS `ttt` FROM `default_cluster:xxt`.`ods1` GROUP BY CASE WHEN quarter(`65F695F4519253F7_06a28`) = '1' THEN concat(year(`65F695F4519253F7_06a28`), '-01-01') WHEN quarter(`65F695F4519253F7_06a28`) = '2' THEN concat(year(`65F695F4519253F7_06a28`), '-04-01') WHEN quarter(`65F695F4519253F7_06a28`) = '3' THEN concat(year(`65F695F4519253F7_06a28`), '-07-01') WHEN quarter(`65F695F4519253F7_06a28`) = '4' THEN concat(year(`65F695F4519253F7_06a28`), '-10-01') ELSE NULL END; ``` Can see that the type of the scan column has been confirmed, but not in group by ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
