White space affecting parsing of range values

On 5/6/20 9:00 AM, Thom Brown wrote:

Hi,

I noticed I'm getting an error when adding white space to a numeric
range. I can run this:

postgres=# SELECT 5::numeric <@ '(,10]'::numrange;
?column?
----------
t
(1 row)

But I can't run this:

postgres=# SELECT 5::numeric <@ '( ,10]'::numrange;
ERROR: invalid input syntax for type numeric: " "
LINE 1: SELECT 5::numeric <@ '( ,10]'::numrange;
^
If one had constructed a series of ranges, and wanted to line them up
vertically for easy comprehension, this wouldn't be possible.

This doesn't seem to be a problem with regular numeric values:

postgres=# SELECT ' 3 '::numeric;
numeric
---------
3
(1 row)

Shouldn't white space be ignored in range values?

https://www.postgresql.org/docs/12/rangetypes.html

"Whitespace is allowed before and after the range value, but any
whitespace between the parentheses or brackets is taken as part of the
lower or upper bound value. (Depending on the element type, it might or
might not be significant.)
"

SELECT 5::numeric <@ '(00,10]'::numrange;
?column?
----------
t

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/6/20 9:00 AM, Thom Brown wrote:

Hi,

I noticed I'm getting an error when adding white space to a numeric
range. I can run this:

postgres=# SELECT 5::numeric <@ '(,10]'::numrange;
?column?
----------
t
(1 row)

But I can't run this:

postgres=# SELECT 5::numeric <@ '( ,10]'::numrange;
ERROR: invalid input syntax for type numeric: " "
LINE 1: SELECT 5::numeric <@ '( ,10]'::numrange;
^
If one had constructed a series of ranges, and wanted to line them up
vertically for easy comprehension, this wouldn't be possible.

This doesn't seem to be a problem with regular numeric values:

postgres=# SELECT ' 3 '::numeric;
numeric
---------
3
(1 row)

Shouldn't white space be ignored in range values?

https://www.postgresql.org/docs/12/rangetypes.html

"Whitespace is allowed before and after the range value, but any
whitespace between the parentheses or brackets is taken as part of the
lower or upper bound value. (Depending on the element type, it might or
might not be significant.)
"

I guess I should read the docs more carefully. Shouldn't this be
insignificant for a numeric value?

SELECT 5::numeric <@ '(00,10]'::numrange;
?column?
----------
t

Your example isn't equivalent to mine. That sets a lower bound.

Thom

On 5/6/20 9:19 AM, Thom Brown wrote:

On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/6/20 9:00 AM, Thom Brown wrote:

Hi,

I noticed I'm getting an error when adding white space to a numeric
range. I can run this:

postgres=# SELECT 5::numeric <@ '(,10]'::numrange;
?column?
----------
t
(1 row)

But I can't run this:

postgres=# SELECT 5::numeric <@ '( ,10]'::numrange;
ERROR: invalid input syntax for type numeric: " "
LINE 1: SELECT 5::numeric <@ '( ,10]'::numrange;
^
If one had constructed a series of ranges, and wanted to line them up
vertically for easy comprehension, this wouldn't be possible.

This doesn't seem to be a problem with regular numeric values:

postgres=# SELECT ' 3 '::numeric;
numeric
---------
3
(1 row)

Shouldn't white space be ignored in range values?

https://www.postgresql.org/docs/12/rangetypes.html

"Whitespace is allowed before and after the range value, but any
whitespace between the parentheses or brackets is taken as part of the
lower or upper bound value. (Depending on the element type, it might or
might not be significant.)
"

I guess I should read the docs more carefully. Shouldn't this be
insignificant for a numeric value?

No:

select ' '::numeric;
ERROR: invalid input syntax for type numeric: " "
LINE 1: select ' '::numeric;

SELECT 5::numeric <@ '(00,10]'::numrange;
?column?
----------
t

Your example isn't equivalent to mine. That sets a lower bound.

