[
https://issues.apache.org/jira/browse/PHOENIX-4796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Harvey updated PHOENIX-4796:
----------------------------
Description:
I have two issues with bigint and integer default value,both a and b have a
default value:
1 secondary Index value is incorrect with index (...,a,b) ,where a is bigint,b
is integer.
2 secondary Index value cannot be updated.
The following is the detail.
I created a table, and have have some columns with an integer default value
{code:java}
>CREATE TABLE IF NOT EXISTS test_table (
id CHAR(32) NOT NULL,
no CHAR(32),
total BIGINT default 0,
score INTEGER default 0,
CONSTRAINT my_pk PRIMARY KEY (id)
)SALT_BUCKETS=30, COMPRESSION='Snappy';
>create index IF NOT EXISTS no_total_score on test_table ( no, total, score);
>upsert into test_table(id, no) values ('1001', 'b'); # total & score will have
>default value.
{code}
Every is OK when i use the pk.
{code:java}
>select * from test_table where id='1001';
+-----------------------------------+-----------------------------------+--------+--------+
| ID | NO | TOTAL | SCORE |
+-----------------------------------+-----------------------------------+--------+--------+
| 1001 | b | 0 | 0 |
+-----------------------------------+-----------------------------------+--------+--------+
{code}
But when I use a secondary Index,
It seems that that integer values in secondary Index cannot be recognized.
{code:java}
>select total,score from test_table where no='b';
+-------------+--------------+
| TOTAL | SCORE |
+-------------+--------------+
| 2147483648 | -1322241999 |
+-------------+--------------+
>select total,score from test_table where no='b' and total=0;
+--------+--------+
| TOTAL | SCORE |
+--------+--------+
+--------+--------+
{code}
Then I change the column 'total' to integer, secondary Index is correct if i
add a row:
{code:java}
CREATE TABLE IF NOT EXISTS test_table2 (
id CHAR(32) NOT NULL,
no CHAR(32),
total INTEGER default 0,
score INTEGER default 0,
CONSTRAINT my_pk PRIMARY KEY (id)
)SALT_BUCKETS=30, COMPRESSION='Snappy';
create index IF NOT EXISTS no_total_score_2 on test_table2 ( no, total, score);
{code}
I modified 'total' with 50
{code:java}
>upsert into test_table2(id, no, total) values ('1001', 'b', 50);
>select * from test_table2 where no='b';
+-----------------------------------+-----------------------------------+--------+--------+
| ID | NO | TOTAL | SCORE |
+-----------------------------------+-----------------------------------+--------+--------+
| 1001 | b | 50 | 0 |
+-----------------------------------+-----------------------------------+--------+--------+
>select no,total,score from test_table2 where no='b';
+-----------------------------------+--------+--------+
| NO | TOTAL | SCORE |
+-----------------------------------+--------+--------+
| b | 0 | 0 |
+-----------------------------------+--------+--------+{code}
The value of 'total' is not updated in secondary Index.So this row cannot be
selected with the new value 50 using secondary Index.
I use sqlline-thin.py.
was:
I have two issues with bigint and integer default value,both a and b have a
default value:
1 secondary Index value is incorrect with index (...,a,b) ,where a is bigint,b
is integer.
2 secondary Index value cannot be updated.
The following is the detail.
I created a table, and have have some columns with integer default value
{code:java}
>CREATE TABLE IF NOT EXISTS test_table (
id CHAR(32) NOT NULL,
no CHAR(32),
total BIGINT default 0,
score INTEGER default 0,
CONSTRAINT my_pk PRIMARY KEY (id)
)SALT_BUCKETS=30, COMPRESSION='Snappy';
>create index IF NOT EXISTS no_total_score on test_table ( no, total, score);
>upsert into test_table(id, no) values ('1001', 'b'); # total & score will have
>default value.
{code}
Every is OK when i use the pk.
{code:java}
>select * from test_table where id='1001';
+-----------------------------------+-----------------------------------+--------+--------+
| ID | NO | TOTAL | SCORE |
+-----------------------------------+-----------------------------------+--------+--------+
| 1001 | b | 0 | 0 |
+-----------------------------------+-----------------------------------+--------+--------+
{code}
But when I use a secondary Index,
It seems that that integer values in secondary Index cannot be recognized.
{code:java}
>select total,score from test_table where no='b';
+-------------+--------------+
| TOTAL | SCORE |
+-------------+--------------+
| 2147483648 | -1322241999 |
+-------------+--------------+
>select total,score from test_table where no='b' and total=0;
+--------+--------+
| TOTAL | SCORE |
+--------+--------+
+--------+--------+
{code}
Then I change the column 'total' to integer, secondary Index is correct if
create a row:
{code:java}
CREATE TABLE IF NOT EXISTS test_table2 (
id CHAR(32) NOT NULL,
no CHAR(32),
total INTEGER default 0,
score INTEGER default 0,
CONSTRAINT my_pk PRIMARY KEY (id)
)SALT_BUCKETS=30, COMPRESSION='Snappy';
create index IF NOT EXISTS no_total_score_2 on test_table2 ( no, total, score);
{code}
I modified 'total' with 50
{code:java}
>upsert into test_table2(id, no, total) values ('1001', 'b', 50);
>select * from test_table2 where no='b';
+-----------------------------------+-----------------------------------+--------+--------+
| ID | NO | TOTAL | SCORE |
+-----------------------------------+-----------------------------------+--------+--------+
| 1001 | b | 50 | 0 |
+-----------------------------------+-----------------------------------+--------+--------+
>select no,total,score from test_table2 where no='b';
+-----------------------------------+--------+--------+
| NO | TOTAL | SCORE |
+-----------------------------------+--------+--------+
| b | 0 | 0 |
+-----------------------------------+--------+--------+{code}
I use sqlline-thin.py.
> indexes of columns with default value cannot be updated
> -------------------------------------------------------
>
> Key: PHOENIX-4796
> URL: https://issues.apache.org/jira/browse/PHOENIX-4796
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.13.1
> Reporter: Harvey
> Priority: Major
>
> I have two issues with bigint and integer default value,both a and b have a
> default value:
> 1 secondary Index value is incorrect with index (...,a,b) ,where a is
> bigint,b is integer.
> 2 secondary Index value cannot be updated.
>
> The following is the detail.
> I created a table, and have have some columns with an integer default value
> {code:java}
> >CREATE TABLE IF NOT EXISTS test_table (
> id CHAR(32) NOT NULL,
> no CHAR(32),
> total BIGINT default 0,
> score INTEGER default 0,
> CONSTRAINT my_pk PRIMARY KEY (id)
> )SALT_BUCKETS=30, COMPRESSION='Snappy';
> >create index IF NOT EXISTS no_total_score on test_table ( no, total, score);
> >upsert into test_table(id, no) values ('1001', 'b'); # total & score will
> >have default value.
> {code}
> Every is OK when i use the pk.
> {code:java}
> >select * from test_table where id='1001';
> +-----------------------------------+-----------------------------------+--------+--------+
> | ID | NO | TOTAL | SCORE |
> +-----------------------------------+-----------------------------------+--------+--------+
> | 1001 | b | 0 | 0 |
> +-----------------------------------+-----------------------------------+--------+--------+
> {code}
> But when I use a secondary Index,
> It seems that that integer values in secondary Index cannot be recognized.
> {code:java}
> >select total,score from test_table where no='b';
> +-------------+--------------+
> | TOTAL | SCORE |
> +-------------+--------------+
> | 2147483648 | -1322241999 |
> +-------------+--------------+
> >select total,score from test_table where no='b' and total=0;
> +--------+--------+
> | TOTAL | SCORE |
> +--------+--------+
> +--------+--------+
> {code}
>
>
> Then I change the column 'total' to integer, secondary Index is correct if i
> add a row:
> {code:java}
> CREATE TABLE IF NOT EXISTS test_table2 (
> id CHAR(32) NOT NULL,
> no CHAR(32),
> total INTEGER default 0,
> score INTEGER default 0,
> CONSTRAINT my_pk PRIMARY KEY (id)
> )SALT_BUCKETS=30, COMPRESSION='Snappy';
> create index IF NOT EXISTS no_total_score_2 on test_table2 ( no, total,
> score);
> {code}
> I modified 'total' with 50
> {code:java}
> >upsert into test_table2(id, no, total) values ('1001', 'b', 50);
> >select * from test_table2 where no='b';
> +-----------------------------------+-----------------------------------+--------+--------+
> | ID | NO | TOTAL | SCORE |
> +-----------------------------------+-----------------------------------+--------+--------+
> | 1001 | b | 50 | 0 |
> +-----------------------------------+-----------------------------------+--------+--------+
> >select no,total,score from test_table2 where no='b';
> +-----------------------------------+--------+--------+
> | NO | TOTAL | SCORE |
> +-----------------------------------+--------+--------+
> | b | 0 | 0 |
> +-----------------------------------+--------+--------+{code}
> The value of 'total' is not updated in secondary Index.So this row cannot be
> selected with the new value 50 using secondary Index.
>
> I use sqlline-thin.py.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
