matrixorigin / matrixorigin/matrixone
MySQL Compatibility: Support column name references in INSERT VALUES expressions
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
MatrixOne does not support referencing table column names in INSERT VALUES expressions, which is a valid MySQL feature. When inserting values, MySQL allows using column names from the target table in expressions to reference existing column values.
## Error Message
```
ERROR 20301 (HY000) at line 1: invalid input: ambiguous column reference 'a'
```
## Failing SQL Statements
The following SQL statements fail in MatrixOne but work correctly in MySQL:
```sql
-- Create table
create table t1 (a int not null);
-- Insert initial value
insert into t1 values (1);
-- Reference column 'a' in INSERT VALUES (should use the last inserted value)
insert into t1 values (a+2);
insert into t1 values (a+3),(a+4);
insert into t1 values (5),(a+6);
```
## Expected Behavior
In MySQL, when you reference a column name in INSERT VALUES, it refers to the column value from the table. The behavior depends on the context:
1. **Single-row INSERT**: The column reference typically refers to the default value or the value being inserted in the same statement
2. **Multi-row INSERT**: The column reference can refer to values from previous rows in the same INSERT statement
For example:
- `insert into t1 values (1);` → inserts 1
- `insert into t1 values (a+2);` → should insert 1+2 = 3 (using the value from column 'a' of the last row)
- `insert into t1 values (a+3),(a+4);` → should insert two rows using the column value
## Test Context
This issue was discovered during MySQL compatibility testing using the official MySQL test suite (`mysql-test/t/insert.test`, line 25-51).
## Impact
- **MySQL Compatibility**: This is a common MySQL feature used in many applications
- **Migration**: Applications using column references in INSERT statements will fail when migrating to MatrixOne
- **Functionality**: Users cannot use column values in INSERT expressions, limiting data manipulation capabilities
## Technical Details
The error "ambiguous column reference" suggests that MatrixOne's parser cannot determine whether 'a' refers to:
1. A column in the target table
2. A variable or other context
MySQL's behavior in this context is well-defined: column names in INSERT VALUES refer to the table columns, and the value used depends on the INSERT context.
## Suggested Fix
1. Implement support for column name references in INSERT VALUES expressions
2. Handle both single-row and multi-row INSERT cases
3. Ensure proper scoping rules (column names should refer to table columns, not variables)
4. Add appropriate error handling for truly ambiguous cases
## Related MySQL Documentation
In MySQL, column references in INSERT VALUES can be used to:
- Reference default values
- Reference values from the same INSERT statement (for multi-row inserts)
- Create computed values based on existing column values
Contributor guide
Assessment
This issue has not been assessed yet.