Phoenix Query Server does not return results (HDP 2.5)

[#1]

Hi

I use HDP 2.5 on Azure (HDInsight Template : HDI 3.5)

When I query Phoenix with PQS with PhoenixDb + Python or curl, I got no results but this message Do you have any idea why ?

{"response":"executeResults","missingStatement":false,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"xxx-xxxxxx:8765"},"results":[{"response":"resultSet","connectionId":"000000-0000-0000-00000000","statementId":1,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":0,"signed":true,"displaySize":40,"label":"COMPANY_ID","columnName":"COMPANY_ID","schemaName":"","precision":0,"scale":0,"tableName":"COMPANY","catalogName":"","type":{"type":"scalar","id":4,"name":"INTEGER","rep":"PRIMITIVE_INT"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Integer"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":225,"label":"NAME","columnName":"NAME","schemaName":"","precision":225,"scale":0,"tableName":"COMPANY","catalogName":"","type":{"type":"scalar","id":12,"name":"VARCHAR","rep":"STRING"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.String"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":null},"firstFrame":{"offset":0,"done":true,"rows":[]},"updateCount":-1,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"xxx-xxxxx:8765"}}]}

CREATE TABLE Company (COMPANY_ID INTEGER PRIMARY KEY, NAME VARCHAR(225));

UPSERT INTO Company VALUES(1, 'Microsoft');

curl -XPOST -H 'request:{"request":"openConnection","connectionId":"000000-0000-0000-00000000"}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

curl -XPOST -H 'request:{"request":"createStatement","connectionId":"000000-0000-0000-00000000"}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

curl -XPOST -H 'request:{"request":"prepareAndExecute","connectionId":"000000-0000-0000-00000000","statementId": 1,"sql": "SELECT * FROM COMPANY","maxRowCount":-1}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

[#2]

"firstFrame":{"offset":0,"done":true,"rows":[]}

Is showing that there were no results read from your query. Can you verify that the results are actually present in your table?

Also, I do not believe the author is maintaining the Python PhoenixDB library. Please refer to the official Apache Calcite Avatica documentation on how to use the JSON API: http://calcite.apache.org/avatica/docs/json_reference.html

[#3]

I don't think the problem is related to phoenixdb since i used curl also to check if it's a driver's problem I downgraded the version of HDI to 3.4 and it works again

I will try another upgrade later and will let you know if I have the problem again

[#4 · Accepted solution]

Turns out this was a bug in the Phoenix Query Server (technically Apache Avatica): https://issues.apache.org/jira/browse/CALCITE-1458

The author of the Phoenix-Sharp (C# driver) https://github.com/Azure/hdinsight-phoenix-sharp had a similar problem to what you were saying. What happens is that a change in serialization was not made fully backwards compatible and the attribute which the phoenixdb driver was likely expected (I have not verified this) was no longer being serialized over the wire. As such, the server was sending the data back in an attribute the client was not aware existed.

I have a fix I'm putting up on CALCITE-1458 shortly which should address the issue without requiring a change in the client.