Azure / Azure/data-api-builder

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

Abierto
#1,001 2 comentarios 0 reacciones 1 asignado Reclamado por @junsu0ms Ver en GitHub
bug known-issue mysql
Lenguaje dominante
C#
Estrellas
1.5k
Forks
370
Merge medio
3 d 22 h
PR fusionados (30 d)
9

Descripción

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"}}]>

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.