You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "Xuxiaotuan (via GitHub)" <gi...@apache.org> on 2024/02/20 09:21:17 UTC
[I] [Bug] The type of the scan column is determined when a view is created, but the type of the group by column is not determined [doris]
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: commits-unsubscribe@doris.apache.org.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
Re: [I] [Bug] The type of the scan column is determined when a view is created, but the type of the group by column is not determined [doris]
Posted by "morningman (via GitHub)" <gi...@apache.org>.
morningman closed issue #31156: [Bug] The type of the scan column is determined when a view is created, but the type of the group by column is not determined
URL: https://github.com/apache/doris/issues/31156
--
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
Re: [I] [Bug] The type of the scan column is determined when a view is created, but the type of the group by column is not determined [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #31156:
URL: https://github.com/apache/doris/issues/31156#issuecomment-3287226167
We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!
--
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