Re: [BUGS] Small bug in union
The basic problem is that PostgreSQL doesn't understand that Null match any
datatype.Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------If you have two table created this way:
create table test00
(
posizione int4 not null primary key,
testo varchar(50),
campo float8,
dataeora datetime
);create table test01
(
posizione int4 not null primary key,
testo varchar(50),
campo float8,
dataeora datetime
);and you try to implement an outer join (not yet supported) using the union
clause this way:SELECT
test00.posizione,
test01.posizione
FROM
test00,
test01
WHERE
test00.posizione = test01.posizione
UNION
SELECT
test00.posizione,
Null
FROM test00
WHERE
NOT EXISTS (SELECT * FROM test01 WHERE test01.posizione = test00.posizione);postgres reports the following error:
ERROR: Each UNION query must have identical target types.
If you replace Null with an integer everything works well, so the datatype
mismatch is detected on the Null.If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------The problem is in src/backend/parser/parse_clause.c in function:
List * transformUnionClause(List *unionClause, List *targetlist)
Near the end there's a check on data types that looks like:
if (((TargetEntry *)lfirst(prev_target))->resdom->restype !=
((TargetEntry *)lfirst(next_target))->resdom->restype)
elog(ERROR,"Each UNION query must have identical target types.");this check should be performed only when both entry are not a Null costant,
else it should be ignored because Null should match any datatype. I don't
know how PostgreSQL handles Null internally else I had changed the code
myself. Anyway I'm sure you PostgreSQL gurus will know how to do it in few
seconds.Hope it helps !
P.S. My compliments to all the development staff. Just few more
enhancements (outer join support, slightly better optimizer and few things
more) and PostgreSQL will compare to (and sometimes beat) most commercial
high quality DBMS.Dr. Sbragion Denis
InfoTecna
Tel, Fax: +39 39 2324054
URL: http://space.tin.it/internet/dsbragio
Thomas, we now get:
select usesysid from pg_user union select null ;
ERROR: type id lookup of 0 failed
which not good either. Can you address this issue?
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)Import Notes
Reply to msg id not found: 3.0.5.32.19980511180624.007ea860@MBox.InfoTecna.com
Thomas, we now get:
select usesysid from pg_user union select null ;
ERROR: type id lookup of 0 failed
which not good either. Can you address this issue?
I'm almost back on-line (I hope) after massive hacker activity took out
the alumni pop server at Caltech. From looking through the hackers
mhonarc archive (hmm, don't much like that name "hackers" anymore; it's
cost us a _lot_ at work and taken me out of Postgres for more than a
month :( I see two issues for me to look at: the one above and the one
from Brett regarding a core dump from a mal-formed query. Will look at
both.
I have some additional patches in the parser area which continue the
type matching/coersion work from the last two months.
I've got patches to put 64-bit integers into the backend; they should
work for Alphas and at least some gcc-compiled 32-bit machines, but
we'll need beta testers to help get the configuration for our other
supported platforms. Once we have that for v6.4, we can also use this
type internally to implement additional types like numeric() and
decimal().
All mail sent to me from June 12 to now has been lost (I lost access to
the pop server and procmail went away, and so my .forward file reference
to procmail was broken and unfixable). It is not yet fixed, but should
be in a day or so. Talk to you then. In the meantime, I will be looking
at the mhonarc archives to keep up (great feature scrappy!)...
- Tom
Import Notes
Resolved by subject fallback
Thomas, we now get:
select usesysid from pg_user union select null ;
ERROR: type id lookup of 0 failed
which not good either. Can you address this issue?
` >
I'm almost back on-line (I hope) after massive hacker activity took out
Yea, that is on my list too. One of us will have it fixed for 6.4. The
rest sounds good to me. I am not particularly happy with 'hackers' name
either.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
Aww. Hackers is a great name. Devel might be more appropriate, but
hackers isn't so bad.
On Tue, 7 July 1998, at 14:47:08, Bruce Momjian wrote:
Show quoted text
Yea, that is on my list too. One of us will have it fixed for 6.4. The
rest sounds good to me. I am not particularly happy with 'hackers' name
either.-- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Brett McCormick <brett@work.chicken.org> writes:
Aww. Hackers is a great name. Devel might be more appropriate, but
hackers isn't so bad.
My two cents: "hackers" != "crackers".
"Hacker" is an ancient and honorable term for a dedicated programmer,
and the PostgreSQL group ought to wear it proudly.
The sort of common thieves and vandals who attacked Caltech's system
don't deserve the name "hacker"; that crowd is trying to appropriate
a term they don't have the right to aspire to. Bad enough that these
low-lifes cause us everyday grief, but to steal the hacking community's
self-label is an intolerable insult. Don't give in to it.
BTW, if you somehow are not familiar with the history of the term
"hacker", you might care to visit the Jargon File
(try http://sagan.earthspace.net/jargon/), and/or Eric Raymond's
page about hacker history and culture
(http://tuxedo.org/~esr/faqs/index.html).
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofTue7Jul1998152743-070013730.41183.877359.211335@web0.speakeasy.org | Resolved by subject fallback
BTW, if you somehow are not familiar with the history of the term
"hacker", you might care to visit the Jargon File
(try http://sagan.earthspace.net/jargon/), and/or Eric Raymond's
page about hacker history and culture
(http://tuxedo.org/~esr/faqs/index.html).
Actually, for some reason we used to use the term (circa mid '70s) to
refer to an unaccomplished, undisciplined coder. Sort of like a bad
golfer is called a hacker (you know, hacking and slashing at the ball).
I haven't found the usage in anything recent though, and the folks who
answered my mail at jargon hadn't heard of that usage either (boy, I
must have way too much time on my hands...). Still have trouble applying
the term to coders with skill :)
- Tom