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-issue-importer opened this issue Dec 14, 2011 · 26 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: JNA (jna)

Attachment: pubob.fbk [ 12067 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Commented by: @dyemanov

Confirmed, thanks.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Commented by: @dyemanov

The problem is somehow related to the RELFNAME field:

select RULFNAME from TREE_TEST1
-- ERROR

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: JNA (jna)

Attachment: pb_empty.fbk [ 12068 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: @dyemanov

Attachment: pubob.fbk [ 12067 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 14, 2011

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 15, 2011

Commented by: JNA (jna)

tested... OK

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

Fix Version: 2.1.5 [ 10420 ]

Fix Version: 2.5.2 [ 10450 ]

Version: 3.0 Initial [ 10301 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 14, 2012

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Mar 27, 2014

Modified by: @pcisar

Link: This issue is related to QA472 [ QA472 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 28, 2015

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 25, 2015

Commented by: @pcisar

Test created.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 25, 2015

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