Обсуждение: Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement
Bug #881: floor function returning double precision with integer arguments in 7.3, returns numeric in 7.2 with the same statement
От
pgsql-bugs@postgresql.org
Дата:
Allan Oepping (allan@pacificwebworks.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 PostgreSQL7.3 states that PostgreSQL 7.3's floor function should return type numeric. In 7.3 it seems to be returning thedouble precision datatype. Oracle 8.0.5 correctly executes the test statments along with PostgreSQL 7.2. (we have to becompatible) 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@daffy.perf.redhat.com) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) #1SMP 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@stripples.devel.redhat.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
pgsql-bugs@postgresql.org writes: > Return datatype of the floor function is different between PostgreSQL 7.2 and PostgreSQL 7.3 the documentation for PostgreSQL7.3 states that PostgreSQL 7.3's floor function should return type numeric. In 7.3 it seems to be returning thedouble precision datatype. Oracle 8.0.5 correctly executes the test statments along with PostgreSQL 7.2. (we have to becompatible) > The documentation mentioned: > http://www.postgresql.org/docs/view.php?version=7.3&file=functions-math.html This table seems not to have been updated when we added float versions of ceil(), floor(), and sign(). You won't get any support for ripping them out again, though. I suggest adding casts to the queries, or possibly defining a version of mod() that takes float8. regards, tom lane