MySQL Bugs: #105170: wrong data returned grouping on substring and bit

Bug #105170	wrong data returned grouping on substring and bit
Submitted:	8 Oct 2021 9:03	Modified:	8 Oct 2021 14:16
Reporter:	Simon Sawyer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any (v10)
Assigned to:		CPU Architecture:	Any
Tags:	bit, GROUP

Description:
I have a table "accident" that has columns `openypwd` VARCHAR(14), `firstaidapplied` BIT(1) amongst others.

It is returning the wrong data when I do the query:

"select substring(openypwd,1,5),firstaidapplied,count(*) from accident where (openypwd like 'Y2021%') group by firstaidapplied,substring(openypwd,1,5)"
Y2021	1	18
Y2021	1	1

But it returns the correct data if I alter the query to add '' to the firstaidapplied field:
"select substring(openypwd,1,5),''+firstaidapplied,count(*) from accident where (openypwd like 'Y2021%') group by firstaidapplied,substring(openypwd,1,5)"

Y2021	0	18
Y2021	1	1

CREATE TABLE `accident` (
  `accidentid` int unsigned NOT NULL AUTO_INCREMENT,
  `create_userid` int unsigned NOT NULL,
  `createdate` datetime NOT NULL,
  `update_userid` int unsigned NOT NULL,
  `updatedate` datetime NOT NULL,
  `datetime` datetime NOT NULL,
  `factorylocationid` int NOT NULL,
  `openypwd` varchar(14) DEFAULT NULL,
  `escalationcriteriaid` int DEFAULT NULL,
  `equipmenttypeid` int DEFAULT NULL,
  `type` int NOT NULL,
  `injured_name` varchar(50) DEFAULT NULL,
  `injured_address` text NOT NULL,
  `injured_postcode` varchar(8) DEFAULT NULL,
  `reporter_name` varchar(50) DEFAULT NULL,
  `reporter_address` text NOT NULL,
  `reporter_postcode` varchar(8) DEFAULT NULL,
  `howhappened` text NOT NULL,
  `cause` text NOT NULL,
  `details` text NOT NULL,
  `bodypartids` varchar(100) DEFAULT NULL,
  `accidentcategoryid` int NOT NULL,
  `dayslost` int DEFAULT NULL,
  `riddor` bit(1) NOT NULL,
  `injured_type` int NOT NULL,
  `firstaider_userid` int DEFAULT NULL,
  `injured_occupation` varchar(50) DEFAULT NULL,
  `reporter_occupation` varchar(50) DEFAULT NULL,
  `firstaidapplied` bit(1) NOT NULL,
  PRIMARY KEY (`accidentid`)
) ENGINE=MyISAM AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb3;

How to repeat:
Do the above queries either on SQL WorkBench (version 8.0.26) or MySQL ODBC 8.0 Unicode Driver (version 8.00.26.00)

Hi Mr. Sawyer,

Thank you very much for your bug report.

However, this is not a bug.

MySQL is following SQL standards fully, so it returns results according to those standards.

Problem is that your second column is not involved, at all, in the aggregation, hence MySQL server can pick any value within the range of each of two aggregates.

You can solve it by adding the second column in GROUP BY or by using some of the new features in 8.0 that enable the returning of the desired values. It is all explained in our Reference Manual.

Not a bug.

I think you've misread it. The 2nd column that is wrong (firstaidapplied) is the very first term in the "group by". So it is involved and should return the correct value.

Sorry Mr. Sawyer,

Since we do not have your data, can you let us know if the following query works correctly:

select substring(openypwd,1,5 as first), firstaidapplied as second,count(*) from accident where (openypwd like 'Y2021%') group by first, second;

If you still get wrong results, then we shall need your data in order to be able to verify your report.

Waiting on your feedback.

Putting "first"/"second" gives the same problematic result. I've anonymised the data, checked it still gives the problem after being anonymised, and uploaded it.

Hi Mr. Sawyer,

We have repeated your data, but it does seem that it is a documentation bug.

This is solely due to the fact that aggregating by BIT type is, actually, undefined by SQL standards. Still, the results are wrong, which is why we have decided to verify this report. Simply, we repeated the behaviour.

Verified as reported.