{"action":"created","issue":{"url":"https://api.github.com/repos/pingcap/tidb/issues/3959","repository_url":"https://api.github.com/repos/pingcap/tidb","labels_url":"https://api.github.com/repos/pingcap/tidb/issues/3959/labels{/name}","comments_url":"https://api.github.com/repos/pingcap/tidb/issues/3959/comments","events_url":"https://api.github.com/repos/pingcap/tidb/issues/3959/events","html_url":"https://github.com/pingcap/tidb/issues/3959","id":246776373,"node_id":"MDU6SXNzdWUyNDY3NzYzNzM=","number":3959,"title":"no support of `ON DUPLICATE KEY UPDATE col=DEFAULT`","user":{"login":"XuHuaiyu","id":9039012,"node_id":"MDQ6VXNlcjkwMzkwMTI=","avatar_url":"https://avatars1.githubusercontent.com/u/9039012?v=4","gravatar_id":"","url":"https://api.github.com/users/XuHuaiyu","html_url":"https://github.com/XuHuaiyu","followers_url":"https://api.github.com/users/XuHuaiyu/followers","following_url":"https://api.github.com/users/XuHuaiyu/following{/other_user}","gists_url":"https://api.github.com/users/XuHuaiyu/gists{/gist_id}","starred_url":"https://api.github.com/users/XuHuaiyu/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/XuHuaiyu/subscriptions","organizations_url":"https://api.github.com/users/XuHuaiyu/orgs","repos_url":"https://api.github.com/users/XuHuaiyu/repos","events_url":"https://api.github.com/users/XuHuaiyu/events{/privacy}","received_events_url":"https://api.github.com/users/XuHuaiyu/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-07-31T14:30:27Z","updated_at":"2018-10-28T21:29:02Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"In MySQL:\r\n``` sql\r\nmysql> create table t1 (a int unique); \r\nmysql> create table t2 (b int default 10);\r\nmysql> insert into t1 (a) values (1);\r\nmysql> insert into t2 (b) values (1); \r\n\r\nmysql> insert into t1 (a) select b from t2 on duplicate key update a=default; \r\nQuery OK, 2 rows affected (0.02 sec)\r\nRecords: 1  Duplicates: 1  Warnings: 0\r\n\r\nmysql> select * from t1;\r\n+------+\r\n| a    |\r\n+------+\r\n| NULL |\r\n+------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> insert into t1 (a) values (1); \r\n\r\nmysql> insert into t1 (a) select b from t2 on duplicate key update a=default(b); \r\nQuery OK, 2 rows affected (0.01 sec)\r\nRecords: 1  Duplicates: 1  Warnings: 0\r\n\r\nmysql> select * from t1;\r\n+------+\r\n| a    |\r\n+------+\r\n| NULL |\r\n|   10 |\r\n+------+\r\n\r\nmysql> drop table t1, t2;\r\n```\r\n\r\nIn TiDB:\r\n``` sql\r\ntidb> create table t1 (a int unique); \r\ntidb> create table t2 (b int default 10);\r\ntidb> insert into t1 (a) values (1);\r\ntidb> insert into t2 (b) values (1); \r\n\r\ntidb> insert into t1 (a) select b from t2 on duplicate key update a=default; \r\nERROR 1105 (HY000): UnknownType: *ast.DefaultExpr\r\n\r\ntidb> select * from t1;\r\n+------+\r\n| a    |\r\n+------+\r\n|    1 |\r\n+------+\r\n1 row in set (0.00 sec)\r\n\r\ntidb> insert into t1 (a) values (1); \r\nERROR 1062 (23000): Duplicate entry '1' for key 'a'\r\n\r\ntidb> insert into t1 (a) select b from t2 on duplicate key update a=default(b); \r\nERROR 1105 (HY000): Unknown column   b.\r\n\r\ntidb> select * from t1;\r\n+------+\r\n| a    |\r\n+------+\r\n|    1 |\r\n+------+\r\n1 row in set (0.00 sec)\r\n\r\ntidb> drop table t1, t2;\r\n```\r\n\r\n\r\nWhat version of TiDB are you using (`tidb-server -V`)?\r\n```\r\nGit Commit Hash: 91fa992f1da80f1065891bb80d81b7f20a883ef7\r\nUTC Build Time: 2017-07-28 04:30:15\r\n```"},"comment":{"url":"https://api.github.com/repos/pingcap/tidb/issues/comments/433743155","html_url":"https://github.com/pingcap/tidb/issues/3959#issuecomment-433743155","issue_url":"https://api.github.com/repos/pingcap/tidb/issues/3959","id":433743155,"node_id":"MDEyOklzc3VlQ29tbWVudDQzMzc0MzE1NQ==","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-10-28T21:29:02Z","updated_at":"2018-10-28T21:29:02Z","author_association":"MEMBER","body":"Confirmed this issue is still present:\r\n```\r\nMySQL [test]> insert into t1 (a) select b from t2 on duplicate key update a=default(b); \r\nERROR 1105 (HY000): UnknownType: *ast.DefaultExpr\r\nMySQL [test]> select tidb_version()\\G\r\n*************************** 1. row ***************************\r\ntidb_version(): Release Version: v2.1.0-rc.3-98-gd65ce86\r\nGit Commit Hash: d65ce8655cd26bb5ec44f9239562013af692d2fa\r\nGit Branch: master\r\nUTC Build Time: 2018-10-24 08:22:37\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"}}