Bug #70880 GROUP BY function produces truncated columns in combination with CONV() or BIN()
Submitted: 11 Nov 2013 14:31 Modified: 12 Dec 2013 19:12
Reporter: Thomas Teves Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.24-log OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 2013 14:31] Thomas Teves
When using the following command:
SELECT name, CONV(num-2451973654,10,2) AS details FROM test GROUP BY details

I expected mysql to return a list of entries containing each a name and a binary representation of an integer. 
Basically, this is what I get, but I spotted an error in the output: the output of the binary string is truncated to 21 characters, the 12 least significant bits are omitted.

When I drop the GROUP BY clause, the binary string is given in full length.

How to repeat:
Step 1: create this table:

  `name` varchar(100) NOT NULL,
  `num` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)

INSERT INTO `test` (`id`, `name`, `num`) VALUES
(1, 'testa', 7714170424),
(2, 'testb', 8609792541),
(3, 'testc', 7714170424),
(4, 'testa', 8209790541);

Step 2: First, execute 

SELECT name, CONV(num-2451973654,10,2) AS details FROM test

and you will get
testa	100111001101001101100000000100010
testb	101101111000010001101110000000111
testc	100111001101001101100000000100010
testa	101010111001100010101000000110111

Please notice the binary strings are 31 chars long

Step 3: Then, execute 

SELECT name, CONV(num-2451973654,10,2) AS details FROM test GROUP BY details

and you will get
testa 100111001101001101100
testa 101010111001100010101
testb 101101111000010001101

Obviously, the binary strings have been truncated to 21 characters.

Step 4:
I wanted to find a workaround and tried to first create a view and then execute the select containing the group by clause. But I got the same result. 

CREATE VIEW t1 AS SELECT name, CONV(num-2451973654,10,2) AS details FROM test
SELECT name, details FROM t1 GROUP BY details

Interestingly though I found out something: 
When looking into the structure of the newly created view t1 (in phpmyadmin), the type for details is VARCHAR(21);

The strangest thing though is that 
SELECT name, details FROM t1
gives out the binary strings in full length. 

What the hell is happening here?
[12 Nov 2013 19:12] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with version 5.5.28 and up. Please upgrade to current version 5.5.34, try with it and inform us if the issue still exists.
[13 Dec 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".