Bug #85111 replace does not update virtual column
Submitted: 21 Feb 2017 19:14 Modified: 4 Jan 2020 12:37
Reporter: Brian Maher Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.17 OS:Linux
Assigned to: CPU Architecture:Any

[21 Feb 2017 19:14] Brian Maher 
Description:
I've confirmed this bug on version 5.7.11 on Amazon RDS hosted MySQL and version 5.7.17 running in a docker container running in virtual box (minikube linux image).

Create a table containing a JSON field datatype and an index for a virtual column. When updating an existing table entry, the index is not updated and the entry can not be found.

How to repeat:
#!/bin/bash
#
# Run mysql in a docker container:
PASS=$(base64 < /dev/random | head -c 30)
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD="$PASS" -d mysql:5.7.17

# I'm on a mac, so I need to figure out the IP of the Linux box, otherwise
# just use localhost here:
HOST=$(echo $DOCKER_HOST | sed 's!tcp://\([^:]*\):[0-9]*!\1!')

# Wait for mysql to start up:
sleep 10

# Connect to mysql:
mysql -A -h $HOST -P 3306 -u root "-p$PASS" <<'EOF'
# Check the version:
select version();
# Create a database and use it:
create database bug;
use bug;
# Create a table with an index on a virtual column:
create table bug (
  pk integer not null,
  content json,
  vcol text generated always as (
    json_unquote(json_extract(content, '$.vcol'))
  ) virtual,
  primary key(pk),
  # If this index is removed, everything works:
  key vcol_index (vcol(1024)) using btree);
# Insert a record:
insert into bug (pk, content) values (1, '{"vcol":"val1"}');
# Show that the virtual index works:
select content from bug where vcol='val1';
# Update vcol using replace:
replace into
    bug(content,pk)
  select
    json_set(content, '$.vcol', 'val2'), pk
  from (select
    content,
    pk
  from bug
  where pk=1) as t;
# vcol index is now broken. We expect
# this to return one record.
select content from bug where vcol='val2';
EOF

# Cleanup the mysql container:
docker rm -f mysql;
[21 Feb 2017 19:25] Brian Maher 
To work around this, insert appears to work correctly:

insert into bug (content, pk) values ('{"vcol":"val5"}', 1) on duplicate key update
  content=json_set(content, '$.vcol', 'val5');

...or update clause appears to work as expected:

update bug set content=json_set(content, '$.vcol', 'val2')
  where pk=1;
[22 Feb 2017 6:40] MySQL Verification Team 
Thank you for the report and test case.
Verified as described.
[4 Jan 2020 12:37] Roy Lyseng 
Posted by developer:
 
Fixed in 8.0.18