Concatenate Issue

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

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.

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

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.

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.

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

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.)

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.

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.

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.

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.)

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 :-).

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.

Hi Rosenfield

(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

Feel better now?

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

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!

Please read the whole thread - it's a bug in older MySQL server versions. Upgrade your server and you're done.

Adding CAST resolved the problem:

select CONCAT('text: ', CAST(count(*) AS CHAR)) from....

After upgrading Heidi today, this issue appeared o.O

This seems weird that it appears that way. No consistency !

Anyway, the fix above helps.

in version 12 does it still exist or what is it called or to what function has it been updated?