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: 28/May/15 02:29 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

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
 

QA Status: Done successfully
Test Details:
Confirmed on 2.5.1:
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation


 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   Change History   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