Bug #105170 wrong data returned grouping on substring and bit
Submitted: 8 Oct 9:03 Modified: 8 Oct 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

[8 Oct 9:03] Simon Sawyer
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)
[8 Oct 12:32] MySQL Verification Team
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.
[8 Oct 12:36] Simon Sawyer
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.
[8 Oct 13:01] MySQL Verification Team
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.
[8 Oct 13:47] Simon Sawyer
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.
[8 Oct 14:16] MySQL Verification Team
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.