Thread: problem with array of boxes

Hello!

I have run into a problem with the array of boxes datatype. Here is a
simple example:

testdb=# CREATE TABLE boxarray_test (col1 BOX[2]);
CREATE

testdb=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
INSERT 32957 1

testdb=# SELECT * FROM boxarray_test;
      col1
---------------
  {(4,4),(2,2)}
(1 row)

Instead of the above, I expected the result of the SELECT to be:

{"(3,3),(1,1)","(4,4),(2,2)"}

Arrays of other geometric types worked like I expected them to do.

Is this a bug?

I'm running PostgreSQL 7.2 on Mac OS X 10.1.3
(powerpc-apple-darwin5.3), compiled by GCC 2.95.2. I ran the
regression tests against my installation and all tests were completed
successfully. (The tests don't seem to cover arrays of geometric
types, though.)

-Andre


--
Andre Radke + mailto:lists@spicynoodles.net + http://www.spicynoodles.net/

Andre Radke <lists@spicynoodles.net> writes:
> I have run into a problem with the array of boxes datatype.

After a little poking at this, it seems that some parts of the array
support code may be failing to pay attention to "typdelim".  Type box
has typdelim set to ';' (it's the only standard datatype whose typdelim
is not ',').  Changing that to ',' made the behavior less unexpected.
Haven't dug into the code yet for a proper fix.

This does beg the question of whether box's typdelim should be ';'
rather than the standard ','.  I can see why that was done: box likes
to use commas in its text representation.  But I really wonder how
much client code will be prepared to cope with arrays represented
with ';' not ',' between items ...

            regards, tom lane

Andre Radke <lists@spicynoodles.net> writes:
> testdb=# CREATE TABLE boxarray_test (col1 BOX[2]);
> testdb=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
> testdb=# SELECT * FROM boxarray_test;
>       col1
> ---------------
>   {(4,4),(2,2)}
> (1 row)

I've finished looking into this, and the short answer is that your input
is not syntactically correct.  Because type box has typdelim = ';', the
correct input would have been

INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}');

(btw, you could omit the double-quote marks here.)  There is indeed a
bug here: since the array parser didn't think the comma was an item
delimiter, IMHO it should have considered the array to contain one item
    (3,3),(1,1),(4,4),(2,2)
which would have provoked an error when handed to the box-datatype input
parser.  Instead the array parser messed up and passed only the second
double-quoted substring to the box input routine.

I have fixed this for 7.3: with the just-committed code, I get

boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
ERROR:  Bad box external representation '(3,3),(1,1),(4,4),(2,2)'
boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}');
INSERT 533436 1
boxes=# INSERT INTO boxarray_test VALUES ('{(3,3),(1,1);(4,4),(2,2)}');
INSERT 533437 1
boxes=# select * from boxarray_test;
           col1
---------------------------
 {(3,3),(1,1);(4,4),(2,2)}
 {(3,3),(1,1);(4,4),(2,2)}
(2 rows)


This still leaves us with the question of whether it's really a good
idea that type box has typdelim ';' and not ',' like everything else
uses.  Anyone have a strong feeling about changing it or not?  If we
change it, we'd instead get this behavior:

boxes=# update pg_type set typdelim = ',' where typname = 'box';
UPDATE 1
boxes=# select * from boxarray_test;
             col1
-------------------------------
 {"(3,3),(1,1)","(4,4),(2,2)"}
 {"(3,3),(1,1)","(4,4),(2,2)"}
(2 rows)

boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}');
INSERT 533438 1

and the double quotes would be required.

One argument against changing is that it'd break pg_dump output for
existing tables containing arrays of boxes ... if any there be.
Given that this hasn't come up before, I wonder if anyone's using 'em.

            regards, tom lane

At 18:01h -0500 16.03.2002, Tom Lane wrote:
>I've finished looking into this, and the short answer is that your input
>is not syntactically correct.  Because type box has typdelim = ';', the
>correct input would have been
>
>INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}');

Thanks! I changed my code to use a semi-colon instead of a comma as
delimiter and that indeed solved my problem.

>This still leaves us with the question of whether it's really a good
>idea that type box has typdelim ';' and not ',' like everything else
>uses.  Anyone have a strong feeling about changing it or not?

I'm relatively new to PostgreSQL, so I don't have a qualified opinion on this.

-Andre


--
Andre Radke + mailto:lists@spicynoodles.net + http://www.spicynoodles.net/