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:
Category:MySQL Server: C API (client library) Severity:S4 (Feature request)
Version:5.1.44 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[31 Mar 2010 14:00] Olaf van der Spek
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()
	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;

128 // #define BINARY_FLAG	128		/* Field is binary   */
[31 Mar 2010 16:27] Peter Laursen
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'
[31 Mar 2010 16:57] MySQL Verification Team
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) |
|             |
1 row in set (0.03 sec)

[1 Apr 2010 6:19] Tonci Grgin
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.
[1 Apr 2010 7:35] Tonci Grgin
Even though I do not agree this is ok, I have to close the report as !Bg... Alas, it is how things are.
[1 Apr 2010 7:44] Olaf van der Spek
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?
[1 Apr 2010 8:34] Sveta Smirnova
Thank you for the feedback.

Your suggestion can be considered as valid feature request, so marking it as such.
[1 Apr 2010 8:38] Olaf van der Spek
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.
[1 Apr 2010 8:40] Olaf van der Spek
This is preventing clients from properly displaying such fields.
[1 Apr 2010 9:57] Olaf van der Spek
I'm still waiting for an explanation of why inet_ntoa returns binary data?
[1 Apr 2010 10:03] Tonci Grgin
Olaf, explanation is in manual... Every string-manipulation/conversion routine I can think of returns BINARY. Please check.

Not that I agree with that...
[1 Apr 2010 10:19] Olaf van der Spek
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?
[1 Apr 2010 10:31] Tonci Grgin
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.
[1 Apr 2010 10:34] Olaf van der Spek
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?
[1 Apr 2010 10:38] Tonci Grgin
Ok, no problem. I'll just leave bug open then...
[1 Apr 2010 10:46] Olaf van der Spek
[1 Apr 2010 11:44] Tonci Grgin
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.
[1 Apr 2010 15:15] Olaf van der Spek
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.").
[2 Apr 2010 10:09] Valeriy Kravchuk
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.
[2 Apr 2010 10:20] Olaf van der Spek
That would be great.
[12 Aug 2013 6:55] MySQL Verification Team
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