On Sunday, December 11, 2011 08:09:36 PM Peter Eisentraut wrote:
> On sön, 2011-12-11 at 04:26 +0100, Andres Freund wrote:
> > > Building a CreateStmt seems to work well enough so far.
> > > The only problem with that approach so far that I found is that:
> > >
> > > CREATE TABLE collate_test2
> > > (
> > >
> > > a int,
> > >
> > > b text COLLATE "POSIX"
> > >
> > > );
> > >
> > > CREATE TABLE collate_test1
> > > (
> > >
> > > a int,
> > >
> > > b text COLLATE "C" NOT NULL
> > >
> > > );
> > >
> > > CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT
> > > a, b FROM collate_test2; -- fail
> > >
> > > failed with:
> > > ERROR: no collation was derived for column "b" with collatable type
> > > text HINT: Use the COLLATE clause to set the collation explicitly.
> > > "works" now.
> >
> > Could you explain why the above should fail? After all the UNION is valid
> > outside the CREATE TABLE and you can even sort on b.
>
> That would be strange, because earlier in the test file there is also
>
> SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2
> ORDER BY 2; -- fail
Yea. I didn't realize that it only fails during execution if there actually
are rows present (confusing behaviour btw).
> The union itself is valid, but because it combines two different
> collations, the collation derivation for the column is "unknown", and so
> it cannot be ordered. And we made the implementation decision to not
> allow creating columns with unknown collation.
I copied that behaviour. Its a bit complicated by the fact that
GetColumnDefCollation cannot be taught to accept an invalidOid so I had to
duplicate the check in CreateTableAs...
I attached the - from my side - final version of the patch. I dislike two
things about it:
* code duplication due to error handling. Before making the error message for
various illegal SELECT INTOs the patch actually shrank the code size... If
anybody has a good idea to avoid duplicating that loop around SelectStmt->ops
I would be happy.
* new executor flags to define whether oids should be returned
Andres
