apache / apache/doris

[Bug] LOAD DATA column list parser rejects backtick-quoted reserved word identifiers (e.g. `execute`)

Open
#66,419 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 23h
Merged PRs (30d)
520

Description

### Search before asking
- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.

### Version
Apache Doris 4.0.2 (single FE + single BE, default configuration)

### What's Wrong?
`LOAD DATA LOCAL INFILE`'s SQL-level column list parser does not support backtick-quoted (or any quoted) reserved word identifiers. When a table column name is a Doris reserved keyword (e.g., `execute`), placing it in the column list — even with backtick quoting — causes a `SyntaxParseException`.

All quoting mechanisms were tested and all fail:

| Quoting | Column list syntax | Error |
|---------|-------------------|-------|
| Backticks | `(id,\`execute\`,name)` | `SyntaxParseException: mismatched input 'execute'` |
| Double quotes | `(id,"execute",name)` | `SyntaxParseException: mismatched input '"execute"'` |
| Square brackets | `(id,[execute],name)` | `SyntaxParseException: mismatched input '['` |
| No quotes | `(id,execute,name)` | `SyntaxParseException: mismatched input 'execute'` |

This is inconsistent with:
1. **SELECT / INSERT INTO** — backtick-quoted reserved words work correctly (e.g., `SELECT \`execute\` FROM tbl` succeeds).
2. **PROPERTIES columns** form of LOAD DATA — `PROPERTIES ("columns"="id,execute,name")` handles reserved words without backticks.
3. **Stream Load** HTTP `columns` header — `columns: id,\`execute\`,name` handles backtick-quoted reserved words.

### What You Expected?
Backtick-quoted reserved word identifiers should work in the LOAD DATA column list, consistent with SELECT / INSERT statements. For example, `(id,\`execute\`,name)` should be valid syntax.

### How to Reproduce?

**1. Table DDL:**

```sql
CREATE TABLE test_execute (
id INT NOT NULL,
`execute` VARCHAR(100),
name VARCHAR(100)
) UNIQUE KEY(id) DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES("replication_num"="1");
```

**2. CSV data (`/tmp/test.csv`):**

```csv
1,EX,alice
2,EX,bob
```

**3. LOAD DATA with backtick-quoted reserved word (FAILS):**

```sql
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
(id,`execute`,name);
```

**Error output:**

```
ERROR 1105 (HY000): errCode = 2, detailMessage = [INTERNAL_ERROR]TStatus: SyntaxParseException:
mismatched input 'execute' expecting {'(', '[', '{', '}', 'ACTIONS', 'ADD', 'AFTER', 'AGG_STATE', ...}
```

**4. Workaround — PROPERTIES columns (works for reserved words, but has a separate limitation):**

```sql
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
PROPERTIES ("columns"="id,execute,name");
-- ✅ This works
```

**5. The workaround's limitation — cannot write hidden column `__DORIS_DELETE_SIGN__`:**

When the table is a Unique Key MOW model and delete operations are needed, `__DORIS_DELETE_SIGN__` must be explicitly written. However, PROPERTIES columns cannot map this hidden column — all rows are silently filtered (0 rows loaded), even with `max_filter_ratio=1.0` and `strict_mode=false`:

```sql
LOAD DATA LOCAL INFILE '/tmp/test_sign.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
PROPERTIES ("columns"="id,execute,name,__DORIS_DELETE_SIGN__","max_filter_ratio"="1.0");
-- ❌ 0 rows loaded (silent data loss, not a strict mode issue)
```

In contrast, the SQL-level column list **does** support `__DORIS_DELETE_SIGN__`:

```sql
LOAD DATA LOCAL INFILE '/tmp/test_sign.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
(id,`execute`,name,__DORIS_DELETE_SIGN__);
-- ❌ Fails due to `execute` (this bug), but __DORIS_DELETE_SIGN__ itself is fine
```

This creates a **mutual exclusion** for tables that have BOTH reserved word columns AND need delete operations:

| Column specification | Reserved words | `__DORIS_DELETE_SIGN__` |
|---------------------|---------------|------------------------|
| SQL-level column list | ❌ (this bug) | ✅ |
| PROPERTIES columns | ✅ | ❌ (silent 0 rows) |

**Cluster info:** Single FE + single BE, Doris 4.0.2, default configuration, `replication_num=1`.

### Anything Else?
Two additional workaround attempts were also tested and confirmed not viable:

**1. SET clause (MySQL syntax) — not supported by Doris:**

```sql
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' (c1, c2, c3)
SET id=c1, `execute`=c2, name=c3;
-- Error: missing '(' at 'id'
```

Doris's LOAD DATA parser does not support the SET clause at all.

**2. Mixing SQL column list + PROPERTIES columns — silent data corruption:**

```sql
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE test_execute
COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n'
(id,c2,name) PROPERTIES ("columns"="id,execute=c2,name");
-- Returns success (rc=0, count=2) but `execute` column is NULL!
```

When both are present, the SQL column list takes precedence and PROPERTIES columns is silently ignored. The temporary name `c2` is not a real table column, so its value is dropped, and `execute` receives NULL — **without any error or warning**.

### 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)

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the LOAD DATA LOCAL INFILE example with the backtick-quoted execute column, then trace the SQL-level column list parser and compare it with the working SELECT or Stream Load parsing paths. Done means the quoted reserved identifier is accepted while __DORIS_DELETE_SIGN__ continues to work, with regression coverage for the reported syntax.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.