Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

HeidiSQL converts string to hexadecimal when UNION is used

giordano2's profile image giordano2 posted 5 years ago in Running SQL scripts Permalink

There is a strange behaviour with HeidiSQL (version is 9.5.0). I would like to collect with UNION two selects. If one select contains COUNT(*) and the other correspoding columns has character the character is converted to hexadecimal (see coloumn 1):

SELECT 'a' c1, 1 c2,  NULL c3, 'b' c4
UNION
SELECT COUNT(*) c1, COUNT(*) c2, COUNT(*)  c3, CAST(COUNT(*) AS CHAR) c4
-- OUTPUT
-- HEX(n)     c2    c3       c4
-- 0x61        1    \N       'b'
-- 0x31        1     1        1

As soon COUNT(*) is used the string is converted. This is not the case if I submit on the console and in older version of HeidiSQL. Any idea how can I avoid this behaviour? I could convert COUNT(*) to string as in column 4, but maybe there is a solution without this hack.

ansgar's profile image ansgar posted 5 years ago Permalink

Can you verify that was not the case on earlier versions?

Normally, the server gives the hint for the data type of a column. And different MySQL and MariaDB versions vary in minor cases here.

Also, I fixed a bug in HeidiSQL, which lead to wrong detected data types, probably this one for another forum thread here.

giordano2's profile image giordano2 posted 5 years ago Permalink

Yes. I have two versions of HeidiSQL: 9.5.0 which gives the hex-output and 9.4.0 which gives the string output.

ansgar's profile image ansgar posted 5 years ago Permalink

Then this is probably due to the above mentioned change.

The console output does not even display "hex()" or similar output for a SELECT BINARY 'a'. So this does not necessarily mean this is wrong in HeidiSQL.

After activating Debug log messages in HeidiSQL, one can see that the MySQL/MariaDB library detects the "c1" column as VARBINARY.

Please note that you can change the output of binary columns not to display hex code, but readable text. See the white button on the main toolbar with the purple "0x" on it. This is also described in the online help page.

giordano2's profile image giordano2 posted 5 years ago Permalink

Thanks for the 0x button. I'm happy with this solution. How can I activate debug log?

ansgar's profile image ansgar posted 5 years ago Permalink

Tools > Preferences > Logging

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.