lordk911 opened a new issue, #27830: URL: https://github.com/apache/doris/issues/27830
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Description A 'insert into select ' statement , which the 'select' part with subquery and [CTE](https://dev.mysql.com/doc/refman/8.0/en/with.html) ： ``` insert into table_result with temp_bhvr as ( select a,b,c from table_a where 1=1 and exists ( select * from table_b ) ) select a,b,c,d,e,f from temp_bhvr bdfs left join ( select * from table_c where 1=1 and ... )wtcui on bdfs.a = wtcui.a ``` Through the query profile, it was found that all records of table_c seem to be fully loaded into memory. <img width="878" alt="企业微信截图_1885fc54-4942-4674-8f76-cd3794c9c104" src="https://github.com/apache/doris/assets/19989300/9883a7e9-dba7-4da8-9f75-a28b6618654b";> It is true that, the SQL can be write better. If I move the table_c out of subquery and do a left join directly. The execution time of SQL will be significantly reduced. Will the query optimizer could do this transparently? ### Solution _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-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
