search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
531 stars 162 forks source link

UPDATE updates all records instead of one #105

Open KES777 opened 8 years ago

KES777 commented 8 years ago 

# select * from site_stat where site_id = 1804;
 site_id |       period        | total | unique | visits | list
---------+---------------------+-------+--------+--------+------
    1804 | 2016-04-16 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-17 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-18 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-20 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-21 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-22 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-23 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-24 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-25 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-26 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-27 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-28 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-04-29 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-05-02 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-05-05 00:00:00 |     5 |      5 |      5 |
    1804 | 2016-05-06 00:00:00 |     5 |      5 |      5 |
(16 rows)

# update site_stat set total=6, "unique"=6, visits=6 where site_id = 1804 and period = cast('2016-05-06 00:00:00' as timestamp);
UPDATE 1

NOTICE: Query result says to us that only one row is updated!! But actually not

# select * from site_stat where site_id = 1804;
 site_id |       period        | total | unique | visits | list
---------+---------------------+-------+--------+--------+------
    1804 | 2016-04-16 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-17 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-18 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-20 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-21 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-22 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-23 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-24 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-25 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-26 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-27 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-28 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-04-29 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-05-02 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-05-05 00:00:00 |     6 |      6 |      6 |
    1804 | 2016-05-06 00:00:00 |     6 |      6 |      6 |
(16 rows)

Select query works fine:
# select * from site_stat where site_id = 1804 and period = '2016-05-06 00:00:00'
;
 site_id |       period        | total | unique | visits | list
---------+---------------------+-------+--------+--------+------
    1804 | 2016-05-06 00:00:00 |     6 |      6 |      6 |
(1 row)

When query mysql directly

mysql> update site_stat set total=4, "unique"=4, visits = 4 where site_id = 1804 and period = '2016-05-06 00:00:00';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"unique"=4, visits = 4 where site_id = 1804 and period = '2016-05-06 00:00:00'' at line 1

I replace " by `. Now it works.

mysql> update site_stat set total=4, `unique`=4, visits = 4 where site_id = 1804 and period = '2016-05-06 00:00:00';
Query OK, 1 row affected (1.96 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql>  select * from site_stat where site_id = 1804;
+---------+---------------------+-------+--------+--------+------+
| site_id | period              | total | unique | visits | list |
+---------+---------------------+-------+--------+--------+------+
|    1804 | 2016-04-16 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-17 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-18 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-20 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-21 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-22 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-23 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-24 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-25 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-26 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-27 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-28 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-04-29 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-05-02 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-05-05 00:00:00 |     6 |      6 |      6 | NULL |
|    1804 | 2016-05-06 00:00:00 |     4 |      4 |      4 | NULL |
+---------+---------------------+-------+--------+--------+------+
16 rows in set (0.00 sec)

Even when I remove 'unique' column from UPDATE query (to not make confusion)

 update site_stat set total=7, visits=7 where site_id = 1804 and period = cast('2016-05-06 00:00:00' as timestamp);
UPDATE 1

It also update all 16 records

# select * from site_stat where site_id = 1804;
 site_id |       period        | total | unique | visits | list 
---------+---------------------+-------+--------+--------+------
    1804 | 2016-04-16 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-17 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-18 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-20 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-21 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-22 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-23 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-24 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-25 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-26 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-27 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-28 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-04-29 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-05-02 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-05-05 00:00:00 |     7 |      6 |      7 | 
    1804 | 2016-05-06 00:00:00 |     7 |      4 |      7 | 
(16 rows)
KES777 commented 8 years ago

For this query:

update site_stat set (total, "unique", visits ) = (7,7,7) where site_id = 1804 and period = cast('2016-05-06 00:00:00' as timestamp);

Mysql log shows:

160512 20:47:48     6697 Query    SET sql_mode='ANSI_QUOTES'
         6697 Query    EXPLAIN site_stat
         6697 Query    SET time_zone = '+00:00'
         6697 Query    SET sql_mode='ANSI_QUOTES'
         6697 Prepare    SELECT `site_id`, `period`, `list` FROM `constructor`.`site_stat` WHERE ((`site_id` = 1804)) AND ((`period` = '2016-05-06 00:00:00')) FOR UPDATE
         6697 Execute    SELECT `site_id`, `period`, `list` FROM `constructor`.`site_stat` WHERE ((`site_id` = 1804)) AND ((`period` = '2016-05-06 00:00:00')) FOR UPDATE
         6697 Prepare    UPDATE `constructor`.`site_stat` SET `total` = ?, `unique` = ?, `visits` = ? WHERE site_id = ?
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Close stmt
         6697 Close stmt

As you can see the expression AND ((period = '2016-05-06 00:00:00')) for UPDATE statement is lost.

KES777 commented 8 years ago

anoter test case:

update site_stat set (total, "unique", visits ) = (7,7,7) where site_id = 1804 and visits = 7;
UPDATE 16

In logs:

         6697 Prepare    SELECT `site_id`, `period`, `list` FROM `constructor`.`site_stat` WHERE ((`site_id` = 1804)) AND ((`visits` = 7)) FOR UPDATE
         6697 Execute    SELECT `site_id`, `period`, `list` FROM `constructor`.`site_stat` WHERE ((`site_id` = 1804)) AND ((`visits` = 7)) FOR UPDATE
         6697 Prepare    UPDATE `constructor`.`site_stat` SET `total` = ?, `unique` = ?, `visits` = ? WHERE site_id = ?
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804
         6697 Execute    UPDATE `constructor`.`site_stat` SET `total` = 7, `unique` = 7, `visits` = 7 WHERE site_id = 1804