MySQL Bugs: #48889: user fn, join, order by (, InnoDB) won't return tinyint

Bug #48889	user fn, join, order by (, InnoDB) won't return tinyint
Submitted:	19 Nov 2009 2:22	Modified:	4 Dec 2009 23:03
Reporter:	Kasey Speakman	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.77, 5.0.86, 5.0.88	OS:	Linux (CentOS 5.2 Final)
Assigned to:		CPU Architecture:	Any

Description:
A function that specifies TINYINT(1) as a return type doesn't actually return TINYINT(1). It returns TINYINT of a different (probably default) length instead. Having at least some hacky workaround to convert to TINYINT(1) is needed for proper conversion to the Boolean type in Connector/NET.

History:
The only way to get a boolean result column in Connector/NET is for the MySQL result column to be of type TINYINT(1). Since CAST and CONVERT do not convert to this type, and most MySQL functions return INT or BIGINT as truth values, I was attempting to use a user function to convert these to TINYINT(1). But this doesn't work.

Connector/NET 5.2.5 (although this problem is not connector related)

How to repeat:
Create a user function:

DROP FUNCTION IF EXISTS `to_bool`;
CREATE FUNCTION `to_bool`(
  var_num BIGINT
) RETURNS tinyint(1)
RETURN var_num;

Use Connector/NET to perform a query:

SELECT to_bool(TRUE) AS my_bool;

NOTE: I haven't been able to find a way to get the raw output from MySQL to see exactly what type it's returning, so I observe that it's doing the wrong thing by the side effect that it's getting converted to the incorrect type in Connector/NET.

Column "my_bool" return type will not be a TINYINT(1) as the function specifies. As a result, in Connector/NET it will get converted to an Int32 rather than a Boolean.

If I were to select from a table with a column type of TINYINT(1) (even wrapped in a stored procedure), it would properly return a TINYINT(1) that gets converted to Boolean in Connector/NET.

In case it's needed, here's full C# code for query with Connector/NET:

using System;
using System.Data;
using MySql.Data.MySqlClient;
...
string query = @"
    DROP FUNCTION IF EXISTS to_bool;
    CREATE FUNCTION to_bool(var_num BIGINT) RETURNS tinyint(1) RETURN var_num;
    SELECT to_bool(TRUE) AS my_bool;"; // outputs Int32
/* compare to:
query = @"
    DROP TEMPORARY TABLE IF EXISTS bool_test;
    CREATE TEMPORARY TABLE bool_test (my_bool tinyint(1) NOT NULL);
    INSERT bool_test VALUES (1);
    SELECT * from bool_test;"; // outputs Boolean
/* */
MySqlConnection con = new MySqlConnection("Database=my_db;Data Source=localhost;User Id=user;Password=password");
DataSet set = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(query, con);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.Fill(set);
string type_name = set.Tables[0].Rows[0]["my_bool"].GetType().Name;
Console.WriteLine(type_name); // console app

Suggested fix:
Make functions return their specified types, especially in the case of TINYINT(1) since it's the only type that can be easily distinguished as a boolean type by outside languages (especially .NET). And a function is the only way to cast to tinyint. :/

Please, check with a newer server, 5.0.86. From what I can see, the result is TINYINT(1). Look:

77-52-1-11:5.0 openxs$ bin/mysql -uroot -T test
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 2
Server version: 5.0.88-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1;
Field   1:  `1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM 

+---+
| 1 |
+---+
| 1 | 
+---+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION `to_bool`(   var_num BIGINT ) RETURNS tinyint(1) RETURN var_num;
Query OK, 0 rows affected (0.02 sec)

mysql> select to_bool(1);
Field   1:  `to_bool(1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NUM 

+------------+
| to_bool(1) |
+------------+
|          1 | 
+------------+
1 row in set (0.00 sec)

mysql> create temporary table tt as select to_bool(1) as a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TEMPORARY TABLE `tt` (
  `a` tinyint(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, if you'll get the same problem with 5.0.86, I'd say it is either a bug in Connector/Net or in your application.

Glad you showed me about -T. I read that option, but only tried "echo 'select ...' | mysql -p" with it in testing, and that doesn't show the type info. So I figured it didn't show types.

Anyway, I'm seeing the same thing you are in my version. It is probably the connector in that case. I'll close this bug and test with a newer connector, then go from there.

Thanks!

Well, I updated Connector/NET and this bug still exists, and also tried on later version (5.0.77) with same effect. I did some further debugging and I found that there are specific conditions that trigger it:

InnoDB engine
result of user function as column alias
using a join (left and inner tried, maybe others too)
specifying an order by condition

Moved to InnoDB category and reopened. Test case follows (no need for tables to contain data to see the bug). Ran as root user:

drop database if exists kasey_test;
create database kasey_test;
use kasey_test;

CREATE TABLE `test1` (
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `test2` (
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE FUNCTION `to_bool`(var_num bigint) RETURNS tinyint(1)
return var_num;

select to_bool(0) accessible from test1 left join test2 using (id) order by test1.id;
select to_bool(0) accessible from dual where 0 = 1;

My results:

#rpm -qa | grep -i mysql-server
mysql-server-5.0.77-3.el5
#mysql -D kasey_test -T -p
Enter password:
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 54198
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select to_bool(0) accessible from test1 left join test2 using (id) order by test1.id;
Field   1:  `accessible`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM 

0 rows in set (0.00 sec)

mysql> select to_bool(0) accessible from dual where 0 = 1;
Field   1:  `accessible`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM 

0 rows in set (0.00 sec)

I can confirm your findings:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 -T kasey_
test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.86-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select to_bool(0) accessible from dual where 0 = 1;
Field   1:  `accessible`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM

0 rows in set (0.02 sec)

mysql> select to_bool(0) accessible from test1 left join test2 using (id) order
by test1.id;
Field   1:  `accessible`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM

0 rows in set (0.00 sec)

This is because when join is used we have "Using temporary; Using filesort" in EXPLAIN results for the query. I think this is a kown bug that may be even fixed.

Fixed in 5.1.42 for sure:

mysql> select to_bool(0) a from test1 left join test2 using (id) order by test1.id;
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM

0 rows in set (0.02 sec)

mysql> select version();
Field   1:  `version()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     12
Max_length: 12
Decimals:   31
Flags:      NOT_NULL

+--------------+
| version()    |
+--------------+
| 5.1.42-debug |
+--------------+
1 row in set (0.00 sec)

But even latest 5.0.88 is still affected:

mysql> select to_bool(0) accessible from test1 left join test2 using (id) order by test1.id;
Field   1:  `accessible`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     1
Max_length: 0
Decimals:   0
Flags:      NUM

0 rows in set (0.00 sec)

mysql> select version();
Field   1:  `version()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     12
Max_length: 12
Decimals:   31
Flags:      NOT_NULL

+--------------+
| version()    |
+--------------+
| 5.0.88-debug |
+--------------+
1 row in set (0.00 sec)

So, we have 5.0.x only bug here.

Issue is addressed in 5.1 and will not be fixed in 5.0

This is not overly helpful since the latest version of CentOS / RHEL only comes with MySQL 5.0.x. But I understand.