Hi All,



I'm on 9.2.0 and I've just confirmed the following...

select floor(-1.9), floor(1.9) ...which returns -1, 1 not the expected
-2, 1.

select ceil(-1.9), ceil(1.9) ... which returns -1, 1, not the expected
-1, 2.





Martin Bowes

Yes, I have similar strange results - tested with II 9.2.0
(int.w32/143):
select floor(-1.8), floor(-1.85) ... returns -2, 0
rather than -2, -2


--
Bodo

On Mar 30, 2010, at 9:18 AM, Ingres Forums wrote:

Try 'em separately:
select floor(-1.8)
select floor(-1.85)

Also, floor(nn.00) isn't returning the right answer,
nor is ceil(nn.00). Fixes being tested now, but they
may take a while to propagate to release code lines.

Karl

Karl Schendel;30490 Wrote:
-2 for the first query - correct, 0 for the second one - wrong


--
Bodo

I have tried this on the following:

II 9.2.1 (int.w32/103) patch: 13686

from: select floor(-1.8), floor(-1.85) I get --> returns -2, -2


John Smedley


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Ingres Forums
Sent: 30 March 2010 15:32
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] floor() and ceil()...wrong, wrong, wrong!


Karl Schendel;30490 Wrote:
-2 for the first query - correct, 0 for the second one - wrong


--
Bodo
------------------------------------------------------------------------
Bodo's Profile: http://community.ingres.com/forum/member.php?userid=16
View this thread:
http://community.ingres.com/forum/sh...ad.php?t=11816

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi Guys,

Sussed it, Try this...
ingunset II_NUMERIC_LITERAL
unsetenv II_NUMERIC_LITERAL
sql iidbdb << SQL_END
-- should be 0.00, 2.00, 2.00 NOT 0, 1, 1
select round(1.99,-1) as a, round(1.99, 0) as b, round(1.99,1) as c;
\p\g
SQL_END

setenv II_NUMERIC_LITERAL float

sql iidbdb << SQL_END
-- should be 0.00, 2.00, 2.00 NOT 0, 1, 1
select round(1.99,-1) as a, round(1.99, 0) as b, round(1.99,1) as c;
\p\g
SQL_END

unsetenv II_NUMERIC_LITERAL

Marty


-----Original Message-----
From: John Smedley [mailto:John.Smedley (AT) ingres (DOT) com]
Sent: 30 March 2010 16:44
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] floor() and ceil()...wrong, wrong, wrong!

I have tried this on the following:

II 9.2.1 (int.w32/103) patch: 13686

from: select floor(-1.8), floor(-1.85) I get --> returns -2, -2


John Smedley


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Ingres Forums
Sent: 30 March 2010 15:32
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] floor() and ceil()...wrong, wrong, wrong!


Karl Schendel;30490 Wrote:
-2 for the first query - correct, 0 for the second one - wrong


--
Bodo
------------------------------------------------------------------------
Bodo's Profile: http://community.ingres.com/forum/member.php?userid=16
View this thread:
http://community.ingres.com/forum/sh...ad.php?t=11816

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

FYI. The problem was that I had II_NUMERIC_LITERAL set to float.

ergo...
select trunc(1.99,-1), trunc(1.99, 0), trunc(1.99,1);
Is equivalent to doing...
select trunc(float4(1.99),-1), trunc(float4(1.99), 0),
trunc(float4(1.99),1);

The 4 functions only provide a decimal output so they have to convert to
a decimal before the function runs. The problem with this is that the
default conversion of float to decimal is decimal(15,0). So you get a
truncated value (1) to pass through the trunc() function...no wonder the
answer seems incorrect.

Try: select ii_dv_desc(decimal(float4(1.99))), decimal(float4(1.99));

However, the more I think about this the more I think the default rule
to convert a float to decimal(15,0) just sucks.

Its a float! Why turn it into a quasi integer?

Why wouldn't we set a default conversion of say decimal(15,10)?
Or something with a default scale > 0.

How about an II_DECIMAL_DEFAULT_SCALE variable which defaults to zero,
but could be set to any integer value in range [0..15].

Marty

Hi Martin,

that's just the fun thing about computers: they do what you tell them to
do, but not always what you want them to do :-)

Have a sunny spring day,

Jürgen



-------- Original-Nachricht --------
Betreff: Re: [Info-Ingres] floor() and ceil()...wrong, wrong, wrong!
Datum: Thu, 8 Apr 2010 13:21:18 +0100
Von: Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>
Antwort an: Ingres and related product discussion forum
<info-ingres (AT) kettleriverconsulting (DOT) com>
An: Ingres and related product discussion forum
<info-ingres (AT) kettleriverconsulting (DOT) com>
CC: John Smedley <John.Smedley (AT) ingres (DOT) com>



FYI. The problem was that I had II_NUMERIC_LITERAL set to float.

ergo...
select trunc(1.99,-1), trunc(1.99, 0), trunc(1.99,1);
Is equivalent to doing...
select trunc(float4(1.99),-1), trunc(float4(1.99), 0),
trunc(float4(1.99),1);

The 4 functions only provide a decimal output so they have to convert to
a decimal before the function runs. The problem with this is that the
default conversion of float to decimal is decimal(15,0). So you get a
truncated value (1) to pass through the trunc() function...no wonder the
answer seems incorrect.

Try: select ii_dv_desc(decimal(float4(1.99))), decimal(float4(1.99));

However, the more I think about this the more I think the default rule
to convert a float to decimal(15,0) just sucks.

Its a float! Why turn it into a quasi integer?

Why wouldn't we set a default conversion of say decimal(15,10)?
Or something with a default scale> 0.

How about an II_DECIMAL_DEFAULT_SCALE variable which defaults to zero,
but could be set to any integer value in range [0..15].

Marty


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

CJ Date is doing a free webcast for O'Reilly - might interest some here..

http://www.oreillynet.com/pub/e/1598

Paul