When we try to use parameters in a more-complicated query (we think specifically when we use joins), our code fails at PrepareRequestAsync(), returning an exception that a parameter is unbound- which to us seems very strange, since we don't want to bind any parameters until we're going to call ExecuteRequestAsync().
We assume that we are doing something wrong in our code, but if that is not the case, we suspect this is a problem in Phoenix or even Calcite. We found this (identical?) problem on the Hortonworks Community forum, unresolved: https://community.hortonworks.com/questions/45896/problem-executing-joins-on-phoenix-query-server.html. It links to [CALCITE-1052]- I can't tell if this is really related- at the very least, the connectionId in our error message matches the connectionId of our PrepareRequest, which it sounds like wasn't true for them. Regardless, their issue was apparently solved by updating to Phoenix 4.7, and I believe we are running 4.4, so if someone with a newer version can confirm that this isn't reproducible in 4.7, I'll make the requests to get our install updated to that.
Table Setup:
DROP TABLE IF EXISTS ShippingCodeFact;
CREATE TABLE IF NOT EXISTS ShippingCodeFact (
CodeId DECIMAL (18, 0),
StringData VARCHAR,
CodeKey BIGINT NOT NULL,
CustomerId VARCHAR,
StartDate TIMESTAMP,
EndDate TIMESTAMP,
CONSTRAINT my_pk PRIMARY KEY(CodeId, StringData, CodeKey)
) SALT_BUCKETS=32, COMPRESSION='GZ';
DROP TABLE IF EXISTS ShippingGlobalCodes;
CREATE TABLE IF NOT EXISTS ShippingGlobalCodes (
GlobalCode VARCHAR,
InternalCode VARCHAR,
CodeName VARCHAR,
CodeKey BIGINT NOT NULL
CONSTRAINT my_pk PRIMARY KEY(GlobalCode, InternalCode, CodeKey)
) SALT_BUCKETS=32;
Code:
var client = new PhoenixClient(null);
var random = new Random();
string connId = "0123456789abcdef";
connId = new string(Enumerable.Repeat(connId, 8).Select(s => s[random.Next(s.Length)]).ToArray());
RequestOptions options = RequestOptions.GetVNetDefaultOptions();
options.AlternativeHost = HOST_SERVER;
OpenConnectionResponse openConnResponse = null;
try
{
Google.Protobuf.Collections.MapField<string, string> info = new Google.Protobuf.Collections.MapField<string, string>();
openConnResponse = client.OpenConnectionRequestAsync(connId, info, options).Result;
ConnectionProperties connProperties = new ConnectionProperties
{
HasAutoCommit = true,
AutoCommit = false,
HasReadOnly = true,
ReadOnly = false,
TransactionIsolation = 0,
Catalog = "",
Schema = "",
IsDirty = true
};
client.ConnectionSyncRequestAsync(connId, connProperties, options).Wait();
string cmdText = "SELECT fact0.CustomerId FROM ShippingCodeFact fact0 INNER JOIN ShippingGlobalCodes gcD1 ON fact0.StringData = gcD1.InternalCode WHERE fact0.StartDate < ? AND fact0.EndDate >= ? AND gcD1.GlobalCode = ?";
var paramList = new Google.Protobuf.Collections.RepeatedField<TypedValue>();
TypedValue v1 = new TypedValue
{
NumberValue = ((new DateTime(2016, 7, 26)).Ticks - (new DateTime(1970, 1, 1)).Ticks)/10000,
Type = Rep.JAVA_SQL_TIMESTAMP
};
TypedValue v2 = new TypedValue
{
NumberValue = ((new DateTime(2016, 4, 26)).Ticks - (new DateTime(1970, 1, 1)).Ticks)/10000,
Type = Rep.JAVA_SQL_TIMESTAMP
};
TypedValue v3 = new TypedValue
{
StringValue = "87603000",
Type = Rep.STRING
};
paramList.Add(v1);
paramList.Add(v2);
paramList.Add(v3);
PrepareResponse prepareResponse = client.PrepareRequestAsync(connId, cmdText, ulong.MaxValue, options).Result;
StatementHandle statementHandle = prepareResponse.Statement;
ExecuteResponse execResponse = client.ExecuteRequestAsync(statementHandle, paramList, ulong.MaxValue, true, options).Result;
FetchResponse fetchResponse = null;
if(!execResponse.Results[0].FirstFrame.Done)
fetchResponse = client.FetchRequestAsync(connId, prepareResponse.Statement.Id, 0, uint.MaxValue, options).Result;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (openConnResponse != null)
{
client.CloseConnectionRequestAsync(connId, options).Wait();
openConnResponse = null;
}
}
Exception "errorMessage":
RuntimeException: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound Parameter at index 3 is unbound -\u003e SQLException: ERROR 2004 (INT05): Parameter value unbound Parameter at index 3 is unbound
(If anyone wants I can paste the whole "exceptions" value- most of it is just the trace)
@duoxu since you've seemed interested in the other issues
![microsoft-github-policy-service[bot] avatar](https://avatars.githubusercontent.com/in/95686?v=4 "microsoft-github-policy-service[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent