{"action":"opened","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/30734","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/30734/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/30734/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/30734/events","html_url":"https://github.com/cockroachdb/cockroach/issues/30734","id":364608516,"node_id":"MDU6SXNzdWUzNjQ2MDg1MTY=","number":30734,"title":"sql: a delete query uses the wrong index","user":{"login":"jordanlewis","id":43821,"node_id":"MDQ6VXNlcjQzODIx","avatar_url":"https://avatars1.githubusercontent.com/u/43821?v=4","gravatar_id":"","url":"https://api.github.com/users/jordanlewis","html_url":"https://github.com/jordanlewis","followers_url":"https://api.github.com/users/jordanlewis/followers","following_url":"https://api.github.com/users/jordanlewis/following{/other_user}","gists_url":"https://api.github.com/users/jordanlewis/gists{/gist_id}","starred_url":"https://api.github.com/users/jordanlewis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jordanlewis/subscriptions","organizations_url":"https://api.github.com/users/jordanlewis/orgs","repos_url":"https://api.github.com/users/jordanlewis/repos","events_url":"https://api.github.com/users/jordanlewis/events{/privacy}","received_events_url":"https://api.github.com/users/jordanlewis/received_events","type":"User","site_admin":false},"labels":[{"id":843052634,"node_id":"MDU6TGFiZWw4NDMwNTI2MzQ=","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/A-sql-planning","name":"A-sql-planning","color":"22aa88","default":false},{"id":78928345,"node_id":"MDU6TGFiZWw3ODkyODM0NQ==","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/C-bug","name":"C-bug","color":"c5def5","default":false},{"id":560667564,"node_id":"MDU6TGFiZWw1NjA2Njc1NjQ=","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/O-support","name":"O-support","color":"666ad1","default":false}],"state":"open","locked":false,"assignee":{"login":"knz","id":642886,"node_id":"MDQ6VXNlcjY0Mjg4Ng==","avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false},"assignees":[{"login":"knz","id":642886,"node_id":"MDQ6VXNlcjY0Mjg4Ng==","avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false}],"milestone":null,"comments":0,"created_at":"2018-09-27T18:53:29Z","updated_at":"2018-09-27T18:53:29Z","closed_at":null,"author_association":"MEMBER","body":"Reported privately. The following table (scrubbed of identifying information) produces an incorrect plan when being deleted from.\r\n\r\n```\r\nroot@127.0.0.1:53703/defaultdb> CREATE TABLE a (\r\n    a INT NOT NULL,\r\n    b STRING NOT NULL,\r\n    c INT NOT NULL,\r\n    d INT NOT NULL,\r\n    e STRING NOT NULL,\r\n    f BOOL NOT NULL DEFAULT false,\r\n    g TIMESTAMP NOT NULL DEFAULT now(),\r\n    h STRING NOT NULL DEFAULT 'foo',\r\n    i TIMESTAMP NOT NULL DEFAULT now(),\r\n    j STRING NOT NULL DEFAULT 'foo',\r\n    k DATE NULL,\r\n    l STRING NULL,\r\n    PRIMARY KEY (a, b, c, d, e, f),\r\n    INDEX (a, e, k, l) STORING (a, b, c, d, e, f),\r\n    INDEX (a, e, k, l) STORING (g, h)\r\n);\r\n\r\nTime: 6.448ms\r\n\r\nroot@127.0.0.1:53703/defaultdb> explain delete from a where a=10 and b='10' and c=1 and d=3;\r\n         tree         | field |   description\r\n+---------------------+-------+-----------------+\r\n  count               |       |\r\n   └── delete         |       |\r\n        │             | from  | a\r\n        └── render    |       |\r\n             └── scan |       |\r\n                      | table | a@a_a_e_k_l_idx\r\n                      | spans | /10-/11\r\n(7 rows)\r\n```\r\n\r\nThe delete should search the primary index, as it can provide a more precise span.\r\n\r\ncc @andy-kimball, but we have to fix this in the heuristic planner if we wish to provide a fix for the 2.0 branch."}}
{"actor":{"display_login":"jordanlewis"}}