Arithmic Error about firebirdex HOT 18 CLOSED

Good news!!
In the case where I had this error before, it is now solved by the update to 0.3.6/0.9.2, so for me this issue can be closed.
Thank you very very much for finding a solution.

from firebirdex.

What is the type of the column in imv.IMV_LESDAGEN?
Also, please give an example of a value of this type.

from firebirdex.

It is an INTEGER field, used as a bit-string.
When no bits are set, the value is 0
When bit 1 is set (00000001) the value = 1
When bit 2 is set(00000010) the value = 2
When bit 3 is set(00000100) the value = 4
and so on...

Basically it just contains integer values, and we use the CASE WHEN expressions to decompose the individual bits.

from firebirdex.

Executing SQL using the isql command gives the following

SQL> select bin_and(Power(2, 0), 1) from rdb$database;
Statement failed, SQLSTATE = 42000
expression evaluation not supported
-Arguments for BIN_AND must be integral types or NUMERIC/DECIMAL without scale
SQL> select bin_and(Cast(Power(2, 0) as integer), 1) from rdb$database;

     BIN_AND
============
           1

I think you need to use CAST.

from firebirdex.

When I execute your experiment in my query tool I get:

I think bin_and is something that works in FirebirdSQL 2.5, but not in >3.0 (I suppose your are on > 3.0)

from firebirdex.

I did some more testing:
The bin_and's are part of a larger query, and when I remove all other fields from the SELECT, it does work.
I then started adding the other fields again one by one, and when adding one of those fields it failed again.
Next, when I did the query with only those 2 fields it works again !!
So, it looks likt it some kind of strange combination of fields that causes the problem. I will try to figure out what exact combination.
The fact remains however that the complete query works in my query tool, but not through Firebirdex.

Can you maybe give me a hint based on the stack trace what the cause of the problem could be? (I already tries CAST on the bin_and expression, but that does not solve the issue)

from firebirdex.

The bug may cause an error if the SQL statement returns many fields, regardless of BIT_AND or string concatenation.
I don't know exactly how many fields is too many.

If I get time later, I will try it with an SQL statement that returns many fields.

from firebirdex.

OK, thanks for looking into it.
For your interest, the query returns 33 fields.

from firebirdex.

I think we are on the right track. When I decrease the query to 32 fields is works.
Is it possible to increase to like 64?

from firebirdex.

I did a little research and it seems that more columns does not always mean NG. I cannot reproduce this.
Is it possible to make the DDL (Create table statement) and select statement that causes the error as small as possible and have it commented here?

The data in the table may be empty, but it seems to be an error

from firebirdex.

This is quite a complex query joining multiple tables and using sub-queries.
Do you want the DDL's for all those tables + the query?

from firebirdex.

Thank you for your instruction.

In my test case, I don't think I need a complex subquery.
I think the problem is the combination of field names and types in the query result set.

For example
cf25829
I added this test case and the test was successful.
( Can you run mix test in your environment? )
I think the cause of this error is the type combination in the result set of the firebirdex.query!() select statement.

The type (and name?) of the create table statement in this test case that matches the error that is occurring? Is there a combination that causes the error?

from firebirdex.

Yesterday, I tried removing different types of fields from the SELECT (the complex CASE WHEN one, a subquery, a varchar, a simple integer) and every time it worked once I was down to 32 fields in the SELECT, and failed when going over 33 fields, regardless of what kind/name of field I removed from the select.

Since we are talking about 33 fields, it is impossible to test every possible combination.

I will try the run your test in my environment later today.
Is there any other information I can provide to help you trace down this issue?

from firebirdex.

Perhaps the problem is not only the number of columns, say 33, but also the types in the result set.

However, I cannot find a pattern that I can reproduce. I wonder if the subquery has something to do with it.

If we can find the combination that causes the error in mix test , I don't think we need any other information.

from firebirdex.

@hermanius

I was able to reproduce and fix a similar problem with efirebirdsql.
nakagami/efirebirdsql@3dc80ba

I have just released efirebirdsql 0.9.2.
Please check this latest version to see if you get the error.

from firebirdex.

Great to see there might be a solution.
Since I'm using Firebirdex could you update Firebirdex to use efirebirdsql 0.9.2. That makes it a lot easier for me to test.

from firebirdex.

I have released version 0.3.6

from firebirdex.

thanks for your help

from firebirdex.