You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "Larborator (via GitHub)" <gi...@apache.org> on 2023/06/12 14:18:35 UTC
[GitHub] [doris] Larborator opened a new issue, #20725: [Bug] Wrong query result
Larborator opened a new issue, #20725:
URL: https://github.com/apache/doris/issues/20725
### 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
1.2.4.1-rc01
### What's Wrong?
仅改变查询条件中的时间分区mt，得到不同的结果。示例和表结构如下：
其中Query SQL1的结果是正确的，Query SQL2的结果少了6条：
2023-04-01
2023-04-01
2023-05-01
2023-05-01
2023-05-01
2023-05-01
Query SQL1:
```sql
SELECT
mt
FROM
test_table
WHERE
mt >= STR_TO_DATE('20230401', '%Y%m%d')
AND
mt <= STR_TO_DATE('20230501', '%Y%m%d')
AND
zone_new = '-1'
AND
second_dept = '-1'
AND
project = '-1'
AND
tag_id = -1
AND
room_id = -1
AND
is_new = -1
AND
pk_parent_type = '-1'
AND
pk_type = '-1'
AND
raffle_type = '-1'
AND
user_type = '-1'
AND
anchor_type = '-1'
AND
hd_type = '-1'
AND
host_type = '-1'
AND
template_type = '-1'
ORDER BY mt
;
```
Result 2:
Query SQL2:
```sql
SELECT
mt
FROM
test_table
WHERE
mt >= STR_TO_DATE('20230301', '%Y%m%d')
AND
mt <= STR_TO_DATE('20230501', '%Y%m%d')
AND
zone_new = '-1'
AND
second_dept = '-1'
AND
project = '-1'
AND
tag_id = -1
AND
room_id = -1
AND
is_new = -1
AND
pk_parent_type = '-1'
AND
pk_type = '-1'
AND
raffle_type = '-1'
AND
user_type = '-1'
AND
anchor_type = '-1'
AND
hd_type = '-1'
AND
host_type = '-1'
AND
template_type = '-1'
ORDER BY mt
;
```
Result 1:
Table DDL:
```sql
CREATE TABLE `test_table` (
`mt` date NULL COMMENT '',
`room_id` bigint(20) NULL COMMENT '',
`tag_id` bigint(20) NULL COMMENT '',
`zone_new` varchar(255) NULL COMMENT '',
`pk_parent_type` varchar(255) NULL COMMENT '',
`pk_type` varchar(255) NULL COMMENT '',
`raffle_type` varchar(255) NULL COMMENT '',
`user_type` varchar(255) NULL COMMENT '',
`anchor_type` varchar(255) NULL COMMENT '',
`hd_parent_type` varchar(255) NULL COMMENT '',
`hd_type` varchar(255) NULL COMMENT '',
`is_new` bigint(20) NULL COMMENT '',
`template_type` varchar(255) NULL COMMENT '',
`host_type` varchar(255) NULL COMMENT '',
`second_dept` varchar(255) NULL COMMENT '',
`project` varchar(255) NULL COMMENT '',
) ENGINE=OLAP
UNIQUE KEY(`mt`, `room_id`, `tag_id`, `zone_new`, `pk_parent_type`, `pk_type`, `raffle_type`, `user_type`, `anchor_type`, `hd_parent_type`, `hd_type`, `is_new`, `template_type`, `host_type`, `second_dept`, `project`)
COMMENT ''
PARTITION BY RANGE(`mt`)()
DISTRIBUTED BY HASH(`mt`, `room_id`) BUCKETS 8
PROPERTIES (
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-12",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "8",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "400",
"dynamic_partition.storage_medium" = "HDD",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2",
"disable_auto_compaction" = "false"
);
```
### What You Expected?
当使用Query SQL2时，正确结果应该如下：
mt
2023-03-01
2023-03-01
2023-03-01
2023-03-01
2023-04-01
2023-04-01
2023-04-01
2023-04-01
2023-05-01
2023-05-01
2023-05-01
2023-05-01
### How to Reproduce?
暂时只能在我们这个表的数据集下复现此问题
### 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
[GitHub] [doris] jacktengg commented on issue #20725: [Bug] Wrong query result
Posted by "jacktengg (via GitHub)" <gi...@apache.org>.
jacktengg commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1594014790
> > i guess replica data inconsistency
>
> hi, I considered this issue before. But when I create a new table and reload data by ETL, this problem still exists.
>
> And I found that when I change the zone new = '-1' in the SQL filter condition to zone_new = -1, I can get the correct result(the Doris table column type and the data type of the original storage are both string). This looks like a bug in the SQL filter condition.
Hi, can you provide the plan and profile of the changed query when `zone_new = -1`?
--
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
[GitHub] [doris] dujl commented on issue #20725: [Bug] Wrong query result
Posted by "dujl (via GitHub)" <gi...@apache.org>.
dujl commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1628080123
https://github.com/apache/doris/pull/19221/files
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1598163321
> @xiaoyaoziyang07 @Larborator you can update to [1.2.5-rc01](https://github.com/apache/doris/releases/tag/1.2.5-rc01), which shoud have fixed this issue.
👌👌👌
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1597319048
> and I have the same problem run the same sql twice return different result；the first return one row, the second return none row;
>
> the follow is query profile [FIRST.txt](https://github.com/apache/doris/files/11767821/FIRST.txt) [SECOND.txt](https://github.com/apache/doris/files/11767824/SECOND.txt)
There may be some bugs in BE's low cardinality optimizing when the fieldType is STRING. You can try adding ```enable_low_cardinality_optimize = false``` to BE's conf file and restart BE.
--
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
[GitHub] [doris] Larborator closed issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator closed issue #20725: [Bug] Wrong query result
URL: https://github.com/apache/doris/issues/20725
--
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
[GitHub] [doris] melody3240 commented on issue #20725: [Bug] Wrong query result
Posted by "melody3240 (via GitHub)" <gi...@apache.org>.
melody3240 commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1589044533
i guess replica data inconsistency
--
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
[GitHub] [doris] jacktengg commented on issue #20725: [Bug] Wrong query result
Posted by "jacktengg (via GitHub)" <gi...@apache.org>.
jacktengg commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1598018665
@xiaoyaoziyang07 @Larborator you can update to [1.2.5-rc01](https://github.com/apache/doris/releases/tag/1.2.5-rc01), which shoud have fixed this issue.
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1589105405
> i guess replica data inconsistency
hi, I considered this issue before. But when I create a new table and reload data by ETL, this problem still exists.
And I found that when I change the zone new = '-1' in the SQL filter condition to zone_new = -1, I can get the correct result(the Doris table column type and the data type of the original storage are both string). This looks like a bug in the SQL filter condition.
--
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
[GitHub] [doris] xiaoyaoziyang07 commented on issue #20725: [Bug] Wrong query result
Posted by "xiaoyaoziyang07 (via GitHub)" <gi...@apache.org>.
xiaoyaoziyang07 commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1628086923
I resolve this problem by this way
enable_low_cardinality_optimize = false
--
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
[GitHub] [doris] xiaoyaoziyang07 commented on issue #20725: [Bug] Wrong query result
Posted by "xiaoyaoziyang07 (via GitHub)" <gi...@apache.org>.
xiaoyaoziyang07 commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1594281519
and I have the same problem
run the same sql twice return different result；the first return one row, the second return none row;
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1628036669
> have u fix the issue? and do u know which pr resolve this issue?
This issue has been fixed in versions 1.2.5-rc01 and later. Try search 'low cardinality' could see the pr.
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1598161900
>
👌👌👌
--
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
[GitHub] [doris] dujl commented on issue #20725: [Bug] Wrong query result
Posted by "dujl (via GitHub)" <gi...@apache.org>.
dujl commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1628018440
have u fix the issue? and do u know which pr resolve this issue?
--
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
[GitHub] [doris] Larborator commented on issue #20725: [Bug] Wrong query result
Posted by "Larborator (via GitHub)" <gi...@apache.org>.
Larborator commented on issue #20725:
URL: https://github.com/apache/doris/issues/20725#issuecomment-1594038383
> > > i guess replica data inconsistency
> >
> >
> > hi, I considered this issue before. But when I create a new table and reload data by ETL, this problem still exists.
> > And I found that when I change the zone new = '-1' in the SQL filter condition to zone_new = -1, I can get the correct result(the Doris table column type and the data type of the original storage are both string). This looks like a bug in the SQL filter condition.
>
> Hi, can you provide the plan and profile of the changed query when `zone_new = -1`?
yes, all here
[3月-5月profile.txt](https://github.com/apache/doris/files/11765788/3.-5.profile.txt)
[3月-5月profile-隐式转换.txt](https://github.com/apache/doris/files/11765789/3.-5.profile-.txt)
[4月-5月profile.txt](https://github.com/apache/doris/files/11765790/4.-5.profile.txt)
--
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