Details
-
Bug
-
Status: Resolved
-
Minor
-
Resolution: Duplicate
-
None
-
None
-
None
-
None
Description
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.
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)
Tested once again, but now select from different table
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)
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.
Attachments
Attachments
Issue Links
- Blocked
-
PHOENIX-3178 Row count incorrect for UPSERT SELECT when auto commit is false
- Closed