Bug #30245 BIT type gets cast to LONG when DISTINCT or GROUP BY is used

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.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32699

ChangeSet@1.2495, 2007-08-17 18:30:41+04:00, evgen@moonbone.local +3 -0
  Bug#30245: A wrong type of a BIT field is reported when grouped by it.
  
  HEAP tables can't index BIT fields. Due to this when grouping by such fields is
  needed they are converted to a fields of the LONG type when temporary table
  is being created. But a side effect of this is that a wrong type of BIT
  fields is returned to a client.
  
  Now the JOIN::prepare and the create_distinct_group functions are create
  additional hidden copy of BIT fields to preserve original fields untouched.
  New hidden fields are used for grouping instead.

Pushed into 5.1.23-beta

Pushed into 5.0.50

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in 5.0.50 and 5.1.23 changelogs as follows:

          Using DISTINCT or GROUP BY on a BIT column in a SELECT
          statement caused the column to be cast internally as an integer, 
          with incorrect results being returned from the query.