Tim

><< 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.

I believe I am. I've posted a screen shot to the binaries illustrating what I think is a problem. You don't see it as such, and its possible that either of both of us is misunderstanding the other.

To reiterate if I cast an integer as a varchar, and I supply a parameter I would expect the integer to be truncated eg

CAST(1000023 AS VARCHAR(4)) should return 1000

If I then catenate that to a constant (eg A) I would expect the result to be 1000A

If I then extended it to

CAST(1000023 AS VARCHAR(4)) +'A'+  CAST(1000023 AS VARCHAR(4))

I would expect 1000A1000

In fact I get  1000023A1

I also get the same result if I use

CAST(1000023 AS VARCHAR) +'A'+  CAST(1000023 AS VARCHAR(8))

I cannot believe this is correct. Is it?

Nor can I believe that

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

where _id is an integer field should create a 60 wide column. Admittedly I don't know what width it should use but that's another discussion.

Roy Lambert

Tim


Whilst making a cuppa I thought "I wonder how CHAR behaves".

You might want to try this

select
CAST(1000023 AS CHAR) +'A'+  CAST(1000023 AS CHAR(8)),
CAST(1000023 AS CHAR(0)) +'A'+  CAST(1000023 AS CHAR(8)),
CAST(1000023 AS CHAR(0)),
CAST(1000023 AS CHAR)
from companies

Substituting any table of your own. I would describe the result as "interesting"

Roy Lambert

ps

I accept that CAST AS CHAR(0) is not something I'm ever liable to want to do in real life

Roy,

<< If I then extended it to

CAST(1000023 AS VARCHAR(4)) +'A'+ CAST(1000023 AS VARCHAR(4))

I would expect 1000A1000

In fact I get 1000023A1 >>

Okay, now I see what you're having an issue with.  It's the same reason that
you can use VARCHAR without a length, i.e. the CAST is not truncating
values, only using the length as an indicator of the expected length of the
expression so that the result set columns are defined properly.  I'm not
sure if this is something that I want to change in a build, however, because
it will break any existing application that relies on it doing what it does
now, i.e. no truncation.  I might have to wait until 2.04 for such a
breaking change.

<< where _id is an integer field should create a 60 wide column. Admittedly
I don't know what width it should use but that's another discussion. >>

If the length of a SELECT expression is 0, then EDB uses a default length of
60.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Eureka we have achieved communication

>Okay, now I see what you're having an issue with. It's the same reason that
>you can use VARCHAR without a length, i.e. the CAST is not truncating
>values, only using the length as an indicator of the expected length of the
>expression so that the result set columns are defined properly. I'm not
>sure if this is something that I want to change in a build, however, because
>it will break any existing application that relies on it doing what it does
>now, i.e. no truncation. I might have to wait until 2.04 for such a
>breaking change.

This is why I kept on referring to aggregating the parameters. It could even be left as is providing that its covered adequately in the manual, preferably with a few worked examples so that it won't come as a surprise to those who RTFM (ie about 0.1% of your customer base

><< where _id is an integer field should create a 60 wide column. Admittedly
>I don't know what width it should use but that's another discussion. >>
>
>If the length of a SELECT expression is 0, then EDB uses a default length of
>60.

Well its a number, and as I said "that's another discussion"

Out of interest what do you think of the CHAR output?

Roy Lambert

Roy,

<< Out of interest what do you think of the CHAR output? >>

I didn't really even look at it - the rules are the same for both CHAR and
VARCHAR, and the only difference is the padding that comes with CHAR
columns.  Changing one will change the other accordingly.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I didn't really even look at it - the rules are the same for both CHAR and
>VARCHAR, and the only difference is the padding that comes with CHAR
>columns. Changing one will change the other accordingly.

You may be right, but program code is sneaky - have a look at the new jpg in the binaries.

Roy Lambert

Roy,

<< You may be right, but program code is sneaky - have a look at the new jpg
in the binaries. >>

Please, be more specific and stop making me guess.  I don't have a lot of
time on my hands right now.  What exactly do you see that you think isn't
correct ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Please, be more specific and stop making me guess. I don't have a lot of
>time on my hands right now. What exactly do you see that you think isn't
>correct ?

I spent a lot of time trying to be more specific with the last related issue, you spent a lot of time refuting my comments, until I finally stumbled across a form of words that you understood.

Please look at the jpg. If after 15 seconds you don't see what I'm talking about I'll happily spend as much time as is needed.

Roy Lambert

Roy,

<< Please look at the jpg. If after 15 seconds you don't see what I'm
talking about I'll happily spend as much time as is needed. >>

No thanks, I'm tired of playing games.  If you won't specify what the
problem is, then I can't help you.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Please look at the jpg. If after 15 seconds you don't see what I'm
>talking about I'll happily spend as much time as is needed. >>
>
>No thanks, I'm tired of playing games. If you won't specify what the
>problem is, then I can't help you.

I resent being told I'm playing games.

I put in a tremendous amount of effort trying to get the message across to you on the behaviour of CAST and VARCHAR. Was that a game?

I invest more time (not much this time fortunately) to produce a query which clearly shows a difference in behaviour with CAST between VARCHAR and CHAR which you say is the same but which the jpg I posted shows isn't only to be told I'm playing games. Where is your evidence for this?

The differences may be due to padding as you say. I don't have the code so I don't know. That's why I produced a query showing the results and posted a jpg to the binaries to show it. The idea was to save both you and me time, not to play games.

Roy Lambert