sip-router

SIP Router Project

Tasklist

FS#193 - no avpops bigint handling breaks custom select queries

Attached to Project: sip-router
Opened by Walter Doekes (wdoekes) - Monday, 19 December 2011, 10:09 GMT
Last edited by Daniel-Constantin Mierla (miconda) - Thursday, 29 December 2011, 09:18 GMT
Task Type Improvement
Category DB interface → libsrdb1 (kamailio)
Status Assigned
Assigned To Daniel-Constantin Mierla (miconda)
Operating System All
Severity Medium
Priority Normal
Reported Version Development
Due in Version Undecided
Due Date Undecided
Percent Complete 0%
Votes 0
Private No

Details

Hi,

If I do this query:

avp_query(”SELECT 1, id, IFNULL(id, 3) FROM sometable WHERE id = 1”, “$avp(val1),$avp(val2),$avp(val3)”);

I expect these values in val1..val3:
1 1 1

However, I get these values:
<null> 1 <null>

This is because there is not DB1_BIGINT handling in avpops_db.c. Only the ‘id’ fields gets read correctly, because it is of type integer (32bits).

I’m using avpops.so and db_mysql.so on 3.2.x.

Attached patch fixes that it works. But it won’t fix actual bigint handling for numbers larger than 32bit on systems where sizeof(int) < 8.

Regards,
Walter Doekes
OSSO B.V.

This task depends upon

Comment by Daniel-Constantin Mierla (miconda) - Monday, 19 December 2011, 11:24 GMT

SQLOPS module converts bigint to string value, wouldn’t be better to do the same in avpops? The use {s.int} transformation in cfg to get the int value.

Comment by Walter Doekes (wdoekes) - Monday, 19 December 2011, 11:51 GMT

Well.. only if that made sense. But it doesn’t.

See my example:

id ⇒ int
IFNULL(id, 1) ⇒ string ⇒ ugly casts

I haven’t touched any big numbers and suddenly I’m required to handle them through a string. I’m not saying this is kamailios fault – mysql is to blame here (I think) for choosing the largest int possible – but fixing this through a string cast doesn’t make it any more intuitive.

I’m not fond of that solution at all.

Comment by Daniel-Constantin Mierla (miconda) - Monday, 19 December 2011, 12:11 GMT

I see the points in your situation, but for generic case, casting big int to int can result in truncation. So if one wants to do it, better do it on demand in the config (note that it is memory operations, barely affects performances). Many can use it just to add it to headers or print it, which is just fine when kept as string in avp, as opposite to have a truncated value, without any control on it.

For your case casting should do the job, but AFAIK mysql does not support casting from bigint to int, but you can write a small function to do that - take big int as parameter and just return it back as int.

Comment by Daniel-Constantin Mierla (miconda) - Monday, 19 December 2011, 12:23 GMT

Or alternative, modparam for avpops (and eventually sqlops) to control the handling of bigints returned by mysql:

- cast to int
- cast to str

Comment by Walter Doekes (wdoekes) - Monday, 19 December 2011, 12:35 GMT
AFAIK mysql does not support casting from bigint to int

Indeed, when figuring out what was broken, I tried the CAST(.. UNSIGNED / TINYINT) but it all failed. First the cast to CHAR returned something usable.

Or alternative, modparam for avpops (and eventually sqlops) to control the handling of bigints returned by mysql:

I like that option. It introduces a place to document reasons for one or the other. (”Are you using big numbers? Use the string. Are you just affected by mysql automatic type selection in dynamic column values? Choose int.”)

Comment by Walter Doekes (wdoekes) - Monday, 19 December 2011, 12:37 GMT

(Of course you could go with a third option: “auto”, only cast to string if the value doesn’t fit in a 32bits int, but that could be very unintuitive as well.)

Comment by Daniel-Constantin Mierla (miconda) - Thursday, 29 December 2011, 09:18 GMT

I am changing it in feature request, since avps are data structs to handle int and str values.

Comment by Walter Doekes (wdoekes) - Thursday, 29 December 2011, 13:24 GMT
I am changing it in feature request, since avps are data structs to handle int and str values.

I think that’s a bit short sighted. Remember that I don’t want any bigints. They’re imposed on me by the SQL backend and/or driver, without any warning and without non-trivial workarounds. Having to create a mysql function that casts to a smaller int is not something immediately obvious when results are simply returned as <null> instead of an expected integer.

I think it’s a bug until it is transparently handled OR until the user is warned (a) that there is a problem (no bigint handling) and preferably (b) how to solve it.

Regards,
Walter

Comment by Daniel-Constantin Mierla (miconda) - Thursday, 29 December 2011, 14:28 GMT

It is a matter of perspective, the issue is still here in a tracker, marked as an improvement request, so as soon someone will provide a patch or a developer will commit the enhancement, it will be closed, till then will be here.

AVPs are from their beginning dealing with string or int values, someone has to know the underline db system to return such values if they are going to be stored in AVPs – reason I don’t see it as a bug, but enhancement request. Even now, there are so many DB types unsupported internally, as each db system has different types, not all standard.

We cannot go through all possible db data types, for each version of db systems – they change, new appear, a.s.o.

Loading...