[
https://issues.apache.org/jira/browse/PHOENIX-5068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16766569#comment-16766569
]
Xinyi Yan commented on PHOENIX-5068:
------------------------------------
{code:java}
sqlline version 1.2.0
0: jdbc:phoenix:> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 99;
99 rows affected (0.072 seconds)
0: jdbc:phoenix:> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 100;
100 rows affected (0.017 seconds)
0: jdbc:phoenix:> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 500;
500 rows affected (0.049 seconds)
{code}
Locally tested, it doesn't have a problem when autocommit is turned on.
Verified on hbase1.3.3 and Phoenix 1.4.
I will fix this bug and update status here.
> Autocommit off is not working as expected might be a bug!?
> ----------------------------------------------------------
>
> Key: PHOENIX-5068
> URL: https://issues.apache.org/jira/browse/PHOENIX-5068
> Project: Phoenix
> Issue Type: Bug
> Reporter: Amarnath Ramamoorthi
> Priority: Minor
> Attachments: test_foo_data.sql
>
>
> Autocommit off is working strange might be a bug!?
> Here is what we found when using autocommit off.
> A table has only 2 int columns and both set as primary key, containing 100
> rows in total.
> On *"autocommit off"* when we try to upsert values in to same table, it says
> 200 rows affected.
> Works fine when we run the same Upsert command but with less than 100 rows
> using WHERE command as you can see below.
> There is something wrong with auto commit off with >= 100 rows upsert`s.
> {code:java}
> 0: jdbc:phoenix:XXYYZZ> select count(*) from "FOO".DEMO;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 100 |
> +-----------+
> 1 row selected (0.025 seconds)
> 0: jdbc:phoenix:XXYYZZ> SELECT * FROM "FOO".DEMO WHERE "id_x"=9741;
> +--------+-----------+
> | id_x | id_y |
> +--------+-----------+
> | 9741 | 63423770 |
> +--------+-----------+
> 1 row selected (0.04 seconds)
> 0: jdbc:phoenix:XXYYZZ> !autocommit off
> Autocommit status: false
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO;
> 200 rows affected (0.023 seconds)
> 0: jdbc:phoenix:XXYYZZ>
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO WHERE
> "id_x"=9741;
> 1 row affected (0.014 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO WHERE
> "id_x"!=9741;
> 99 rows affected (0.045 seconds)
> 0: jdbc:phoenix:XXYYZZ>
> 0: jdbc:phoenix:XXYYZZ> !autocommit on
> Autocommit status: true
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO;
> 100 rows affected (0.065 seconds)
> {code}
> Tested once again, but now select from different table
> {code:java}
> 0: jdbc:phoenix:XXYYZZ> !autocommit off
> Autocommit status: false
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit
> 100;
> 200 rows affected (0.052 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit
> 99;
> 99 rows affected (0.029 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit
> 500;
> 1,000 rows affected (0.041 seconds)
> {code}
> Still the same, It shows the rows affected is 1,000 even though we have it
> limited to 500. It keeps doubling up.
> Would be really helpful if someone could help on this please.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
