Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcf57ef4

Browse files
author
Neil Conway
committed
Implement width_bucket() for the float8 data type.
The implementation is somewhat ugly logic-wise, but I don't see aneasy way to make it more concise.When writing this, I noticed that my previous implementation ofwidth_bucket() doesn't handle NaN correctly: postgres=# select width_bucket('NaN', 1, 5, 5); width_bucket -------------- 6 (1 row)AFAICS SQL:2003 does not define a NaN value, so it doesn't address howwidth_bucket() should behave here. The patch changes width_bucket() sothat ereport(ERROR) is raised if NaN is specified for the operand or thelower or upper bounds to width_bucket(). For float8, NaN is disallowedfor any of the floating-point inputs, and +/- infinity is disallowedfor the histogram bounds (but allowed for the operand).Update docs and regression tests, bump the catversion.
1 parentda07c81 commitcf57ef4

File tree

8 files changed

+210
-69
lines changed

8 files changed

+210
-69
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.350 2007/01/12 23:34:54 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.351 2007/01/16 21:41:12 neilc Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -854,6 +854,16 @@
854854
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
855855
<entry><literal>3</literal></entry>
856856
</row>
857+
858+
<row>
859+
<entry><literal><function>width_bucket</function>(<parameter>op</parameter> <type>dp</type>, <parameter>b1</parameter> <type>dp</type>, <parameter>b2</parameter> <type>dp</type>, <parameter>count</parameter> <type>int</type>)</literal></entry>
860+
<entry><type>int</type></entry>
861+
<entry>return the bucket to which <parameter>operand</> would
862+
be assigned in an equidepth histogram with <parameter>count</>
863+
buckets, in the range <parameter>b1</> to <parameter>b2</></entry>
864+
<entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
865+
<entry><literal>3</literal></entry>
866+
</row>
857867
</tbody>
858868
</tgroup>
859869
</table>

‎src/backend/utils/adt/float.c

Lines changed: 83 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.146 2007/01/06 20:21:29 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/float.c,v 1.147 2007/01/1621:41:13 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2745,6 +2745,88 @@ float84ge(PG_FUNCTION_ARGS)
27452745
PG_RETURN_BOOL(float8_cmp_internal(arg1,arg2) >=0);
27462746
}
27472747

2748+
/*
2749+
* Implements the float8 version of the width_bucket() function
2750+
* defined by SQL2003. See also width_bucket_numeric().
2751+
*
2752+
* 'bound1' and 'bound2' are the lower and upper bounds of the
2753+
* histogram's range, respectively. 'count' is the number of buckets
2754+
* in the histogram. width_bucket() returns an integer indicating the
2755+
* bucket number that 'operand' belongs to in an equiwidth histogram
2756+
* with the specified characteristics. An operand smaller than the
2757+
* lower bound is assigned to bucket 0. An operand greater than the
2758+
* upper bound is assigned to an additional bucket (with number
2759+
* count+1). We don't allow "NaN" for any of the float8 inputs, and we
2760+
* don't allow either of the histogram bounds to be +/- infinity.
2761+
*/
2762+
Datum
2763+
width_bucket_float8(PG_FUNCTION_ARGS)
2764+
{
2765+
float8operand=PG_GETARG_FLOAT8(0);
2766+
float8bound1=PG_GETARG_FLOAT8(1);
2767+
float8bound2=PG_GETARG_FLOAT8(2);
2768+
int32count=PG_GETARG_INT32(3);
2769+
int32result;
2770+
2771+
if (count <=0.0)
2772+
ereport(ERROR,
2773+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2774+
errmsg("count must be greater than zero")));
2775+
2776+
if (isnan(operand)||isnan(bound1)||isnan(bound2))
2777+
ereport(ERROR,
2778+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2779+
errmsg("operand, lower bound and upper bound cannot be NaN")));
2780+
2781+
/* Note that we allow "operand" to be infinite */
2782+
if (is_infinite(bound1)||is_infinite(bound2))
2783+
ereport(ERROR,
2784+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2785+
errmsg("lower and upper bounds must be finite")));
2786+
2787+
if (bound1<bound2)
2788+
{
2789+
if (operand<bound1)
2790+
result=0;
2791+
elseif (operand >=bound2)
2792+
{
2793+
result=count+1;
2794+
/* check for overflow */
2795+
if (result<count)
2796+
ereport(ERROR,
2797+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
2798+
errmsg("integer out of range")));
2799+
}
2800+
else
2801+
result= ((float8)count* (operand-bound1) / (bound2-bound1))+1;
2802+
}
2803+
elseif (bound1>bound2)
2804+
{
2805+
if (operand>bound1)
2806+
result=0;
2807+
elseif (operand <=bound2)
2808+
{
2809+
result=count+1;
2810+
/* check for overflow */
2811+
if (result<count)
2812+
ereport(ERROR,
2813+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
2814+
errmsg("integer out of range")));
2815+
}
2816+
else
2817+
result= ((float8)count* (bound1-operand) / (bound1-bound2))+1;
2818+
}
2819+
else
2820+
{
2821+
ereport(ERROR,
2822+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
2823+
errmsg("lower bound cannot equal upper bound")));
2824+
result=0;/* keep the compiler quiet */
2825+
}
2826+
2827+
PG_RETURN_INT32(result);
2828+
}
2829+
27482830
/* ========== PRIVATE ROUTINES ========== */
27492831

