Azure / Azure/data-api-builder

[Known Issue] MySQL: Update Fails on tables with Computed columns

Open
#1,001 2 comments 0 reactions 1 assignee Claimed by @junsu0ms View on GitHub
bug known-issue mysql
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

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.