{"action":"created","issue":{"url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/4","repository_url":"https://api.github.com/repos/natsys/mariadb_10.2","labels_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/4/labels{/name}","comments_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/4/comments","events_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/4/events","html_url":"https://github.com/natsys/mariadb_10.2/issues/4","id":175220702,"number":4,"title":"Select from view over versioned table returns empty resultset","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":"ee0701"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2016-09-06T11:10:28Z","updated_at":"2016-09-06T11:10:32Z","closed_at":null,"body":"<a href=\"https://github.com/hagrid-the-developer\"><img src=\"https://avatars.githubusercontent.com/u/2307024?v=3\" align=\"left\" width=\"96\" height=\"96\" hspace=\"10\"></img></a> **Issue by [hagrid-the-developer](https://github.com/hagrid-the-developer)**\n_Monday Sep 05, 2016 at 08:12 GMT_\n_Originally opened as https://github.com/natsys/mariadb/issues/3_\n\n----\n\nIt seems that when one creates system versioned table, creates view over it, runs some insert(s) over the table and/or view and then runs select over the view, then select over view in mysql/mariadb client returns correct values. On the other hand in tests it returns empty set.\r\n\r\nEg. in mysql client:\r\n\r\n    > CREATE TABLE t1(x INT UNSIGNED, y INT UNSIGNED, Sys_start TIMESTAMP(6) GENERATED  ALWAYS AS ROW START, Sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (Sys_start, Sys_end)) WITH SYSTEM VERSIONING;\r\n    Query OK, 0 rows affected (0.28 sec)\r\n\r\n    > create view vt1 as select x, y from t1;\r\n    Query OK, 0 rows affected (0.05 sec)\r\n\r\n    > insert into t1(x, y) values(1,10);\r\n    Query OK, 1 row affected (0.04 sec)\r\n\r\n    > select * from t1;\r\n     +------+------+----------------------------+----------------------------+\r\n     | x    | y    | Sys_start                  | Sys_end                    |\r\n     +------+------+----------------------------+----------------------------+\r\n     |    1 |   10 | 2016-09-05 10:01:44.392996 | 2038-01-19 04:14:07.000000 |\r\n    +------+------+----------------------------+----------------------------+\r\n    1 row in set (0.01 sec)\r\n\r\n    > select * from vt1;\r\n    +------+------+\r\n    | x    | y    |\r\n    +------+------+\r\n    |    1 |   10 |\r\n    +------+------+\r\n    1 row in set (0.01 sec)\r\n\r\nBut in tests, similar statements lead to empty result for select over view, eg. see [insert test](https://github.com/natsys/mariadb_10.2/blob/df/syntax/mysql-test/r/insert.result#L755-L764) .\n"},"comment":{"url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/comments/244919518","html_url":"https://github.com/natsys/mariadb_10.2/issues/4#issuecomment-244919518","issue_url":"https://api.github.com/repos/natsys/mariadb_10.2/issues/4","id":244919518,"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},"created_at":"2016-09-06T11:10:32Z","updated_at":"2016-09-06T11:10:32Z","body":"<a href=\"https://github.com/midenok\"><img src=\"https://avatars.githubusercontent.com/u/3307037?v=3\" align=\"left\" width=\"48\" height=\"48\" hspace=\"10\"></img></a> **Comment by [midenok](https://github.com/midenok)**\n_Monday Sep 05, 2016 at 13:28 GMT_\n\n----\n\n#### Important: some delays caused by stepping inside `send_data` lead to correct output.\r\n\r\n## Protocol analysis\r\n```\r\nb net_serv.cc:404\r\ncommands\r\nbt\r\nc\r\nend\r\n```\r\n\r\n### Metadata\r\n```\r\n#0  my_net_write (net=0x7fffdf452330, packet=0x7ffff7f768a0 \"\\002j\\367\\367\\377\\177\", len=1) at /home/midenok/src/mariadb/hagrid/src/sql/net_serv.cc:404\r\n#1  0x00000000005d2814 in Protocol::send_result_set_metadata (this=0x7fffdf452600, list=0x7fffdf456328, flags=5) at /home/midenok/src/mariadb/hagrid/src/sql/protocol.cc:742\r\n#2  0x000000000064ec18 in select_send::send_result_set_metadata (this=0x7fffdf523a78, list=..., flags=5) at /home/midenok/src/mariadb/hagrid/src/sql/sql_class.cc:2810\r\n#3  0x00000000006d897b in JOIN::exec_inner (this=0x7fffdf523a98) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:3416\r\n#4  0x00000000006d7dac in JOIN::exec (this=0x7fffdf523a98) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:3229\r\n#5  0x00000000006d90e9 in mysql_select (thd=0x7fffdf452070, tables=0x7fffdf523380, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_o\r\nptions=2147748608, result=0x7fffdf523a78, unit=0x7fffdf455ac8, select_lex=0x7fffdf456210) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:3613\r\n#6  0x00000000006cdd3a in handle_select (thd=0x7fffdf452070, lex=0x7fffdf455a00, result=0x7fffdf523a78, setup_tables_done_option=0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select\r\n```\r\n\r\n### Data\r\n```\r\n#0  my_net_write (net=0x7fffdf452330, packet=0x7fffdf51e070 \"\\004\\070\\060\\060\\061\\004\\071\\060\\060\\061t1\\002t1\\001y\\001y\\f?\", len=10) at /home/midenok/src/mariadb/hagrid/src/sql/net_serv.\r\ncc:404\r\n#1  0x00000000005d31e1 in Protocol::write (this=0x7fffdf452600) at /home/midenok/src/mariadb/hagrid/src/sql/protocol.cc:882\r\n#2  0x000000000064ee66 in select_send::send_data (this=0x7fffdf523a78, items=...) at /home/midenok/src/mariadb/hagrid/src/sql/sql_class.cc:2880\r\n#3  0x0000000000700e8a in end_send (join=0x7fffdf523a98, join_tab=0x7fffdf525dd8, end_of_records=false) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:19507\r\n#4  0x00000000006fe931 in evaluate_join_record (join=0x7fffdf523a98, join_tab=0x7fffdf525a30, error=0) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:18558\r\n#5  0x00000000006fe152 in sub_select (join=0x7fffdf523a98, join_tab=0x7fffdf525a30, end_of_records=false) at /home/midenok/src/mariadb/hagrid/src/sql/sql_select.cc:18333\r\n```\r\n\r\n### `len` analysis\r\n#### Incorrect behaviour\r\n##### TABLE\r\n* Metadata: 1 + 32 + 32\r\n* Data: 10\r\n\r\n##### VIEW\r\n* Metadata: 1 + 38 + 38\r\n* Data: **no data!**\r\n\r\nIn automated mode (without delays) no data packets was sent for VIEW!\n"}}
{"actor":{"display_login":"midenok"}}