27502832
#ifndefHAVE_CBRT

‎src/backend/utils/adt/numeric.c

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Copyright (c) 1998-2007, PostgreSQL Global Development Group
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.98 2007/01/05 22:19:41 momjian Exp $
17+
* $PostgreSQL: pgsql/src/backend/utils/adt/numeric.c,v 1.99 2007/01/16 21:41:13 neilc Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -874,16 +874,17 @@ numeric_floor(PG_FUNCTION_ARGS)
874874
}
875875

876876
/*
877-
* width_bucket_numeric() -
877+
* Implements the numeric version of the width_bucket() function
878+
* defined by SQL2003. See also width_bucket_float8().
878879
*
879880
* 'bound1' and 'bound2' are the lower and upper bounds of the
880881
* histogram's range, respectively. 'count' is the number of buckets
881882
* in the histogram. width_bucket() returns an integer indicating the
882-
* bucket number that 'operand' belongsin for an equiwidth histogram
883+
* bucket number that 'operand' belongsto in an equiwidth histogram
883884
* with the specified characteristics. An operand smaller than the
884885
* lower bound is assigned to bucket 0. An operand greater than the
885886
* upper bound is assigned to an additional bucket (with number
886-
* count+1).
887+
* count+1). We don't allow "NaN" for any of the numeric arguments.
887888
*/
888889
Datum
889890
width_bucket_numeric(PG_FUNCTION_ARGS)
@@ -901,6 +902,13 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
901902
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
902903
errmsg("count must be greater than zero")));
903904

905+
if (NUMERIC_IS_NAN(operand)||
906+
NUMERIC_IS_NAN(bound1)||
907+
NUMERIC_IS_NAN(bound2))
908+
ereport(ERROR,
909+
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION),
910+
errmsg("operand, lower bound and upper bound cannot be NaN")));
911+
904912
init_var(&result_var);
905913
init_var(&count_var);
906914

@@ -937,6 +945,7 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
937945
break;
938946
}
939947

948+
/* if result exceeds the range of a legal int4, we ereport here */
940949
result=numericvar_to_int4(&result_var);
941950

942951
free_var(&count_var);
@@ -946,8 +955,6 @@ width_bucket_numeric(PG_FUNCTION_ARGS)
946955
}
947956

948957
/*
949-
* compute_bucket() -
950-
*
951958
* If 'operand' is not outside the bucket range, determine the correct
952959
* bucket for it to go. The calculations performed by this function
953960
* are derived directly from the SQL2003 spec.

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.371 2007/01/09 02:14:15 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.372 2007/01/16 21:41:13 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200701081
56+
#defineCATALOG_VERSION_NO200701161
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.436 2007/01/05 22:19:53 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.437 2007/01/16 21:41:13 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -636,6 +636,8 @@ DATA(insert OID = 309 ( float84gt PGNSP PGUID 12 f f t f i 2 16 "701 700" _
636636
DESCR("greater-than");
637637
DATA(insertOID=310 (float84gePGNSPPGUID12fftfi216"701 700"_null__null__null_float84ge-_null_ ));
638638
DESCR("greater-than-or-equal");
639+
DATA(insertOID=320 (width_bucketPGNSPPGUID12fftfi423"701 701 701 23"_null__null__null_width_bucket_float8-_null_ ));
640+
DESCR("bucket number of operand in equidepth histogram");
639641

640642
DATA(insertOID=311 (float8PGNSPPGUID12fftfi1701"700"_null__null__null_ftod-_null_ ));
641643
DESCR("convert float4 to float8");

‎src/include/utils/builtins.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.285 2007/01/05 22:19:58 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.286 2007/01/16 21:41:14 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -380,6 +380,7 @@ extern Datum float84lt(PG_FUNCTION_ARGS);
380380
externDatumfloat84le(PG_FUNCTION_ARGS);
381381
externDatumfloat84gt(PG_FUNCTION_ARGS);
382382
externDatumfloat84ge(PG_FUNCTION_ARGS);
383+
externDatumwidth_bucket_float8(PG_FUNCTION_ARGS);
383384

384385
/* dbsize.c */
385386
externDatumpg_tablespace_size_oid(PG_FUNCTION_ARGS);

