[BUGS] order by using functions under unions
Eric Pare ([EMAIL PROTECTED]) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
order by using functions under unions
Long Description
When using union over queries, if the order by clause occurs using a function, the
output will not be correctly ordered. You need to query the function and then use the
alias to order over it.
Sample Code
Here is a short example where this touchy bug occurs :
create database mydb;
\c mydb
create table temp ( name varchar(12));
insert into temp values ('eric');
insert into temp values ('daniel');
insert into temp values ('ernst');
insert into temp values ('chantal');
having the four names above, selecting the 2 names starting with an 'e'
and then making the union with the others (the first letter isn't an 'e') looks like
this :
select name from temp where name like 'e%'
union
select name from temp where name not like 'e%'
order by substr(name,1,4) asc;
name
-
eric
ernst
chantal
daniel
(4 rows)
The order by didn't work correctly -- and should be chantal, daniel, eric, ernst
Trying somewhat of an oposite :
select name from temp where name not like 'e%'
union
select name from temp where name like 'e%'
order by substr(name,1,4) desc;
name
-
daniel
chantal
eric
ernst
(4 rows)
the problem seems to occur while trying to order over a function, specially because if
you remove the substr function and try to "order by" over the name, the output is
correctly ordered.
an easy way to solve this problem is to select what you want to order over and then
order on the alias (that way you do not order over a function...I guess)
i.e. :
select name, substr(name,1,4) from temp where name not like 'e%'
union
select name, substr(name,1,4) from temp whare name like 'e%'
order by substr desc;
hope the example is clear enough and that the bug hasn't already been reported 100+
times...!
Eric Pare
[EMAIL PROTECTED]
No file was uploaded with this report
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] order by using functions under unions
[EMAIL PROTECTED] writes: > select name from temp where name like 'e%' > union > select name from temp where name not like 'e%' > order by substr(name,1,4) asc; This isn't supported. 7.1 knows that it can't do it: regression=# select name from temp where name like 'e%' regression-# union regression-# select name from temp where name not like 'e%' regression-# order by substr(name,1,4) asc; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns It'd be nice to make it happen for the case you illustrate (ORDER BY on an expression using only result columns) but that's not done yet. In the meantime you can work around it (again, in 7.1) by using an explicit subselect: regression=# select name from ( regression(# select name from temp where name like 'e%' regression(# union regression(# select name from temp where name not like 'e%' regression(# ) ss regression=# order by substr(name,1,4) asc; name - chantal daniel eric ernst (4 rows) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Re: [HACKERS] Re: date conversion (was Re: Re: v7.1.1 branched and released on Tuesday ...)
Thomas Lockhart <[EMAIL PROTECTED]> writes: > I'm not sure that tm_isdst == -1 is a legitimate indicator for mktime() > failure on all platforms; it indicates "don't know", but afaik there is > no defined behavior for the rest of the fields in that case. Can we be > assured that for all platforms the other fields are not damaged? We can't; further investigation showed that another form of the problem was mktime() setting the y/m/d/h/m/s fields one hour earlier than what it was given --- ie, pass it 00:00:00 of a DST forward transition date, get back neither 00:00:00 nor 01:00:00 (either of which would be plausible) but 23:00:00 of the day before! What I did about this was to coalesce all of the three or four places that use mktime just to probe for DST status into a single routine (DetermineLocalTimeZone) that is careful to pass mktime a copy of the original struct tm. No matter how brain dead the system mktime is, it can't screw up the other fields that way ;-). Then we trust tm_isdst and tm_gmtoff only if tm_isdst >= 0. Possibly we'll find that it'd be a good idea to test also for return value == -1, but the tm_isdst test seems to be sufficient for the known bug cases. > Not sure how much code we should put in to guard for cases we can't even > test (RH 5.1 is pretty old). Yeah, but the above-described behavior is reported on RH 7.1 (by two different people). I'm afraid we can't ignore that... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
