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: [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]
