Bug #102685 ST_Astext seems to invert geometry Blob text
Submitted: 22 Feb 2021 10:52 Modified: 6 Mar 2021 10:56
Reporter: Mx Dog Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: GIS Severity:S2 (Serious)
Version: OS:Windows (win 10 )
Assigned to: CPU Architecture:Any
Tags: Function error, Geometry, Geospacial

[22 Feb 2021 10:52] Mx Dog
Description:
Bug #94382 Possible Repeat but I do not believe Workbench is the problem 

Function ST_AsText or MySql-Server server seems to be randomly reversing data stored in a geometry blob. in my case a column has a single point in latitude,longitude order SRID 4326 an example blob in WKT  is :
POINT (41.640771092229699 -86.292634483638253) ***This is the order i need it **

a sample returned by a query : 
POINT(-86.89296090224927 41.71869284609824) *** Reversed from actual value ***

a sample of some randomness :

# Lat_Home, Long_Home, Lat_Destination, Long_Destination
'-86.89296090224927', '41.76869284609824', '41.71369284609824', '-86.89296090224927'
One set reversed the other correct.  

How to repeat:

set up as explained and run queries on data in format as shown 

Suggested fix:
I THINK this became an issue when the functions where changed from 5x to 8x that disallowed nested functions and this could very well just be a symptom of that i.e the documentation/examples have not caught up and we are doing this wrong ..if that is the case then there should be an error invoked to the client...

I did notice reading over the internatioal-WKT specs that they seem to use longitude then latitude ( could there be a rule conflict in the code ? )     

But I think that somewhere or another someone is flipping a bit on the byte arrays  in the parsing from WKB to WKT that would explain how we are getting perfect flips 

ie. counter for array is supposed to be 0 for the first value ..somewhere in the parsing it gets changed and the array is now indexed plus one and the other value is picked up from....? Not sure how that could happen I mean you are just reading a binary string and converting to acsii with a rule table ? 
but I am just guessing from what I read here on the structure ...

https://dev.mysql.com/doc/refman/8.0/en/gis-data-formats.html

Somehow those are getting flipped which you would think would drop values not reverse them .
[22 Feb 2021 14:18] MySQL Verification Team
Hi Mr. Dog,

Thank you for your bug report.

However, we need more data from you. We need a fully repeatable test case for MySQL 8.0.23, executed with mysql CLI program and not with Workbench.

Hence, send us a test case as described above, executed only with mysql.exe and without pointers to any differences between 5.7 and 8.0.

Please, use only release 8.0.23 of our MySQL server and client.

We are waiting on your feedback.
[23 Feb 2021 16:10] Mx Dog
SQL To Generate the test Database and data I have

Attachment: ST_AsText_Test_2021-02-23.sql (application/octet-stream, text), 3.36 KiB.

[23 Feb 2021 16:11] Mx Dog
Console screen shot showing problem

Attachment: MySQ_ST_AsText.PNG (image/png, text), 11.58 KiB.

[23 Feb 2021 16:15] Mx Dog
Added the requested Data. There are a couple of comments in the .SQL file that may or may not be useful.
[25 Feb 2021 13:41] MySQL Verification Team
Hi Mr. Dog,

Thank you for your bug report.

However, this is not a bug. If you have read read our Reference Manual to the last detail, you would have noticed that you have to use some of the options to get the order of longitude and latitude in the desired order.

When I ran your query, as you have written it, without using optional argument, I have got the identical result as you:

BlueChip	        POINT(-86.89296090224927 41.71869284609824)
4Winds-SB	POINT(-86.29263448363825 41.6407710922297)
4Winds-NB	POINT(-86.70830041779814 41.78173296422582)
4Winds-DP	POINT(-86.16231096249841 41.945720369297725)

However, when I ran it with a proper option, I have got the result that you wanted to see:

BlueChip	        POINT(41.71869284609824 -86.89296090224927)
4Winds-SB	POINT(41.6407710922297 -86.29263448363825)
4Winds-NB	POINT(41.78173296422582 -86.70830041779814)
4Winds-DP	POINT(41.945720369297725 -86.16231096249841)

Hence, this is not a bug.
[6 Mar 2021 9:55] Mx Dog
OK...you say I have to add the correct options ..well you didn't show what you did to get those results but that is not my point here at all...manipulating the data after the fact is not what I am pointing out, it is the fact that something is transposing right to left and left to right for those 2 fields.

For instance just now I went to add another place in that table ..copied right off of google maps as lat/long. 

update bleahbleah
 set GeoLoc = ST_geomfromtext(ST_astext(Point(41.69410516729817,-87.5071310591952)),4326)

and this is what ends up in the BLOB

POINT (-87.507131059195203 41.694105167298169)

...So there is some translation-juxtaposition going on there. Nowhere is there anything IMPLIED that those 2 numbers should be in anything but the order in which I listed them. So if that is not a bug it is a damn confusing and frankly silly way to treat what should be a very simple function. Insert POINT(#X,#Y) into that blob as Point(X,Y) NOT for some inexplicable reason POINT(Y,X). With no options involved it should just put them in as they are listed without regard to anything else. 

And by the way I did read the manuals for like 3 days dinking with this trying to get these in the order I want them ( i.e. copied lat/long ). So if 
SRS geometry information as points are treated as LAT,LONG there should be a big disclaimer in the docs that point this out..this is the closest I have found. which kinda,sorta might imply that.  

 FROM: https://dev.mysql.com/doc/refman/8.0/en/gis-wkt-functions.html#function_st-pointfromtext
<QUOTE>
 For geographic SRS geometry arguments, if any argument has a longitude or latitude that is out of range, an error occurs:

    If a longitude value is not in the range (−180, 180], an ER_LONGITUDE_OUT_OF_RANGE error occurs.

    If a latitude value is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error occurs. 

Ranges shown are in degrees. If an SRS uses another unit, the range uses the corresponding values in its unit. The exact range limits deviate slightly due to floating-point arithmetic. 
</QUOTE>

all of which is beside the point. I do realize I can just switch the numbers and make it work but why should that be the answer? Maybe a server wide format for GEO i.e set ST_SET_GEOCORD_LAT_LONG if LONG/LAT is the standard ? 

The point again is it should go into the blob as listed not interpreted other then size constraints as default and never should the number order be changed from what is listed WITHOUT an option being explicitly declared.
[6 Mar 2021 10:56] Mx Dog
You say not a bug ..let me put this in terms of another language c# or any other really.

static void Main(){
  string[] point = new string[4] {"4326","POINT", "41.639683386223453","-87.418526718186897" };
  Console.WriteLine(point[1] + "(" + point[2] + " "+point[3] + ")");
  Console.WriteLine(point[1] + "(" + point[3] + " "+point[2] + ")");
}
CORRECT OUT  : POINT(41.639683386223453 -87.418526718186897)
A BUGGED OUT : POINT(-87.418526718186897 41.639683386223453)

Do you see why this sure looks like a bug to me ?