{"action":"created","issue":{"url":"https://api.github.com/repos/metabase/metabase/issues/1786","labels_url":"https://api.github.com/repos/metabase/metabase/issues/1786/labels{/name}","comments_url":"https://api.github.com/repos/metabase/metabase/issues/1786/comments","events_url":"https://api.github.com/repos/metabase/metabase/issues/1786/events","html_url":"https://github.com/metabase/metabase/issues/1786","id":128281307,"number":1786,"title":"When joining in a way that returns columns with same name, columns are suffixed and missing metadata","user":{"login":"camsaul","id":1455846,"avatar_url":"https://avatars.githubusercontent.com/u/1455846?v=3","gravatar_id":"","url":"https://api.github.com/users/camsaul","html_url":"https://github.com/camsaul","followers_url":"https://api.github.com/users/camsaul/followers","following_url":"https://api.github.com/users/camsaul/following{/other_user}","gists_url":"https://api.github.com/users/camsaul/gists{/gist_id}","starred_url":"https://api.github.com/users/camsaul/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/camsaul/subscriptions","organizations_url":"https://api.github.com/users/camsaul/orgs","repos_url":"https://api.github.com/users/camsaul/repos","events_url":"https://api.github.com/users/camsaul/events{/privacy}","received_events_url":"https://api.github.com/users/camsaul/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/metabase/metabase/labels/Bug","name":"Bug","color":"CC333F"},{"url":"https://api.github.com/repos/metabase/metabase/labels/Query%20Processor","name":"Query Processor","color":"68BEFC"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2016-01-23T01:19:02Z","updated_at":"2016-01-23T22:39:08Z","closed_at":null,"body":"e.g. `name` and `name_2`. I believe this is done automatically by JDBC.\r\n\r\n*  Since we don't know the source of `name_2` the correct Metadata doesn't get returned along with it. \r\n*  It would be better to give it a name like `<table_name>.name`.\r\n\r\nSee also #1447.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/metabase/metabase/issues/comments/174227778","html_url":"https://github.com/metabase/metabase/issues/1786#issuecomment-174227778","issue_url":"https://api.github.com/repos/metabase/metabase/issues/1786","id":174227778,"user":{"login":"agilliland","id":1987787,"avatar_url":"https://avatars.githubusercontent.com/u/1987787?v=3","gravatar_id":"","url":"https://api.github.com/users/agilliland","html_url":"https://github.com/agilliland","followers_url":"https://api.github.com/users/agilliland/followers","following_url":"https://api.github.com/users/agilliland/following{/other_user}","gists_url":"https://api.github.com/users/agilliland/gists{/gist_id}","starred_url":"https://api.github.com/users/agilliland/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/agilliland/subscriptions","organizations_url":"https://api.github.com/users/agilliland/orgs","repos_url":"https://api.github.com/users/agilliland/repos","events_url":"https://api.github.com/users/agilliland/events{/privacy}","received_events_url":"https://api.github.com/users/agilliland/received_events","type":"User","site_admin":false},"created_at":"2016-01-23T22:39:08Z","updated_at":"2016-01-23T22:39:08Z","body":"I checked this out a little bit and this is specific to the clojure.jdbc wrapper, which is sadly not well setup to handle the situation where multiple columns have the same name.\r\n\r\nHere's some quick example code:\r\n\r\n```\r\n;; clojure JDBC\r\n(jdbc/with-db-transaction [conn (jdbc-details mysql-details)]\r\n  (jdbc/query conn \"select f.name, t.name from metabase_field f join metabase_table t on f.table_id = t.id\"))\r\n\r\nOutput:\r\n({:name \"Metric\", :name_2 \"rh_reservenightlyemail\"} {:name \"Yesterday\", :name_2 \"rh_reservenightlyemail\"} {:name \"Last Week\", :name_2 \"rh_reservenightlyemail\"} {:name \"WoW\", :name_2 \"rh_reservenightlyemail\"})\r\n```\r\n\r\n```\r\n;; raw Java JDBC\r\n(jdbc/with-db-transaction [conn (jdbc-details mysql-details)]\r\n  (let [stmt (.createStatement (:connection conn))\r\n        rs   (.executeQuery stmt \"select f.name, t.name from metabase_field f join metabase_table t on f.table_id = t.id\")\r\n        rsmd (.getMetaData rs)]\r\n    (doseq [idx (range (.getColumnCount rsmd))]\r\n      (println idx \"=\" (.getTableName rsmd (+ 1 idx)) (.getColumnName rsmd (+ 1 idx))))))\r\n\r\nOutput:\r\n0 = metabase_field name\r\n1 = metabase_table name\r\n```\r\n\r\nSo the actual Java JDBC driver is not doing this, it's the fact that the clojure jdbc code wants to convert the ResultSet into a Map and it's trying to key the map using just the column name, so collisions are possible.\r\n\r\nHere's the offending code ... https://github.com/clojure/java.jdbc/blob/master/src/main/clojure/clojure/java/jdbc.clj#L295-L312\r\n\r\nSince we probably don't want to rewrite the clojure.jdbc code I think the best/most sensible way to address this problem is to construct our SQL with explicit names for every expected column.  If we specify the name of every column when we generate the SQL then we can ensure there are no collisions and we can fully control the mapping of the results back to the Metabase metadata."}}