"Kevin Grittner" writes:
> Jeff Wu wrote:
>> what needs to happen to get this fix in?
> Well, "fix" implies that there is a bug, which there isn't.
I think what Jeff was actually suggesting was that we commit the
proposed added HINT. I didn't like the hint patch at all --- as given,
the hint
Jeff Wu wrote:
> what needs to happen to get this fix in?
Well, "fix" implies that there is a bug, which there isn't. The
current behavior doesn't violate the requirements of the standard,
nor is it a regression from the behavior of any previous PostgreSQL
release. What we're talking about i
So I'm a n00b to the open source community, but what needs to happen to get
this fix in?
On 14 April 2011 15:13, Kevin Grittner wrote:
> Tom Lane wrote:
> > "Kevin Grittner" writes:
>
> >> That means that all three of the databases you tested have
> >> extensions to the standard similar to wha
Tom Lane wrote:
> "Kevin Grittner" writes:
>> That means that all three of the databases you tested have
>> extensions to the standard similar to what is being contemplated
>> for PostgreSQL.
>
> Uh, no, it proves they all extend the standard to allow NULL to be
> written without an immediate
"Kevin Grittner" writes:
> Mike Fowler wrote:
>> So to summarise, Oracle and PostgreSQL need minor tweaks to run
>> cleanly and SQLServer and MySQL do not.
> That means that all three of the databases you tested have
> extensions to the standard similar to what is being contemplated for
> Postgr
Mike Fowler wrote:
> So to summarise, Oracle and PostgreSQL need minor tweaks to run
> cleanly and SQLServer and MySQL do not.
The FROM DUAL in Oracle has nothing to do with the issue at hand.
That is just because they always require a FROM clause on every
SELECT. DUAL is a special table wit
On 14/04/11 17:05, Kevin Grittner wrote:
SELECT 1,null,null FROM DUAL
UNION
SELECT 2,3,null FROM DUAL
UNION
SELECT 3,null,4 FROM DUAL
Sadly I can't profess to knowing Oracle, however if I run the query as
suggested I get:
1 | NULL | NULL
1 | |
2 |
Mike Fowler wrote:
> SELECT 1,null,null
> UNION
> SELECT 2,3,null
> UNION
> SELECT 3,null,4
> In Oracle I get a delicious error message:
>
> Error: ORA-00923: FROM keyword not found where expected
For Oracle, shouldn't that be:
SELECT 1,null,null FROM DUAL
UNION
SELECT 2,3,null FROM DUAL
On 13/04/11 19:32, Tom Lane wrote:
"Jeff Wu" writes:
The UNION construct (as noted on this page:
http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
cast unknown types to TEXT, however, if you try to do three or more UNIONs
the order in which the UNIONs are executed will ca
Tom Lane wrote:
> Well, the case that is bothering me is stuff like
>
> (select '1' union select '1 ') union all select 2;
>
> The first union produces 1 row if you resolve the constants as
> integers, but 2 rows if you resolve as text, which I think is what
> the spec would expect here.
"Kevin Grittner" writes:
> I'm not sure the spec requires *any* of them to work.
It doesn't. NULL in the standard is not part of the generic expression
syntax; it only appears as (which
lets it be the direct argument of CAST, or the DEFAULT value for a table
column) and as (which lets it
be an
"Kevin Grittner" writes:
> Tom Lane wrote:
>> The sticking point is just that in purely syntactic terms this is
>> action-at-a-distance, and so it's hard to square with the spec. I
>> think that our current reading (in which the '1' and '2' get
>> resolved as text) is actually closer to what the
Tom Lane wrote:
> so far as I can see the spec simply disallows a
> not-explicitly-cast NULL constant in cases like this, which seems
> if anything even less friendly than what we're doing.
Just to illustrate the current behavior:
test=# select null union select 1;
?column?
--
Tom Lane wrote:
> you wish that in
>
> (select '1' union select '2') union select 3
>
> the fact that the third value is clearly integer would influence
> the choice of the resolved type of the first UNION.
Yeah.
> My vision of how to implement that is different than what you seem
>
"Kevin Grittner" writes:
> Tom Lane wrote:
>> Consider
>>
>> select '1' union select '1 ';
>>
>> How many rows does that produce? You cannot answer without
>> imputing a data type to the columns. "text" will give a different
>> answer than "integer" or "bpchar".
> Well, if we were to assign
Tom Lane wrote:
> Consider
>
> select '1' union select '1 ';
>
> How many rows does that produce? You cannot answer without
> imputing a data type to the columns. "text" will give a different
> answer than "integer" or "bpchar".
Well, if we were to assign both to type unknown initially
"Kevin Grittner" writes:
> From my perspective the "right" answer is to be able to resolve two
> unknown types to unknown rather than text in a few places where we
> are currently compelled to assign a concrete type.
Well, it's not so easy as that. Consider
select '1' union select '1 ';
Tom Lane wrote:
> The reason we haven't done it is that it looks like the SQL
> standard requires type resolution for set-ops to happen one pair
> of input relations at a time.
Well, it also requires that an unadorned quoted literal is of type
char(n). This is inextricably tied in with the Po
"Jeff Wu" writes:
> The UNION construct (as noted on this page:
> http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
> cast unknown types to TEXT, however, if you try to do three or more UNIONs
> the order in which the UNIONs are executed will cause some columns to be
> cast
The following bug has been logged online:
Bug reference: 5974
Logged by: Jeff Wu
Email address: j...@atlassian.com
PostgreSQL version: 9.0
Operating system: Mac OS X
Description:UNION construct type cast gives poor error message
Details:
The UNION construct (as note
20 matches
Mail list logo
