{"action":"closed","issue":{"url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql/issues/4","repository_url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql","labels_url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql/issues/4/labels{/name}","comments_url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql/issues/4/comments","events_url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql/issues/4/events","html_url":"https://github.com/Lordess/benchmarksql_for_mysql/issues/4","id":242571078,"number":4,"title":"MyCat cannot handle JOIN SQL in newOrderTransaction()","user":{"login":"Lordess","id":14990623,"avatar_url":"https://avatars2.githubusercontent.com/u/14990623?v=3","gravatar_id":"","url":"https://api.github.com/users/Lordess","html_url":"https://github.com/Lordess","followers_url":"https://api.github.com/users/Lordess/followers","following_url":"https://api.github.com/users/Lordess/following{/other_user}","gists_url":"https://api.github.com/users/Lordess/gists{/gist_id}","starred_url":"https://api.github.com/users/Lordess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Lordess/subscriptions","organizations_url":"https://api.github.com/users/Lordess/orgs","repos_url":"https://api.github.com/users/Lordess/repos","events_url":"https://api.github.com/users/Lordess/events{/privacy}","received_events_url":"https://api.github.com/users/Lordess/received_events","type":"User","site_admin":false},"labels":[{"id":641707333,"url":"https://api.github.com/repos/Lordess/benchmarksql_for_mysql/labels/question","name":"question","color":"cc317c","default":true}],"state":"closed","locked":false,"assignee":{"login":"Lordess","id":14990623,"avatar_url":"https://avatars2.githubusercontent.com/u/14990623?v=3","gravatar_id":"","url":"https://api.github.com/users/Lordess","html_url":"https://github.com/Lordess","followers_url":"https://api.github.com/users/Lordess/followers","following_url":"https://api.github.com/users/Lordess/following{/other_user}","gists_url":"https://api.github.com/users/Lordess/gists{/gist_id}","starred_url":"https://api.github.com/users/Lordess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Lordess/subscriptions","organizations_url":"https://api.github.com/users/Lordess/orgs","repos_url":"https://api.github.com/users/Lordess/repos","events_url":"https://api.github.com/users/Lordess/events{/privacy}","received_events_url":"https://api.github.com/users/Lordess/received_events","type":"User","site_admin":false},"assignees":[{"login":"Lordess","id":14990623,"avatar_url":"https://avatars2.githubusercontent.com/u/14990623?v=3","gravatar_id":"","url":"https://api.github.com/users/Lordess","html_url":"https://github.com/Lordess","followers_url":"https://api.github.com/users/Lordess/followers","following_url":"https://api.github.com/users/Lordess/following{/other_user}","gists_url":"https://api.github.com/users/Lordess/gists{/gist_id}","starred_url":"https://api.github.com/users/Lordess/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Lordess/subscriptions","organizations_url":"https://api.github.com/users/Lordess/orgs","repos_url":"https://api.github.com/users/Lordess/repos","events_url":"https://api.github.com/users/Lordess/events{/privacy}","received_events_url":"https://api.github.com/users/Lordess/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2017-07-13T02:53:51Z","updated_at":"2017-07-13T03:56:10Z","closed_at":"2017-07-13T03:56:09Z","body":"# phenononon\r\n\r\n  newOrderTransaction() has a fixed(issue #3) JOIN SQL belows. But MyCat( 1.6.5 BETA ) cannot handle it properly, too.. Have to work around than just waiting MyCat's fix. Again.\r\n\r\n``` Java\r\n.prepareStatement(\"SELECT c.c_discount, c.c_last, c.c_credit, w.w_tax\"\r\n    + \"  FROM customer AS c, warehouse AS w\"\r\n    + \" WHERE w.w_id = ? AND w.w_id = c.c_w_id\"\r\n    + \" AND c.c_d_id = ? AND c.c_id = ?\");\r\n```\r\n\r\n# workaround\r\n\r\n  Split the JOIN into an equivalent two step SELECT sequence.\r\n\r\n  Firstly, get the **w_id**, **w_tax** from *warehouse*. Because **w_id** is the \r\nprimary key of *warehouse*, no more than one record will be returned. So we can use basic variables to cahce it.\r\n\r\n``` Java\r\n.prepareStatement(\"SELECT w_id, w_tax\"\r\n    + \" FROM warehouse\"\r\n    + \" WHERE w_id = ? \");\r\n\r\n// store w_id of the result set in Java variable w_w_id as a middle data\r\n```\r\n\r\n  Secondly, try to get other fields from *customer* by cached **w_id**.\r\n\r\n`` Java\r\n .prepareStatement(\"SELECT c_discount, c_last, c_credit\"\r\n     + \" FROM customer\"\r\n     + \" WHERE c_w_id = \"+ w_w_id +\" AND\"\r\n                      + \" c_d_id = ? AND c_id = ?\");\r\n``\r\n\r\n  Finally, return **w_tax** from step one, **c_discount**, **c_last** and **c_credit** from step two."}}
{"actor":{"display_login":"Lordess"}}