matrixorigin / matrixorigin/matrixone

MySQL Compatibility: Support column name references in INSERT VALUES expressions

Open
#23,066 0 comments 0 reactions 1 assignee Assigned to @XuPeng-SH View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.