apache / apache/datafusion

Simplify `col1 || 'a' || 'b' || col2` to `col1 || 'ab' || col2`

Open
#17,158 9 comments 0 reactions 0 assignees View on GitHub
enhancement performance
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.