Multiple join produce "DSL SYNTAX ERROR"

[CrafterKolyan]

When you try to do at least two joins you get something like
SELECT * FROM Table1 INNER JOIN Table2 INNER JOIN Table3 ON <cond1> ON <cond2> which is incorrect DSL Syntax.
Correct:
SELECT * FROM Table1 INNER JOIN Table2 ON <cond1> INNER JOIN Table3ON <cond2>

[Tapac]

Exposed uses the first approach with join ... on ... join ... on.
Could you provide a sample where you got dsl syntax error?

[CrafterKolyan]

Actually the bug is a little bit different and connected only with MySQL.
Here is an example (it connects to H2, because we don't actually know how to create example using MySQL):

import org.jetbrains.exposed.dao.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object Users : IntIdTable() {
    val name = varchar("name", length = 50)
    val cityId = reference("city_id", Cities)
}

object Cities : IntIdTable() {
    val name = varchar("name", 50)
}

object Developers : Table() {
    val userId = reference("user_id", Users)
    val jetBrainsTeam = varchar("jetBrains_team", 255)
}

fun main(args: Array<String>) {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

    transaction {
        SchemaUtils.create(Users, Cities, Developers)

        val city_id = Cities.insertAndGetId {
            it[name] = "Moscow"
        }

        val user_id = Users.insertAndGetId {
            it[cityId] = city_id
            it[name] = "Nikolay"
        }

        Developers.insert {
            it[userId] = user_id
            it[jetBrainsTeam] = "RubyMine"
        }

        val select = (Developers innerJoin (Cities innerJoin Users)).slice(Users.id).select { // <----------------------- Here it does wrong join
            (Developers.jetBrainsTeam eq "RubyMine") and (Users.name eq "Nikolay") and (Cities.name eq "Moscow")
        }
        println(select.prepareSQL(QueryBuilder(false)))
    }
}

Actual SQL query (gets SQL SYNTAX ERROR):

SELECT Users.id
FROM Developers
INNER JOIN Cities INNER JOIN Users ON Cities.ID = Users.city_id ON USERS.ID = Developers.user_id
WHERE Developers.jetbrains_team = 'RubyMine' and Users.name = 'Nikolay' and Cities.name = 'Moscow'

Expected SQL query:

SELECT Users.id
FROM Developers
INNER JOIN (Cities INNER JOIN Users ON Cities.ID = Users.city_id) ON USERS.ID = Developers.user_id
WHERE Developers.jetbrains_team = 'RubyMine' and Users.name = 'Nikolay' and Cities.name = 'Moscow'

Got this bug when were trying to create innerJoin by recursion:

private fun joinWithDependencies(): ColumnSet {
    if (dependency == null) {
        return this
    }
    return this innerJoin dependency.joinWithDependencies()
}

[CrafterKolyan]

You can check SQL Syntax Error on SQL Fiddle:
http://sqlfiddle.com/#!9/0abc4f/4

[nikitabobko]

+1. Also got this bug on days (spent a week trying to investigate it).

[Tapac]

I guess it happens beacause MySql requires separate name for part inside braces.
Try to swap inner join parts to dependency.joinWithDependency() innerJoin this

[CrafterKolyan]

Thanks for workaround. But I still think it is not really an obvious behaviour.

[Tapac]

Fixed in master (added braces on right part of join if it is also join)