Re: [HACKERS] ordering RH6.1
| От | Lamar Owen |
|---|---|
| Тема | Re: [HACKERS] ordering RH6.1 |
| Дата | |
| Msg-id | 385981DF.9762BFC8@wgcr.org обсуждение исходный текст |
| Ответ на | Re: [HACKERS] ordering RH6.1 (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: [HACKERS] ordering RH6.1
(Lamar Owen <lamar.owen@wgcr.org>)
Re: [HACKERS] ordering RH6.1 (Tom Lane <tgl@sss.pgh.pa.us>) Re: [HACKERS] ordering RH6.1 (Don Baccus <dhogaza@pacifier.com>) |
| Список | pgsql-hackers |
[Cristian, Jeff: we have a problem here. RedHat 6.1 Install versus
RedHat 6.0 upgraded to 6.1 behaves differently. Ideas of where to start
looking?]
Frans Van Elsacker wrote:
> But we received the same bad results as before.
> column1
> -------
> 1
> 100
> 11
> 2
> (4 rows)
>
> Any Idea ?
Ok, I bit the bullet and spent the whole day (plus or minus a couple of
hours) putting together a test bed. I have three machines in this
testbed:
1.) My production server, running Mandrake 5.3, Postgresql 6.5.3-2nl;
2.) My backup server, running RedHat 6.0, Postgresql 6.5.3-2nl;
3.) My development server, freshly installed with RH 6.1 + all updates,
PostgreSQL 6.5.3-2nl.
I have now reproduced the results. HOWEVER, my home machine didn't
reproduce the earlier results, and it is RedHat 6.1 (an upgrade from RH
6.0).
For Mandrake 5.3:
column1
-------
1
2
11
100
(4 rows)
For RH 6.0: ditto Mandrake 5.3.
for RH 6.1 (fresh install):
column1
-------
1
100
11
2
So, I moved the physical database structure over from the 6.1 machine to
the 6.0 machine and redid the select: the right results.
The RedHat 6.0 machine is running the same exact postgres binaries that
the RedHat 6.1 machine is running -- the 6.5.3-2nl rpms were built on my
home RedHat 6.1 machine. The Mandrake 5.3 machine is running the RedHat
5.2 binaries built by the alternate boot set on the development server
(which is why it took most of the day to set things up....).
Ok, hackers:
What library routine is used to do the order by in this case?
I'm going to retry this exact set of queries again at home -- I wasn't
able to reproduce the last set of results -- but we'll see what happens
here.
Strange.
I'll see what I can find -- this also explains some strange regression
results I was mailed awhile back. In fact, let's try regression on the
RH 6.1 fresh install.... AND I AM GETTING FAILURES THAT I HAVE NEVER
GOTTEN AT HOME ON MY UPGRADE REDHAT 6.1!
Recap while I'm waiting for regression to finish:
The fresh install of RedHat 6.1 is from the exact same CD that I
upgraded my home box from RH 6.0. The ONLY difference is the fresh
install versus the upgrade -- same versions of PostgreSQL. I am going to
double check regression at home, but I have not seen these results
before, and I distinctly remember running regression at home. I'll keep
you all updated.
[Nine minutes later]
Failures: float8, geometry, select implicit, select having, and select
views. The regress.out and regression.diffs are attached. Float8 and
geometry are normal.
Looking at the regression diffs, it is obvious that there is a collation
problem here. But where is this collation sequence problem coming from?
(Note that the 6.5.3-2nl RPMs are built without locale support.)
I'm going to go digging into a diff of my home machine versus this new
RH 6.1 install.
--
Lamar Owen
WGCR Internet Radio
*** expected/float8.out Sat Jan 23 19:12:59 1999
--- results/float8.out Thu Dec 16 19:02:15 1999
***************
*** 189,201 ****
QUERY: SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
ERROR: Bad float8 input format -- overflow
QUERY: SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
! ERROR: pow() result is out of range
QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ;
ERROR: can't take log of zero
QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ;
ERROR: can't take log of a negative number
QUERY: SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
! ERROR: exp() result is out of range
QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
ERROR: float8div: divide by zero error
QUERY: SELECT '' AS five, FLOAT8_TBL.*;
--- 189,217 ----
QUERY: SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
ERROR: Bad float8 input format -- overflow
QUERY: SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
! bad|?column?
! ---+--------
! |0
! |NaN
! |NaN
! |NaN
! |NaN
! (5 rows)
!
QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ;
ERROR: can't take log of zero
QUERY: SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ;
ERROR: can't take log of a negative number
QUERY: SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
! bad| ?column?
! ---+--------------------
! | 1
! |7.39912306090513e-16
! | 0
! | 0
! | 1
! (5 rows)
!
QUERY: SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
ERROR: float8div: divide by zero error
QUERY: SELECT '' AS five, FLOAT8_TBL.*;
----------------------
*** expected/geometry.out Sun Dec 13 18:49:18 1998
--- results/geometry.out Thu Dec 16 19:02:21 1999
***************
*** 112,118 ****
|(-5,-12) |[(10,-10),(-3,-4)] |(-1.60487804878049,-4.64390243902439)
|(10,10) |[(10,-10),(-3,-4)] |(2.39024390243902,-6.48780487804878)
|(0,0) |[(-1000000,200),(300000,-40)]|(0.0028402365895872,15.384614860264)
! |(-10,0) |[(-1000000,200),(300000,-40)]|(-9.99715942258202,15.3864610140473)
|(-3,4) |[(-1000000,200),(300000,-40)]|(-2.99789812267519,15.3851688427303)
|(5.1,34.5)|[(-1000000,200),(300000,-40)]|(5.09647083221496,15.3836744976925)
|(-5,-12) |[(-1000000,200),(300000,-40)]|(-4.99494420845634,15.3855375281616)
--- 112,118 ----
|(-5,-12) |[(10,-10),(-3,-4)] |(-1.60487804878049,-4.64390243902439)
|(10,10) |[(10,-10),(-3,-4)] |(2.39024390243902,-6.48780487804878)
|(0,0) |[(-1000000,200),(300000,-40)]|(0.0028402365895872,15.384614860264)
! |(-10,0) |[(-1000000,200),(300000,-40)]|(-9.99715942258202,15.3864610140472)
|(-3,4) |[(-1000000,200),(300000,-40)]|(-2.99789812267519,15.3851688427303)
|(5.1,34.5)|[(-1000000,200),(300000,-40)]|(5.09647083221496,15.3836744976925)
|(-5,-12) |[(-1000000,200),(300000,-40)]|(-4.99494420845634,15.3855375281616)
***************
*** 409,433 ****
QUERY: SELECT '' AS six, polygon(f1)
FROM CIRCLE_TBL;
six|polygon
!
---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
!
|((-3,0),(-2.59807621135332,1.5),(-1.5,2.59807621135332),(-1.83690953073357e-16,3),(1.5,2.59807621135332),(2.59807621135332,1.5),(3,3.67381906146713e-16),(2.59807621135332,-1.5),(1.5,-2.59807621135332),(5.5107285922007e-16,-3),(-1.5,-2.59807621135332),(-2.59807621135332,-1.5))
!
|((-99,2),(-85.6025403784439,52),(-49,88.6025403784439),(0.999999999999994,102),(51,88.6025403784439),(87.6025403784439,52),(101,2.00000000000001),(87.6025403784439,-48),(51,-84.6025403784438),(1.00000000000002,-98),(-49,-84.6025403784439),(-85.6025403784438,-48))
!
|((-4,3),(-3.33012701892219,5.5),(-1.5,7.33012701892219),(1,8),(3.5,7.33012701892219),(5.33012701892219,5.5),(6,3),(5.33012701892219,0.500000000000001),(3.5,-1.33012701892219),(1,-2),(-1.5,-1.33012701892219),(-3.33012701892219,0.499999999999998))
!
|((-2,2),(-1.59807621135332,3.5),(-0.5,4.59807621135332),(1,5),(2.5,4.59807621135332),(3.59807621135332,3.5),(4,2),(3.59807621135332,0.500000000000001),(2.5,-0.598076211353315),(1,-1),(-0.5,-0.598076211353316),(-1.59807621135332,0.499999999999999))
!
|((90,200),(91.3397459621556,205),(95,208.660254037844),(100,210),(105,208.660254037844),(108.660254037844,205),(110,200),(108.660254037844,195),(105,191.339745962156),(100,190),(95,191.339745962156),(91.3397459621556,195))
!
|((0,0),(13.3974596215561,50),(50,86.6025403784439),(100,100),(150,86.6025403784439),(186.602540378444,50),(200,1.22460635382238e-14),(186.602540378444,-50),(150,-86.6025403784438),(100,-100),(50,-86.6025403784439),(13.3974596215562,-50))
(6 rows)
QUERY: SELECT '' AS six, polygon(8, f1)
FROM CIRCLE_TBL;
six|polygon
!
---+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
!
|((-3,0),(-2.12132034355964,2.12132034355964),(-1.83690953073357e-16,3),(2.12132034355964,2.12132034355964),(3,3.67381906146713e-16),(2.12132034355964,-2.12132034355964),(5.5107285922007e-16,-3),(-2.12132034355964,-2.12132034355964))
!
|((-99,2),(-69.7106781186548,72.7106781186548),(0.999999999999994,102),(71.7106781186547,72.7106781186548),(101,2.00000000000001),(71.7106781186548,-68.7106781186547),(1.00000000000002,-98),(-69.7106781186547,-68.7106781186548))
!
|((-4,3),(-2.53553390593274,6.53553390593274),(1,8),(4.53553390593274,6.53553390593274),(6,3),(4.53553390593274,-0.535533905932737),(1,-2),(-2.53553390593274,-0.535533905932738))
!
|((-2,2),(-1.12132034355964,4.12132034355964),(1,5),(3.12132034355964,4.12132034355964),(4,2),(3.12132034355964,-0.121320343559642),(1,-1),(-1.12132034355964,-0.121320343559643))
!
|((90,200),(92.9289321881345,207.071067811865),(100,210),(107.071067811865,207.071067811865),(110,200),(107.071067811865,192.928932188135),(100,190),(92.9289321881345,192.928932188135))
!
|((0,0),(29.2893218813452,70.7106781186548),(100,100),(170.710678118655,70.7106781186548),(200,1.22460635382238e-14),(170.710678118655,-70.7106781186547),(100,-100),(29.2893218813453,-70.7106781186548))
(6 rows)
QUERY: SELECT '' AS six, circle(f1, 50.0)
--- 409,433 ----
QUERY: SELECT '' AS six, polygon(f1)
FROM CIRCLE_TBL;
six|polygon
!
---+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
!
|((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359017709e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718035418e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077053127e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138))
!
|((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795))
!
|((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887966),(-3.33012701896897,0.500000000081028))
!
|((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.59807621137373),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048617))
!
|((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162))
!
|((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239345139e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795))
(6 rows)
QUERY: SELECT '' AS six, polygon(8, f1)
FROM CIRCLE_TBL;
six|polygon
!
---+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
!
|((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359017709e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718035418e-11),(2.12132034353258,-2.12132034358671),(-4.59307077053127e-11,-3),(-2.12132034359753,-2.12132034352175))
!
|((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181134),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917))
!
|((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586))
!
|((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752))
!
|((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261))
!
|((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181134),(200,-1.02068239345139e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917))
(6 rows)
QUERY: SELECT '' AS six, circle(f1, 50.0)
----------------------
*** expected/select_implicit.out Sun Aug 8 17:39:34 1999
--- results/select_implicit.out Thu Dec 16 19:04:51 1999
***************
*** 14,23 ****
--------+-----
AAAA | 2
BBBB | 2
- CCCC | 2
- XXXX | 1
bbbb | 1
cccc | 2
(6 rows)
QUERY: SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c;
--- 14,23 ----
--------+-----
AAAA | 2
BBBB | 2
bbbb | 1
+ CCCC | 2
cccc | 2
+ XXXX | 1
(6 rows)
QUERY: SELECT count(*) FROM test_missing_target GROUP BY test_missing_target.c;
***************
*** 25,34 ****
-----
2
2
- 2
- 1
1
2
(6 rows)
QUERY: SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
--- 25,34 ----
-----
2
2
1
2
+ 2
+ 1
(6 rows)
QUERY: SELECT count(*) FROM test_missing_target GROUP BY a ORDER BY b;
***************
*** 87,96 ****
--------+-----
AAAA | 2
BBBB | 2
- CCCC | 2
- XXXX | 1
bbbb | 1
cccc | 2
(6 rows)
QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 3;
--- 87,96 ----
--------+-----
AAAA | 2
BBBB | 2
bbbb | 1
+ CCCC | 2
cccc | 2
+ XXXX | 1
(6 rows)
QUERY: SELECT c, count(*) FROM test_missing_target GROUP BY 3;
----------------------
*** expected/select_having.out Wed Sep 2 19:37:11 1998
--- results/select_having.out Thu Dec 16 19:04:52 1999
***************
*** 30,37 ****
GROUP BY c HAVING count(*) > 2 OR min(a) = max(a);
c |max
--------+---
- XXXX | 0
bbbb | 5
(2 rows)
QUERY: DROP TABLE test_having;
--- 30,37 ----
GROUP BY c HAVING count(*) > 2 OR min(a) = max(a);
c |max
--------+---
bbbb | 5
+ XXXX | 0
(2 rows)
QUERY: DROP TABLE test_having;
----------------------
*** expected/select_views.out Mon Feb 23 08:59:17 1998
--- results/select_views.out Thu Dec 16 19:05:21 1999
***************
*** 411,416 ****
--- 411,430 ----
I- 580 | 21
I- 580 | 22
I- 580 | 22
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 2
+ I- 580/I-680 Ramp| 4
+ I- 580/I-680 Ramp| 4
+ I- 580/I-680 Ramp| 4
+ I- 580/I-680 Ramp| 4
+ I- 580/I-680 Ramp| 5
+ I- 580/I-680 Ramp| 6
+ I- 580/I-680 Ramp| 6
+ I- 580/I-680 Ramp| 6
I- 580 Ramp| 2
I- 580 Ramp| 2
I- 580 Ramp| 2
***************
*** 661,680 ****
I- 580 Ramp| 8
I- 580 Ramp| 8
I- 580 Ramp| 8
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 2
- I- 580/I-680 Ramp| 4
- I- 580/I-680 Ramp| 4
- I- 580/I-680 Ramp| 4
- I- 580/I-680 Ramp| 4
- I- 580/I-680 Ramp| 5
- I- 580/I-680 Ramp| 6
- I- 580/I-680 Ramp| 6
- I- 580/I-680 Ramp| 6
I- 680 | 2
I- 680 | 2
I- 680 | 2
--- 675,680 ----
----------------------
Вложения
В списке pgsql-hackers по дате отправления:
Следующее
От: Peter EisentrautДата:
Сообщение: Re: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING