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

String truncation error when selecting from a VIEW with UNION inside [CORE3697] #4045

Closed
firebird-automations opened this issue Dec 14, 2011 · 26 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: JNA (jna)

Is related to QA472

Attachments:
pb_empty.fbk

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.

Commits: 6753377 529e0bc

====== Test Details ======

Confirmed on 2.5.1:
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation

@firebird-automations
Copy link
Collaborator Author

Modified by: JNA (jna)

environment: Версия FB 2.5 Firebird-2.5.0.26074_1_Win32.exe ODS 11.2 BD in UTF-8 cod
OS Win 2003

=>

Версия 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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Firebird-2.5.0.26074_1_Win32 Arithmetic overflow when use VIEW with UNION => String truncation error when selecting from a VIEW with UNION inside

@firebird-automations
Copy link
Collaborator Author

Commented by: JNA (jna)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: JNA (jna)

Attachment: pubob.fbk [ 12067 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: JNA (jna)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Confirmed, thanks.

@firebird-automations
Copy link
Collaborator Author

Commented by: JNA (jna)

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The problem is somehow related to the RELFNAME field:

select RULFNAME from TREE_TEST1
-- ERROR

@firebird-automations
Copy link
Collaborator Author

Commented by: JNA (jna)

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
;

@firebird-automations
Copy link
Collaborator Author

Modified by: JNA (jna)

Attachment: pb_empty.fbk [ 12068 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Attachment: pubob.fbk [ 12067 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: JNA (jna)

tested... OK

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

Version: 3.0 Initial [ 10301 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA472 [ QA472 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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