SELECT 5::numeric <@ numrange(NULL ,10, '(]');
?column?
----------
t

From previous link:

"-- Using NULL for either bound causes the range to be unbounded on that
side.
SELECT numrange(NULL, 2.2);"

Thom

--
Adrian Klaver
adrian.klaver@aklaver.com

Thom Brown <thom@linux.com> writes:

I guess I should read the docs more carefully. Shouldn't this be
insignificant for a numeric value?

That would require the range code to know whether the subtype considers
whitespace significant (or perhaps more usefully, whether an all-spaces
input is valid). We've stayed away from requiring range_in to have any
type-specific knowledge of that sort.

Still, you could argue that the rule ought to be "an empty or all-blank
value must be quoted to distinguish it from an omitted bound" rather than
"an empty value must be quoted to distinguish it from an omitted bound".

I'm not sure if we could get away with redefining that at this point,
though. It looks like range_out quotes such values already, so maybe a
change wouldn't be totally catastrophic (in the sense of breaking dump
files). But I still suspect there would be more people unhappy than
happy.

regards, tom lane

On Wed, 6 May 2020 at 17:30, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/6/20 9:19 AM, Thom Brown wrote:

On Wed, 6 May 2020 at 17:13, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 5/6/20 9:00 AM, Thom Brown wrote:

Hi,

I noticed I'm getting an error when adding white space to a numeric
range. I can run this:

postgres=# SELECT 5::numeric <@ '(,10]'::numrange;
?column?
----------
t
(1 row)

But I can't run this:

postgres=# SELECT 5::numeric <@ '( ,10]'::numrange;
ERROR: invalid input syntax for type numeric: " "
LINE 1: SELECT 5::numeric <@ '( ,10]'::numrange;
^
If one had constructed a series of ranges, and wanted to line them up
vertically for easy comprehension, this wouldn't be possible.

This doesn't seem to be a problem with regular numeric values:

postgres=# SELECT ' 3 '::numeric;
numeric
---------
3
(1 row)

Shouldn't white space be ignored in range values?

https://www.postgresql.org/docs/12/rangetypes.html

"Whitespace is allowed before and after the range value, but any
whitespace between the parentheses or brackets is taken as part of the
lower or upper bound value. (Depending on the element type, it might or
might not be significant.)
"

I guess I should read the docs more carefully. Shouldn't this be
insignificant for a numeric value?

No:

select ' '::numeric;
ERROR: invalid input syntax for type numeric: " "
LINE 1: select ' '::numeric;

SELECT 5::numeric <@ '(00,10]'::numrange;
?column?
----------
t

Your example isn't equivalent to mine. That sets a lower bound.

SELECT 5::numeric <@ numrange(NULL ,10, '(]');
?column?
----------
t

Yes, I guess the numrange function would be a decent substitute in this case.

From previous link:

"-- Using NULL for either bound causes the range to be unbounded on that
side.
SELECT numrange(NULL, 2.2);"

--
Thom

On Wed, 6 May 2020 at 17:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Thom Brown <thom@linux.com> writes:

I guess I should read the docs more carefully. Shouldn't this be
insignificant for a numeric value?

That would require the range code to know whether the subtype considers
whitespace significant (or perhaps more usefully, whether an all-spaces
input is valid). We've stayed away from requiring range_in to have any
type-specific knowledge of that sort.

Still, you could argue that the rule ought to be "an empty or all-blank
value must be quoted to distinguish it from an omitted bound" rather than
"an empty value must be quoted to distinguish it from an omitted bound".

I'm not sure if we could get away with redefining that at this point,
though. It looks like range_out quotes such values already, so maybe a
change wouldn't be totally catastrophic (in the sense of breaking dump
files). But I still suspect there would be more people unhappy than
happy.

Okay, I see that this isn't really worth changing. It's surprising
behaviour, but I can see it's not a huge issue, and can be worked
around anyway.

Thanks

--
Thom