Description:
The bit type gets cast/converted to a long when it is DISTINCTed or GROUP BYed.
How to repeat:
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (b BIT) ENGINE=MyISAM;
INSERT INTO bit_test (b) VALUES (1), (0);
SELECT b FROM bit_test;
SELECT DISTINCT b FROM bit_test;
SELECT b FROM bit_test GROUP BY b;
We can see initially that the output of these commands is strange:
mysql> SELECT b FROM bit_test;
+------+
| b |
+------+
| |
| |
+------+
2 rows in set (0.00 sec)
mysql> SELECT DISTINCT b FROM bit_test;
+------+
| b |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT b FROM bit_test GROUP BY b;
+------+
| b |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)
Using gdb we can see why that is:
(gdb) break mysql.cc:2366
Breakpoint 1 at 0x8438: file mysql.cc, line 2366.
Breakpoint 2 at 0x8527: file mysql.cc, line 2366.
warning: Multiple breakpoints were set.
Use the "delete" command to delete unwanted breakpoints.
(gdb) run -u root test
Starting program: /Users/jcole/src/mysql-5.0.41.profile/client/.libs/mysql -u root test
Reading symbols for shared libraries ..+ done
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 219
Server version: 5.0.45-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select b from bit_test;
Breakpoint 1, print_table_data (result=0x52c498) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
Breakpoint 2, print_table_data (result=0x52c498) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
2368 uint length= column_names ? field->name_length : 0;
(gdb) p *field
$1 = {
name = 0x181d6a8 "b",
org_name = 0x181d6b0 "b",
table = 0x181d688 "bit_test",
org_table = 0x181d698 "bit_test",
db = 0x181d680 "test",
catalog = 0x181d678 "def",
def = 0x0,
length = 1,
max_length = 1,
name_length = 1,
org_name_length = 1,
table_length = 8,
org_table_length = 8,
db_length = 4,
catalog_length = 3,
def_length = 0,
flags = 32,
decimals = 0,
charsetnr = 63,
type = MYSQL_TYPE_BIT
}
(gdb) c
Continuing.
Breakpoint 2, print_table_data (result=0x52c498) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) c
Continuing.
+------+
| b |
+------+
| |
| |
+------+
2 rows in set (0.01 sec)
mysql> select distinct b from bit_test;
Breakpoint 1, print_table_data (result=0x52c448) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
Breakpoint 2, print_table_data (result=0x52c448) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
2368 uint length= column_names ? field->name_length : 0;
(gdb) p *field
$2 = {
name = 0x181b6a0 "b",
org_name = 0x181b6a8 "b",
table = 0x181b688 "bit_test",
org_table = 0x181b698 "",
db = 0x181b680 "test",
catalog = 0x181b678 "def",
def = 0x0,
length = 1,
max_length = 1,
name_length = 1,
org_name_length = 1,
table_length = 8,
org_table_length = 0,
db_length = 4,
catalog_length = 3,
def_length = 0,
flags = 32800,
decimals = 0,
charsetnr = 63,
type = MYSQL_TYPE_LONG
}
(gdb) c
Continuing.
Breakpoint 2, print_table_data (result=0x52c448) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) c
Continuing.
+------+
| b |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec)
mysql> select b from bit_test group by b;
Breakpoint 1, print_table_data (result=0x52c648) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
Breakpoint 2, print_table_data (result=0x52c648) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) n
2368 uint length= column_names ? field->name_length : 0;
(gdb) p *field
$3 = {
name = 0x181d6a0 "b",
org_name = 0x181d6a8 "b",
table = 0x181d688 "bit_test",
org_table = 0x181d698 "",
db = 0x181d680 "test",
catalog = 0x181d678 "def",
def = 0x0,
length = 1,
max_length = 1,
name_length = 1,
org_name_length = 1,
table_length = 8,
org_table_length = 0,
db_length = 4,
catalog_length = 3,
def_length = 0,
flags = 32800,
decimals = 0,
charsetnr = 63,
type = MYSQL_TYPE_LONG
}
(gdb) c
Continuing.
Breakpoint 2, print_table_data (result=0x52c648) at mysql.cc:2366
2366 while ((field = mysql_fetch_field(result)))
(gdb) c
Continuing.
+------+
| b |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)
mysql>
Suggested fix:
Ensure that the return type is MYSQL_TYPE_BIT regardless of SQL features acting on the column.
Description: The bit type gets cast/converted to a long when it is DISTINCTed or GROUP BYed. How to repeat: DROP TABLE IF EXISTS bit_test; CREATE TABLE bit_test (b BIT) ENGINE=MyISAM; INSERT INTO bit_test (b) VALUES (1), (0); SELECT b FROM bit_test; SELECT DISTINCT b FROM bit_test; SELECT b FROM bit_test GROUP BY b; We can see initially that the output of these commands is strange: mysql> SELECT b FROM bit_test; +------+ | b | +------+ | | | | +------+ 2 rows in set (0.00 sec) mysql> SELECT DISTINCT b FROM bit_test; +------+ | b | +------+ | 1 | | 0 | +------+ 2 rows in set (0.00 sec) mysql> SELECT b FROM bit_test GROUP BY b; +------+ | b | +------+ | 0 | | 1 | +------+ 2 rows in set (0.00 sec) Using gdb we can see why that is: (gdb) break mysql.cc:2366 Breakpoint 1 at 0x8438: file mysql.cc, line 2366. Breakpoint 2 at 0x8527: file mysql.cc, line 2366. warning: Multiple breakpoints were set. Use the "delete" command to delete unwanted breakpoints. (gdb) run -u root test Starting program: /Users/jcole/src/mysql-5.0.41.profile/client/.libs/mysql -u root test Reading symbols for shared libraries ..+ done Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 219 Server version: 5.0.45-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select b from bit_test; Breakpoint 1, print_table_data (result=0x52c498) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n Breakpoint 2, print_table_data (result=0x52c498) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n 2368 uint length= column_names ? field->name_length : 0; (gdb) p *field $1 = { name = 0x181d6a8 "b", org_name = 0x181d6b0 "b", table = 0x181d688 "bit_test", org_table = 0x181d698 "bit_test", db = 0x181d680 "test", catalog = 0x181d678 "def", def = 0x0, length = 1, max_length = 1, name_length = 1, org_name_length = 1, table_length = 8, org_table_length = 8, db_length = 4, catalog_length = 3, def_length = 0, flags = 32, decimals = 0, charsetnr = 63, type = MYSQL_TYPE_BIT } (gdb) c Continuing. Breakpoint 2, print_table_data (result=0x52c498) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) c Continuing. +------+ | b | +------+ | | | | +------+ 2 rows in set (0.01 sec) mysql> select distinct b from bit_test; Breakpoint 1, print_table_data (result=0x52c448) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n Breakpoint 2, print_table_data (result=0x52c448) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n 2368 uint length= column_names ? field->name_length : 0; (gdb) p *field $2 = { name = 0x181b6a0 "b", org_name = 0x181b6a8 "b", table = 0x181b688 "bit_test", org_table = 0x181b698 "", db = 0x181b680 "test", catalog = 0x181b678 "def", def = 0x0, length = 1, max_length = 1, name_length = 1, org_name_length = 1, table_length = 8, org_table_length = 0, db_length = 4, catalog_length = 3, def_length = 0, flags = 32800, decimals = 0, charsetnr = 63, type = MYSQL_TYPE_LONG } (gdb) c Continuing. Breakpoint 2, print_table_data (result=0x52c448) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) c Continuing. +------+ | b | +------+ | 1 | | 0 | +------+ 2 rows in set (0.00 sec) mysql> select b from bit_test group by b; Breakpoint 1, print_table_data (result=0x52c648) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n Breakpoint 2, print_table_data (result=0x52c648) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) n 2368 uint length= column_names ? field->name_length : 0; (gdb) p *field $3 = { name = 0x181d6a0 "b", org_name = 0x181d6a8 "b", table = 0x181d688 "bit_test", org_table = 0x181d698 "", db = 0x181d680 "test", catalog = 0x181d678 "def", def = 0x0, length = 1, max_length = 1, name_length = 1, org_name_length = 1, table_length = 8, org_table_length = 0, db_length = 4, catalog_length = 3, def_length = 0, flags = 32800, decimals = 0, charsetnr = 63, type = MYSQL_TYPE_LONG } (gdb) c Continuing. Breakpoint 2, print_table_data (result=0x52c648) at mysql.cc:2366 2366 while ((field = mysql_fetch_field(result))) (gdb) c Continuing. +------+ | b | +------+ | 0 | | 1 | +------+ 2 rows in set (0.00 sec) mysql> Suggested fix: Ensure that the return type is MYSQL_TYPE_BIT regardless of SQL features acting on the column.