Query on geo_pos column by non primary key

CrateDB
1

Hi,

During the upgrade from 3.3.5 to 4.2.1, I’ve run into the issue where I’m getting latitude and longitude distorted when querying by a column that is not the primary key.
To illustrate the problem, I’ve created the following gist:

You can run it on a clean Crate.IO instance and it will create a table with a primary key column, a string column, and a geo_point column, and then it will insert a document.

There are two tests, one querying that entry by primary key, and one by the other (non-primary key) column.
The test with primary key passes as expected, however the other test fails because the longitude/latitude precision got changed. Here’s are the log snippets:

This is correct:

2020-08-13 16:14:25,631 INFO  [Test worker] GeoPointPrecisionTest: Query done, result found
2020-08-13 16:14:25,632 INFO  [Test worker] GeoPointPrecisionTest: Position Object: (11.1543467,50.52103)
2020-08-13 16:14:25,634 INFO  [Test worker] GeoPointPrecisionTest: Position String: (11.1543467,50.52103)
2020-08-13 16:14:25,634 INFO  [Test worker] GeoPointPrecisionTest: Position PGobject: (11.1543467,50.52103)
2020-08-13 16:14:25,634 INFO  [Test worker] GeoPointPrecisionTest: Position PGpoint: (11.1543467,50.52103)

This is not correct:

2020-08-13 16:14:25,640 INFO  [Test worker] GeoPointPrecisionTest: Query done, result found
2020-08-13 16:14:25,641 INFO  [Test worker] GeoPointPrecisionTest: Position Object: (11.154346643015742,50.52102999296039)
2020-08-13 16:14:25,641 INFO  [Test worker] GeoPointPrecisionTest: Position String: (11.154346643015742,50.52102999296039)
2020-08-13 16:14:25,641 INFO  [Test worker] GeoPointPrecisionTest: Position PGobject: (11.154346643015742,50.52102999296039)
2020-08-13 16:14:25,642 INFO  [Test worker] GeoPointPrecisionTest: Position PGpoint: (11.154346643015742,50.52102999296039)

Because of this, I have to implement a workaround to fetch primary key column values, and then execute another query by primary key.

What am I missing?

Thanks

2

@senchi This looks like a bug, could you please create an issue on Github? As you mentioned: “during the upgrade”, I guess this issue still occurs after all nodes where upgraded, right?

3

Hi @smu,

Yes, sorry, “during” was a poor choice of words. I’ve meant “after” the upgrade. This is still happening and is causing lots of troubles for us right now.

I’ve created this bug report: https://github.com/crate/crate/issues/10452

Also, I wouldn’t recommend the “workaround” to pre-fetch primary keys and then fetch data by primary key as it is not working correctly in a cluster environment. The two selects could go to different nodes, and if that happens swift enough, you might end up with unexpected data in the result set.
Besides, it does increase the data volume transferred over the network, which produces additional costs in an AWS environment.

4

I just tried it with 4.2.3

CREATE TABLE IF NOT EXISTS myTable (
  id string primary key,
  some_other_property string,
  my_location geo_point);

INSERT INTO myTable (id, some_other_property, my_location) VALUES ('abc', 'some data', 'POINT( 11.1543467 50.5210300 )');

SELECT my_location FROM myTable limit 100;
-- SELECT OK, 1 row in set (0.005 sec)
-- Result: [11.1543467,50.52103]

SELECT my_location FROM myTable where id='abc' limit 100;
-- SELECT OK, 1 row in set (0.005 sec)
-- Result: [11.1543467,50.52103]

SELECT my_location FROM myTable where some_other_property='some data' limit 100;
-- SELECT OK, 1 row in set (0.029 sec)
-- Result: [11.1543467,50.52103]
5

Hi @proddata,

May I ask how exactly have you tried? I’m still having the same issue in 4.2.3 and in 4.2.4 Community Edition build as well.
When I do this in the Crate.io SQL Console all seems fine, but when you use my code example then the issue is exposed.
Could it have something to do with the PG Wire Protocol?
As you can see in my code example, I’m establishing the connection via 5432 TCP port and the following driver:

Class.forName("io.crate.client.jdbc.CrateDriver");

gradle:

implementation 'io.crate:crate-jdbc:2.6.0'

Thanks

6

@senchi

I tried it within the CrateDB SQL Console which is using the http endpoint.
Though I also tried it with DBeaver which is using the Crate JDBC Driver with port 5432 and got the same result.

This definitely seems strange and might need some further investigation by one of the database engineers.