Is this a bug ou a change in code

[pmiguelmartins]

I have this sql :

select
concat(e.cpost_codigo, if(e.cpost_rua=0,"", concat('-',e.cpost_rua)), " ", e.cpost_nome) as cp
from entidades e
where e.firmsid = 2

in version 7.0 alpha the field cp was a string

in version 7.0.1 beta the field cp is byte

Is this a permanent change ou a bug

Pedro Miguel Martins

[mdaems]

This looks like a bug.
Can you try to file a bug report in our sourceforge tracker (we're trying to fade out the Mantis bug tracker). You need a sourceforge login to do so. It's the menu item 'Tickets' on the sourceforge project page.

Can you provide a little more information:
- Compiler version
- Database version
- create code for the entidades table

Thanks,

Mark

[pmiguelmartins]

Just sent the ticket

Thanks

Pedro Martins

[EgonHugeist]

pmiguelmartins,

Have a look to your ticket.

I can't reproduce this issue. Have allways a string field as result.

[pmiguelmartins]

I deleted all zeos and reinstall from the beginning
create again my table

and the problem persist

If I change the sql code to

cast ( ..... as char ) ......


works well

Pedro Martins

[EgonHugeist]

pmiguelmartins,

i'm still not able to reproducve this issue. We've got only two case to assum a ftByte type: If we get 'TINYINT' brom the meatinformation tables or we got FIELD_TYPE_TINY from the ResultSet-Meta informations.

Are there really differneces between MySQL5.5.3 and your version to expect. I'm still not able to reproduce this issue.

Anybody else?

[pmiguelmartins]

EgonHugeist,

I think the problem is caused because a field in the concat is of type integer

If my create a concat with all fields of type char works just fine


Pedro Miguel Martins

[EgonHugeist]

pmiguelmartins,

this i do understand, but why do you have a different behavior between Alpha and Beta? Nothing on type detection has been changed for MySQL. Are you really sure the Database, Compiler and statement are 100% equal? Except the Zeos-Version?

[pmiguelmartins]

EgonHugeist,

I can assure that are everything equal except the zeos version

I rename the folder that I have with alpha and beta and with alpha folder works and with beta doesn't (I inclusive delete all dcu's and bpl's to assure)

I know that this isn't the solution but I change this code and works fine

ZDbcMySqlUtils.pas (function ConvertMySQLHandleToSQLType)


change this :

FIELD_TYPE_STRING:
if (FieldFlags and BINARY_FLAG) = 0 then
if ( CharEncoding = ceUTF8 ) and UTF8StringAsWideField then
Result := stUnicodeString
else
Result := stString
else
Result := stBytes;


to this :


FIELD_TYPE_STRING:
if ( CharEncoding = ceUTF8 ) and UTF8StringAsWideField then
Result := stUnicodeString
else
Result := stString;




Pedro Miguel Martins

[EgonHugeist]

pmiguelmartins,

i agree that patch is no solution. I've downloaded the deprecated Alpha version and compared the Resultset-Metadata type detections.

Z7 Alpha (probably wrong! Varbinary(ftBytes) is not available there):

Code: Select all

...
    FIELD_TYPE_VARCHAR:
      Result := stString;
   FIELD_TYPE_VAR_STRING:
      Result := stString;
   FIELD_TYPE_STRING:
      Result := stString;
...

Zeos7-Beta (all necessary types are included):

Code: Select all

...
    FIELD_TYPE_VARCHAR,
    FIELD_TYPE_VAR_STRING,
    FIELD_TYPE_STRING:
      if (FieldFlags and BINARY_FLAG) = 0 then
        if ( CharEncoding = ceUTF8 ) and UTF8StringAsWideField then
          Result := stUnicodeString
        else
          Result := stString
      else
        Result := stBytes;
...

If i understand you right is your result ftBytes (not ftByte like you've reported before) and removing the Binflag check solves this issue.

I personally can't see a bug on Zeos7-Beta side. I'm still wondering that i've allways the expected result: ftString/ftWideString with MySQL 5.5.3.

Is it possible that we've differnt behaviors to expect? Can you check your proposal with a newer server please? Are the (bug)reports to find on MySQL?

I guess our *fixed* code is right. The old one was wrong because of missing VARBINARY support. I must admit i'm not willing to change this again...

[pmiguelmartins]

EgonHugeist,

I will try this weekend with mysql version 5.5.3 and give the results

Thanks again


Pedro Miguel Martins

[EgonHugeist]

pmiguelmartins,

Mark pointed me to this link: http://bugs.mysql.com/bug.php?id=52505

As far as i do understand is it a MySQL bug where the most funtions did return the binary-flag. This behavior has been fixed since 5.5.x. The reason for our differences.

Actually we discuss about a Zeos solution to handle this Non-Zeos bug. Any proposals save what Zeos can do to handle this issue (i personlly see non because of a broken VARBINARY type)? On the other hand i would propose you use your CAS() instead which is save for all MySQL-Versions. If you agree, than it would be fine if you write a comment to your ticked and close this issue.

What do you think?

[pmiguelmartins]

EgonHugeist,

I think you are right this is a MySQL bug ...

So in this case is better to close the ticket and hope that other user don't have this problem


Pedro Miguel Martins

[EgonHugeist]

pmiguelmartins,

a good choise. I personally do not see any issues on Zeos. I'll point all users to this issue if they are running into the same issue. Like i wrote: I've no idea for a common solution which could handle your issue with the MySQL bug and with the FieldType stbytes (binary/varbinary).

Looking forward for closing the ticked..