| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 5070.1 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Tue Feb 25 1997 08:59 | 4 |
| Yes this looks wrong. The data type of the subquery value is VARCHAR(31), but
looks like it got changed to CHAR. Please submit a bug report.
Ian
|
| 5070.2 | <thanks / bug 457273> | 12294::AROELANT | | Tue Feb 25 1997 09:57 | 5 |
| thanks Ian,
Bug nr: 457273
Andre.
|
| 5070.3 | <Intended behaviour> | 12294::AROELANT | | Mon Mar 17 1997 03:04 | 27 |
| Well,
this seems to be intended behaviour, here is the response on the BUG.
Andre.
The reported change in behavior is part of the changes made in conformance
to the ANSI and IOS SQL92 standards. According to the standards, SUBSTRING
takes on the datatype of the first expression specified for the substring,
which is RDB$RELATION_NAME in the case cited. Since RDB$RELATION_NAME has
a datatype of CHAR(31), the SUBSTRING expression will have a datatype of
CHAR(31). Therefore, if you want the substring to be trimmed of blanks, use
CAST for Oracle Rdb V6.0 or you can also use TRIM for Oracle Rdb V6.1 or
later. The following example will generate the results you requested, using
Oracle Rdb V6.0.
/
select
(
select
substring ( cast (RDB$RELATION_NAME as varchar(31)) from 1 for 1)
from RDB$RELATIONS
where RDB$RELATION_NAME <= 'Z'
limit to 1 row
) || '|'
from RDB$RELATIONS
limit to 1 row
;
|
| 5070.4 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Mon Mar 17 1997 10:13 | 6 |
| I helped close the bug. I was thinking of TRIM which returns VARCHAR, but
SUBSTRING returns the data type of the first argument. Apparently a bug was
fixed but not release noted for some reason. This may have been a side
affect of some other cleanup.
Ian
|
| 5070.5 | SUBSTRING datatype inconsistent behaviour ? | ORAREP::KETJE::VANPAMEL | Geert.Van.Pamel@bro | Tue Mar 18 1997 07:36 | 39 |
|
Ian, isn't there an inconsistency in the following two commands ?
If you say SUBSTRING is returning the base datatype, why is it
still returning a CHAR (1) in the 2nd RSE ?
SQL> select
cont> (select substring (RDB$RELATION_NAME from 1 for 1)
cont> from RDB$RELATIONS
cont> limit to 1 row)
cont> || '|'
cont> from RDB$RELATIONS
cont> limit to 1 row
cont> ;
F |
1 row selected
SQL> select
cont> substring (RDB$RELATION_NAME from 1 for 1) || '|'
cont> from RDB$RELATIONS
cont> limit to 1 row;
F|
1 row selected
SQL> show version
Current version of SQL is: DEC SQL V6.1-04
Underlying versions are:
Database with filename SQL$DATABASE
DEC Rdb V6.1-04
Rdb/Dispatch V6.1-04 (OpenVMS VAX)
(I am the original call logger for this problem)
Geert.
Oracle Internal Technical Support, Digital Europe
|
| 5070.6 | | NOVA::SMITHI | Don't understate or underestimate Rdb! | Tue Mar 18 1997 10:01 | 17 |
| If you look at the displayed output you will see that the result is CHAR(32)
i.e. SQL reserves space for 32 characters.
SQL> select substring(rdb$relation_name from 1 for 1) || '|' as "Name",
cont> rdb$relation_id as "Id"
cont> from rdb$relations limit to 1 row;
Name Id
R| 4
1 row selected
In the BLR you will also see that the result data type for the expression is
also 32 characters.
000A (00010) | | DSC$K_DTYPE_CHAR 32 (sub-type: 0)
The difference is that a subquery returns a result before being concatenated.
Ian
|