sql查询抛出带有having count子句的arrayindexoutofboundsexception

gorkyyrv  于 2021-07-13  发布在  Java
关注(0)|答案(1)|浏览(378)

我想按计数(favorite.favorite\u id)顺序显示结果,并使用基于光标的分页。
但是,在查询中添加'having count()'子句时,我发现了arrayindexoutofboundsexception。

select
        album0_.album_id as col_0_0_,
        count(favoriteli2_.favorite_id) as cnt,
        user1_.user_id as user_id1_8_1_,
        album0_.album_id as album_id1_0_0_,
        album0_.create_date as create_d2_0_0_,
        album0_.modify_date as modify_d3_0_0_,
        album0_.description as descript4_0_0_,
        album0_.thumbnail as thumbnai5_0_0_,
        album0_.title as title6_0_0_,
        album0_.user_id as user_id8_0_0_,
        album0_.version as version7_0_0_,
        user1_.code as code2_8_1_,
        user1_.introduction as introduc3_8_1_,
        user1_.name as name4_8_1_,
        user1_.pic as pic5_8_1_
    from
        album album0_
    inner join
        user user1_
            on album0_.user_id=user1_.user_id
    left outer join
        favorite favoriteli2_
            on album0_.album_id=favoriteli2_.album_id
    group by
        album0_.album_id
    order by
        cnt desc, album0_.album_id desc

上面的查询工作正常。

select
        album0_.album_id as col_0_0_,
        count(favoriteli2_.favorite_id) as cnt,
        user1_.user_id as user_id1_8_1_,
        album0_.album_id as album_id1_0_0_,
        album0_.create_date as create_d2_0_0_,
        album0_.modify_date as modify_d3_0_0_,
        album0_.description as descript4_0_0_,
        album0_.thumbnail as thumbnai5_0_0_,
        album0_.title as title6_0_0_,
        album0_.user_id as user_id8_0_0_,
        album0_.version as version7_0_0_,
        user1_.code as code2_8_1_,
        user1_.introduction as introduc3_8_1_,
        user1_.name as name4_8_1_,
        user1_.pic as pic5_8_1_
    from
        album album0_
    inner join
        user user1_
            on album0_.user_id=user1_.user_id
    left outer join
        favorite favoriteli2_
            on album0_.album_id=favoriteli2_.album_id
    group by
        album0_.album_id
    having 
        count(favoriteli2_.favorite_id) < 2  --this causes the problem
    order by
        cnt desc, album0_.album_id desc

但是这个有count()子句的方法不起作用。
下面是arrayindexoutofboundsexception发生的方法[org.h2.command.dml.select.updateagg(选择。java:542)]

/**
 * Update any aggregate expressions with the query stage.
 * @param columnCount number of columns
 * @param stage see STAGE_RESET/STAGE_GROUP/STAGE_WINDOW in DataAnalysisOperation
 */
void updateAgg(int columnCount, int stage) {
    for (int i = 0; i < columnCount; i++) {
        if ((groupByExpression == null || !groupByExpression[i])
                && (groupByCopies == null || groupByCopies[i] < 0)) {
            Expression expr = expressions.get(i);
            expr.updateAggregate(session, stage);
        }
    }
}

列数=16
groupbyexpression:大小16
groupbycopies:大小15
表达式:大小16(包含15个选定列,1个比较(=count(favoriteli2.favorite\u id)<2))
我认为“groupbycopies”是错误的原因。我该怎么修?

sqserrrh

sqserrrh1#

虽然mysql/mariadb允许这样做,但我认为h2(像许多其他sql rdbms一样)希望groupby具有所有非聚合列(使用album\u id将它们全部添加到groupby中)。所以我想试试。事实上,您看到的异常是由逻辑从组中抛出的,这就是为什么我倾向于这样做的原因。

相关问题