MySQL Bugs: #40239: Order by on a select including geometry type columns gives empty result on Sparc

Bug #40239	Order by on a select including geometry type columns gives empty result on Sparc
Submitted:	22 Oct 2008 10:32	Modified:	18 Mar 2009 12:18
Reporter:	Bjørn Munch	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Tests	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Solaris (SPARC, 32-bit)
Assigned to:		CPU Architecture:	Any
Tags:	gis, order

Description:
This is seen with 5.0.45 in OpenSolaris (tested on snv_95), as well as the 5.0.67 which I built for integration into Web Stack, run on S10U5.

Tables are created thus (in test archive_gis):

   CREATE TABLE gis_point  (fid INTEGER, g POINT);

Then it's filled with a few rows, and we do

   SELECT fid, AsText(g) FROM gis_point ORDER by fid;

This gives no result!  But if the order by clause is removed, it does!

The same is seen with all selects with order by from all the tables in
this test that use geometry types like POINT, POLYGON etc.

If I add a second integer field to the table and modify the select to
include fid and this new column but not 'g', it works.  So, it's only
if the select refers to one of the geometry type columns that the
order by gives an empty result.

Someone has also reported this on snv_79, he confirms in email that
this too was only on Sparc:

http://www.opensolaris.org/jive/thread.jspa?messageID=187317&#187317

I have also found one incident reported with 5.0.41 on PowerPC:

http://forums.mysql.com/read.php?117,153069,153069

Both of these seem to have experienced the same bug.

How to repeat:
mysql-test-run.pl archive_gis consistenty fails

There are some other tests running the same queries but those do not
fail; there must be something about the context of this particular
test which triggers it.

NB this is only on Sparc; the exact same test on x86 succeeds.

Thank you for the report.

I can not repeat described behavior with mysql-5.0.67-solaris10-sparc-64bit on Solaris 10. Please indicate accurate package name you use or provide configure string if you compiled server yourself. Also please check in archive_gis fails in same fashion like in bug #38959

This was not found using any mysql provided binaries. I compiled my own 5.0.67 for integration into Web Stack and then confirmed the error using 5.0.45 as it comes bundled with OpenSolaris (under /usr/mysql/5.0).

However, I now see that with the OpenSolaris binaries, it does *not* fail with the 64-bit binaries, only 32-bit. This is also the case with 5.0.67 which came in build 100.

Config options (excluding directory names) from config.log:

--with-mysqld-libs=-lmtmalloc --with-client-ldflags=-static --with-mysql-ldflags=-static --with-server-suffix= --enable-shared --enable-thread-safe-client --with-named-curses=-lcurses --with-pic --with-big-tables --with-yassl --with-readline --with-archive-storage-engine --with-blackhole-storage-engine --with-csv-storage-engine --with-example-storage-engine --with-federated-storage-engine --with-innodb --with-extra-charsets=complex --enable-local-infile --with-ndbcluster 

Compiler is Sun Studio 11 with options (32-bit) -xarch=v8 -xregs=no%appl -W2,-x
wrap_int -xspace -W0,-Lt -xO4 -Xa -xildoff -xc99=all -xnorunpath -m32 -xc99=none

Just to make it clear: in my testing of 5.0.67 on S10, *both* 32-bit and 64-bit fail, it's only in OpenSolaris that it's specific to 32-bit.

The compiler flags might be slightly different in the two builds.

I ran in to the same problem only my case is partially different. I am applying the ORDER BY on a column of type FLOAT . The query is a simple one in the following format:

SELECT * FROM tablename WHERE integerColumn = 1 ORDER BY floatColumn ASC LIMIT 1

Without the ORDER BY this query gives a result, with the ORDER BY clause this query returns an empty resultset. I 'fixed' this problem by rejoining the same table on it's primary key like this:

SELECT t1.* FROM tablename AS t1 tablename AS t2 WHERE t1.integerColumn = 1 AND t1.primaryKey = t2.primaryKey ORDER BY t2.floatColumn 

This is of course a strange and incorrect fix. I have no idea what is causing the problem, could it be a bad index?

I am using Windows with MySQL 5.0.51b-community-nt .

Maybe this is a completely different bug?

Test case used:

SET storage_engine=archive;
CREATE TABLE gis_point (fid INTEGER, g POINT);
INSERT INTO gis_point VALUES
(101, PointFromText('POINT(10 10)')),
(102, PointFromText('POINT(20 10)')),
(103, PointFromText('POINT(20 20)')),
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
SELECT fid, AsText(g) FROM gis_point ORDER by fid;

Bjorn,

thank you for the feedback. I could not repeat described behavior after compiling current version 5.0.77 using options provided. Please try this version and if problem still exists provide repeatable test case like i did in previous comment.

Reinoud,

thank you for the feedback. This looks like different problem. Additionally version 5.0.51 is old and many bugs were fixed since. Please try version 5.0.77 and if problem still exists open new bug report.

I just tried with the newest 5.0.79 binaries and did not see the problem. So it may have been fixed by some other change or be dependent on some small detail.

Thank you for the feedback.

Closed as "Can't repeat" because last comment.