You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Nick Dimiduk <nd...@gmail.com> on 2015/03/31 03:47:23 UTC
Bound parameters in create table statements
Working on PHOENIX-971, I'm wondering what the expected behavior should be
for PreparedStatements created from CREATE TABLE sql with parameters.
Calcite's Avatica depends on the statement to identify the parameter types
at compile time, and return meaningful values for method invocations on
ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
recognizing the number of parameters in my sql, but is not inferring type
information.
My question is: should Avatica be more flexible in allowing "fuzzy"
signatures for PreparedStatements, or should Phoenix's
StatementPlan#compile methods be determining parameter types in all cases?
Thanks,
Nick
Re: Bound parameters in create table statements
Posted by Julian Hyde <ju...@hydromatic.net>.
I have logged https://issues.apache.org/jira/browse/CALCITE-664 to track this on the Calcite/Avatica side. We do not plan to fix it for Calcite 1.2.
> On Apr 1, 2015, at 1:48 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
> No crucial for functionality, but a component of our test suite. My goal
> with this patch was to prove confidence in the implementation by having all
> IT tests be parameterized to run directly against Phoenix, and against
> Phoenix through the query server. Since this is used by the tests, it
> stymies this goal.
>
> On Wed, Apr 1, 2015 at 12:11 PM, James Taylor <ja...@apache.org>
> wrote:
>
>> For the SELECT example you use, Phoenix would infer the type based on
>> the column type of ID. We don't support parameterized precision/scale
>> create statements as you've shown. It's really only for the pre-split
>> information, and that's mainly because there's not a good way to pass
>> arbitrary byte[] values through constants - it's much easier to bind
>> them with stmt.setBytes(1, arbitraryByteArray);
>>
>> I think it's a detail, though, that can be left for later IMHO. It's
>> not crucial functionality.
>>
>> FWIW, Phoenix infers types whenever possible, but sometimes it's
>> ambiguous. For example:
>> SELECT ? + ? FROM T;
>> In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
>> INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
>> it out, we use null for the type in the metadata APIs.
>>
>> On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
>>> Adding back dev@calcite
>>>
>>> On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>
>>>> Poking around with HSQLDB, it seems parameter metadata is made available
>>>> after statement preparation for select statements. (Presumably inferred
>>>> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
>>>> not support parameterized create statements:
>>>>
>>>> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
>>>> NULL, pk varchar(?) NOT NULL)")
>>>>
>>>> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError
>> (:-1)
>>>>
>>>> I think that if Phoenix is going to support parameterized create table
>>>> statements, it should infer parameter types and populate
>> ParameterMetaData
>>>> accordingly.
>>>>
>>>> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>>
>>>>> Hi Gabriel,
>>>>>
>>>>> Yes, we do this in the Phoenix test harness for parameterizing split
>>>>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>>>>> Long, boolean). I ran into this while porting QueryIT to run vs. the
>> query
>>>>> server.
>>>>>
>>>>> -n
>>>>>
>>>>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <gabriel.reid@gmail.com
>>>
>>>>> wrote:
>>>>>
>>>>>> Could you explain how you're using prepared statements for DDL
>>>>>> statements?
>>>>>> Are you parameterizing parts of the DDL statements with question
>> marks to
>>>>>> be filled in by the PreparedStatement parameters?
>>>>>>
>>>>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>>>>
>>>>>>> Working on PHOENIX-971, I'm wondering what the expected behavior
>>>>>> should be
>>>>>>> for PreparedStatements created from CREATE TABLE sql with
>> parameters.
>>>>>>> Calcite's Avatica depends on the statement to identify the parameter
>>>>>> types
>>>>>>> at compile time, and return meaningful values for method
>> invocations on
>>>>>>> ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>>>>>> recognizing the number of parameters in my sql, but is not inferring
>>>>>> type
>>>>>>> information.
>>>>>>>
>>>>>>> My question is: should Avatica be more flexible in allowing "fuzzy"
>>>>>>> signatures for PreparedStatements, or should Phoenix's
>>>>>>> StatementPlan#compile methods be determining parameter types in all
>>>>>> cases?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Nick
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>
Re: Bound parameters in create table statements
Posted by Julian Hyde <ju...@hydromatic.net>.
I have logged https://issues.apache.org/jira/browse/CALCITE-664 to track this on the Calcite/Avatica side. We do not plan to fix it for Calcite 1.2.
> On Apr 1, 2015, at 1:48 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
> No crucial for functionality, but a component of our test suite. My goal
> with this patch was to prove confidence in the implementation by having all
> IT tests be parameterized to run directly against Phoenix, and against
> Phoenix through the query server. Since this is used by the tests, it
> stymies this goal.
>
> On Wed, Apr 1, 2015 at 12:11 PM, James Taylor <ja...@apache.org>
> wrote:
>
>> For the SELECT example you use, Phoenix would infer the type based on
>> the column type of ID. We don't support parameterized precision/scale
>> create statements as you've shown. It's really only for the pre-split
>> information, and that's mainly because there's not a good way to pass
>> arbitrary byte[] values through constants - it's much easier to bind
>> them with stmt.setBytes(1, arbitraryByteArray);
>>
>> I think it's a detail, though, that can be left for later IMHO. It's
>> not crucial functionality.
>>
>> FWIW, Phoenix infers types whenever possible, but sometimes it's
>> ambiguous. For example:
>> SELECT ? + ? FROM T;
>> In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
>> INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
>> it out, we use null for the type in the metadata APIs.
>>
>> On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
>>> Adding back dev@calcite
>>>
>>> On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>
>>>> Poking around with HSQLDB, it seems parameter metadata is made available
>>>> after statement preparation for select statements. (Presumably inferred
>>>> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
>>>> not support parameterized create statements:
>>>>
>>>> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
>>>> NULL, pk varchar(?) NOT NULL)")
>>>>
>>>> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError
>> (:-1)
>>>>
>>>> I think that if Phoenix is going to support parameterized create table
>>>> statements, it should infer parameter types and populate
>> ParameterMetaData
>>>> accordingly.
>>>>
>>>> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>>
>>>>> Hi Gabriel,
>>>>>
>>>>> Yes, we do this in the Phoenix test harness for parameterizing split
>>>>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>>>>> Long, boolean). I ran into this while porting QueryIT to run vs. the
>> query
>>>>> server.
>>>>>
>>>>> -n
>>>>>
>>>>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <gabriel.reid@gmail.com
>>>
>>>>> wrote:
>>>>>
>>>>>> Could you explain how you're using prepared statements for DDL
>>>>>> statements?
>>>>>> Are you parameterizing parts of the DDL statements with question
>> marks to
>>>>>> be filled in by the PreparedStatement parameters?
>>>>>>
>>>>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com>
>> wrote:
>>>>>>
>>>>>>> Working on PHOENIX-971, I'm wondering what the expected behavior
>>>>>> should be
>>>>>>> for PreparedStatements created from CREATE TABLE sql with
>> parameters.
>>>>>>> Calcite's Avatica depends on the statement to identify the parameter
>>>>>> types
>>>>>>> at compile time, and return meaningful values for method
>> invocations on
>>>>>>> ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>>>>>> recognizing the number of parameters in my sql, but is not inferring
>>>>>> type
>>>>>>> information.
>>>>>>>
>>>>>>> My question is: should Avatica be more flexible in allowing "fuzzy"
>>>>>>> signatures for PreparedStatements, or should Phoenix's
>>>>>>> StatementPlan#compile methods be determining parameter types in all
>>>>>> cases?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Nick
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
No crucial for functionality, but a component of our test suite. My goal
with this patch was to prove confidence in the implementation by having all
IT tests be parameterized to run directly against Phoenix, and against
Phoenix through the query server. Since this is used by the tests, it
stymies this goal.
On Wed, Apr 1, 2015 at 12:11 PM, James Taylor <ja...@apache.org>
wrote:
> For the SELECT example you use, Phoenix would infer the type based on
> the column type of ID. We don't support parameterized precision/scale
> create statements as you've shown. It's really only for the pre-split
> information, and that's mainly because there's not a good way to pass
> arbitrary byte[] values through constants - it's much easier to bind
> them with stmt.setBytes(1, arbitraryByteArray);
>
> I think it's a detail, though, that can be left for later IMHO. It's
> not crucial functionality.
>
> FWIW, Phoenix infers types whenever possible, but sometimes it's
> ambiguous. For example:
> SELECT ? + ? FROM T;
> In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
> INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
> it out, we use null for the type in the metadata APIs.
>
> On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> > Adding back dev@calcite
> >
> > On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com>
> wrote:
> >
> >> Poking around with HSQLDB, it seems parameter metadata is made available
> >> after statement preparation for select statements. (Presumably inferred
> >> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
> >> not support parameterized create statements:
> >>
> >> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
> >> NULL, pk varchar(?) NOT NULL)")
> >>
> >> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError
> (:-1)
> >>
> >> I think that if Phoenix is going to support parameterized create table
> >> statements, it should infer parameter types and populate
> ParameterMetaData
> >> accordingly.
> >>
> >> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com>
> wrote:
> >>
> >>> Hi Gabriel,
> >>>
> >>> Yes, we do this in the Phoenix test harness for parameterizing split
> >>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
> >>> Long, boolean). I ran into this while porting QueryIT to run vs. the
> query
> >>> server.
> >>>
> >>> -n
> >>>
> >>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <gabriel.reid@gmail.com
> >
> >>> wrote:
> >>>
> >>>> Could you explain how you're using prepared statements for DDL
> >>>> statements?
> >>>> Are you parameterizing parts of the DDL statements with question
> marks to
> >>>> be filled in by the PreparedStatement parameters?
> >>>>
> >>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com>
> wrote:
> >>>>
> >>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
> >>>> should be
> >>>> > for PreparedStatements created from CREATE TABLE sql with
> parameters.
> >>>> > Calcite's Avatica depends on the statement to identify the parameter
> >>>> types
> >>>> > at compile time, and return meaningful values for method
> invocations on
> >>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
> >>>> > recognizing the number of parameters in my sql, but is not inferring
> >>>> type
> >>>> > information.
> >>>> >
> >>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
> >>>> > signatures for PreparedStatements, or should Phoenix's
> >>>> > StatementPlan#compile methods be determining parameter types in all
> >>>> cases?
> >>>> >
> >>>> > Thanks,
> >>>> > Nick
> >>>> >
> >>>>
> >>>
> >>>
> >>
>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
No crucial for functionality, but a component of our test suite. My goal
with this patch was to prove confidence in the implementation by having all
IT tests be parameterized to run directly against Phoenix, and against
Phoenix through the query server. Since this is used by the tests, it
stymies this goal.
On Wed, Apr 1, 2015 at 12:11 PM, James Taylor <ja...@apache.org>
wrote:
> For the SELECT example you use, Phoenix would infer the type based on
> the column type of ID. We don't support parameterized precision/scale
> create statements as you've shown. It's really only for the pre-split
> information, and that's mainly because there's not a good way to pass
> arbitrary byte[] values through constants - it's much easier to bind
> them with stmt.setBytes(1, arbitraryByteArray);
>
> I think it's a detail, though, that can be left for later IMHO. It's
> not crucial functionality.
>
> FWIW, Phoenix infers types whenever possible, but sometimes it's
> ambiguous. For example:
> SELECT ? + ? FROM T;
> In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
> INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
> it out, we use null for the type in the metadata APIs.
>
> On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> > Adding back dev@calcite
> >
> > On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com>
> wrote:
> >
> >> Poking around with HSQLDB, it seems parameter metadata is made available
> >> after statement preparation for select statements. (Presumably inferred
> >> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
> >> not support parameterized create statements:
> >>
> >> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
> >> NULL, pk varchar(?) NOT NULL)")
> >>
> >> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError
> (:-1)
> >>
> >> I think that if Phoenix is going to support parameterized create table
> >> statements, it should infer parameter types and populate
> ParameterMetaData
> >> accordingly.
> >>
> >> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com>
> wrote:
> >>
> >>> Hi Gabriel,
> >>>
> >>> Yes, we do this in the Phoenix test harness for parameterizing split
> >>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
> >>> Long, boolean). I ran into this while porting QueryIT to run vs. the
> query
> >>> server.
> >>>
> >>> -n
> >>>
> >>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <gabriel.reid@gmail.com
> >
> >>> wrote:
> >>>
> >>>> Could you explain how you're using prepared statements for DDL
> >>>> statements?
> >>>> Are you parameterizing parts of the DDL statements with question
> marks to
> >>>> be filled in by the PreparedStatement parameters?
> >>>>
> >>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com>
> wrote:
> >>>>
> >>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
> >>>> should be
> >>>> > for PreparedStatements created from CREATE TABLE sql with
> parameters.
> >>>> > Calcite's Avatica depends on the statement to identify the parameter
> >>>> types
> >>>> > at compile time, and return meaningful values for method
> invocations on
> >>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
> >>>> > recognizing the number of parameters in my sql, but is not inferring
> >>>> type
> >>>> > information.
> >>>> >
> >>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
> >>>> > signatures for PreparedStatements, or should Phoenix's
> >>>> > StatementPlan#compile methods be determining parameter types in all
> >>>> cases?
> >>>> >
> >>>> > Thanks,
> >>>> > Nick
> >>>> >
> >>>>
> >>>
> >>>
> >>
>
Re: Bound parameters in create table statements
Posted by James Taylor <ja...@apache.org>.
For the SELECT example you use, Phoenix would infer the type based on
the column type of ID. We don't support parameterized precision/scale
create statements as you've shown. It's really only for the pre-split
information, and that's mainly because there's not a good way to pass
arbitrary byte[] values through constants - it's much easier to bind
them with stmt.setBytes(1, arbitraryByteArray);
I think it's a detail, though, that can be left for later IMHO. It's
not crucial functionality.
FWIW, Phoenix infers types whenever possible, but sometimes it's
ambiguous. For example:
SELECT ? + ? FROM T;
In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
it out, we use null for the type in the metadata APIs.
On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> Adding back dev@calcite
>
> On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com> wrote:
>
>> Poking around with HSQLDB, it seems parameter metadata is made available
>> after statement preparation for select statements. (Presumably inferred
>> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
>> not support parameterized create statements:
>>
>> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
>> NULL, pk varchar(?) NOT NULL)")
>>
>> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError (:-1)
>>
>> I think that if Phoenix is going to support parameterized create table
>> statements, it should infer parameter types and populate ParameterMetaData
>> accordingly.
>>
>> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>>
>>> Hi Gabriel,
>>>
>>> Yes, we do this in the Phoenix test harness for parameterizing split
>>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>>> Long, boolean). I ran into this while porting QueryIT to run vs. the query
>>> server.
>>>
>>> -n
>>>
>>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
>>> wrote:
>>>
>>>> Could you explain how you're using prepared statements for DDL
>>>> statements?
>>>> Are you parameterizing parts of the DDL statements with question marks to
>>>> be filled in by the PreparedStatement parameters?
>>>>
>>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>>>>
>>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
>>>> should be
>>>> > for PreparedStatements created from CREATE TABLE sql with parameters.
>>>> > Calcite's Avatica depends on the statement to identify the parameter
>>>> types
>>>> > at compile time, and return meaningful values for method invocations on
>>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>>> > recognizing the number of parameters in my sql, but is not inferring
>>>> type
>>>> > information.
>>>> >
>>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
>>>> > signatures for PreparedStatements, or should Phoenix's
>>>> > StatementPlan#compile methods be determining parameter types in all
>>>> cases?
>>>> >
>>>> > Thanks,
>>>> > Nick
>>>> >
>>>>
>>>
>>>
>>
Re: Bound parameters in create table statements
Posted by James Taylor <ja...@apache.org>.
For the SELECT example you use, Phoenix would infer the type based on
the column type of ID. We don't support parameterized precision/scale
create statements as you've shown. It's really only for the pre-split
information, and that's mainly because there's not a good way to pass
arbitrary byte[] values through constants - it's much easier to bind
them with stmt.setBytes(1, arbitraryByteArray);
I think it's a detail, though, that can be left for later IMHO. It's
not crucial functionality.
FWIW, Phoenix infers types whenever possible, but sometimes it's
ambiguous. For example:
SELECT ? + ? FROM T;
In theory, param 1 could be bound to a TIMESTAMP and param 2 to an
INTEGER. Or they could both be DECIMAL, etc. If Phoenix can't figure
it out, we use null for the type in the metadata APIs.
On Wed, Apr 1, 2015 at 11:49 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> Adding back dev@calcite
>
> On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com> wrote:
>
>> Poking around with HSQLDB, it seems parameter metadata is made available
>> after statement preparation for select statements. (Presumably inferred
>> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
>> not support parameterized create statements:
>>
>> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
>> NULL, pk varchar(?) NOT NULL)")
>>
>> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError (:-1)
>>
>> I think that if Phoenix is going to support parameterized create table
>> statements, it should infer parameter types and populate ParameterMetaData
>> accordingly.
>>
>> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>>
>>> Hi Gabriel,
>>>
>>> Yes, we do this in the Phoenix test harness for parameterizing split
>>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>>> Long, boolean). I ran into this while porting QueryIT to run vs. the query
>>> server.
>>>
>>> -n
>>>
>>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
>>> wrote:
>>>
>>>> Could you explain how you're using prepared statements for DDL
>>>> statements?
>>>> Are you parameterizing parts of the DDL statements with question marks to
>>>> be filled in by the PreparedStatement parameters?
>>>>
>>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>>>>
>>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
>>>> should be
>>>> > for PreparedStatements created from CREATE TABLE sql with parameters.
>>>> > Calcite's Avatica depends on the statement to identify the parameter
>>>> types
>>>> > at compile time, and return meaningful values for method invocations on
>>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>>> > recognizing the number of parameters in my sql, but is not inferring
>>>> type
>>>> > information.
>>>> >
>>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
>>>> > signatures for PreparedStatements, or should Phoenix's
>>>> > StatementPlan#compile methods be determining parameter types in all
>>>> cases?
>>>> >
>>>> > Thanks,
>>>> > Nick
>>>> >
>>>>
>>>
>>>
>>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
Adding back dev@calcite
On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> Poking around with HSQLDB, it seems parameter metadata is made available
> after statement preparation for select statements. (Presumably inferred
> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
> not support parameterized create statements:
>
> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
> NULL, pk varchar(?) NOT NULL)")
>
> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError (:-1)
>
> I think that if Phoenix is going to support parameterized create table
> statements, it should infer parameter types and populate ParameterMetaData
> accordingly.
>
> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
>> Hi Gabriel,
>>
>> Yes, we do this in the Phoenix test harness for parameterizing split
>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>> Long, boolean). I ran into this while porting QueryIT to run vs. the query
>> server.
>>
>> -n
>>
>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
>> wrote:
>>
>>> Could you explain how you're using prepared statements for DDL
>>> statements?
>>> Are you parameterizing parts of the DDL statements with question marks to
>>> be filled in by the PreparedStatement parameters?
>>>
>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>>>
>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
>>> should be
>>> > for PreparedStatements created from CREATE TABLE sql with parameters.
>>> > Calcite's Avatica depends on the statement to identify the parameter
>>> types
>>> > at compile time, and return meaningful values for method invocations on
>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>> > recognizing the number of parameters in my sql, but is not inferring
>>> type
>>> > information.
>>> >
>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
>>> > signatures for PreparedStatements, or should Phoenix's
>>> > StatementPlan#compile methods be determining parameter types in all
>>> cases?
>>> >
>>> > Thanks,
>>> > Nick
>>> >
>>>
>>
>>
>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
Adding back dev@calcite
On Wed, Apr 1, 2015 at 11:48 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> Poking around with HSQLDB, it seems parameter metadata is made available
> after statement preparation for select statements. (Presumably inferred
> from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
> not support parameterized create statements:
>
> user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
> NULL, pk varchar(?) NOT NULL)")
>
> HsqlException unexpected token: ? org.hsqldb.error.Error.parseError (:-1)
>
> I think that if Phoenix is going to support parameterized create table
> statements, it should infer parameter types and populate ParameterMetaData
> accordingly.
>
> On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
>> Hi Gabriel,
>>
>> Yes, we do this in the Phoenix test harness for parameterizing split
>> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
>> Long, boolean). I ran into this while porting QueryIT to run vs. the query
>> server.
>>
>> -n
>>
>> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
>> wrote:
>>
>>> Could you explain how you're using prepared statements for DDL
>>> statements?
>>> Are you parameterizing parts of the DDL statements with question marks to
>>> be filled in by the PreparedStatement parameters?
>>>
>>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>>>
>>> > Working on PHOENIX-971, I'm wondering what the expected behavior
>>> should be
>>> > for PreparedStatements created from CREATE TABLE sql with parameters.
>>> > Calcite's Avatica depends on the statement to identify the parameter
>>> types
>>> > at compile time, and return meaningful values for method invocations on
>>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>>> > recognizing the number of parameters in my sql, but is not inferring
>>> type
>>> > information.
>>> >
>>> > My question is: should Avatica be more flexible in allowing "fuzzy"
>>> > signatures for PreparedStatements, or should Phoenix's
>>> > StatementPlan#compile methods be determining parameter types in all
>>> cases?
>>> >
>>> > Thanks,
>>> > Nick
>>> >
>>>
>>
>>
>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
Poking around with HSQLDB, it seems parameter metadata is made available
after statement preparation for select statements. (Presumably inferred
from column type, as in "SELECT * FROM TEST_TABLE WHERE id = ?". It does
not support parameterized create statements:
user=> (.prepareStatement conn "CREATE TABLE TEST_TABLE_P(id INTEGER NOT
NULL, pk varchar(?) NOT NULL)")
HsqlException unexpected token: ? org.hsqldb.error.Error.parseError (:-1)
I think that if Phoenix is going to support parameterized create table
statements, it should infer parameter types and populate ParameterMetaData
accordingly.
On Tue, Mar 31, 2015 at 1:06 PM, Nick Dimiduk <nd...@gmail.com> wrote:
> Hi Gabriel,
>
> Yes, we do this in the Phoenix test harness for parameterizing split
> points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
> Long, boolean). I ran into this while porting QueryIT to run vs. the query
> server.
>
> -n
>
> On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
> wrote:
>
>> Could you explain how you're using prepared statements for DDL statements?
>> Are you parameterizing parts of the DDL statements with question marks to
>> be filled in by the PreparedStatement parameters?
>>
>> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>>
>> > Working on PHOENIX-971, I'm wondering what the expected behavior should
>> be
>> > for PreparedStatements created from CREATE TABLE sql with parameters.
>> > Calcite's Avatica depends on the statement to identify the parameter
>> types
>> > at compile time, and return meaningful values for method invocations on
>> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
>> > recognizing the number of parameters in my sql, but is not inferring
>> type
>> > information.
>> >
>> > My question is: should Avatica be more flexible in allowing "fuzzy"
>> > signatures for PreparedStatements, or should Phoenix's
>> > StatementPlan#compile methods be determining parameter types in all
>> cases?
>> >
>> > Thanks,
>> > Nick
>> >
>>
>
>
Re: Bound parameters in create table statements
Posted by Nick Dimiduk <nd...@gmail.com>.
Hi Gabriel,
Yes, we do this in the Phoenix test harness for parameterizing split
points. See o.a.p.q.BaseTest#createTestTable(String, String, byte[][],
Long, boolean). I ran into this while porting QueryIT to run vs. the query
server.
-n
On Tue, Mar 31, 2015 at 11:58 AM, Gabriel Reid <ga...@gmail.com>
wrote:
> Could you explain how you're using prepared statements for DDL statements?
> Are you parameterizing parts of the DDL statements with question marks to
> be filled in by the PreparedStatement parameters?
>
> On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
>
> > Working on PHOENIX-971, I'm wondering what the expected behavior should
> be
> > for PreparedStatements created from CREATE TABLE sql with parameters.
> > Calcite's Avatica depends on the statement to identify the parameter
> types
> > at compile time, and return meaningful values for method invocations on
> > ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
> > recognizing the number of parameters in my sql, but is not inferring type
> > information.
> >
> > My question is: should Avatica be more flexible in allowing "fuzzy"
> > signatures for PreparedStatements, or should Phoenix's
> > StatementPlan#compile methods be determining parameter types in all
> cases?
> >
> > Thanks,
> > Nick
> >
>
Re: Bound parameters in create table statements
Posted by Gabriel Reid <ga...@gmail.com>.
Could you explain how you're using prepared statements for DDL statements?
Are you parameterizing parts of the DDL statements with question marks to
be filled in by the PreparedStatement parameters?
On Tue, Mar 31, 2015 at 3:48 AM Nick Dimiduk <nd...@gmail.com> wrote:
> Working on PHOENIX-971, I'm wondering what the expected behavior should be
> for PreparedStatements created from CREATE TABLE sql with parameters.
> Calcite's Avatica depends on the statement to identify the parameter types
> at compile time, and return meaningful values for method invocations on
> ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
> recognizing the number of parameters in my sql, but is not inferring type
> information.
>
> My question is: should Avatica be more flexible in allowing "fuzzy"
> signatures for PreparedStatements, or should Phoenix's
> StatementPlan#compile methods be determining parameter types in all cases?
>
> Thanks,
> Nick
>
Re: Bound parameters in create table statements
Posted by Julian Hyde <ju...@gmail.com>.
I wasn't even aware that JDBC let you prepare a DDL statement (such as CREATE TABLE). And if you can't prepare, you can't bind parameters.
Oracle's JDBC driver doc [ http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28932 ] has an apologetic tone, so they seem to think that it should be possible, even if they can't:
> Executing DDL Statements
>
> You must execute Data Definition Language (DDL) statements with Statement
> objects. If you use PreparedStatements objects orCallableStatements objects,
> then the DDL statement takes effect only on the first execution. This can cause
> unexpected behavior if the SQL statements are in a statement cache.
I see no reason in principle why Avatica should not allow you to prepare DDL, query the parameter types, then bind and execute. Provided the underlying engine supports it, of course.
Julian
> On Mar 30, 2015, at 9:47 PM, Nick Dimiduk <nd...@gmail.com> wrote:
>
> Working on PHOENIX-971, I'm wondering what the expected behavior should be
> for PreparedStatements created from CREATE TABLE sql with parameters.
> Calcite's Avatica depends on the statement to identify the parameter types
> at compile time, and return meaningful values for method invocations on
> ParameterMetaData. It looks like Phoenix's CreateTableCompiler is
> recognizing the number of parameters in my sql, but is not inferring type
> information.
>
> My question is: should Avatica be more flexible in allowing "fuzzy"
> signatures for PreparedStatements, or should Phoenix's
> StatementPlan#compile methods be determining parameter types in all cases?
>
> Thanks,
> Nick