Thread: Odd 'except' and 'default' interaction behavior

We noticed a strange behavior involving 'except' and 'default'
interaction.  Example from psql:

wpbb=# \d robtest
          Table "robtest"
 Attribute |  Type   |  Modifier
-----------+---------+-------------
 id        | integer | default '1'
 userid    | integer |
 articleid | integer |

wpbb=# insert into robtest (userid,articleid) select '1'::int4,'2'::int4 except select '2'::int4,'5'::int4;
ERROR:  Each UNION | EXCEPT | INTERSECT query must have the same number of columns.

wpbb=# alter table robtest alter column id drop default;
ALTER

wpbb=# insert into robtest (userid,articleid) select '1'::int4,'2'::int4 except select '2'::int4,'5'::int4;
INSERT 306530 1


Notice that error regarding the usage of except and the # of columns.
Strange.  Yet, that exact same query works fine when we remove a
default on a column on the table which isn't being referenced.

I'm not sure if this is a bug, a poorly written error message, or my
ignorance. :')  We are using Postgresql 7.0.3 release under Linux:

phil=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3 (1 row)


Thanks!!


Phil

--
Philip Edelbrock -- IS Manager -- Edge Design, Corvallis, OR
   phil@netroedge.com -- http://www.netroedge.com/~phil
 PGP F16: 01 D2 FD 01 B5 46 F4 F0  3A 8B 9D 7E 14 7F FB 7A

phil@netroedge.com writes:
> We noticed a strange behavior involving 'except' and 'default'
> interaction.  Example from psql:

This is a known bug.  It's fixed in 7.1.

            regards, tom lane

Hi Phil,

There is a place on the techdocs.postgresql.org website that is purely
for listing gotcha's like this sounds to be.  It's so people NOT running
the very latest and greatest versions of PostgreSQL can be aware of
known things to avoid doing, potentical gotcha's, etc.

Can you tell me more about this strange behaviour?  If it's appropriate,
I'll try to get it onto the techdocs website.

Regards and best wishes,

Justin Clift

Tom Lane wrote:
>
> phil@netroedge.com writes:
> > We noticed a strange behavior involving 'except' and 'default'
> > interaction.  Example from psql:
>
> This is a known bug.  It's fixed in 7.1.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi

Justin Clift <jclift@iprimus.com.au> writes:
> Can you tell me more about this strange behaviour?  If it's appropriate,
> I'll try to get it onto the techdocs website.

The underlying bug is that in

    INSERT INTO foo SELECT blah blah FROM bar

the INSERT processing tries to realign the SELECT's target list to match
what the INSERTed tuples need to be.  This may include reordering the
values, adding NULL or default expressions for missing columns, datatype
coercions, etc.

Unfortunately the INSERT code is too stupid to know that when the SELECT
has a top-level UNION, EXCEPT, or INTERSECT operator, there are multiple
target lists that would all need to be adjusted the same way.  It only
changes the first one, leading to funny errors later on.

There are other cases that break too, IIRC, mostly involving GROUP BY
or ORDER BY in the SELECT.

This is all fixed in 7.1 by the simple expedient of using two levels
of target list, so that we don't have to munge the SELECT.

            regards, tom lane