[soci-users] MySQL Backend "bad_cast" with UNSIGNED_FLAG on mysql long long types

[Daniel Walter]

Hi there,

I am experiencing some annoying problems with type casting in mysql backend.
In my non trivial SQL query I am doing casts for generated columns to
avoid problems with the type the server sends for each column.

,CAST(COALESCE((p2e.inserted IS NOT NULL),0) AS UNSIGNED) AS 'exists'
,CAST(COALESCE(p2e.active,0) AS UNSIGNED) AS 'active'
,CAST(UNIX_TIMESTAMP(COALESCE(p2e.last_full_update,0)) AS UNSIGNED)
AS 'last_full_update'
,CAST(0 AS UNSIGNED) AS already_ordered

The problem is, that these "CAST" columns do not getting the same type
from server! For example field "exists" and "active" are of type
MYSQL_TYPE_LONG (unsigned flag set) and "already_ordered" and
"last_full_update" are of type MYSQL_TYPE_LONGLONG (unsigned flag set).
So I cant rely on the resulting type of the "CAST" call.

Here are the lines with the problem from statement.cpp in mysql backend:

case FIELD_TYPE_LONG: //MYSQL_TYPE_LONG:
type = field->flags & UNSIGNED_FLAG ? dt_long_long
: dt_integer;
break;
case FIELD_TYPE_LONGLONG: //MYSQL_TYPE_LONGLONG:
type = field->flags & UNSIGNED_FLAG ? dt_unsigned_long_long :
dt_long_long;
break;


The problem shows up if i try to use such a field in my orm mapping. At
the beginning I used:

p.exists = v.get<long long>("exists", 0);

Which is at least logically wrong. Because i casted the column to
"UNSIGNED" and the mapping from the MySQL backend returned an signed
type? But, not matter, this code was working right... until the type
changed to MYSQL_TYPE_LONGLONG. The maped type was than "unsigned long
long" and I got the "bad_cast" error.

[Pawel Aleksander Fedorynski]

Hi,

The problem as I see it is not really with a specific mapping of some
unsigned types to signed types, but with the fact that v.get<...>() throws
when the types don't match exactly, instead of trying to put the value of
the requested field into whatever type the user wants it in (and only
throwing when that's not possible.) The general understanding was that
soci should be changed, I think Mateusz (cc-ed) started writing a design
doc about it.

As long as we keep the current behavior, i.e., force the users to use
specific integral types, I personally think the current mapping is very
sane. The most natural default integral type in C++ is int so it's fairly
obvious that all smaller types, signed or not, should be interpreted as
int. And for unsigned long long there is no choice either because no other
type is large enough to fit all values from unsigned long long. So the
only non-obvious choice was to convert unsigned long to dt_long_long, which
can be justified by consistency with all smaller unsigned types which are
interpreted as a signed value of a larger type. Even if that decision were
concluded wrong (which I don't think it was) it would be hard to change now
since user code may depend on it (if we change the type some user code may
start to throw.)

Thanks,

Aleksander


On Mon, Jul 22, 2013 at 1:38 PM, Daniel Walter <

Post by Daniel Walter
Hi there,
I am experiencing some annoying problems with type casting in mysql backend.
In my non trivial SQL query I am doing casts for generated columns to
avoid problems with the type the server sends for each column.
,CAST(COALESCE((p2e.inserted IS NOT NULL),0) AS UNSIGNED) AS 'exists'
,CAST(COALESCE(p2e.active,0) AS UNSIGNED) AS 'active'
,CAST(UNIX_TIMESTAMP(COALESCE(p2e.last_full_update,0)) AS UNSIGNED)
AS 'last_full_update'
,CAST(0 AS UNSIGNED) AS already_ordered
The problem is, that these "CAST" columns do not getting the same type
from server! For example field "exists" and "active" are of type
MYSQL_TYPE_LONG (unsigned flag set) and "already_ordered" and
"last_full_update" are of type MYSQL_TYPE_LONGLONG (unsigned flag set).
So I cant rely on the resulting type of the "CAST" call.
type = field->flags & UNSIGNED_FLAG ? dt_long_long
: dt_integer;
break;
dt_long_long;
break;
The problem shows up if i try to use such a field in my orm mapping. At
p.exists = v.get<long long>("exists", 0);
Which is at least logically wrong. Because i casted the column to
"UNSIGNED" and the mapping from the MySQL backend returned an signed
type? But, not matter, this code was working right... until the type
changed to MYSQL_TYPE_LONGLONG. The maped type was than "unsigned long
long" and I got the "bad_cast" error.

[Mateusz Loskot]

Post by Pawel Aleksander Fedorynski
The problem as I see it is not really with a specific mapping of some
unsigned types to signed types, but with the fact that v.get<...>() throws
when the types don't match exactly, instead of trying to put the value of
the requested field into whatever type the user wants it in (and only
throwing when that's not possible.) The general understanding was that soci
should be changed, I think Mateusz (cc-ed) started writing a design doc
about it.

Generally, I agree with Aleksander.
Unfortunately, I haven't managed to move forward with improvements of
integer types support, I'm too busy at the moment.

My idea was simple to document the C++<->SQL backend mapping first,
in order to cover most/all C-language C99 (stdint,h) and C++11 integer types.
Then, discuss changes that would be required - ideally if all from/to
transformations
go through common layer in Core.f

Here is table I aimed to fill first:
https://github.com/SOCI/soci/wiki/RFC1

Anyone, feel free to start filling this table so we can discuss the
exact mappings.
FYI, it is planned for SOCI 4.0.0 - no date set.

Best regards,