{"action":"created","issue":{"url":"https://api.github.com/repos/pingcap/tidb/issues/3563","repository_url":"https://api.github.com/repos/pingcap/tidb","labels_url":"https://api.github.com/repos/pingcap/tidb/issues/3563/labels{/name}","comments_url":"https://api.github.com/repos/pingcap/tidb/issues/3563/comments","events_url":"https://api.github.com/repos/pingcap/tidb/issues/3563/events","html_url":"https://github.com/pingcap/tidb/issues/3563","id":239358706,"node_id":"MDU6SXNzdWUyMzkzNTg3MDY=","number":3563,"title":"different behaviour between MySQL and TiDB on update multi tables","user":{"login":"hicqu","id":8407317,"node_id":"MDQ6VXNlcjg0MDczMTc=","avatar_url":"https://avatars1.githubusercontent.com/u/8407317?v=4","gravatar_id":"","url":"https://api.github.com/users/hicqu","html_url":"https://github.com/hicqu","followers_url":"https://api.github.com/users/hicqu/followers","following_url":"https://api.github.com/users/hicqu/following{/other_user}","gists_url":"https://api.github.com/users/hicqu/gists{/gist_id}","starred_url":"https://api.github.com/users/hicqu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hicqu/subscriptions","organizations_url":"https://api.github.com/users/hicqu/orgs","repos_url":"https://api.github.com/users/hicqu/repos","events_url":"https://api.github.com/users/hicqu/events{/privacy}","received_events_url":"https://api.github.com/users/hicqu/received_events","type":"User","site_admin":false},"labels":[{"id":541179553,"node_id":"MDU6TGFiZWw1NDExNzk1NTM=","url":"https://api.github.com/repos/pingcap/tidb/labels/type/compatibility","name":"type/compatibility","color":"e99695","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-06-29T03:48:42Z","updated_at":"2018-11-24T16:42:05Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"Here is the SQL:\r\n```sql\r\nDROP TABLE IF EXISTS users;\r\nCREATE TABLE users (\r\n    id INTEGER NOT NULL AUTO_INCREMENT,\r\n    name VARCHAR(30) NOT NULL,\r\n    some_update VARCHAR(30),\r\n    PRIMARY KEY (id)\r\n);\r\n\r\nDROP TABLE IF EXISTS addresses;\r\nCREATE TABLE addresses (\r\n    id INTEGER NOT NULL AUTO_INCREMENT,\r\n    user_id INTEGER,\r\n    email_address VARCHAR(50) NOT NULL,\r\n    PRIMARY KEY (id)\r\n);\r\n\r\nINSERT INTO users (id, name, some_update) VALUES\r\n(8, 'ed', 'value'),\r\n(9, 'fred', 'value');\r\n\r\nINSERT INTO addresses (id, user_id, email_address) VALUES\r\n(2, 8, 'ed@wood.com'),\r\n(3, 8, 'ed@bettyboop.com'),\r\n(4, 9, 'fred@fred.com');\r\n\r\nUPDATE addresses, users SET users.name='ed2', users.some_update='im the update', addresses.email_address=users.name\r\nWHERE users.id = addresses.user_id AND users.name = 'ed';\r\n\r\nSELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses ORDER BY addresses.id;\r\n```\r\nOn MySQL it returns:\r\n```\r\n+----+---------+---------------+\r\n| id | user_id | email_address |\r\n+----+---------+---------------+\r\n|  2 |       8 | ed            |\r\n|  3 |       8 | ed2           |\r\n|  4 |       9 | fred@fred.com |\r\n+----+---------+---------------+\r\n```\r\nBut on Tidb it returns:\r\n```\r\n+----+---------+---------------+\r\n| id | user_id | email_address |\r\n+----+---------+---------------+\r\n|  2 |       8 | ed2           |\r\n|  3 |       8 | ed2           |\r\n|  4 |       9 | fred@fred.com |\r\n+----+---------+---------------+\r\n```\r\n\r\nIt seems in MySQL, when the record has been changed, the other side of join can know that, so only one row in `addresses` can be modified. But in TiDB, both two records are updated.\r\n\r\nIn postgres, we can't update multi tables in one transaction, so there is no this problem.\r\n\r\nWhich behaviour is better? @hanfei1991 "},"comment":{"url":"https://api.github.com/repos/pingcap/tidb/issues/comments/441380048","html_url":"https://github.com/pingcap/tidb/issues/3563#issuecomment-441380048","issue_url":"https://api.github.com/repos/pingcap/tidb/issues/3563","id":441380048,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MTM4MDA0OA==","user":{"login":"morgo","id":57982,"node_id":"MDQ6VXNlcjU3OTgy","avatar_url":"https://avatars2.githubusercontent.com/u/57982?v=4","gravatar_id":"","url":"https://api.github.com/users/morgo","html_url":"https://github.com/morgo","followers_url":"https://api.github.com/users/morgo/followers","following_url":"https://api.github.com/users/morgo/following{/other_user}","gists_url":"https://api.github.com/users/morgo/gists{/gist_id}","starred_url":"https://api.github.com/users/morgo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/morgo/subscriptions","organizations_url":"https://api.github.com/users/morgo/orgs","repos_url":"https://api.github.com/users/morgo/repos","events_url":"https://api.github.com/users/morgo/events{/privacy}","received_events_url":"https://api.github.com/users/morgo/received_events","type":"User","site_admin":false},"created_at":"2018-11-24T16:42:05Z","updated_at":"2018-11-24T16:42:05Z","author_association":"MEMBER","body":"Confirming that this is still the case in TiDB:\r\n```\r\nmysql> SELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses ORDER BY addresses.id;\r\n+----+---------+---------------+\r\n| id | user_id | email_address |\r\n+----+---------+---------------+\r\n|  2 |       8 | ed2           |\r\n|  3 |       8 | ed2           |\r\n|  4 |       9 | fred@fred.com |\r\n+----+---------+---------------+\r\n3 rows in set (0.00 sec)\r\n\r\nmysql> select tidb_version()\\G\r\n*************************** 1. row ***************************\r\ntidb_version(): Release Version: v2.1.0-rc.3-219-g1e0876fe8-dirty\r\nGit Commit Hash: 1e0876fe810a832721aac52275dd2b7792fd2892\r\nGit Branch: flush\r\nUTC Build Time: 2018-11-24 01:12:47\r\nGoVersion: go version go1.11 linux/amd64\r\nRace Enabled: false\r\nTiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e\r\nCheck Table Before Drop: false\r\n1 row in set (0.00 sec)\r\n```"}}
{"actor":{"display_login":"morgo"}}