Azure / Azure/data-api-builder
[Known Issue] MySQL: Update Fails on tables with Computed columns
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
We already have tests that verifies the same for MSSQL and POSTGRES, PR:#1000.
But we have ignored the test that checks update for MySQL as it was failing.
Basically, The table `sales` contains a computed column (`total`) and when updating other columns (`subtotal` and `tax`) it is supposed to automatically update the value for computed column (`total = subtotal + tax`)
when I tried to manually do the update by directly executing update statement, it works smoothly
but executing the query generated by the DAB engine is somehow giving me the error:" the value specified for generated column is not allowed "
## Repro:
drop table if exists sales;
CREATE TABLE sales (
id int PRIMARY KEY,
item_name text,
subtotal decimal(18,2),
tax decimal(18,2),
total decimal(18,2) generated always as (subtotal + tax) stored
);
desc sales;
INSERT INTO sales(id, item_name, subtotal, tax) VALUES (1, 'Watch', 249.00, 20.59), (2, 'Montior', 120.50, 11.12);
select * from sales;
update sales
SET subtotal = 111.22,
tax = 5.20
where id = 2;
select * from sales;
### GraphQL Mutation
```graphql
mutation{
updateSales(id: 2, item: {subtotal:111.22, tax: 5.20}) {
id
item_name
subtotal
tax
total
}
}
```
### Generated query:
```sql
SET @LU_0 := 0;
SET @LU_1 := 0;
SET @LU_2 := 0;
SET @LU_3 := 0;
SET @LU_4 := 0;
UPDATE `sales` SET `sales`.`subtotal` = @param1, `sales`.`tax` = @param2 , `id` = (SELECT @LU_0 := `id`), `item_name` = (SELECT @LU_1 := `item_name`), `subtotal` = (SELECT @LU_2 := `subtotal`), `tax` = (SELECT @LU_3 := `tax`), `total` = (SELECT @LU_4 := `total`) WHERE `sales`.`id` = @param0; SET @ROWCOUNT=ROW_COUNT(); SELECT @LU_0 AS `id`, @LU_1 AS `item_name`, @LU_2 AS `subtotal`, @LU_3 AS `tax`, @LU_4 AS `total` WHERE @ROWCOUNT > 0;
```
Error: <[{"message":"The value specified for generated column \u0027total\u0027 in table \u0027sales\u0027 is not allowed.","extensions":{"code":"DatabaseOperationFailed"}}]>
Contributor guide
Assessment
This issue has not been assessed yet.