You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2021/12/10 12:32:43 UTC
[GitHub] [incubator-doris] zenoyang opened a new issue #7374: [Bug] The view cannot be extracted and spliced behind the cache key, causing data consistency problems
zenoyang opened a new issue #7374:
URL: https://github.com/apache/incubator-doris/issues/7374
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
### Version
0.15
### What's Wrong?
The view cannot be extracted and spliced behind the cache key, causing data consistency problems.
### What You Expected?
When querying, the view can be extracted and spliced into the query sql as the cache key, so that the query data will not be inconsistent due to the view update.
### How to Reproduce?
prepare:
```sql
set enable_sql_cache=true;
create database db_test;
use db_test;
CREATE TABLE `tbl1` (
`dt` int(11) NULL,
`org_id` int(11) NULL
) ENGINE=OLAP
AGGREGATE KEY(`dt`, `org_id`)
PARTITION BY RANGE(`dt`)
(PARTITION p20211209 VALUES [("20211208"), ("20211209")))
DISTRIBUTED BY HASH(`org_id`) BUCKETS 10
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
CREATE VIEW `view1` AS
SELECT `org_id` AS `org_id`, `dt` AS `dt` FROM `default_cluster:db_test`.`tbl1`;
```
first query:
```sql
SELECT origin.org_id AS ord_id
FROM (
SELECT view1.org_id
FROM db_test.view1 view1
WHERE view1.dt = 20211208
AND view1.org_id IN (2132)
LIMIT 0, 10000
) origin;
Empty set (0.08 sec)
```
In the first query, there is no data in the table, and the query will return empty, which is in line with expectations.
insert data and same query:
```sql
insert into tbl1 values(20211208, 2132);
SELECT origin.org_id AS ord_id
FROM (
SELECT view1.org_id
FROM db_test.view1 view1
WHERE view1.dt = 20211208
AND view1.org_id IN (2132)
LIMIT 0, 10000
) origin;
Empty set (0.01 sec)
```
expected results:
+--------+
| ord_id |
+--------+
| 2132 |
+--------+
Through debugging and printing the log, it can be found that the return result of `SqlCache#getSqlWithViewStmt()` is:
> SELECT `origin`.`org_id` AS `org_id` FROM (SELECT `org_id` AS `org_id` FROM `db_test`.`view1` WHERE `dt` = 20211208 AND `org_id` IN (2132)) origin
expected results:
> SELECT `origin`.`org_id` AS `org_id` FROM (SELECT `org_id` AS `org_id` FROM `db_test`.`view1` WHERE `dt` = 20211208 AND `org_id` IN (2132)) originSELECT `org_id` AS `org_id`, `dt` AS `dt` FROM `default_cluster:db_test`.`tbl1`
### Anything Else?
_No response_
### Are you willing to submit PR?
- [X] 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
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] [incubator-doris] zenoyang closed issue #7374: [Bug] The view cannot be extracted and spliced behind the cache key, causing data consistency problems
Posted by GitBox <gi...@apache.org>.
zenoyang closed issue #7374:
URL: https://github.com/apache/incubator-doris/issues/7374
--
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