Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Conversion error when both GROUP/ORDER BY expressions and WHERE expressions contain literals [CORE5743] #6007

Closed
firebird-automations opened this issue Feb 8, 2018 · 12 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Pierre Yager (pierrey)

Is duplicated by CORE5749

Attachments:
core_5743.sql
Core5743.fdb

This query was known to work in Firebird 3.0.2 :

select
2 as TYPE_MVT,
TYPE_REGLEMENT,
DEVISE,
null as BANQUE
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3)) and (ANNULE is false)
and (periode = ?)
group by 1,2,3,4

Now,, in Firebird 3.0.3 I get :

Overflow occurred during data type conversion.
conversion error from string "2".

I have to cast "2" as Integer to make it work again.

select
cast(2 as Integer) as TYPE_MVT,
TYPE_REGLEMENT,
DEVISE,
null as BANQUE
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3)) and (ANNULE is false)
and (periode = ?)
group by 1,2,3,4

Commits: 90c072f c249b62

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I need reproducible test case, with metadata and data of JOURNAL_CAISSE.

@firebird-automations
Copy link
Collaborator Author

Commented by: Pierre Yager (pierrey)

I just extracted the "JOURNAL_CAISSE" table, its domains and several rows of production data from my production database.

The database is declared as this in database.conf :

core_5743 = P:/Databases/Neptis30/Core5743.fdb
{
SecurityDatabase = core_5743
}

Just create the database, run the script and try to prepare/run this query :

select
2 as TYPE_MVT,
TYPE_REGLEMENT,
DEVISE,
null as BANQUE,
sum(QUANTITE) as NOMBRE,
sum(VALEUR) as MONTANT
from JOURNAL_CAISSE
where (TYPE_MOUVEMENT in (2, 3)) and (ANNULE is false)
group by 1,2,3,4

@firebird-automations
Copy link
Collaborator Author

Modified by: Pierre Yager (pierrey)

Attachment: core_5743.sql [ 13207 ]

Attachment: Core5743.fdb [ 13208 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.4 [ 10863 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Query parsing failed => Conversion error when GROUP/ORDER BY expressions contain literals

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Conversion error when GROUP/ORDER BY expressions contain literals => Conversion error when both GROUP/ORDER BY expressions and WHERE expressions contain literals

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE5749 [ CORE5749 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: John Franck (bozzy)

I have a similar case, but mine doesn't involve grouping.

My table:

CREATE TABLE TEST_TABLE
(
TEST_FIELD_C varchar(1) NOT NULL,
TEST_FIELD_N integer,
CONSTRAINT PK_TEST_TABLE PRIMARY KEY (TEST_FIELD_C)
);

And my query:

select
0 as DUMMY_FIELD
from
TEST_TABLE
where
TEST_FIELD_C = 'A'
order by
DUMMY_FIELD

What I get:

Engine Code : 335544334
Engine Message :
conversion error from string "A"

This occurs with a numeric constant field and a condition on a character field.

The same occurs if we swap field types, i.e. character constant field and condition on numeric field:

select
'A' as DUMMY_FIELD
from
TEST_TABLE
where
TEST_FIELD_N = 0
order by
DUMMY_FIELD

Note that if I remove the primary key constraint from the table, then both the queries will work. Also, like the original case, casting the constant to a specified type solves the problem. Removing the ORDER BY clause also again solves the problem.

Please, can someone verify if this behaviour has the same root cause than the original case, and so if it's already solved in 3.0.4?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It's the same. Either GROUP BY or ORDER BY is necessary.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants