Description:
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:
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`num` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
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
and
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?
