codeleep opened a new issue, #66419: URL: https://github.com/apache/doris/issues/66419
### 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) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
