Unexpected error, with select inside another select.

[VovaStelmashchuk]

I have the following sql query.

select *
from cocktails
where (cocktails.name like '%Ін%')
  AND (2 = (select distinct count(tag_id)
            from cocktails_to_tags
            where cocktails_to_tags.cocktail_id = cocktails.id
              and cocktails_to_tags.tag_id = ANY (ARRAY [1, 5])));

I'm try to write the same using exposed.

private fun getQuerySearchPlusTags(search: String, tags: List<Int>): Query {
    return CocktailsTable.select {
        (CocktailsTable.name.lowerCase() like "%$search%".lowercase()) and (
                (CocktailToTagTable.select {
                    (CocktailToTagTable.cocktailId eq CocktailsTable.id) and (CocktailToTagTable.tagId inList tags)
                }.count() eq tags.size))
    }
}

private infix fun Long.eq(i: Int): Expression<Boolean> {
    return if (this == i.toLong()) {
        Op.TRUE
    } else {
        Op.FALSE
    }
}

The exposed throw the error.

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "cocktails"
  Position: 79
SQL: [Failed on expanding args for SELECT: org.jetbrains.exposed.sql.Query@73c995be]

It's looks like a wrong behavior.

[Tapac]

Can you please confirm issue on the latest version?

[joc-a]

Hey @VovaStelmashchuk. Could you please check if you still have this issue on the latest Exposed version 0.43.0?

[VovaStelmashchuk]

I can't reproduce this particular query now, because the product requirements have changed a lot, and there are no data structures that existed before. But I tried it on a similar query, everything works as expected, I think we can close the issue. If I find something that doesn't work, I will open a new one or reopen this issue.