Tim

I think this is a bug, but could be WAD.

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref,

returns a column 9 wide (well length(CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) shows 9) but I'm only shown 1 character (eg 1)

If I use CAST(C._ID AS VARCHAR(6))+'/'+CAST(Site._SiteID AS VARCHAR(6)) I get the right result (eg 1000001/0) and length(CAST(C._ID AS VARCHAR(6))+'/'+CAST(Site._SiteID AS VARCHAR(6)) shows 9


Both C._ID and Site._SiteID are defined as integer.

Messing around further and eyeballing the result what I think is happening is ElevateDB is adding the parameters for the VARCHARs and 1 for the constant and "trimming" the catenated result to fit. If I reduce the parameters as follows

CAST(C._ID AS VARCHAR(4))+'/'+CAST(Site._SiteID AS VARCHAR(4))

I still get the full result of 1000001/0

I don't know if this is a special case but I definitely think its wrong. My view is if a parameter is there it applies within the CAST and if no parameter is there it should catenate the fields and work out the length from the result, a bit like it does if you join two VARCHARs together.

Roy Lambert

Roy,

<< I think this is a bug, but could be WAD.

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, >>

If you're running this in a query, then you need to make sure to specify the
size of the VARCHARs in the CAST expressions.   Not doing so will result in
EDB just using 0 for the size of the resultant CAST expression.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< I think this is a bug, but could be WAD.
>
> CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref, >>
>
>If you're running this in a query, then you need to make sure to specify the
>size of the VARCHARs in the CAST expressions. Not doing so will result in
>EDB just using 0 for the size of the resultant CAST expression.

Not quite. You just need to specify ANY combined length that is equal to or greater than the size minus 1 (for the /)  of the output for the expression as a whole. eg

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR(14)) AS Ref

works fine. However, so does

CAST(C._ID AS VARCHAR) as ID

So individual elements work fine with no parameter its just in an expression

Another example

CAST(C._ID AS VARCHAR)+CAST(Site._SiteID AS VARCHAR(2)) AS Ref ,

determines the size to use from CAST(Site._SiteID AS VARCHAR(2))

Roy Lambert

Roy,

<< Not quite. >>

Umm, I'm looking at the code right now, and it most definitely uses 0 as the
length when you don't specify it in the VARCHAR data type.

<< You just need to specify ANY combined length that is equal to or greater
than the size minus 1 (for the /) of the output for the expression as a
whole. eg >>

And, how exactly does that make what I said incorrect ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Umm, I'm looking at the code right now, and it most definitely uses 0 as the
>length when you don't specify it in the VARCHAR data type.
>
><< You just need to specify ANY combined length that is equal to or greater
>than the size minus 1 (for the /) of the output for the expression as a
>whole. eg >>
>
>And, how exactly does that make what I said incorrect ?

Because....

<<Not doing so will result in
EDB just using 0 for the size of the resultant CAST expression.>>

If your statement above is correct then

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref,

would have returned / only. What EDB is doing is using the aggregate sum of any parameters for any CAST AS VARCHAR expressions, adding in the length of any constants (call this MaxSpecified), formatting the integers (in this case) somehow, catenating the resultant expressions and returning the up to MaxSpecified characters in a column MaxSpecified wide.

So your assertion is correct only if you consider that there is only ONE CAST expression in

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref,

and I count TWO

Roy Lambert

Roy,

<< If your statement above is correct then

CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref,

would have returned / only. >>

No.  Again, what I said was:

Not doing so will result in EDB just using 0 for the size of the resultant
CAST expression.

Notice I said CAST *expression*, not *expressions* (plural).  Each CAST
expression is going to have a length of 0.  Thus the resultant expression
length will be 1 (0+1+0), and that is what EDB will use for the length of
the column in the result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< If your statement above is correct then
>
> CAST(C._ID AS VARCHAR)+'/'+CAST(Site._SiteID AS VARCHAR) AS Ref,
>
> would have returned / only. >>
>
>No. Again, what I said was:
>
>Not doing so will result in EDB just using 0 for the size of the resultant
>CAST expression.
>
>Notice I said CAST *expression*, not *expressions* (plural). Each CAST
>expression is going to have a length of 0. Thus the resultant expression
>length will be 1 (0+1+0), and that is what EDB will use for the length of
>the column in the result set.

This may just be a US vs UK interpretation but

<<Not doing so will result in EDB just using 0 for the size of the resultant CAST expression.>>

in my view, and using the above expression and assuming C._ID is 1000012, Site._SiteID is 0 would result in

CAST(C._ID AS VARCHAR) produces zero length string
CAST(Site._SiteID AS VARCHAR) produces zero length string

so

zero length string + / + zero length string

NOT

1

What it seems to be doing as I said (and it may just be a language thing) is

<<What EDB is doing is using the aggregate sum of any parameters for any CAST AS VARCHAR expressions, adding in the length of any constants (call this MaxSpecified), formatting the integers (in this case) somehow, catenating the resultant expressions and returning the up to MaxSpecified characters in a column MaxSpecified wide.>>

This seems to be what your example is suggesting.

Also consider this one

create table xxx as
select cast(_id as varchar) from companies  
with no data

produces a table like

CREATE TABLE "xxx"
(
"Expression" VARCHAR(60) COLLATE "ANSI",

)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Is this a special case? I have no idea where the 60 comes from.

Roy Lambert

Roy,

<< CAST(C._ID AS VARCHAR) produces zero length string
CAST(Site._SiteID AS VARCHAR) produces zero length string

so

zero length string + / + zero length string

NOT

1 >>

1 is the resulting length of the string, and 0+1+1 is the breakdown of where
that length came from.  They're the same thing.

I'm really spending way too much time on this, so I'm going to just leave
you with what I originally said: if you specify the length for the VARCHAR,
then you won't have a problem.  In fact, I'm going to make sure that not
doing so produces an error in the future, just so I don't have to have this
conversation again.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'm really spending way too much time on this, so I'm going to just leave
>you with what I originally said: if you specify the length for the VARCHAR,
>then you won't have a problem. In fact, I'm going to make sure that not
>doing so produces an error in the future, just so I don't have to have this
>conversation again.

Having an error is preferable to how it currently works.

.Are you also going to adjust it so that each CAST respects its own parameters or is it still going to be an aggregate for the compound expression?.

Roy Lambert

Roy,

<< Having an error is preferable to how it currently works. >>

How it works now is perfectly reasonable, but you aren't understanding it
for some reason.

<< Are you also going to adjust it so that each CAST respects its own
parameters or is it still going to be an aggregate for the compound
expression?. >>

For the last time:

The CAST function is not doing anything in aggregate with respect to the
lengths of the VARCHARs.  They couldn't even if they wanted to - they have
absolutely zero knowledge of one another.

The result set of a query is determined *before* the query is executed.
Thus, EDB has *no idea* what the actual data will look like when the query
is executed.  It only uses the compiled version of the query's SELECT column
expressions to determine what the lengths of the result set columns should
be.  Therefore, when it looks at your expression it sees:

0 + 1 + 0

It then sets the result column length to 1.  Now, when the query is
executed, any data that is longer than 1 character will get truncated.

All you need to do is specify a length, say 10, and then EDB will then see:

10 + 1 + 10

and then set aside sufficient space in the result set column for the
expression.  That's it, simple as pie.

--
Tim Young
Elevate Software
www.elevatesoft.com