ST_X and ST_Y don't work as expected

[doublebyte1]

I want to use the ST_X() and ST_Y() geometry functions, in order to get the x,y coordinates of a point, and build a string from it. If I use them within a select, like this:

select ST_X(geom), ST_Y(geom) from tweets where geom is not null limit 10; 

They work is expected:

2.444087        41.653078
1.108611        41.190833
0.622647        41.630436
2.212209        41.405437
2.132334        41.372308
0.42043 49.337155
0.42043 49.337155
2.007902        41.318285
2.183024        41.405962
2.20352 41.45851

In order to build the string, I make the query like this:

select concat(ST_Y(geom), "," , ST_X(geom)) from tweets where geom is not null limit 10;

And as a result, it overrides the second call (ST_X), with the results of the first call (ST_Y):

41.653078,41.653078
41.190833,41.190833
41.630436,41.630436
41.405437,41.405437
41.372308,41.372308
49.337155,49.337155
49.337155,49.337155
41.318285,41.318285
41.405962,41.405962
41.45851,41.45851

If I reverse, the call like this:

select concat(ST_X(geom), "," , ST_Y(geom)) from tweets where geom is not null limit 10;

,the same thing applies and I have the ST_X coordinate repeated in place of ST_Y:

2.444087,2.444087
1.108611,1.108611
0.622647,0.622647
2.212209,2.212209
2.132334,2.132334
0.42043,0.42043
0.42043,0.42043
2.007902,2.007902
2.183024,2.183024
2.20352,2.20352

Could this be a bug?

[climbage]

Are you on the same version as you were before? We've seen this before but have never been able to repro to figure out what the issue is.

[doublebyte1]

1. Hadoop 2.6.0
2. Hive 0.14.0
3. esri-tools for hadoop 2.0. (the link you passed me on the other issue)

[climbage]

Thanks. I just realized you had already given us this information in the last issue. Would you mind testing out a couple of jar files if I try a bunch of different changes to see what the issue is?

[doublebyte1]

No problem: glad to help

[climbage]

Great. I'll put something together.

[climbage]

@doublebyte1 Are you able to compile this project with ant? I think I might actually have an idea. If not, I'll send you a jar.

[climbage]

Also, this is supposed to be fixed in Hive 14, but you might try setting hive.cache.expr.evaluation to false and see if that fixes it.

[doublebyte1]

@climbage which project would you like me to compile?

[doublebyte1]

I confirm that setting hive.cache.expr.evaluation to FALSE, removes the bug.

[climbage]

Oh, that's definitely a bug in Hive then. There is still the question of whether we are doing something different that causes this bug to happen.

Thanks for testing that out.

[randallwhitman]

For cross-reference, I assume the Hive issue referred to is [HIVE-7314] Wrong results of UDF when hive.cache.expr.evaluation is set. The relevant condition appears to be the nesting of UDFs, in this case concatenation around ST_Geometry calls. A fix/workaround of changing the getDisplayString function in the UDF is mentioned on the hive mailing list (another thread) and StackOverflow. However, it appears that is applicable to GenericUDF only, not to basic UDF.

[randallwhitman]

Does the issue reproduce with spatial-framework-for-hadoop master? On our development cluster, now with Hive-.14 and Hadoop-2.6 (HDP-2.2), I get:

hive> select concat(ST_X(shape), "," , ST_Y(shape))   from randall.test15e3; 
15.0,5.0
5.0,35.0
23.0,23.0
32.0,22.0
37.0,28.0
22.0,33.0
28.0,38.0
34.0,34.0
62.0,19.0
71.0,14.0
75.0,25.0
65.0,35.0
55.0,49.0
65.0,45.0
45.0,66.0

[doublebyte1]

Thanks for the feedback, I haven't use this for a while. I'll come back to you, when I do it again.

[randallwhitman]

Does the issue reproduce with spatial-framework-for-hadoop v1.2?

[doublebyte1]

Sorry @randallwhitman : I no longer have the development environment to try this