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

Concatenate Issue

User, date Message
Written by muzza4
5 years ago
Category: General
51 posts since Mon, 04 Dec 06
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
Written by rosenfield
5 years ago
127 posts since Wed, 24 Jan 07
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.
Written by muzza4
5 years ago
51 posts since Mon, 04 Dec 06
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
Written by ansgar
5 years ago
4801 posts since Fri, 07 Apr 06
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.
Written by kalvaro
5 years ago
564 posts since Thu, 29 Nov 07

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.
Written by x2A
5 years ago
14 posts since Fri, 22 May 09
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
Written by rosenfield
5 years ago
127 posts since Wed, 24 Jan 07

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 :-).
Written by x2A
5 years ago
14 posts since Fri, 22 May 09
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.
Written by muzza4
5 years ago
51 posts since Mon, 04 Dec 06
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
Written by rosenfield
5 years ago
127 posts since Wed, 24 Jan 07

muzza4 wrote: Feel better now?



I didn't feel particularly bad at the time, so no I guess?
Written by kkiran
5 years ago
4 posts since Thu, 11 Jun 09
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!
Written by ansgar
5 years ago
4801 posts since Fri, 07 Apr 06
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.