{"action":"created","issue":{"url":"https://api.github.com/repos/h2database/h2database/issues/529","repository_url":"https://api.github.com/repos/h2database/h2database","labels_url":"https://api.github.com/repos/h2database/h2database/issues/529/labels{/name}","comments_url":"https://api.github.com/repos/h2database/h2database/issues/529/comments","events_url":"https://api.github.com/repos/h2database/h2database/issues/529/events","html_url":"https://github.com/h2database/h2database/issues/529","id":235980070,"number":529,"title":"query runs endlessly with H2 version >= 1.4.195","user":{"login":"chdh","id":1008944,"avatar_url":"https://avatars3.githubusercontent.com/u/1008944?v=4","gravatar_id":"","url":"https://api.github.com/users/chdh","html_url":"https://github.com/chdh","followers_url":"https://api.github.com/users/chdh/followers","following_url":"https://api.github.com/users/chdh/following{/other_user}","gists_url":"https://api.github.com/users/chdh/gists{/gist_id}","starred_url":"https://api.github.com/users/chdh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chdh/subscriptions","organizations_url":"https://api.github.com/users/chdh/orgs","repos_url":"https://api.github.com/users/chdh/repos","events_url":"https://api.github.com/users/chdh/events{/privacy}","received_events_url":"https://api.github.com/users/chdh/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2017-06-14T19:08:23Z","updated_at":"2017-07-30T17:08:50Z","closed_at":null,"body":"I have a complex query in a web application which used to run normal for several years. But with newer H2 versions, it takes endless time. I managed to reduce the query and produce a test case:\r\n\r\n```\r\ndrop table if exists temp1;\r\n\r\ncreate table temp1 (\r\n   f1 int not null primary key,\r\n   f2 int );\r\n\r\ninsert into temp1\r\n   select x, x/100\r\n      from system_range(1, 90000);\r\n\r\ncreate index temp1_index1\r\n   on temp1 (f2, f1);\r\n\r\nselect a.f1\r\n   from temp1 a\r\n       inner join (\r\n          select f1\r\n             from temp1\r\n             where f2 in (-1, -2)\r\n       ) b on a.f1 = b.f1\r\n  order by a.f1\r\n```\r\n\r\nThis test case works with H2 versions up to 1.4.194, but runs endlessly with 1.4.195 and 1.4.196.\r\n\r\nThe web application is a bit different than this test case. It only works up to H2 version 1.4.190 and the problem starts already with 1.4.191."},"comment":{"url":"https://api.github.com/repos/h2database/h2database/issues/comments/318915298","html_url":"https://github.com/h2database/h2database/issues/529#issuecomment-318915298","issue_url":"https://api.github.com/repos/h2database/h2database/issues/529","id":318915298,"user":{"login":"grandinj","id":796121,"avatar_url":"https://avatars1.githubusercontent.com/u/796121?v=4","gravatar_id":"","url":"https://api.github.com/users/grandinj","html_url":"https://github.com/grandinj","followers_url":"https://api.github.com/users/grandinj/followers","following_url":"https://api.github.com/users/grandinj/following{/other_user}","gists_url":"https://api.github.com/users/grandinj/gists{/gist_id}","starred_url":"https://api.github.com/users/grandinj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/grandinj/subscriptions","organizations_url":"https://api.github.com/users/grandinj/orgs","repos_url":"https://api.github.com/users/grandinj/repos","events_url":"https://api.github.com/users/grandinj/events{/privacy}","received_events_url":"https://api.github.com/users/grandinj/received_events","type":"User","site_admin":false},"created_at":"2017-07-30T17:08:50Z","updated_at":"2017-07-30T17:08:50Z","body":"In theory, the way the planner works is this\r\n\r\n    if the number of joins is small then\r\n        calculate the cost of executing the joins in all possible combinations, pick the smallest one\r\n   else\r\n        choose a bunch of different join combinations and calculate their costs, pick the smallest one\r\n\r\nsince we only have one or two joins here, we should be examining all possible combinations.\r\n\r\nWhich means we have a bug in our cost calculations somewhere.\r\n\r\nWe should already be multiplying outer node costs by inner node costs, but somewhere we are probably mis-estimating the number of rows involved.\r\nWhich is not surprising because in this case the inner loop and the outer loop are linked.\r\n\r\n"}}
{"actor":{"display_login":"grandinj"}}