[Bug] LOAD DATA column list parser rejects backtick-quoted reserved word identifiers (e.g. `execute`)
- 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
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