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;
