Having issue on alias on subquery

[4whomtbts]

Thank you kotlin-jdsl team for developing and maintaining such a great project like this!

Version Information kotlin-jdsl : 3.3.1 hibernate-core: 6.4.4

I have a MySQL tables as follows.

CREATE TABLE brand
(
    id         BIGINT      NOT NULL AUTO_INCREMENT,
    brand_name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_brand_name (brand_name)
);

CREATE TABLE product
(
    id       BIGINT NOT NULL AUTO_INCREMENT,
    brand_id BIGINT NOT NULL,
    price    BIGINT,
    category INT    NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_product_category_price (category, price)
);

Then, I want to write query as follows.

select b.brand_name, price, p.category
from product p
         inner join (select category, min(price) as min_price
                     from product
                     group by category) AS min_prices
                    on p.category = min_prices.category and p.price = min_prices.min_price
         inner join brand b on p.brand_id = b.id;

So, I tried to convert above MySQL query into kotlin-jdsl like as follows. Since, I couldn't find any example codes similar to this one in the kotlin-jdsl gitbook and github issues; Honestly, I'm not sure If It's correct code. (excerpted significant paragraph from a entire code for a brevity.)

           val product = entity(Product::class)
            val nestedProduct = entity(Product::class, "nested_product")
            val productGroupByMinPrice = selectNew<MinPriceProductInCategoryProjection>(
                min(Product::price).alias(expression(Long::class, "inner_price")),
                (path(Product::category) as Path<Int>).`as`(expression(Int::class, "inner_category")),
            ).from(nestedProduct)
                .groupBy(path(Product::category))
                .asEntity("min_prices")

            select(product)
                .from(
                    product,
                    innerFetchJoin(Product::brand),
                    join(productGroupByMinPrice).on(
                        (path(Product::category) as Path<Int>).eq(path(MinPriceProductInCategoryProjection::category))
                            .and(
                                (path(Product::price).eq(path(MinPriceProductInCategoryProjection::price))),
                            ),
                    ),
                )

....

data class MinPriceProductInCategoryProjection(
    val category: Int,
    val price: Long,
)

When the above query is executed, hibernate occurs SemanticException with message Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)

exception message indicates the absence of alias on the select query of the nested query. But, I believe that I provided adequate alias to both of selection node. (may be I'm wrong..)

min(Product::price).alias(expression(Long::class, "inner_price")),
(path(Product::category) as Path<Int>).`as`(expression(Int::class, "inner_category"))

I read some related code in a hibernate library that causes exception. It seems that selection nodes lost there alias information when they arrives below method . .

org.hibernate.query.derived.AnonymousTupleType#AnonymousTupleType(org.hibernate.query.sqm.tree.select.SqmSelectableNode<?>[])

Either there is any mistakes on my kotlin-jdsl or kotlin-jdsl doesn't support kind of queries like this?

Thank you!

[shouwn]

I didn't realize that subqueries require an alias when they are in a from clause.

What you want is the same as defining a DrivedEntity. https://kotlin-jdsl.gitbook.io/docs/jpql-with-kotlin-jdsl/subqueries#derived-entity

You can write a query like the following:

data class MinPrices(
    val category: Int,
    val minPrice: Long,
)

data class Row(
    val brandName: String,
    val price: Long,
    val category: Int,
)

repository.findAll {
    val minPrices = select<MinPrices>(
        path(Product::category).alias(expression("category1")),
        min(Product::price).alias(expression("price1")),
    ).from(
        entity(Product::class),
    ).groupBy(
        path(Product::category),
    ).asEntity("minPrices")

    selectNew<Row>(
        path(Brand::brandName),
        path(Product::price),
        path(Product::category),
    ).from(
        entity(Product::class),
        join(Product::brand),
        join(minPrices).on(
            and(
                path(Product::category).eq(expression("category1")),
                path(Product::price).eq(expression("price1")),
            ),
        ),
    )
}.forEach { println(it) }

This query is then passed to MySQL as the following:

select b1_0.brand_name, p1_0.price, p1_0.category
from
  product p1_0
  join brand b1_0 on b1_0.id = p1_0.brand_id
  join (
    select p2_0.category, min(p2_0.price)
    from product p2_0
    group by p2_0.category
  ) minPrices1_0(category1, price1) 
    on (p1_0.category = minPrices1_0.category1) and (p1_0.price = minPrices1_0.price1);

[4whomtbts]

@shouwn Thank you a lot, shouwn.🙇 I applied the code you suggested and It works like a charm.

Since, the exception message only says about 'missing alias in select expression' I've never dreamed to specify aliases in the 'on' expression 😅 I just naively guessed, kotlin-jdsl would pair.

I didn't realize that subqueries require an alias when they are in a from clause.

you also didn't expect that we have to specify aliases of fields in subquery when It's used in the context of 'on' clause? Am I getting you correctly?

join(minPrices).on(
  and(
    path(Product::category).eq(expression("category1")),
    path(Product::price).eq(expression("price1")),
  ),

https://kotlin-jdsl.gitbook.io/docs/jpql-with-kotlin-jdsl/subqueries#derived-entity I've referenced the above link before, But unfortunately, I couldn't find out hint to solve my issue Because the example seems to work well without specifying alias on the 'from' clause. 😅 How it was possible that the example is working?

[shouwn]

@4whomtbts I saw your answer too late.

The alias is used in the on clause because the projection of the derived table has an alias. You can just think of it as the same as in SQL.

The derived table called minPrices has aliased category to category1 and price to price1 as column names, so if you want to reference the columns of that table in an on clause or a where clause, you need to use those aliases.

The reason I put alias in the on clause is because of the error Hibernate threw. If Hibernate hadn't thrown the error, I wouldn't have aliased the columns in the DerivedTable, and therefore wouldn't have used the alias in the on clause.

And I realized I might have encountered that error before, I just forgot... My example also has aliases on the columns of the DerivedTable...

The reason why I didn't use alias in the from clause is because in my example I didn't need to create a condition. If I needed a condition like join or where in my example, I would have used alias.

[shouwn]

Oh, and you were wondering why I didn't use alias in the select clause as well?

My example was wrong, I just used employeeId as an alias for the employeeId column by mistake and it worked because they have the same name.

It would be better to include examples like join, so I'll fix the example later.

[shouwn]

I will close this issue. If you have any additional questions, please reopen it.