Issue Details (XML | Word | Printable)

Key: CORE-5743
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pierre Yager
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Conversion error when both GROUP/ORDER BY expressions and WHERE expressions contain literals

Created: 08/Feb/18 04:55 PM   Updated: 23/Apr/18 05:00 PM
Component/s: Engine
Affects Version/s: 3.0.3
Fix Version/s: 3.0.4

File Attachments: 1. File Core5743.fdb (1.75 MB)
2. File core_5743.sql (19 kB)

Environment: Windows 7, Delphi using UIB and Firebird Client DLL (reproducible within IBExpert)
Issue Links:
Duplicate
 

QA Status: Done successfully


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 08/Feb/18 11:15 PM
I need reproducible test case, with metadata and data of JOURNAL_CAISSE.

Pierre Yager added a comment - 09/Feb/18 10:37 AM
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

John Franck added a comment - 23/Apr/18 04:28 PM
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?

Dmitry Yemanov added a comment - 23/Apr/18 05:00 PM
It's the same. Either GROUP BY or ORDER BY is necessary.