Re: [HACKERS] ordering RH6.1 - Mailing list pgsql-hackers

From Lamar Owen
Subject Re: [HACKERS] ordering RH6.1
Date
Msg-id 385981DF.9762BFC8@wgcr.org
Whole thread Raw
In response to Re: [HACKERS] ordering RH6.1  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: [HACKERS] ordering RH6.1
Re: [HACKERS] ordering RH6.1
Re: [HACKERS] ordering RH6.1
List 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 ----

----------------------


Attachment

pgsql-hackers by date:

Previous
From: Frans Van Elsacker
Date:
Subject: Re: [HACKERS] ordering RH6.1
Next
From: Peter Eisentraut
Date:
Subject: Re: [HACKERS] SELECT ... AS ... names in WHERE/GROUP BY/HAVING