distal-attribute
distal-attribute
distal-attribute
distal-attribute

Concatenate Issue

muzza4 posted 6 years ago in General
Hi there,

In version 4 this query

select concat('Text String ',
(select count(*) from club where billing = 'Y')) as Text
from hub_info

results in

HEX(Text)
5465787420537472696E67203331

while in version 3.2 it results in

Text
Text String 31

Is this a bug, or what do I need to do to my query to get it to work as it does under 3.2?

Regards
Muzza
rosenfield posted 6 years ago
That's issue #863, and yes, it was (is?) a bug in the server.

Upgrading the server to the newest version probably does the trick.
muzza4 posted 6 years ago
Hi Rosenfield,

That doesn't seem right to me. HeidiSQL 3.2 displays that data well, and HeidiSQL 4.0 does not.

Since I run both Heidi version against the same database, this proves it's Heidi not the server at fault (doesn't it?).

Regards
ansgar posted 6 years ago
It's the server which returns a binary collation for CONCAT(bla...). HeidiSQL has been changed to display binary data as hex so it's safe to edit such content. As rosenfield said, upgrading the server fixes that - CONCAT() then returns a text collation.
kalvaro posted 6 years ago

anse wrote: It's the server which returns a binary collation for CONCAT(bla...). HeidiSQL has been changed to display binary data as hex so it's safe to edit such content. As rosenfield said, upgrading the server fixes that - CONCAT() then returns a text collation.



I understand it's a server bug, but working in your own server is probably the least common situation for a developer and a client-side workaround is often the only feasible solution. It's sad to need to write workarounds from third-party bugs that were fixed long ago but it happens all the time. I know that: I'm a web developer and I've wasted endless hours fixing IE6 bugs for those guys who have the latest eMule but will never upgrade their browser.

Is it possible to invent a reasonable workaround when the server version is known to be buggy and the result belongs to a calculated field? We users would absolutely appreciate it.

x2A posted 6 years ago
I don't believe this is a bug, either in heidi or mysql. This is just how casting works, how it ideally should work, and it's easily overridden with cast(). The result of COUNT(*) is numberical, not character, which forces the resulting concat to be upgraded to support it. Binaries should not be silently converted to strings, and silent conversion should not be relied on, as you will hit these issues when you move to a platform that does Do The Right Thing (as Heidi is here)

The correct SQL with explicit conversion would be something like:

SELECT CONCAT('Records: ', CAST(COUNT(*) AS CHAR)) AS `Text` FROM `club` WHERE `billing`='Y'

Notice how similar things occur with, for example, datetime functions, which return values depend on the context they're called with:

SELECT CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP()+0
--> '2009-05-26 06:37:47', 20090526063747
rosenfield posted 6 years ago

muzza4 wrote: Since I run both Heidi version against the same database, this proves it's Heidi not the server at fault (doesn't it?).



No.

(I think the particular kind of logical fallacy you're exhibiting here is a hasty generalization.)

kalvaro wrote: Is it possible to invent a reasonable workaround when the server version is known to be buggy and the result belongs to a calculated field? We users would absolutely appreciate it.



No, because:

A loss of information occurs when the server concatenates two text strings with a specific character set and throws away the name of the character set. There's no way to recover that information.

If there had been a way to magically recover that information, a workaround would surely already have been put in place, considering the number of people that are, ahem, too lazy to upgrade their server to a less buggy version.

x2A wrote: The result of COUNT(*) is numberical, not character, which forces the resulting concat to be upgraded to support it



Actually, if you run Wireshark and take a dump of the protocol data, you'll see that the server sends integers in ASCII (or compatible) format.

Similarly when integers are sent in a query from HeidiSQL to the server; the integers are transmitted as text (namely the characters with ordinal values 48 through 57), not as numbers.

Anyway.

x2A wrote: Binaries should not be silently converted to strings, and silent conversion should not be relied on, as you will hit these issues when you move to a platform that does Do The Right Thing



Agreed.

x2A wrote: The correct SQL with explicit conversion would be something like:

SELECT CONCAT('Records: ', CAST(COUNT(*) AS CHAR)) AS `Text` FROM `club` WHERE `billing`='Y'



... and of course, the CAST() could in theory be moved from the server and to HeidiSQL. For example, there could be a dropdown box in HeidiSQL where you could choose to interpret data that the server says is binary as text using a specific character set.

Argueably, moving the CAST() into the GUI would be a nice thing to do. (Being that the purpose in life for the HeidiSQL GUI is to translate everything the user points at into SQL anyway.)

It's just a lot of work, and after it's been implemented it's a lot of maintenance.

And a third reason noone has bothered implementing CAST in the GUI, is that in practice it is only useful in cases where (a) the server is buggy or (b) the user has chosen a wrong data type for a column, and in both cases there are much better fixes already available. (The fixes being (a) upgrade the server and (b) choose the correct data type.)

x2A wrote:
Notice how similar things occur with, for example, datetime functions, which return values depend on the context they're called with:

SELECT CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP()+0
--> '2009-05-26 06:37:47', 20090526063747



Nice, hadn't thought of that :-).
x2A posted 6 years ago
Actually, if you run Wireshark and take a dump of the protocol data, you'll see that the server sends integers in ASCII (or compatible) format

Sure, but internally, as far as MySQL's concerned, the two are different, as running the following demonstrates:

SELECT CONCAT('string',1), CONCAT('string','1')
--> 0x737472696E6731, 'string1'

I think Heidi's doing absolutely the right thing by not hiding that fact.
muzza4 posted 6 years ago
Hi Rosenfield

wrote: (I think the particular kind of logical fallacy you're exhibiting here is a hasty generalization.)



Feel better now?

And thanks X2A for answering my question.

Cheers
rosenfield posted 6 years ago

muzza4 wrote: Feel better now?



I didn't feel particularly bad at the time, so no I guess?

kkiran posted 6 years ago
The issue still persists in the latest version. This is the first time I used heidisql (used concat_ws()) and got a hex value! Hope it is fixed soon!
ansgar posted 6 years ago
Please read the whole thread - it's a bug in older MySQL server versions. Upgrade your server and you're done.

Please login to leave a reply, or register at first.