Bug #101304 mysql 5.7, 8 insert on duplicate key update on view inserts nothing without err
Submitted: 24 Oct 2020 4:58 Modified: 24 Oct 2020 14:12
Reporter: Brad Jones Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7 thru 8.0.21 OS:Debian (Buster)
Assigned to: CPU Architecture:x86 (KVM VM)

[24 Oct 2020 4:58] Brad Jones
Description:
up to and including 5.6 this insert works on a view to a table in a different db
starting with mysql 5.7 it inserts nothing with no error

-- this inserts nothing no error
use tb;
insert into a2(aId,v,Qty)
Select a1Id*1000,concat(v,'sum'),Sum(Qty) as Qty
From a1
Group By a1Id,v
on duplicate key update
 v=values(v)
 ,Qty=values(Qty)
;

How to repeat:
drop database if exists ta;
drop database if exists tb;

CREATE DATABASE ta ;
CREATE DATABASE tb ;

CREATE TABLE ta.a1 (
  a1Id INT NOT NULL,
  v VARCHAR(45) NULL,
  Qty int default 1,
  PRIMARY KEY (a1Id))
;
CREATE TABLE ta.a2 (
  aId INT NOT NULL,
  v VARCHAR(45) NULL,
  Qty int default 0,
  PRIMARY KEY (aId))
;
use tb;
create or replace view a1 as Select * From ta.a1;
create or replace view a2 as Select * From ta.a2;
use ta;
insert into a1(a1Id,v)
values(1,'v1')
;

use tb;
insert into a1(a1Id,v)
values(2,'v2')
;

use ta;
insert into a1(a1Id,v)
values(3,'v3')
on duplicate key update
 v='v3'
;

use tb;
insert into a1(a1Id,v)
Select 4,'v4'
on duplicate key update
 v='v4'
;

use tb;
insert into a1(a1Id,v)
Select 5,'v5'
on duplicate key update
 v=values(v)
;

-- this inserts nothing no error
use tb;
insert into a2(aId,v,Qty)
Select a1Id*1000,concat(v,'sum'),Sum(Qty) as Qty
From a1
Group By a1Id,v
on duplicate key update
 v=values(v)
 ,Qty=values(Qty)
;

-- this works
use tb;
insert into a2(aId,v)
Select a1Id,concat(v,'nosum')
From a1
Group By a1Id,v
on duplicate key update
 v=values(v)
;

Select * From a1;
Select * From a2;
[24 Oct 2020 14:12] MySQL Verification Team
Thank you for the bug report.