MySQL Bugs: #52505: MYSQL_FIELD::flags & BINARY

Bug #52505	MYSQL_FIELD::flags & BINARY_FLAG
Submitted:	31 Mar 2010 14:00	Modified:	12 Aug 2013 7:17
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S4 (Feature request)
Version:	5.1.44	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
The type of "select inet_ntoa(2130706433)" appears to be binary. This isn't right.
It's not limited to inet_ntoa.

This is a problem for clients that display binary as hex.

How to repeat:
#include <cassert>
#include <iostream>
#include <memory>
#include <mysql/mysql.h>

using namespace std;

int main()
{
	MYSQL h;
	assert(mysql_init(&h));
	assert(!mysql_options(&h, MYSQL_READ_DEFAULT_GROUP, ""));
	assert(mysql_real_connect(&h, NULL, NULL, NULL, NULL, 0, NULL, 0));
	assert(!mysql_query(&h, "select inet_ntoa(2130706433)"));
	MYSQL_RES* result = mysql_store_result(&h);
	MYSQL_FIELD* field = mysql_fetch_field(result);
	cout << field->flags << endl;
	return 0;
}

Output:
128 // #define BINARY_FLAG	128		/* Field is binary   */

simpler test case:

SELECT INET_NTOA(2130706433) COLLATE utf8_general_ci;
/* returns

Error Code : 1253
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'
*/

C:\DBS>5.1\bin\mysql -uroot --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.46-Win X64-log Source distribution

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

mysql> select inet_ntoa(2130706433);
Field   1:  `inet_ntoa(2130706433)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     31
Max_length: 9
Decimals:   0
Flags:      BINARY

+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.03 sec)

mysql>

Well, as I already said in Bug#10491, we are throwing this BINARY all around...
  "This problem represents grave issue for connectors. They can't work around it, at least not reliably. Ad hoc user queries do not allow connector to distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary..."

This is probably due to the fact that we do not have BINARY type at C API level so BINARY fields are determined through binary collation (63) and binary flag values.

Even though I do not agree this is ok, I have to close the report as !Bg... Alas, it is how things are.

Why?
Why does inet_ntoa have to return a binary collation?
Why can't the general issue be fixed by introducing the binary type or at least adding a REAL_BINARY_FLAG?

Thank you for the feedback.

Your suggestion can be considered as valid feature request, so marking it as such.

Why a feature request? It's not like I'm asking you to add support for binary field types, those types are there already. Implementation just isn't at 100% yet.

This is preventing clients from properly displaying such fields.

I'm still waiting for an explanation of why inet_ntoa returns binary data?

Olaf, explanation is in manual... Every string-manipulation/conversion routine I can think of returns BINARY. Please check.

Not that I agree with that...

Got a link (to that manual page)?
I'm sure it's documented, but is it explained (rationale)?
Why can't it be changed/fixed?

Sure it can be fixed! That's why Sveta proposed change in severity. Please lower the severity to S4 (feature request) and we'll see what will happen.

Why would it be a feature request? The return type is just wrong. That's a bug, not a request for a new feature, is it?

Ok, no problem. I'll just leave bug open then...

Why?

Because you are harassing me even though I already explained that such is the nature of conversions in MySQL. Now you made me run about mail archives and work-logs losing hours of my time and depriving others of my help. This is just not fair. Even after I agreed with all you said.

Anyways, the problem you're presenting in last two reports is well known, from ad-hoc queries like "SHOW CREATE...", parameters, triggers, concat ... The safest bet when it comes to conversion functions is a binary string as it can represent almost everything. And this is what the server is doing now. Thus this is actually a feature request to introduce the BINARY type or at least add a REAL_BINARY_FLAG (your words).

So how come this is not a feature request?

We can go on like this forever, so my suggestion still is:
  Feature request to either introduce the BINARY type or at least add a REAL_BINARY_FLAG (your words).

With this I can agree and defend my ruling. Otherwise, bug will remain open.

Come on, I'm just asking for an explanation.
At first you closed the report with !Bg without offering a solution.
Then Sveta said it was a feature request. MySQL seems to have a very different definition of features than me. A feature would be binary fields. Providing a way for clients to get this field type (properly) is part of that same feature, it's not a new feature.
But if it makes you feel better, I'll change the severity. Although it seemed Sveta had already done something like that ("so marking it as such.").

I think the problem with inet_ntoa() and some other functions will be solved in upcoming 5.5 in frames of http://forge.mysql.com/worklog/task.php?id=2649.

That would be great.

on 5.5.33 no more binary:

mysql> SELECT INET_NTOA(167773449);
Field   1:  `INET_NTOA(167773449)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     93
Max_length: 8
Decimals:   0
Flags: