[
https://issues.apache.org/jira/browse/PHOENIX-7358?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
chaijunjie updated PHOENIX-7358:
--------------------------------
Description:
When execute these sql, the upsert result is wrong, it is not same as order by
result.
{code:java}
create table t1(
c1 char(1) not null primary key,
c2 char(1),
c3 char(1)
) SALT_BUCKETS=8;
create table t2(
cc1 char(1) not null primary key,
cc2 char(1),
cc3 char(1)
) SALT_BUCKETS=8;
upsert into t1 values('1','0','a');
upsert into t1 values('2','1','b');
upsert into t1 values('3','1','c');
upsert into t1 values('4','0','d');
upsert into t1 values('5','1','e');
select c2,c1,c3 from t1 order by c3 desc;upsert into t2(cc1,cc2,cc3) select
c2,c1,c3 from t1 order by c3 desc;
select * from t2; {code}
0: jdbc:phoenix:> select c2,c1,c3 from t1 order by c3 desc;
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
|C2|C1|C3|
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
|1 |5 |e |
|0 |4 |d |
|1 |3 |c |
|1 |2 |b |
|0 |1 |a |
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
5 rows selected (0.048 seconds)
0: jdbc:phoenix:> upsert into t2(cc1,cc2,cc3) select c2,c1,c3 from t1 order by
c3 desc;
5 rows affected (0.045 seconds)
0: jdbc:phoenix:> select * from t2;
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
|CC1|CC2|CC3|
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
|1 |2 |b |
|0 |4 |d |
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
2 rows selected (0.017 seconds)
0: jdbc:phoenix:>
----------------------------------------------------------------------------------------
The target table data should be
1 2 b
0 1 a
When use order by in select query, should we also use
UpsertingParallelIteratorFactory?
https://github.com/apache/phoenix/blob/281a5456a688e03dd1dd8c4fba95011fdd9f4f6e/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java#L586
I think if we use order by should keep the result order to upsert data, so it
should not use coprocessor？
was:
When execute these sql, the upsert result is wrong, it is not same as order by
result.
{code:java}
create table t1(
c1 char(1) not null primary key,
c2 char(1),
c3 char(1)
) SALT_BUCKETS=8;
create table t2(
cc1 char(1) not null primary key,
cc2 char(1),
cc3 char(1)
) SALT_BUCKETS=8;
upsert into t1 values('1','0','a');
upsert into t1 values('2','1','b');
upsert into t1 values('3','1','c');
upsert into t1 values('4','0','d');
upsert into t1 values('5','1','e');
select c2,c1,c3 from t1 order by c3 desc;upsert into t2(cc1,cc2,cc3) select
c2,c1,c3 from t1 order by c3 desc;
select * from t2; {code}
0: jdbc:phoenix:> select c2,c1,c3 from t1 order by c3 desc;
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
|C2|C1|C3|
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
|1 |5 |e |
|0 |4 |d |
|1 |3 |c |
|1 |2 |b |
|0 |1 |a |
{+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
5 rows selected (0.048 seconds)
0: jdbc:phoenix:> upsert into t2(cc1,cc2,cc3) select c2,c1,c3 from t1 order by
c3 desc;
5 rows affected (0.045 seconds)
0: jdbc:phoenix:> select * from t2;
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
|CC1|CC2|CC3|
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
|1 |2 |b |
|0 |4 |d |
{+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
2 rows selected (0.017 seconds)
0: jdbc:phoenix:>
----------------------------------------------------------------------------------------
The target table data should be
1 2 b
0 1 a
When use order by in select query, should we also use
UpsertingParallelIteratorFactory?
> Upsert select result wrong when use order by in query
> -----------------------------------------------------
>
> Key: PHOENIX-7358
> URL: https://issues.apache.org/jira/browse/PHOENIX-7358
> Project: Phoenix
> Issue Type: Bug
> Components: core
> Affects Versions: 5.1.2
> Reporter: chaijunjie
> Assignee: chaijunjie
> Priority: Major
> Attachments: testUpsertSelectWithOrderBy.patch
>
>
> When execute these sql, the upsert result is wrong, it is not same as order
> by result.
> {code:java}
> create table t1(
> c1 char(1) not null primary key,
> c2 char(1),
> c3 char(1)
> ) SALT_BUCKETS=8;
> create table t2(
> cc1 char(1) not null primary key,
> cc2 char(1),
> cc3 char(1)
> ) SALT_BUCKETS=8;
> upsert into t1 values('1','0','a');
> upsert into t1 values('2','1','b');
> upsert into t1 values('3','1','c');
> upsert into t1 values('4','0','d');
> upsert into t1 values('5','1','e');
> select c2,c1,c3 from t1 order by c3 desc;upsert into t2(cc1,cc2,cc3) select
> c2,c1,c3 from t1 order by c3 desc;
> select * from t2; {code}
> 0: jdbc:phoenix:> select c2,c1,c3 from t1 order by c3 desc;
> {+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
> |C2|C1|C3|
> {+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
> |1 |5 |e |
> |0 |4 |d |
> |1 |3 |c |
> |1 |2 |b |
> |0 |1 |a |
> {+}--{-}{{-}}{-}{-}{+}{-}{{-}}++{{-}}{-}--
> 5 rows selected (0.048 seconds)
> 0: jdbc:phoenix:> upsert into t2(cc1,cc2,cc3) select c2,c1,c3 from t1 order
> by c3 desc;
> 5 rows affected (0.045 seconds)
> 0: jdbc:phoenix:> select * from t2;
> {+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
> |CC1|CC2|CC3|
> {+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
> |1 |2 |b |
> |0 |4 |d |
> {+}---{-}{{-}}{-}{-}{+}-{-}{{-}}++{{-}}{-}---
> 2 rows selected (0.017 seconds)
> 0: jdbc:phoenix:>
> ----------------------------------------------------------------------------------------
> The target table data should be
> 1 2 b
> 0 1 a
> When use order by in select query, should we also use
> UpsertingParallelIteratorFactory?
> https://github.com/apache/phoenix/blob/281a5456a688e03dd1dd8c4fba95011fdd9f4f6e/phoenix-core-client/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java#L586
> I think if we use order by should keep the result order to upsert data, so it
> should not use coprocessor？
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
