Manfred Steinsiek created DERBY-6788:
----------------------------------------
Summary: Wrong value inserted by INSERT INTO with multiple
subselects
Key: DERBY-6788
URL: https://issues.apache.org/jira/browse/DERBY-6788
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.1.1, 10.10.2.0, 10.9.1.0, 10.8.2.2, 10.7.1.1,
10.6.2.1, 10.5.3.0, 10.4.2.0, 10.3.3.0, 10.2.2.0, 10.1.1.0
Environment: not relevant, tested under Windows 7 32bit
Reporter: Manfred Steinsiek
Priority: Critical
Certain INSERT INTO statements with multiple subselects insert wrong values,
i.e. may lead to data corruption.
Here is a very simple example how to reproduce this bug: Start with a new
(empty) Derby database, create 3 tiny tables, and insert two records:
create table m1 (k varchar(64), s decimal);
create table m2 (k varchar(64), s decimal);
create table v (s decimal);
insert into m1 values ('Bug', 2015);
insert into m2 values ('Bug', 1957);
Now, the following (likewise simple) select
select res.* from (select d2.s from m1
left join (select k,s from m2) as d2 on m1.k=d2.k) as res
yields 1957, of course. - Of course? Not entirely: If I add an INSERT INTO to
that select, i.e.
insert into v (select res.* from (select d2.s from m1
left join (select k,s from m2) as d2 on m1.k=d2.k) as res)
then table v contains 1 row (as it should), but in this row s=4,355,431. This
value is interesting, because 4355431 = Hex 427567 and 'B' = X42, 'u'=X75,
'g'=X67.
Finally, if I slightly modify the INSERT INTO above as
insert into v (select res.* from (select d2.s*1 from m1
left join (select k,s from m2) as d2 on m1.k=d2.k) as res)
then it works correct.
This phenomenon arose with every Derby version 10.x I tried (see list above).
Possibly this bug is related to DERBY-6786, where similar INSERT INTOs with
subselects appear.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
