Issue Details (XML | Word | Printable)

Key: CORE-3697
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: JNA
Votes: 0
Watchers: 0
Operations

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

String truncation error when selecting from a VIEW with UNION inside

Created: 14/Dec/11 02:56 AM   Updated: 27/Mar/14 12:22 PM
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. File pb_empty.fbk (69 kB)

Environment:
Версия 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
Issue Links:
Relate
 

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: Ascending order - Click to sort in descending order
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.

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;




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.

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

Dmitry Yemanov added a comment - 14/Dec/11 06:19 AM
Confirmed, thanks.

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


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

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
;

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).

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.

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