[#CORE-3697] String truncation error when selecting from a VIEW with UNION inside

Firebird Core

String truncation error when selecting from a VIEW with UNION inside

Created: 14/Dec/11 02:56 AM Updated: 14/Mar/12 09:50 AM

Component/s:

Engine

Affects Version/s:

2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 2.0.6, 2.5.0, 2.1.4, 2.5.1

Fix Version/s:

2.1.5, 2.5.2

Time Tracking:

Not Specified

File Attachments:	1. pb_empty.fbk (69 kB)
Environment:	« Hide Версия FB 2.5 Firebird-2.5.0.26074_1_Win32.exe ODS 11.2 BD in UTF-8 cod OS Win 2003 In Last Version 2.5.1 I have same error Show » Версия FB 2.5 Firebird-2.5.0.26074_1_Win32.exe ODS 11.2 BD in UTF-8 cod OS Win 2003 In Last Version 2.5.1 I have same error

Planning Status:

Unspecified

Description

« Hide

First
create view TREE_TEST (
    ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME )
as
select uidorg , nameorg
, '0' , ' G.UIDORG = ' || '''' || uidorg || ''''
, '' , 'KODORG'
from org_delivery;

select ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME
from TREE_TEST - All result OK

Second. And now i add UNION clause
create view TREE_TEST (
    ID, TEXT, PARENTID, CONDITIONS,
    STMT, RULFNAME )
as
select 0, 'Организации', NULL, '', ''
, 'KODORG'
from rdb$database
union
select uidorg
, nameorg
, '0'
, ' G.UIDORG = ' || '''' || uidorg || ''''
, ''
, 'KODORG'
from org_delivery
select ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME
from TREE_TEST
ERROR
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 04:16 AM

What was the connection charset? Please also post a DDL for the ORG_DELIVERY table.

[ Permalink | « Hide ]

JNA added a comment - 14/Dec/11 05:29 AM

All text fileds as VARCHAR(nnn) CHARACTER SET UTF8
in a domain
CREATE DOMAIN VC254 AS
VARCHAR(254) CHARACTER SET UTF8
COLLATE UTF8;

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 05:39 AM

Once more, what was the *connection* charset? What is the data type of UIDORG? It would be much better if you could post a reproducible test case or attach the database to this ticket.

[ Permalink | « Hide ]

JNA added a comment - 14/Dec/11 06:03 AM

I'm attach backup file
in this database
view TREE_TEST working ok. (I'm add in this view another union)
view TREE_TEST1 not working

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 06:19 AM

Confirmed, thanks.

[ Permalink | « Hide ]

JNA added a comment - 14/Dec/11 06:20 AM

Oh, sorry, I'm forgot.
The query like this (without one field):
select ID, TEXT, PARENTID, CONDITIONS, STMT from TREE_TEST1
working properly
but like this:
select ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME
from TREE_TEST1
not working

And this sample working OK
1.
CREATE TABLE TREE_HEADER (
    ID INTEGER NOT NULL,
    TEXT VC254 /* VC254 = VARCHAR(254) */,
    PARENTID INTEGER,
    CONDITIONS VC254 /* VC254 = VARCHAR(254) */,
    STMT VC254 /* VC254 = VARCHAR(254) */,
    RULFNAME VC30 /* VC30 = VARCHAR(30) */
);
2. INSERT INTO TREE_HEADER (ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME) VALUES (1, 'Организации', 0, NULL, NULL, NULL);

3. And changing view TREE_TEST, add union

select ID, TEXT, PARENTID, CONDITIONS, STMT, RULFNAME
from TREE_HEADER
where id = 1
union
select 0, 'Организации', NULL, '', '' , 'KODORG' from org_delivery
union
select uidorg , nameorg , '0' , ' G.UIDORG = ' || '''' || uidorg || '''' , '' , 'KODORG'
from org_delivery

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 06:29 AM

The problem is somehow related to the RELFNAME field:

select RULFNAME from TREE_TEST1
-- ERROR

[ Permalink | « Hide ]

JNA added a comment - 14/Dec/11 06:57 AM - edited

Agree, this work properly
When explicit cast to same type and length
CREATE OR ALTER VIEW TREE_TEST1(
    ID,
    TEXT,
    PARENTID,
    CONDITIONS,
    STMT,
    RULFNAME)
AS
select 0, 'Организации', NULL, '', ''
, cast('KODORG' as char(6))
from org_delivery
union
select uidorg
, nameorg
, '0'
, ' G.UIDORG = ' || '''' || uidorg || ''''
, ''
, cast('KODORG' as char(6))
from org_delivery
;

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 12:42 PM

The problem appears only if the view has been created in the multi-byte connection charset (UTF8 in this case).

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 14/Dec/11 04:09 PM

Please test the next (tomorrow's) snapshot build, it should have the issue fixed.

[ Permalink | « Hide ]

JNA added a comment - 15/Dec/11 07:02 AM - edited

tested... OK