You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by ka...@barclays.com on 2015/12/21 17:38:10 UTC
Alter table - can't add column to table which has PK column type is
VARBINARY
Hello all,
I am trying to alter table (adding new column) to a table which has a primary key column of type VARBINARY. I am getting the following error:
Error: ERROR 1015 (42J04): Cannot add column to table when the last PK column is of type VARBINARY or ARRAY. columnName=ID
SQLState: 42J04
ErrorCode: 1015
Is this not supported? What are the other options please?
Thanks
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
Re: Alter table - can't add column to table which has PK column type
is VARBINARY
Posted by James Taylor <ja...@apache.org>.
You can have the same column name more than once if they're in different
column families (or one is part of the PK constraint and the other isn't).
You'll need to reference the ones in column families by prefixing them with
the column family name. The default column family name if one isn't
referenced explicitly is "0", so you could reference the ID column like
this: "0".ID
On Mon, Dec 21, 2015 at 12:25 PM, <ka...@barclays.com> wrote:
> Thanks James.
>
>
>
> While doing some testing on this, I have accidentally ended up with two
> columns having same name/type but different column sizes. Please see below
> (table with column name “ID” repeating twice):
>
>
>
>
>
>
>
> *From:* James Taylor [mailto:jamestaylor@apache.org]
> *Sent:* Monday, December 21, 2015 13:29
> *To:* user
> *Subject:* Re: Alter table - can't add column to table which has PK
> column type is VARBINARY
>
>
>
> Correct - it has to do with the way we encode column values in the row
> key. Since a VARBINARY can be any length with any bytes, we cannot know
> where it ends. Thus we only allow it at the end of the row key. With a
> BINARY, you're telling Phoenix how big it is, so it can occur anywhere in
> the PK constraint. For more information, see PHOENIX-1223.
>
>
>
>
>
>
>
> On Mon, Dec 21, 2015 at 9:33 AM, <ka...@barclays.com> wrote:
>
> Strangely, if the primary key column is of type BINARY (instead of
> VARBINARY), columns can be added using alter table.
>
>
>
> *From:* Ramanathan, Kannan: IT (NYK)
> *Sent:* Monday, December 21, 2015 11:38
> *To:* user@phoenix.apache.org
> *Subject:* Alter table - can't add column to table which has PK column
> type is VARBINARY
>
>
>
> Hello all,
>
>
>
> I am trying to alter table (adding new column) to a table which has a
> primary key column of type VARBINARY. I am getting the following error:
>
>
>
> Error: ERROR 1015 (42J04): Cannot add column to table when the last PK
> column is of type VARBINARY or ARRAY. columnName=ID
>
> SQLState: 42J04
>
> ErrorCode: 1015
>
>
>
> Is this not supported? What are the other options please?
>
>
>
> Thanks
>
> Kannan.
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
>
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
RE: Alter table - can't add column to table which has PK column
type is VARBINARY
Posted by ka...@barclays.com.
Thanks James.
While doing some testing on this, I have accidentally ended up with two columns having same name/type but different column sizes. Please see below (table with column name “ID” repeating twice):
[cid:image001.png@01D13C03.D9189390]
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Monday, December 21, 2015 13:29
To: user
Subject: Re: Alter table - can't add column to table which has PK column type is VARBINARY
Correct - it has to do with the way we encode column values in the row key. Since a VARBINARY can be any length with any bytes, we cannot know where it ends. Thus we only allow it at the end of the row key. With a BINARY, you're telling Phoenix how big it is, so it can occur anywhere in the PK constraint. For more information, see PHOENIX-1223.
On Mon, Dec 21, 2015 at 9:33 AM, <ka...@barclays.com>> wrote:
Strangely, if the primary key column is of type BINARY (instead of VARBINARY), columns can be added using alter table.
From: Ramanathan, Kannan: IT (NYK)
Sent: Monday, December 21, 2015 11:38
To: user@phoenix.apache.org<ma...@phoenix.apache.org>
Subject: Alter table - can't add column to table which has PK column type is VARBINARY
Hello all,
I am trying to alter table (adding new column) to a table which has a primary key column of type VARBINARY. I am getting the following error:
Error: ERROR 1015 (42J04): Cannot add column to table when the last PK column is of type VARBINARY or ARRAY. columnName=ID
SQLState: 42J04
ErrorCode: 1015
Is this not supported? What are the other options please?
Thanks
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
Re: Alter table - can't add column to table which has PK column type
is VARBINARY
Posted by James Taylor <ja...@apache.org>.
Correct - it has to do with the way we encode column values in the row key.
Since a VARBINARY can be any length with any bytes, we cannot know where it
ends. Thus we only allow it at the end of the row key. With a BINARY,
you're telling Phoenix how big it is, so it can occur anywhere in the PK
constraint. For more information, see PHOENIX-1223.
On Mon, Dec 21, 2015 at 9:33 AM, <ka...@barclays.com> wrote:
> Strangely, if the primary key column is of type BINARY (instead of
> VARBINARY), columns can be added using alter table.
>
>
>
> *From:* Ramanathan, Kannan: IT (NYK)
> *Sent:* Monday, December 21, 2015 11:38
> *To:* user@phoenix.apache.org
> *Subject:* Alter table - can't add column to table which has PK column
> type is VARBINARY
>
>
>
> Hello all,
>
>
>
> I am trying to alter table (adding new column) to a table which has a
> primary key column of type VARBINARY. I am getting the following error:
>
>
>
> Error: ERROR 1015 (42J04): Cannot add column to table when the last PK
> column is of type VARBINARY or ARRAY. columnName=ID
>
> SQLState: 42J04
>
> ErrorCode: 1015
>
>
>
> Is this not supported? What are the other options please?
>
>
>
> Thanks
>
> Kannan.
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
> _______________________________________________
>
> This message is for information purposes only, it is not a recommendation,
> advice, offer or solicitation to buy or sell a product or service nor an
> official confirmation of any transaction. It is directed at persons who are
> professionals and is not intended for retail customer use. Intended for
> recipient only. This message is subject to the terms at:
> www.barclays.com/emaildisclaimer.
>
> For important disclosures, please see:
> www.barclays.com/salesandtradingdisclaimer regarding market commentary
> from Barclays Sales and/or Trading, who are active market participants; and
> in respect of Barclays Research, including disclosures relating to specific
> issuers, please see http://publicresearch.barclays.com.
>
> _______________________________________________
>
RE: Alter table - can't add column to table which has PK column
type is VARBINARY
Posted by ka...@barclays.com.
Strangely, if the primary key column is of type BINARY (instead of VARBINARY), columns can be added using alter table.
From: Ramanathan, Kannan: IT (NYK)
Sent: Monday, December 21, 2015 11:38
To: user@phoenix.apache.org
Subject: Alter table - can't add column to table which has PK column type is VARBINARY
Hello all,
I am trying to alter table (adding new column) to a table which has a primary key column of type VARBINARY. I am getting the following error:
Error: ERROR 1015 (42J04): Cannot add column to table when the last PK column is of type VARBINARY or ARRAY. columnName=ID
SQLState: 42J04
ErrorCode: 1015
Is this not supported? What are the other options please?
Thanks
Kannan.
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer<http://www.barclays.com/emaildisclaimer>.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer<http://www.barclays.com/salesandtradingdisclaimer> regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________
_______________________________________________
This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.
For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.
_______________________________________________