MySQL Bugs: #94973: Wrong result with subquery in where clause and order by

Bug #94973	Wrong result with subquery in where clause and order by
Submitted:	10 Apr 2019 16:12	Modified:	25 Apr 2019 13:07
Reporter:	Andreas Kohlbecker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.25	OS:	Linux (Ubuntu 4.15.0-47-generic)
Assigned to:		CPU Architecture:	x86 (64 bit)

Description:
Server Version 5.7.25

SELECT 
this_.nomenclaturallyRelevant
from ReferenceB this_ where this_.id in (select this_.id as y0_ from ReferenceB this_ 
where this_.refType='JOU') order by this_.externalLink asc;

alters the values of nomenclaturallyRelevant from 0 to 1 in the result set when the order by is added to the select statement

How to repeat:
CREATE TABLE `ReferenceB` (
  `id` int(11) NOT NULL,
  `nomenclaturallyRelevant` bit(1) NOT NULL,
  `refType` varchar(255) NOT NULL,
  `externalLink` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

SELECT 
this_.nomenclaturallyRelevant
from ReferenceB this_ where this_.id in (select this_.id as y0_ from ReferenceB this_ 
where this_.refType='JOU') order by this_.externalLink asc;

INSERT INTO cdm_phycobank_production.ReferenceB
(id, nomenclaturallyRelevant, refType, externalLink)
VALUES(1, 0, 'JOU', NULL);
INSERT INTO cdm_phycobank_production.ReferenceB
(id, nomenclaturallyRelevant, refType, externalLink)
VALUES(2, 0, 'JOU', NULL);

SELECT 
this_.nomenclaturallyRelevant
from ReferenceB this_ where this_.id in (select this_.id as y0_ from ReferenceB this_ 
where this_.refType='JOU') order by this_.externalLink asc;

--> produces the wrong results with altered values

SELECT 
this_.nomenclaturallyRelevant
from ReferenceB this_ where this_.id in (select this_.id as y0_ from ReferenceB this_ 
where this_.refType='JOU');

--> produces the correct results

HI,

Thank you for your bug report.

I have not managed to repeat the behaviour. Are you sure that all you need is a single row ???

We need to be able to repeat the behaviour, for which we need your data. At least the minimum number of rows that would show the behaviour.

Last, are you sure you are using our server ??? This is because I can not fine `this_` as a keyword neither in our lexer nor in our parser.

The example is reduced to the bare minimum required to reproduce the problem. This is why the SELECT statement only selects the affected row.

All data to reproduce the issue is created by the INSERT statements.

The 'this_' in the example is the alias for the table ReferenceB, it is not a keyword. 

I found this bug in the oracle mysql server version 5.7.25 as distributed with Linux Ubuntu 16.04.

Unfortunately I missed deleting a line from the example that I gave to reproduce the issue. To reproduce the issue you first need to create the table. Then execute the INSERT statements. Only now the SELECT statement makes sense.     

Here again the steps required to reproduce the issue:

 
CREATE TABLE `ReferenceB` (
  `id` int(11) NOT NULL,
  `nomenclaturallyRelevant` bit(1) NOT NULL,
  `refType` varchar(255) NOT NULL,
  `externalLink` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO cdm_phycobank_production.ReferenceB (id, nomenclaturallyRelevant, refType, externalLink) VALUES(1, 0, 'JOU', NULL);
INSERT INTO cdm_phycobank_production.ReferenceB (id, nomenclaturallyRelevant, refType, externalLink) VALUES(2, 0, 'JOU', NULL);

SELECT this_.nomenclaturallyRelevant from ReferenceB this_ where this_.id in (select this_.id as y0_ from ReferenceB this_ where this_.refType='JOU') order by this_.externalLink asc;

--> produces the wrong results with altered bit values: 1 instead of 0

Hi,

I have run your script two times. First time with BOOLEAN column being 0 in both rows and in second run with  BOOLEAN column being 1 in both rows.

Both times I have got the empty set.

I did some further tests and I could reproduce this bug with the mysql community server version 5.7.25 installed under windows 10. So this issue is for sure not depended on the operation system.

I simplified the script to reproduced the issue even more (all aliases and db names removed, field name simplified):

-----------------------------------------------------
CREATE TABLE `ReferenceB` (
  `id` int(11) NOT NULL,
  `bitField` bit(1) NOT NULL,
  `refType` varchar(255) NOT NULL,
  `externalLink` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO ReferenceB (id, bitField, refType, externalLink) VALUES(1, 0, 'JOU', NULL);
INSERT INTO ReferenceB (id, bitField, refType, externalLink) VALUES(2, 0, 'JOU', NULL);

SELECT bitField from ReferenceB  where id in (select id as y0_ from ReferenceB  where refType='JOU') order by externalLink asc;
-- > produces the wrong resultset with altered bit values: 1 instead of 0 in each of the two rows

SELECT bitField from ReferenceB where id in (select id as y0_ from ReferenceB where refType='JOU');
-- > this produces the correct resultset
-----------------------------------------------------

I ran this script a couple of times on windows 10 and on linux. The bug was reproducible each time. Please try again yourself.

Hi,

I have managed to reproduce it myself.

Thank you for your contribution.

Verified as reported.