{"action":"closed","issue":{"url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/52","repository_url":"https://api.github.com/repos/natsys/mariadb_10.2","labels_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/52/labels{/name}","comments_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/52/comments","events_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/52/events","html_url":"https://github.com/natsys/mariadb_10.2/issues/52","id":183614412,"number":52,"title":"saved_where does not provide idempotency on INNER JOIN inside SP","user":{"login":"midenok","id":3307037,"avatar_url":"https://avatars.githubusercontent.com/u/3307037?v=3","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/natsys/mariadb_10.2/labels/bug","name":"bug","color":"b60205"}],"state":"closed","locked":false,"assignee":{"login":"midenok","id":3307037,"avatar_url":"https://avatars.githubusercontent.com/u/3307037?v=3","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false},"assignees":[{"login":"midenok","id":3307037,"avatar_url":"https://avatars.githubusercontent.com/u/3307037?v=3","gravatar_id":"","url":"https://api.github.com/users/midenok","html_url":"https://github.com/midenok","followers_url":"https://api.github.com/users/midenok/followers","following_url":"https://api.github.com/users/midenok/following{/other_user}","gists_url":"https://api.github.com/users/midenok/gists{/gist_id}","starred_url":"https://api.github.com/users/midenok/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/midenok/subscriptions","organizations_url":"https://api.github.com/users/midenok/orgs","repos_url":"https://api.github.com/users/midenok/repos","events_url":"https://api.github.com/users/midenok/events{/privacy}","received_events_url":"https://api.github.com/users/midenok/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/natsys/mariadb_10.2/milestones/3","html_url":"https://github.com/natsys/mariadb_10.2/milestones/0.3%20Our%20extensions","labels_url":"https://api.github.com/repos/natsys/mariadb_10.2/milestones/3/labels","id":2028923,"number":3,"title":"0.3 Our extensions","description":"0.40 (should be 0.30) Our extensions:\r\n    * Omitting sys_trx_start, sys_trx_end, and period from CREATE TABLE.\r\n    * Per column versioning.\r\n","creator":{"login":"krizhanovsky","id":884694,"avatar_url":"https://avatars.githubusercontent.com/u/884694?v=3","gravatar_id":"","url":"https://api.github.com/users/krizhanovsky","html_url":"https://github.com/krizhanovsky","followers_url":"https://api.github.com/users/krizhanovsky/followers","following_url":"https://api.github.com/users/krizhanovsky/following{/other_user}","gists_url":"https://api.github.com/users/krizhanovsky/gists{/gist_id}","starred_url":"https://api.github.com/users/krizhanovsky/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/krizhanovsky/subscriptions","organizations_url":"https://api.github.com/users/krizhanovsky/orgs","repos_url":"https://api.github.com/users/krizhanovsky/repos","events_url":"https://api.github.com/users/krizhanovsky/events{/privacy}","received_events_url":"https://api.github.com/users/krizhanovsky/received_events","type":"User","site_admin":false},"open_issues":1,"closed_issues":4,"state":"open","created_at":"2016-09-26T13:58:35Z","updated_at":"2016-10-18T17:46:45Z","due_on":"2016-10-21T00:00:00Z","closed_at":null},"comments":1,"created_at":"2016-10-18T07:55:55Z","updated_at":"2016-10-18T17:46:45Z","closed_at":"2016-10-18T17:46:45Z","body":"```sql\r\ndelimiter ~~\r\ncreate or replace procedure creat(\r\n  sys_type varchar(255),\r\n  engine varchar(255),\r\n  fields varchar(255))\r\nbegin\r\n  set @str0= concat('(\r\n    x int,\r\n    y int,\r\n    sys_start ', sys_type, ' generated always as row start,\r\n    sys_end ', sys_type, ' generated always as row end,\r\n    period for system_time (sys_start, sys_end))\r\n  with system versioning\r\n  engine ', engine);\r\n  set @str= concat('create or replace table t1', @str0);\r\n  prepare stmt from @str; execute stmt; drop prepare stmt;\r\n  set @str= concat('create or replace table t2', @str0);\r\n  prepare stmt from @str; execute stmt; drop prepare stmt;\r\n  \r\n  insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);\r\n  insert into t2 values (1, 2), (2, 1), (3, 1);\r\nend~~\r\n\r\ncreate or replace procedure test()\r\nbegin\r\n  select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x where t1.y > 1 and t2.y = 2;\r\nend~~\r\n\r\ncreate or replace procedure test2() modifies sql data\r\nbegin\r\n  select t1.x as x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1, t2 where t1.y > 1 and t2.y = 2;\r\nend~~\r\ndelimiter ;\r\n```\r\n```\r\nMariaDB [test]> call test;\r\n+--------+------+------+------+\r\n| IJ1_x1 | y1   | x2   | y2   |\r\n+--------+------+------+------+\r\n|      1 |    2 |    1 |    2 |\r\n|      1 |    3 |    1 |    2 |\r\n+--------+------+------+------+\r\n2 rows in set (0.01 sec)\r\n\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\nMariaDB [test]> call test;\r\n+--------+------+------+------+\r\n| IJ1_x1 | y1   | x2   | y2   |\r\n+--------+------+------+------+\r\n|      1 |    2 |    1 |    2 |\r\n|      1 |    3 |    1 |    2 |\r\n+--------+------+------+------+\r\n2 rows in set (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [test]> call test;\r\n+--------+------+------+------+\r\n| IJ1_x1 | y1   | x2   | y2   |\r\n+--------+------+------+------+\r\n|      1 |    1 |    1 |    2 |\r\n|      1 |    2 |    1 |    2 |\r\n|      1 |    3 |    1 |    2 |\r\n+--------+------+------+------+\r\n3 rows in set (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [test]> call test2;\r\n+------+------+------+------+\r\n| x1   | y1   | x2   | y2   |\r\n+------+------+------+------+\r\n|    1 |    2 |    1 |    2 |\r\n|    1 |    3 |    1 |    2 |\r\n|    4 |    4 |    1 |    2 |\r\n|    5 |    5 |    1 |    2 |\r\n+------+------+------+------+\r\n4 rows in set (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [test]> call test2;\r\n+------+------+------+------+\r\n| x1   | y1   | x2   | y2   |\r\n+------+------+------+------+\r\n|    1 |    2 |    1 |    2 |\r\n|    1 |    3 |    1 |    2 |\r\n|    4 |    4 |    1 |    2 |\r\n|    5 |    5 |    1 |    2 |\r\n+------+------+------+------+\r\n4 rows in set (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nMariaDB [test]> call test2;\r\n+------+------+------+------+\r\n| x1   | y1   | x2   | y2   |\r\n+------+------+------+------+\r\n|    1 |    1 |    1 |    2 |\r\n|    1 |    1 |    2 |    1 |\r\n|    1 |    1 |    3 |    1 |\r\n|    1 |    2 |    1 |    2 |\r\n|    1 |    2 |    2 |    1 |\r\n|    1 |    2 |    3 |    1 |\r\n|    1 |    3 |    1 |    2 |\r\n|    1 |    3 |    2 |    1 |\r\n|    1 |    3 |    3 |    1 |\r\n|    4 |    4 |    1 |    2 |\r\n|    4 |    4 |    2 |    1 |\r\n|    4 |    4 |    3 |    1 |\r\n|    5 |    5 |    1 |    2 |\r\n|    5 |    5 |    2 |    1 |\r\n|    5 |    5 |    3 |    1 |\r\n+------+------+------+------+\r\n15 rows in set (0.00 sec)\r\n\r\nQuery OK, 0 rows affected (0.00 sec)\r\n```\r\n\r\nRecovery of `saved_where` have no effect on third execution of SP. Though it works good when there is no INNER JOIN."}}
{"actor":{"display_login":"midenok"}}