Comments (3)
BaurzhanSakhariev
commented on May 25, 2024
Original query plan
[Eval[(SELECT field2 FROM (cte2)) AS field3] (rows=unknown)
└ CorrelatedJoin[target_field2, field1, (SELECT field2 FROM (cte2))] (rows=unknown)
└ Rename[target_field2, field1] AS subquery1 (rows=unknown)
└ Eval['abc' AS target_field2, 'def' AS field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)
└ SubPlan
└ Eval[field2] (rows=0)
└ Rename[field2] AS cte2 (rows=0)
└ Eval['abc' AS field2] (rows=0)
└ Limit[2::bigint;0::bigint] (rows=0)
└ Limit[1::bigint;0] (rows=0)
└ OrderBy[(SELECT count(*) FROM (subquery2)) AS sorting_field ASC] (rows=0)
└ Filter[('abc' AS field2 = target_field2)] (rows=0)
└ CorrelatedJoin[field1, unused_field1, (SELECT count(*) FROM (subquery2))] (rows=unknown)
└ Rename[field1] AS cte1 (rows=unknown)
└ Limit[1::bigint;0] (rows=unknown)
└ Collect[information_schema.tables | ['def' AS field1] | true] (rows=unknown)
└ SubPlan
└ Limit[2::bigint;0::bigint] (rows=1)
└ HashAggregate[count(*)] (rows=1)
└ Rename[id, field2, field1] AS subquery2 (rows=0)
└ Eval[2 AS id, 'abc' AS field2, 'def' AS field1] (rows=0)
└ Filter[(('abc' AS field2 = 'abc') AND ('def' AS field1 = field1))] (rows=0)
└ TableFunction[empty_row | [] | true] (rows=unknown)]
Plan of the query without unused field
[Eval[(SELECT field2 FROM (cte2)) AS field3] (rows=unknown)
└ CorrelatedJoin[target_field2, field1, (SELECT field2 FROM (cte2))] (rows=unknown)
└ Rename[target_field2, field1] AS subquery1 (rows=unknown)
└ Eval['abc' AS target_field2, 'def' AS field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)
└ SubPlan
└ Eval[field2] (rows=0)
└ Rename[field2, field1, sorting_field] AS cte2 (rows=0)
└ Eval['abc' AS field2, field1, (SELECT count(*) FROM (subquery2)) AS sorting_field] (rows=0)
└ Limit[2::bigint;0::bigint] (rows=0)
└ Limit[1::bigint;0] (rows=0)
└ OrderBy[(SELECT count(*) FROM (subquery2)) AS sorting_field ASC] (rows=0)
└ Filter[('abc' AS field2 = target_field2)] (rows=0)
└ CorrelatedJoin[field1, (SELECT count(*) FROM (subquery2))] (rows=unknown)
└ Rename[field1] AS cte1 (rows=unknown)
└ Limit[1::bigint;0] (rows=unknown)
└ Collect[information_schema.tables | ['def' AS field1] | true] (rows=unknown)
└ SubPlan
└ Limit[2::bigint;0::bigint] (rows=1)
└ HashAggregate[count(*)] (rows=1)
└ Rename[id, field2, field1] AS subquery2 (rows=0)
└ Eval[2 AS id, 'abc' AS field2, 'def' AS field1] (rows=0)
└ Filter[(('abc' AS field2 = 'abc') AND ('def' AS field1 = field1))] (rows=0)
└ TableFunction[empty_row | [] | true] (rows=unknown)]
Plan of the query without FROM information_schema.tables limit 1
[Eval[(SELECT field2 FROM (cte2)) AS field3] (rows=unknown)
└ CorrelatedJoin[target_field2, field1, (SELECT field2 FROM (cte2))] (rows=unknown)
└ Rename[target_field2, field1] AS subquery1 (rows=unknown)
└ Eval['abc' AS target_field2, 'def' AS field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)
└ SubPlan
└ Eval[field2] (rows=0)
└ Rename[field2, field1, sorting_field] AS cte2 (rows=0)
└ Eval['abc' AS field2, field1, (SELECT count(*) FROM (subquery2)) AS sorting_field] (rows=0)
└ Limit[2::bigint;0::bigint] (rows=0)
└ Limit[1::bigint;0] (rows=0)
└ OrderBy[(SELECT count(*) FROM (subquery2)) AS sorting_field ASC] (rows=0)
└ Filter[('abc' AS field2 = target_field2)] (rows=0)
└ CorrelatedJoin[field1, unused_field1, (SELECT count(*) FROM (subquery2))] (rows=unknown)
└ Rename[field1, unused_field1] AS cte1 (rows=unknown)
└ Eval['def' AS field1, 5 AS unused_field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)
└ SubPlan
└ Limit[2::bigint;0::bigint] (rows=1)
└ HashAggregate[count(*)] (rows=1)
└ Rename[id, field2, field1] AS subquery2 (rows=0)
└ Eval[2 AS id, 'abc' AS field2, 'def' AS field1] (rows=0)
└ Filter[(('abc' AS field2 = 'abc') AND ('def' AS field1 = field1))] (rows=0)
└ TableFunction[empty_row | [] | true] (rows=unknown)]
Plan of the query without filters in the sorting_field subquery
[Eval[(SELECT field2 FROM (cte2)) AS field3] (rows=unknown)
└ CorrelatedJoin[target_field2, field1, (SELECT field2 FROM (cte2))] (rows=unknown)
└ Rename[target_field2, field1] AS subquery1 (rows=unknown)
└ Eval['abc' AS target_field2, 'def' AS field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)
└ SubPlan
└ Limit[2::bigint;0::bigint] (rows=0)
└ Eval[field2] (rows=0)
└ Limit[1::bigint;0] (rows=0)
└ OrderBy[sorting_field ASC] (rows=0)
└ Filter[(field2 = target_field2)] (rows=0)
└ Rename[field2, sorting_field] AS cte2 (rows=unknown)
└ MultiPhase (rows=unknown)
└ Eval['abc' AS field2, (SELECT count(*) FROM (subquery2)) AS sorting_field] (rows=unknown)
└ Rename[] AS cte1 (rows=unknown)
└ Limit[1::bigint;0] (rows=unknown)
└ Collect[information_schema.tables | [] | true] (rows=unknown)
└ Limit[2::bigint;0::bigint] (rows=1)
└ HashAggregate[count(*)] (rows=1)
└ Rename[id, field2, field1] AS subquery2 (rows=unknown)
└ Eval[2 AS id, 'abc' AS field2, 'def' AS field1] (rows=unknown)
└ TableFunction[empty_row | [] | true] (rows=unknown)]
from crate.
BaurzhanSakhariev
commented on May 25, 2024
Not posting plan without ORDER BY as issue is probably related to ORDER BY + correlated subquery.
Last posted plan (without filters) is quite different from other 3, has only 1 CorrelatedJoin operator.
The most interesting are first 2 workarounds (plan without unused_field and plan without FROM information_schema).
Diff is basically that top-most CorrelatedJoin gets a different cte2 -
both workarounds
have
└ Rename[field2, field1, sorting_field] AS cte2 (rows=0)
└ Eval['abc' AS field2, field1, (SELECT count(*) FROM (subquery2)) AS sorting_field] (rows=0)
whereas not-working query has
└ Rename[field2] AS cte2 (rows=0)
└ Eval['abc' AS field2] (rows=0)
UPD: Reverting #13895 helps, prunning should not be done in some cases.
from crate.
Related Issues (20)
- max_shards_per_node not behaving as documented HOT 5
- Improve SQLParseException to include query and approximate position of the error. HOT 2
- COPY FROM does not work on all files inside folder HOT 10
- Vector Store: Provide distance functions as scalar functions HOT 3
- Support for CREATE TYPE HOT 5
- Unable to copy data between tables using the syntax: `INSERT INTO test2 (SELECT * FROM test)` HOT 5
- Docker image for 5.5.4 unavailable HOT 3
- Prepared Statements Incorrectly Storing Partition Information HOT 5
- Allow non-superusers to run ALTER TABLE REROUTE commands
- Disallow setting both balance.index and balance.shard factors to 0
- Improve primary shards balancing/reduce primary shard write overhead HOT 1
- Support `GROUP BY ALL` HOT 6
- Avoid adding unreleased file in EOL branches (maybe we need a custom plugin?) HOT 2
- Support Parquet as export file format for `COPY TO` HOT 2
- Insert into column with default value doesnt work "standalone" HOT 1
- Enable more flexible killing of jobs HOT 4
- schema_rename_replacement in RESTORE SNAPSHOT not working as expected with partitioned table HOT 2
- Allow referencing grandparent fields
- GRANT on unknown objects need to be rejected HOT 6
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from crate.