slow query with many-to-many due to grouping around join

[Brian Craft]

I was able to write some many-to-many joins after reading the unit tests, however the performance is extremely poor. From the unit tests:

(deftest test-many-to-many-join
  (is (= (str "dry run :: SELECT \"mtm2\".* FROM (\"mtm2\" "
              "LEFT JOIN \"mtm1_mtm2\" "
              "ON \"mtm2\".\"id\" = \"mtm1_mtm2\".\"mtm2_id\") "
              "LEFT JOIN \"mtm1\" "
              "ON \"mtm1_mtm2\".\"mtm1_id\" = \"mtm1\".\"id\" :: []\n")
         (with-out-str (dry-run (select mtm2 (join mtm1)))))))

With the grouping around the first join, "explain" shows it can't use the index on mtm1.id: it's a full table scan. Removing the grouping, it uses the index.

This is with H2. I'm wondering 1) if this is peculiar to H2 or if the join is generically written in a way that prevents using the index, and 2) if there's a simple way to avoid the grouping. With a first glance at the code I didn't see where it's being added.

[Brian Craft]

Answering my own questions, 1) mysql handles it fine, so it's at least partially an h2 issue, and 2) I can work around it very messily by monkey patching korma.sql.utils/left-assoc to omit the parentheses.