Issue Details (XML | Word | Printable)

Key: CORE-1346
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Robert Lakis
Votes: 0
Watchers: 0
Operations

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

lpad and rpad with two columns not working

Created: 05/Jul/07 11:01 AM   Updated: 20/Apr/11 09:49 AM
Component/s: None
Affects Version/s: 2.1 Beta 1, 2.1.0, 2.1.1, 2.1.2, 2.1.3
Fix Version/s: 2.5 Beta 2, 2.1.4

Time Tracking:
Not Specified

Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
Check this:
select lpad('xxx', 8, '0') one, lpad('yyy', 8, '0') two from rdb$database

gives:
can't format message 13:796 -- message system code -4.
Dynamic SQL Error.
SQL error code = -204.
Implementation limit exceeded.
block size exceeds implementation restriction.


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 05/Jul/07 11:40 AM
Unfortunatelly, this is as designed!
We don't support return strings of variable length, it needs a maximum size.

When you use two LPAD, another FB limit is reached.

Robert Lakis added a comment - 05/Jul/07 11:51 AM
this example is working:

select lpad('123', 8, '0') || ' string ' || lpad('1240', 8, '0') col from rdb$database

Adriano dos Santos Fernandes added a comment - 05/Jul/07 11:53 AM
In theory, we can know the size, when it's constant.
But actually, this violates complete the layering, and is not supported with current code.

Workaround is to cast the result to a varchar of know size:
select cast(lpad('xxx', 8, '0') as varchar(8)) one, cast(lpad('yyy', 8, '0') as varchar(8)) two from rdb$database;

Dmitry Yemanov added a comment - 16/Jul/07 09:49 AM
Adriano, should we consider SUBSTRING as also violating the layering? :-)

Adriano dos Santos Fernandes added a comment - 16/Jul/07 10:48 AM
Here is what I've replied to Claudio:

> Hello, I read your explanation here
> http://tracker.firebirdsql.org/browse/CORE-1346
> In theory, we can know the size, when it's constant.
> But actually, this violates complete the layering, and is not supported with
> current code.
>
> Won't substring violate the layering, too? It works with the size of the
> field. Initially, I made it to adjust the result to the length parameter,
> but this was against the standard.
And I supposed it was removed?

> However, I don't remember any technical
> problem to make it work with the field and the constant length. The problem
> was simply the logic was not in the SQL specification.
When things go to DataTypeUtil and SysFunction, we don't have access to the constant.
My idea when creating these files was to make code compatible with DSQL and JRD.

But the problem with PAD is that it can return strings with greater length than the original one, different from SUBSTRING.

Adriano dos Santos Fernandes added a comment - 02/Sep/09 05:59 AM
Backported to 2.1.4.

Pavel Cisar added a comment - 20/Apr/11 09:49 AM
QA test added.