Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement

Allan Oepping (allan(at)pacificwebworks(dot)com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement

Long Description
Return datatype of the floor function is different between PostgreSQL 7.2 and PostgreSQL 7.3 the documentation for PostgreSQL 7.3 states that PostgreSQL 7.3's floor function should return type numeric. In 7.3 it seems to be returning the double precision datatype. Oracle 8.0.5 correctly executes the test statments along with PostgreSQL 7.2. (we have to be compatible)

The documentation mentioned:
http://www.postgresql.org/docs/view.php?version=7.3&file=functions-math.html

To duplicate:

****************************
create table floor_test
(
test bigint not null
);

insert into floor_test (test) values (8);
insert into floor_test (test) values (12);
insert into floor_test (test) values (136);

select * from floor_test where mod(floor(test / 128),2)=1;
select * from floor_test where mod(trunc(test / 128,0),2)=1;
select * from floor_test where mod(floor(test / 4),2)=1;
select * from floor_test where mod(trunc(test / 4,0),2)=1;

SELECT version();

delete from floor_test;
drop table floor_test;
****************************

behavior(correct) in 7.2.3(using the psql client):
Linux version 2.4.18-17.8.0smp (bhcompile(at)daffy(dot)perf(dot)redhat(dot)com) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) #1 SMP Tue Oct 8 12:39:01 EDT 2002

internet=# create table floor_test
internet-# (
internet(# test bigint not null
internet(# );
CREATE
internet=#
internet=# insert into floor_test (test) values (8);
INSERT 11830189 1
internet=# insert into floor_test (test) values (12);
INSERT 11830190 1
internet=# insert into floor_test (test) values (136);
INSERT 11830191 1
internet=#
internet=# select * from floor_test where mod(floor(test / 128),2)=1;
test
------
136
(1 row)

internet=# select * from floor_test where mod(trunc(test / 128,0),2)=1;
test
------
136
(1 row)

internet=# select * from floor_test where mod(floor(test / 4),2)=1;
test
------
12
(1 row)

internet=# select * from floor_test where mod(trunc(test / 4,0),2)=1;
test
------
12
(1 row)

internet=#
internet=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

internet=#
internet=# delete from floor_test;
DELETE 3
internet=# drop table floor_test;
DROP

behavior in 7.3.1(using the psql client):
Linux version 2.4.18-19.7.x (bhcompile(at)stripples(dot)devel(dot)redhat(dot)com) (gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-112)) #1 Thu Dec 12 09:00:42 EST 2002

internet=# create table floor_test
internet-# (
internet(# test bigint not null
internet(# );
CREATE TABLE
internet=#
internet=# insert into floor_test (test) values (8);
INSERT 1938595 1
internet=# insert into floor_test (test) values (12);
INSERT 1938596 1
internet=# insert into floor_test (test) values (136);
INSERT 1938597 1
internet=#
internet=# select * from floor_test where mod(floor(test / 128),2)=1;
ERROR: Function mod(double precision, integer) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
internet=# select * from floor_test where mod(trunc(test / 128,0),2)=1;
test
------
136
(1 row)

internet=# select * from floor_test where mod(floor(test / 4),2)=1;
ERROR: Function mod(double precision, integer) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
internet=# select * from floor_test where mod(trunc(test / 4,0),2)=1;
test
------
12
(1 row)

internet=#
internet=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

internet=#
internet=# delete from floor_test;
DELETE 3
internet=# drop table floor_test;
DROP TABLE
internet=#

Sample Code
create table floor_test
(
test bigint not null
);

insert into floor_test (test) values (8);
insert into floor_test (test) values (12);
insert into floor_test (test) values (136);

select * from floor_test where mod(floor(test / 128),2)=1;
select * from floor_test where mod(trunc(test / 128,0),2)=1;
select * from floor_test where mod(floor(test / 4),2)=1;
select * from floor_test where mod(trunc(test / 4,0),2)=1;

SELECT version();

delete from floor_test;
drop table floor_test;

No file was uploaded with this report