Simplify `col1 || 'a' || 'b' || col2` to `col1 || 'ab' || col2`
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
- Broken out from https://github.com/apache/datafusion/issues/3599
Basically when optimizing queries, doing operations on scalars at plan time is more efficient than doing them at query time
For eample, a query like this
```sql
select c1 || 'a' || 'b' || c2 from t;
```
Would be more efficient to do like this (evaluate the `a` || `b` at plan time to `ab`)
```sql
select c1 || 'ab' || c2 from t;
```
You can see that this currently does not happen:
```
> create table t (c1 varchar, c2 varchar);
0 row(s) fetched.
Elapsed 0.001 seconds.
> insert into t values ('a', 'b');
+-------+
| count |
+-------+
| 1 |
+-------+
```
The explain plan still shows the `'a' || 'b'`:
```sql
> explain select c1 || 'a' || 'b' || c2 from t;
+---------------+-------------------------------+
| plan_type | plan |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
| | │ ProjectionExec │ |
| | │ -------------------- │ |
| | │ t.c1 || Utf8("a") || Utf8(│ |
| | │ "b") || t.c2: │ |
| | │ c1 || a || b || c2 │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ DataSourceExec │ |
| | │ -------------------- │ |
| | │ bytes: 368 │ |
| | │ format: memory │ |
| | │ rows: 1 │ |
| | └───────────────────────────┘ |
| | |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.003 seconds.
> explain format indent select c1 || 'a' || 'b' || c2 from t;
+---------------+------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+------------------------------------------------------------------------------------------------------+
| logical_plan | Projection: t.c1 || Utf8View("a") || Utf8View("b") || t.c2 AS t.c1 || Utf8("a") || Utf8("b") || t.c2 |
| | TableScan: t projection=[c1, c2] |
| physical_plan | ProjectionExec: expr=[c1@0 || a || b || c2@1 as t.c1 || Utf8("a") || Utf8("b") || t.c2] |
| | DataSourceExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+------------------------------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.
```
Interestingly, if you change the order, you can see that `'a' || 'b'` has been collapsed
```sql
> explain format indent select 'a' || 'b' || c2 from t;
+---------------+----------------------------------------------------------------------+
| plan_type | plan |
+---------------+----------------------------------------------------------------------+
| logical_plan | Projection: Utf8View("ab") || t.c2 AS Utf8("a") || Utf8("b") || t.c2 |
| | TableScan: t projection=[c2] |
| physical_plan | ProjectionExec: expr=[ab || c2@0 as Utf8("a") || Utf8("b") || t.c2] |
| | DataSourceExec: partitions=1, partition_sizes=[1] |
| | |
+---------------+----------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.
```
Contributor guide
Research direction
Reproduce the issue with the provided CREATE TABLE, INSERT, and EXPLAIN queries, comparing the two operand orders in the logical and physical plans. Trace the query-optimization path responsible for scalar evaluation and confirm that the mixed column/scalar expression is shown with the adjacent string literals combined while preserving the query result.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100