{"action":"created","issue":{"url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1460","repository_url":"https://api.github.com/repos/greenplum-db/gpdb","labels_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1460/labels{/name}","comments_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1460/comments","events_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1460/events","html_url":"https://github.com/greenplum-db/gpdb/pull/1460","id":196492639,"number":1460,"title":"Refactor planner code's handling of sort ordering in Motion nodes","user":{"login":"hlinnaka","id":191602,"avatar_url":"https://avatars.githubusercontent.com/u/191602?v=3","gravatar_id":"","url":"https://api.github.com/users/hlinnaka","html_url":"https://github.com/hlinnaka","followers_url":"https://api.github.com/users/hlinnaka/followers","following_url":"https://api.github.com/users/hlinnaka/following{/other_user}","gists_url":"https://api.github.com/users/hlinnaka/gists{/gist_id}","starred_url":"https://api.github.com/users/hlinnaka/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hlinnaka/subscriptions","organizations_url":"https://api.github.com/users/hlinnaka/orgs","repos_url":"https://api.github.com/users/hlinnaka/repos","events_url":"https://api.github.com/users/hlinnaka/events{/privacy}","received_events_url":"https://api.github.com/users/hlinnaka/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-12-19T18:53:50Z","updated_at":"2016-12-21T22:02:48Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/greenplum-db/gpdb/pulls/1460","html_url":"https://github.com/greenplum-db/gpdb/pull/1460","diff_url":"https://github.com/greenplum-db/gpdb/pull/1460.diff","patch_url":"https://github.com/greenplum-db/gpdb/pull/1460.patch"},"body":"@hsyuan asked me to have a quick look at PR #1450. I'm not entirely sure why it didn't work as intended, but it got me thinking, why we need to track sort ordering in Flow nodes in the first place. It seems error-prone, having to keep the column index information up-to-date throughout the later stages of the planner, given that PostgreSQL doesn't need to do that. Digging into that rabbit hole, I ended up with this patch.\r\n"},"comment":{"url":"https://api.github.com/repos/greenplum-db/gpdb/issues/comments/268653379","html_url":"https://github.com/greenplum-db/gpdb/pull/1460#issuecomment-268653379","issue_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1460","id":268653379,"user":{"login":"bhuvnesh2703","id":10293569,"avatar_url":"https://avatars.githubusercontent.com/u/10293569?v=3","gravatar_id":"","url":"https://api.github.com/users/bhuvnesh2703","html_url":"https://github.com/bhuvnesh2703","followers_url":"https://api.github.com/users/bhuvnesh2703/followers","following_url":"https://api.github.com/users/bhuvnesh2703/following{/other_user}","gists_url":"https://api.github.com/users/bhuvnesh2703/gists{/gist_id}","starred_url":"https://api.github.com/users/bhuvnesh2703/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bhuvnesh2703/subscriptions","organizations_url":"https://api.github.com/users/bhuvnesh2703/orgs","repos_url":"https://api.github.com/users/bhuvnesh2703/repos","events_url":"https://api.github.com/users/bhuvnesh2703/events{/privacy}","received_events_url":"https://api.github.com/users/bhuvnesh2703/received_events","type":"User","site_admin":false},"created_at":"2016-12-21T22:02:48Z","updated_at":"2016-12-21T22:02:48Z","body":"Testing with this fix, we observed the below failures in table_function.\r\n\r\n1. Redistribute motion is removed from the plan after this fix and it produces wrong results\r\nOn Master:\r\n```\r\npivotal=# explain SELECT * FROM table_function.multiset_5( TABLE (SELECT * FROM table_function.example ORDER BY b, a SCATTER RANDOMLY) );\r\n                                             QUERY PLAN\r\n-----------------------------------------------------------------------------------------------------\r\n Gather Motion 3:1  (slice2; segments: 3)  (cost=2.27..2.42 rows=10 width=36)\r\n   ->  Table Function Scan on multiset_5  (cost=2.27..2.42 rows=4 width=36)\r\n         ->  Sort  (cost=2.27..2.29 rows=4 width=16)\r\n               Sort Key: example.b, example.a\r\n               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..2.10 rows=4 width=16)\r\n                     ->  Seq Scan on example  (cost=0.00..2.10 rows=4 width=16)\r\n Optimizer status: legacy query optimizer\r\n(7 rows)\r\n\r\npivotal=# SELECT * FROM table_function.multiset_5( TABLE (SELECT * FROM table_function.example ORDER BY b, a SCATTER RANDOMLY) );\r\n a |      b\r\n---+-------------\r\n 1 |  value1.2/4\r\n 2 |  value2.3/3\r\n 1 |  value1.1/4\r\n 1 |  value1.4/4\r\n 3 |  value3.1/2\r\n 4 |  value4.1/1\r\n 1 |  value1.3/4\r\n 2 |  value2.1/3\r\n 2 |  value2.2/3\r\n 3 |  value3.2/2\r\n(10 rows)\r\n```\r\n\r\nWith this fix:\r\n```\r\ngpadmin=# SELECT * FROM table_function.multiset_5( TABLE (SELECT * FROM table_function.example ORDER BY b, a SCATTER RANDOMLY) );\r\n a |     b\r\n---+------------\r\n 3 | value3.1/2\r\n 3 | value3.2/2\r\n 4 | value4.1/1\r\n 1 | value1.1/4\r\n 1 | value1.2/4\r\n 1 | value1.3/4\r\n 1 | value1.4/4\r\n 2 | value2.1/3\r\n 2 | value2.2/3\r\n 2 | value2.3/3\r\n(10 rows)\r\n\r\ngpadmin=# explain SELECT * FROM table_function.multiset_5( TABLE (SELECT * FROM table_function.example ORDER BY b, a SCATTER RANDOMLY) );\r\n                                  QUERY PLAN\r\n------------------------------------------------------------------------------\r\n Gather Motion 3:1  (slice1; segments: 3)  (cost=2.63..2.93 rows=20 width=36)\r\n   ->  Table Function Scan on multiset_5  (cost=2.63..2.93 rows=7 width=36)\r\n         ->  Sort  (cost=2.63..2.68 rows=7 width=16)\r\n               Sort Key: example.b, example.a\r\n               ->  Seq Scan on example  (cost=0.00..2.20 rows=7 width=16)\r\n Optimizer status: legacy query optimizer\r\n(6 rows)\r\n```\r\n\r\n2. Looks like this fix attaches the tupdesc but the results are wrong.\r\n\r\nOn Master\r\n```\r\n  select q from (select max(f1) from int4_tbl group by f1 order by f1) q;\r\nERROR:  record type has not been registered\r\n```\r\n\r\nWith this fix\r\n```\r\n  select q from (select max(f1) from int4_tbl group by f1 order by f1) q;\r\n        q\r\n ----------------\r\n  (-2147483647,)\r\n  (-123456,)\r\n  (0,)\r\n  (123456,)\r\n  (2147483647,)\r\n (5 rows)\r\n```\r\n\r\n3.  Table Function scan is pulled up before Gather Motion\r\nOn Master:\r\n```\r\npivotal=# explain SELECT * FROM multiset_5( TABLE (SELECT * FROM example ORDER BY a, b) );\r\n                                  QUERY PLAN\r\n------------------------------------------------------------------------------\r\n Gather Motion 3:1  (slice1; segments: 3)  (cost=2.27..2.42 rows=10 width=36)\r\n   ->  Table Function Scan on multiset_5  (cost=2.27..2.42 rows=4 width=36)\r\n         ->  Sort  (cost=2.27..2.29 rows=4 width=16)\r\n               Sort Key: example.a, example.b\r\n               ->  Seq Scan on example  (cost=0.00..2.10 rows=4 width=16)\r\n Optimizer status: legacy query optimizer\r\n```\r\nWith this fix\r\n```\r\ngpadmin=# explain SELECT * FROM multiset_5( TABLE (SELECT * FROM example ORDER BY a, b) );\r\n                                     QUERY PLAN\r\n------------------------------------------------------------------------------------\r\n Table Function Scan on multiset_5  (cost=2.63..2.93 rows=20 width=36)\r\n   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=2.63..2.68 rows=20 width=16)\r\n         Merge Key: example.a, example.b\r\n         ->  Sort  (cost=2.63..2.68 rows=7 width=16)\r\n               Sort Key: example.a, example.b\r\n               ->  Seq Scan on example  (cost=0.00..2.20 rows=7 width=16)\r\n Optimizer status: legacy query optimizer\r\n```\r\n"}}
{"actor":{"display_login":"bhuvnesh2703"}}