Thread: ordering RH6.1
On Wed, 08 Dec 1999, I wrote:
> I have a table with a field varchar(5), filed with right alligned numbers.
> Ordering was fine, just like we expected compared with nummeric.
>
> Starting from RedHat version 6.1 the ordening seems to remove the leading
> blanco's, what was not for use. I try different versions of postgres, as
> there are 6.5.2-1, 6.5.3-1, 6.5.3-2
> I also try to change varchar in char, and remove the index on the varchar
> field but nothing helps.
Today i take up again and see the ordering for me (third time i install
RH6.1), now
with postgres version 6.5.2-1the problem is always the same
When i do the following
CREATE TABLE BLANK (column1 varchar(5));
INSERT INTO BLANK (column1) VALUES (' 1');
INSERT INTO BLANK (column1) VALUES (' 11');
INSERT INTO BLANK (column1) VALUES (' 100');
INSERT INTO BLANK (column1) VALUES (' 2');
then:
SELECT * FROM BLANK order by column1;
I received
110011 2 --> mark also a not aligned output.
and I expected 1 2 11 100
Anybody has an idea??
Frans
Frans Van Elsacker <fve@atbib.be> writes:
> When i do the following
> CREATE TABLE BLANK (column1 varchar(5));
> INSERT INTO BLANK (column1) VALUES (' 1');
> INSERT INTO BLANK (column1) VALUES (' 11');
> INSERT INTO BLANK (column1) VALUES (' 100');
> INSERT INTO BLANK (column1) VALUES (' 2');
> then:
> SELECT * FROM BLANK order by column1;
> I received
> 1
> 100
> 11
> 2 --> mark also a not aligned output.
> and I expected
> 1
> 2
> 11
> 100
> Anybody has an idea??
Bizarre. I see the expected results under both 6.5.3 and current
development sources:
play=> SELECT * FROM BLANK order by column1;
column1
------- 1 2 11 100
(4 rows)
I wonder if this could be a LOCALE or MULTIBYTE issue. Do you have
either feature enabled in your copy, and if so what locale/encoding
do you use? (I'm running plain vanilla no-USE_LOCALE, no-MULTIBYTE
code, so that might be why I don't see anything funny...)
regards, tom lane
Tom Lane wrote: >Bizarre. I see the expected results under both 6.5.3 and current >development sources: > >play=> SELECT* FROM BLANK order by column1; >column1 >------- > 1 > 2 > 11 > 100 >(4 rows) > >I wonder if thiscould be a LOCALE or MULTIBYTE issue. Do you have >either feature enabled in your copy, and if so what locale/encoding>do you use? (I'm running plain vanilla no-USE_LOCALE, no-MULTIBYTE >code, so that might be why I don't seeanything funny...) I've tried this with both locale and multibyte enabled and with LANG=en_GB. The results are correct. (Version = 6.5.3). -- Vote against SPAM: http://www.politik-digital.de/spam/ ======================================== Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "The fear of the LORD is the instruction of wisdom,and before honour is humility." Proverbs 15:33
Tom Lane wrote: > I wonder if this could be a LOCALE or MULTIBYTE issue. Do you have > either feature enabled in your copy, and if so what locale/encoding > do you use? (I'm running plain vanilla no-USE_LOCALE, no-MULTIBYTE > code, so that might be why I don't see anything funny...) He's running the RPM distribution, which at that release has --enable-locale but no multibyte. Using the no-locale RPM's I last built, I can't reproduce his results. Frans, try out the no-locale rpm set and see if the result changes, if you please. (using wget, you would do: wget http://www.ramifordistat.net/postgres/RPMS/redhat-6.x/postgresql*-2nl.i386.rpm ) This will verify whether it is locale-related or not. I would install the locale RPMs and test for you right now, but my 6.1 machine is at home. If it is inconvenient for you to download this, let me know, and I'll try to test tonight at home -- although, I've been meaning to do just that for nearly a week now, but I haven't even fired up the machine at home in the last week. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
I download all the rpm from (http://www.ramifordistat.net/postgres/RPMS/redhat-6.x/postgresql*-2nl.i386. rpm) and have install each of them in redhat 6.1. But we received the same bad results as before. - my redhat was downloaded from a mirror site as a cd-image- and there where some older version of postgres (v 6.5.2-1) installedon our test system before. They were first uninstalled. This are the only two packages that we have running on our machine. I've choose the half-automatic graphic install of redhat. Selected some standard tools like ftp, network tool,... and choose a belgian keyboard. I have left the timezone selection as default. Strange things! I see only the following posibilities : - Our redhat cd-image is different from yours (Why ??) - influence of the olderversions - keyboard settings ??? This is our result : [postgres@dekatest pgsql]$ psql test Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute queryYou are currentlyconnected to the database: test test=> CREATE TABLE BLANK (column1 varchar(5)); CREATE test=> INSERT INTO BLANK (column1) VALUES (' 1'); INSERT 587145 1 test=> INSERT INTO BLANK (column1) VALUES (' 11'); INSERT 587146 1 test=> INSERT INTO BLANK (column1) VALUES (' 100'); INSERT 587147 1 test=> INSERT INTO BLANK (column1) VALUES (' 2'); INSERT 587148 1 test=> SELECT * FROM BLANK order by column1; column1 ------- 1 100 11 2 (4 rows) Any Idea ? greetings, Frans At 10:55 15/12/99 -0500, you wrote: >Tom Lane wrote: > >> I wonder if this could be a LOCALE or MULTIBYTE issue. Do you have >> either feature enabled in your copy, and if so what locale/encoding >> do you use? (I'm running plain vanilla no-USE_LOCALE, no-MULTIBYTE >> code, so that might be why I don't see anything funny...) > >He's running the RPM distribution, which at that release has >--enable-locale but no multibyte. > >Using the no-locale RPM's I last built, I can't reproduce his results. > >Frans, try out the no-locale rpm set and see if the result changes, if >you please. (using wget, you would do: wget >http://www.ramifordistat.net/postgres/RPMS/redhat-6.x/postgresql*-2nl.i386. rpm >) > >This will verify whether it is locale-related or not. I would install >the locale RPMs and test for you right now, but my 6.1 machine is at >home. If it is inconvenient for you to download this, let me know, and >I'll try to test tonight at home -- although, I've been meaning to do >just that for nearly a week now, but I haven't even fired up the machine >at home in the last week. > >-- >Lamar Owen >WGCR Internet Radio >1 Peter 4:11 > >
[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
On Thu, 16 Dec 1999, Lamar Owen wrote: > [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?] > 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. Ok, confirmation. On my home machine, which was upgraded to RedHat 6.1 from RedHat 6.0, I get the correct results: column1 ------ 1 11 100 2 (4 rows) > 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. Update: regression tests that fail on my 6.0-6.1 home machine: float8 and geometry -- which are normal to fail on RedHat any version. IOW, no collation problems at home! Oh, I'm running the exact same postgresql binary RPM's at home as I am running on the fresh RH 6.1 install at work. Time to dig into date and time stamps on installed RPMs versus updated RPMs. Frans, try installing RedHat 6.0 on a box, then upgrading to RH 6.1, then rerun your tests and see what happens. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Lamar Owen <lamar.owen@wgcr.org> writes:
> For RH 6.0: [ correct results ]
> 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.
Wow. Same data files, same binaries, different results. Sure looks
like the finger is pointing at 6.1's libc. (I'm assuming that the
binaries make use of a shared-library libc, not statically-linked-in
routines, right?)
> Ok, hackers:
> What library routine is used to do the order by in this case?
If you compiled with USE_LOCALE, it's strcoll(); if not, strncmp().
See varstr_cmp() in src/backend/utils/adt/varlena.c.
> 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.)
OK...
Your regression failures show collation problems in all three of bpchar,
varchar, and name. (But curiously, not for text ... hmm ...). bpchar
and varchar both use varstr_cmp(), but namelt just calls strncmp
unconditionally --- see adt/name.c. So the evidence is looking very
strong that strncmp has got some kind of problem on RH 6.1.
regards, tom lane
At 07:20 PM 12/16/99 -0500, Lamar Owen wrote: >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! I know that AOLserver works on 6.0 and has problems on 6.1 (a 2.2.12 kernel) and works again if you upgrade your 6.1 to a 2.2.13 kernel. (have to use "-i" on 6.1 or it won't work, though I forget offhand what "-i" does for AOLserver) This ain't specific help but at least PostgreSQL's not alone in having weird problems with RH 6.* releases. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
On Thu, 16 Dec 1999, Tom Lane wrote: > Lamar Owen <lamar.owen@wgcr.org> writes: > Wow. Same data files, same binaries, different results. Sure looks > like the finger is pointing at 6.1's libc. (I'm assuming that the > binaries make use of a shared-library libc, not statically-linked-in > routines, right?) Right. > Your regression failures show collation problems in all three of bpchar, > varchar, and name. (But curiously, not for text ... hmm ...). bpchar > and varchar both use varstr_cmp(), but namelt just calls strncmp > unconditionally --- see adt/name.c. So the evidence is looking very > strong that strncmp has got some kind of problem on RH 6.1. More information: the LOCALE enabled-binaries act the same way. So, there's an issue with both strcoll and strncmp. What gets me is that it works perfectly fine on my RedHat 6.1 box that was upgraded from RedHat 6.0 -- but it does not work fine at all on a box that I did a fresh install on today -- from the same CD I did the upgrade. Hmmm.... -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 > > regards, tom lane
On Thu, 16 Dec 1999, Lamar Owen wrote: > More information: the LOCALE enabled-binaries act the same way. So, there's an > issue with both strcoll and strncmp. What gets me is that it works perfectly > fine on my RedHat 6.1 box that was upgraded from RedHat 6.0 -- but it does not > work fine at all on a box that I did a fresh install on today -- from the same > CD I did the upgrade. Any differences in the environment variables maybe? Cristian -- ---------------------------------------------------------------------- Cristian Gafton -- gafton@redhat.com -- Red Hat, Inc. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~UNIX is user friendly. It's just selective about whoits friends are.
On Thu, 16 Dec 1999, Lamar Owen wrote: > On Thu, 16 Dec 1999, Lamar Owen wrote: > > > [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?] > > 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. > > Ok, confirmation. On my home machine, which was upgraded to RedHat 6.1 from > RedHat 6.0, I get the correct results: More information: it seems that the i18n support is the cause of this. If you remove or rename the file /etc/sysconfig/i18n and restart, then even the fresh RedHat 6.1 install provides the correct results for this query. This file, I think, is created during a fresh installation of RH 6.1 -- it doesn't seem to belong to any RPM. An upgrade wouldn't create this file..... (Jeff? Cristian? am I right on this?) Running regression, I get the float8 and geometry failures, but I now get an opr_sanity failure -- but the other collation failures are gone. The opr_sanity failure diff: *** expected/opr_sanity.out Wed May 12 11:02:34 1999 --- results/opr_sanity.out Thu Dec 16 22:39:45 1999 *************** *** 48,56 **** (p1.proargtypes[0] < p2.proargtypes[0]); proargtypes|proargtypes -----------+----------- - 25| 1043 1042| 1043 ! (2 rows) QUERY: SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 --- 48,55 ---- (p1.proargtypes[0] < p2.proargtypes[0]); proargtypes|proargtypes -----------+----------- 1042| 1043 ! (1 row) QUERY: SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 HOWEVER, after doing an initdb and rerunning regression, this test no longer fails. FWIW. I seems that charmap (i18n) rears its ugly head even if locale doesn't. -- Lamar Owen WGCR Internet Radio
On Thu, 16 Dec 1999, Cristian Gafton wrote: > On Thu, 16 Dec 1999, Lamar Owen wrote: > > > More information: the LOCALE enabled-binaries act the same way. So, there's an > > issue with both strcoll and strncmp. What gets me is that it works perfectly > > fine on my RedHat 6.1 box that was upgraded from RedHat 6.0 -- but it does not > > work fine at all on a box that I did a fresh install on today -- from the same > > CD I did the upgrade. > > Any differences in the environment variables maybe? In a nutshell, yes. /etc/sysconfig/i18n on the fresh install sets LANG, LC_ALL, and LINGUAS all to be "en_US". The upgraded machine at home doesn't have an /etc/sysconfig/i18n -- nor does the RH 6.0 box. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
> > Failures: float8, geometry, select implicit, select having, and select > views. The regress.out and regression.diffs are attached. Float8 and > geometry are normal. More problem data... We installed RH 6.1 fresh, then installed pgsql 6.5.2 from tar.gz. Failures on the following regression tests: float8,geometry, opr_sanity, sanity_check, random, and misc. On a hunch from this thread, I then removed all postgresql-related RPM packages (with 'rpm -e'), rebuilt pgsql 6.5.2, andall regression tests passed (except float8 and geometry, which is normal). I've also noticed some new (and possibly related?) RH 6.1 wierdness with a fairly mature perl module, Date::Manip-5.35, thatwasn't showing up on RH 6.0. It is now failing the first time it attempts to ascertain the timezone, and then appearsto succeed everytime thereafter for a given process (and TZ is clearly set in the configuration for the module aswell as showing up with the 'date' command). The RPM removal above had no effect on that problem. Cheers, Ed Loehr
Lamar Owen wrote: > More information: it seems that the i18n support is the cause of this. If you > remove or rename the file /etc/sysconfig/i18n and restart, then even the fresh > RedHat 6.1 install provides the correct results for this query. This file, I > think, is created during a fresh installation of RH 6.1 -- it doesn't seem to > belong to any RPM. An upgrade wouldn't create this file..... (Jeff? Cristian? > am I right on this?) Still more data... After renaming the file /etc/sysconfig/i18n and rebooting, the perl module Date::Manip timezone lookup failure described previously has ceased. It seems there may be at least two issues, possibly related. My pgsql regression tests were fixed by nuking the pgsql-related RPMs, but that didn't fix the Date::Manip perl module problem. Renaming i18n did. I didn't test the SELECT query in question prior to making these changes, but that SELECT query does indeed now return expected results. Cheers, Ed Loehr
> After renaming the file /etc/sysconfig/i18n and rebooting, the perl module > Date::Manip timezone lookup failure described previously has ceased. I spoke too soon. Timezone problem is not fixed by this as it first appeared. Cheers, Ed Loehr