‎src/test/regress/expected/numeric.out

Lines changed: 63 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -730,55 +730,77 @@ SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
730730
(7 rows)
731731

732732
DROP TABLE ceil_floor_round;
733-
-- Testing for width_bucket()
734-
-- NULL result
735-
SELECT width_bucket(NULL, NULL, NULL, NULL);
736-
width_bucket
737-
--------------
738-
739-
(1 row)
740-
733+
-- Testing for width_bucket(). For convenience, we test both the
734+
-- numeric and float8 versions of the function in this file.
741735
-- errors
742736
SELECT width_bucket(5.0, 3.0, 4.0, 0);
743737
ERROR: count must be greater than zero
744738
SELECT width_bucket(5.0, 3.0, 4.0, -5);
745739
ERROR: count must be greater than zero
746-
SELECT width_bucket(3.0, 3.0, 3.0, 888);
740+
SELECT width_bucket(3.5, 3.0, 3.0, 888);
741+
ERROR: lower bound cannot equal upper bound
742+
SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, 0);
743+
ERROR: count must be greater than zero
744+
SELECT width_bucket(5.0::float8, 3.0::float8, 4.0::float8, -5);
745+
ERROR: count must be greater than zero
746+
SELECT width_bucket(3.5::float8, 3.0::float8, 3.0::float8, 888);
747747
ERROR: lower bound cannot equal upper bound
748+
SELECT width_bucket('NaN', 3.0, 4.0, 888);
749+
ERROR: operand, lower bound and upper bound cannot be NaN
750+
SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888);
751+
ERROR: operand, lower bound and upper bound cannot be NaN
748752
-- normal operation
749-
CREATE TABLE width_bucket_test (operand numeric);
750-
COPY width_bucket_test FROM stdin;
753+
CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8);
754+
COPY width_bucket_test (operand_num) FROM stdin;
755+
UPDATE width_bucket_test SET operand_f8 = operand_num::float8;
751756
SELECT
752-
operand,
753-
width_bucket(operand, 0, 10, 5) AS wb_1,
754-
width_bucket(operand, 10, 0, 5) AS wb_2,
755-
width_bucket(operand, 2, 8, 4) AS wb_3,
756-
width_bucket(operand, 5.0, 5.5, 20) AS wb_4,
757-
width_bucket(operand, -25, 25, 10) AS wb_5
757+
operand_num, operand_f8,
758+
width_bucket(operand_num, 0, 10, 5) AS wb_1,
759+
width_bucket(operand_f8, 0, 10, 5) AS wb_1f,
760+
width_bucket(operand_num, 10, 0, 5) AS wb_2,
761+
width_bucket(operand_f8, 10, 0, 5) AS wb_2f,
762+
width_bucket(operand_num, 2, 8, 4) AS wb_3,
763+
width_bucket(operand_f8, 2, 8, 4) AS wb_3f,
764+
width_bucket(operand_num, 5.0, 5.5, 20) AS wb_4,
765+
width_bucket(operand_f8, 5.0, 5.5, 20) AS wb_4f,
766+
width_bucket(operand_num, -25, 25, 10) AS wb_5,
767+
width_bucket(operand_f8, -25, 25, 10) AS wb_5f
758768
FROM width_bucket_test;
759-
operand | wb_1 | wb_2 | wb_3 | wb_4 | wb_5
760-
------------------+------+------+------+------+------
761-
-5.2 | 0 | 6 | 0 | 0 | 4
762-
-0.0000000000001 | 0 | 6 | 0 | 0 | 5
763-
0.0000000000001 | 1 | 5 | 0 | 0 | 6
764-
1 | 1 | 5 | 0 | 0 | 6
765-
1.99999999999999 | 1 | 5 | 0 | 0 | 6
766-
2 | 2 | 5 | 1 | 0 | 6
767-
2.00000000000001 | 2 | 4 | 1 | 0 | 6
768-
3 | 2 | 4 | 1 | 0 | 6
769-
4 | 3 | 4 | 2 | 0 | 6
770-
4.5 | 3 | 3 | 2 | 0 | 6
771-
5 | 3 | 3 | 3 | 1 | 7
772-
5.5 | 3 | 3 | 3 | 21 | 7
773-
6 | 4 | 3 | 3 | 21 | 7
774-
7 | 4 | 2 | 4 | 21 | 7
775-
8 | 5 | 2 | 5 | 21 | 7
776-
9 | 5 | 1 | 5 | 21 | 7
777-
9.99999999999999 | 5 | 1 | 5 | 21 | 7
778-
10 | 6 | 1 | 5 | 21 | 8
779-
10.0000000000001 | 6 | 0 | 5 | 21 | 8
780-
NaN | 6 | 0 | 5 | 21 | 11
781-
(20 rows)
769+
operand_num | operand_f8 | wb_1 | wb_1f | wb_2 | wb_2f | wb_3 | wb_3f | wb_4 | wb_4f | wb_5 | wb_5f
770+
------------------+------------------+------+-------+------+-------+------+-------+------+-------+------+-------
771+
-5.2 | -5.2 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 4 | 4
772+
-0.0000000001 | -1e-10 | 0 | 0 | 6 | 6 | 0 | 0 | 0 | 0 | 5 | 5
773+
0.000000000001 | 1e-12 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
774+
1 | 1 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
775+
1.99999999999999 | 1.99999999999999 | 1 | 1 | 5 | 5 | 0 | 0 | 0 | 0 | 6 | 6
776+
2 | 2 | 2 | 2 | 5 | 5 | 1 | 1 | 0 | 0 | 6 | 6
777+
2.00000000000001 | 2.00000000000001 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
778+
3 | 3 | 2 | 2 | 4 | 4 | 1 | 1 | 0 | 0 | 6 | 6
779+
4 | 4 | 3 | 3 | 4 | 4 | 2 | 2 | 0 | 0 | 6 | 6
780+
4.5 | 4.5 | 3 | 3 | 3 | 3 | 2 | 2 | 0 | 0 | 6 | 6
781+
5 | 5 | 3 | 3 | 3 | 3 | 3 | 3 | 1 | 1 | 7 | 7
782+
5.5 | 5.5 | 3 | 3 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
783+
6 | 6 | 4 | 4 | 3 | 3 | 3 | 3 | 21 | 21 | 7 | 7
784+
7 | 7 | 4 | 4 | 2 | 2 | 4 | 4 | 21 | 21 | 7 | 7
785+
8 | 8 | 5 | 5 | 2 | 2 | 5 | 5 | 21 | 21 | 7 | 7
786+
9 | 9 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
787+
9.99999999999999 | 9.99999999999999 | 5 | 5 | 1 | 1 | 5 | 5 | 21 | 21 | 7 | 7
788+
10 | 10 | 6 | 6 | 1 | 1 | 5 | 5 | 21 | 21 | 8 | 8
789+
10.0000000000001 | 10.0000000000001 | 6 | 6 | 0 | 0 | 5 | 5 | 21 | 21 | 8 | 8
790+
(19 rows)
791+
792+
-- for float8 only, check positive and negative infinity: we require
793+
-- finite bucket bounds, but allow an infinite operand
794+
SELECT width_bucket(0.0::float8, 'Infinity'::float8, 5, 10); -- error
795+
ERROR: lower and upper bounds must be finite
796+
SELECT width_bucket(0.0::float8, 5, '-Infinity'::float8, 20); -- error
797+
ERROR: lower and upper bounds must be finite
798+
SELECT width_bucket('Infinity'::float8, 1, 10, 10),
799+
width_bucket('-Infinity'::float8, 1, 10, 10);
800+
width_bucket | width_bucket
801+
--------------+--------------
802+
11 | 0
803+
(1 row)
782804

783805
DROP TABLE width_bucket_test;
784806
-- TO_CHAR()
@@ -800,7 +822,7 @@ SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999')
800822
(10 rows)
801823

802824
SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
803-
FROM num_data;
825+
FROM num_data;
804826
to_char_2 | to_char
805827
-----------+--------------------------------------------
806828
| .000,000,000,000,000

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp