{"action":"created","issue":{"url":"https://api.github.com/repos/pingcap/tidb/issues/6933","repository_url":"https://api.github.com/repos/pingcap/tidb","labels_url":"https://api.github.com/repos/pingcap/tidb/issues/6933/labels{/name}","comments_url":"https://api.github.com/repos/pingcap/tidb/issues/6933/comments","events_url":"https://api.github.com/repos/pingcap/tidb/issues/6933/events","html_url":"https://github.com/pingcap/tidb/issues/6933","id":336619693,"node_id":"MDU6SXNzdWUzMzY2MTk2OTM=","number":6933,"title":"use TableDual when the access range is empty","user":{"login":"zz-jason","id":5268763,"node_id":"MDQ6VXNlcjUyNjg3NjM=","avatar_url":"https://avatars0.githubusercontent.com/u/5268763?v=4","gravatar_id":"","url":"https://api.github.com/users/zz-jason","html_url":"https://github.com/zz-jason","followers_url":"https://api.github.com/users/zz-jason/followers","following_url":"https://api.github.com/users/zz-jason/following{/other_user}","gists_url":"https://api.github.com/users/zz-jason/gists{/gist_id}","starred_url":"https://api.github.com/users/zz-jason/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zz-jason/subscriptions","organizations_url":"https://api.github.com/users/zz-jason/orgs","repos_url":"https://api.github.com/users/zz-jason/repos","events_url":"https://api.github.com/users/zz-jason/events{/privacy}","received_events_url":"https://api.github.com/users/zz-jason/received_events","type":"User","site_admin":false},"labels":[{"id":667671533,"node_id":"MDU6TGFiZWw2Njc2NzE1MzM=","url":"https://api.github.com/repos/pingcap/tidb/labels/component/planner","name":"component/planner","color":"1d76db","default":false},{"id":257000608,"node_id":"MDU6TGFiZWwyNTcwMDA2MDg=","url":"https://api.github.com/repos/pingcap/tidb/labels/status/TODO","name":"status/TODO","color":"207de5","default":false},{"id":256872088,"node_id":"MDU6TGFiZWwyNTY4NzIwODg=","url":"https://api.github.com/repos/pingcap/tidb/labels/type/enhancement","name":"type/enhancement","color":"84b6eb","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-06-28T13:23:19Z","updated_at":"2018-08-28T05:32:49Z","closed_at":null,"author_association":"MEMBER","body":"Let's say we have the following table:\r\n```sql\r\ndrop table if exists t;\r\ncreate table t(a decimal primary key);\r\n```\r\n\r\nThe following query leads to an empty range, which indicates no data will be read from the storage:\r\n```sql\r\nTiDB(localhost:4000) > desc select * from t where a>5 and a<3;\r\n+-------------------+------+------------------------------------+-------+\r\n| id                | task | operator info                      | count |\r\n+-------------------+------+------------------------------------+-------+\r\n| IndexReader_9     | root | index:IndexScan_8                  | 0.00  |\r\n| └─IndexScan_8     | cop  | table:t, index:a, keep order:false | 0.00  |\r\n+-------------------+------+------------------------------------+-------+\r\n2 rows in set (0.00 sec)\r\n```\r\n\r\nWe can replace the whole operator DAG with a `TableDual` to further reduce the execution overhead.\r\n\r\nFor further optimization, we can add a rule which operates on `TableDual` to remove the unnecessary execution overhead. For example, we can replace an **inner join** with a `TableDual` when one of its child is a `TableDual`, that is to say, the following DAG can be optimized to a single `TableDual` operator as well:\r\n```sql\r\nTiDB(localhost:4000) > desc select * from t join t1 where t.a>5 and t.a<3;\r\n+------------------------+------+-----------------------------------------------+--------------+\r\n| id                     | task | operator info                                 | count        |\r\n+------------------------+------+-----------------------------------------------+--------------+\r\n| HashRightJoin_9        | root | inner join, inner:IndexReader_14              | 100000000.00 |\r\n| ├─IndexReader_14       | root | index:IndexScan_13                            | 0.00         |\r\n| │ └─IndexScan_13       | cop  | table:t, index:a, keep order:false            | 0.00         |\r\n| └─TableReader_16       | root | data:TableScan_15                             | 10000.00     |\r\n|   └─TableScan_15       | cop  | table:t1, range:[-inf,+inf], keep order:false | 10000.00     |\r\n+------------------------+------+-----------------------------------------------+--------------+\r\n5 rows in set (0.00 sec)\r\n```"},"comment":{"url":"https://api.github.com/repos/pingcap/tidb/issues/comments/416456523","html_url":"https://github.com/pingcap/tidb/issues/6933#issuecomment-416456523","issue_url":"https://api.github.com/repos/pingcap/tidb/issues/6933","id":416456523,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNjQ1NjUyMw==","user":{"login":"winoros","id":7846227,"node_id":"MDQ6VXNlcjc4NDYyMjc=","avatar_url":"https://avatars1.githubusercontent.com/u/7846227?v=4","gravatar_id":"","url":"https://api.github.com/users/winoros","html_url":"https://github.com/winoros","followers_url":"https://api.github.com/users/winoros/followers","following_url":"https://api.github.com/users/winoros/following{/other_user}","gists_url":"https://api.github.com/users/winoros/gists{/gist_id}","starred_url":"https://api.github.com/users/winoros/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/winoros/subscriptions","organizations_url":"https://api.github.com/users/winoros/orgs","repos_url":"https://api.github.com/users/winoros/repos","events_url":"https://api.github.com/users/winoros/events{/privacy}","received_events_url":"https://api.github.com/users/winoros/received_events","type":"User","site_admin":false},"created_at":"2018-08-28T05:32:49Z","updated_at":"2018-08-28T05:32:49Z","author_association":"MEMBER","body":"@shenli It works but not enough. It's range may become nil in future optimization."}}
{"actor":{"display_login":"winoros"}}