Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] - <Column "xxxx" must be in the GROUP BY list; SQL statement> #898

Closed
yinzsw opened this issue Jan 24, 2025 · 3 comments
Closed

[BUG] - <Column "xxxx" must be in the GROUP BY list; SQL statement> #898

yinzsw opened this issue Jan 24, 2025 · 3 comments
Labels
bug Something isn't working

Comments

@yinzsw
Copy link

yinzsw commented Jan 24, 2025

Jimmer Version

0.9.48

JDK Version

JDK21

Database

h2

OS

Linux

Expected behavior

return empty list

Actual behavior

throw org.h2.jdbc.JdbcSQLSyntaxErrorException
But in fact, there is no problem with SQL syntax

plugins
    id("org.springframework.boot") version "3.4.0"
    id("io.spring.dependency-management") version "1.1.6"

dependencies
    runtimeOnly("com.h2database:h2")
    implementation("org.liquibase:liquibase-core")
    implementation("org.springframework.boot:spring-boot-starter-jdbc")

Description

 /*
     *
     * generate template sql:
     *
     * select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID)
     * from GAME_RECORD_DETAIL tb_1_
     * where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ?
     * group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT;
     *
     * while {@code path} is 111111
     *
     * variables: [^.{0}, ^.{0}, %11:11:11, ^.{0}]  Return result successfully => []
     * variables: [^.{1}, ^.{1}, %1:11:11, ^.{1}] Return result successfully => []
     * variables: [^.{3}, ^.{3}, %11:11, ^.{3}] throw exception 
     *
     * */
    public HashMap<String, Object> recommend(String path /*\d{6}*/) {
        String hour = path.substring(0, 2);
        String min = path.substring(2, 4);
        String sec = path.substring(4, 6);
        String fmtValue = "%s:%s:%s".formatted(hour, min, sec);

        var detailTable = Tables.GAME_RECORD_DETAIL_TABLE;
        HashMap<String, Object> map = new HashMap<>();
        for (int i = 0; i < path.length(); i++) {
            int deleteCount = i + i / 2;
            String likeValue = fmtValue.substring(deleteCount);
            String regexp = "^.{%s}".formatted(deleteCount);
            var group = Expression.string().sql("REGEXP_REPLACE(%e, %v,'')", it -> it.expression(detailTable.timeCode()).value(regexp));

            var statistic = gameRecordDetailRepository.sql()
                    .createQuery(detailTable)
                    .where(group.like(likeValue, LikeMode.END))
                    .groupBy(group, detailTable.result())
                    .select(group, detailTable.result(), detailTable.recordId().count(true))
                    .execute();

            System.out.println(statistic);
            map.put(likeValue, statistic);
        }
        return map;
    }

Reproduction steps

When executing the recommend method

Generated SQL

sql: select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT
``


### Relation Model

```java
@Entity
public interface GameRecordDetail {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    String id();

    String bet();

    String result();

    BigDecimal stake();

    BigDecimal payout();

    String timeCode();

    @ManyToOne
    GameRecord record();
}

ID,BET,RESULT,TIME_CODE,STAKE,PAYOUT,RECORD_ID
715250154532099322,3,3,08:13:15,6.00,23.00,17e603b7b88f62bca997d53e
715250154533943322,3,1,08:19:13,2.00,0.00,17e6040b0f7257c1f36eecc1
715250154534416322,1,2,08:20:21,4.00,0.00,17e6041b0028d18209c3e4e6
715250154534828322,1,1,08:21:35,4.00,15.00,17e6042c49631843b3666873

create table GAME_RECORD_DETAIL
(
    ID        CHARACTER VARYING(255) not null,
    BET       CHARACTER VARYING(255),
    RESULT    CHARACTER VARYING(255),
    TIME_CODE CHARACTER VARYING(255),
    STAKE     NUMERIC(10, 2),
    PAYOUT    NUMERIC(10, 2),
    RECORD_ID CHARACTER VARYING(255),
    constraint PK_GAME_RECORD_DETAIL
        primary key (ID)
);

Screenshots

No response

Logs

2025-01-24T22:07:19.096+08:00 INFO 6563 --- [game-spider] [omcat-handler-7] o.b.jimmer.sql.runtime.ExecutorForLog : jimmer> sql: select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, variables: [^.{0}, ^.{0}, %11:11:11, ^.{0}], purpose: QUERY
[]
2025-01-24T22:07:19.101+08:00 INFO 6563 --- [game-spider] [omcat-handler-7] o.b.jimmer.sql.runtime.ExecutorForLog : jimmer> sql: select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, variables: [^.{1}, ^.{1}, %1:11:11, ^.{1}], purpose: QUERY
[]
2025-01-24T22:07:19.105+08:00 INFO 6563 --- [game-spider] [omcat-handler-7] o.b.jimmer.sql.runtime.ExecutorForLog : jimmer> sql: select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, variables: [^.{3}, ^.{3}, %11:11, ^.{3}], purpose: QUERY
2025-01-24T22:07:19.110+08:00 ERROR 6563 --- [game-spider] [omcat-handler-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.babyfish.jimmer.sql.exception.ExecutionException: Cannot execute SQL statement: select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, variables: [^.{3}, ^.{3}, %11:11, ^.{3}]] with root cause

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "TB_1_.TIME_CODE" must be in the GROUP BY list; SQL statement:
select REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT, count(distinct tb_1_.RECORD_ID) from GAME_RECORD_DETAIL tb_1_ where REGEXP_REPLACE(tb_1_.TIME_CODE, ?,'') like ? group by REGEXP_REPLACE(tb_1_.TIME_CODE, ?,''), tb_1_.RESULT [90016-232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:644) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.3.232.jar:2.3.232]
at org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:266) ~[h2-2.3.232.jar:2.3.232]
at org.h2.expression.OperationN.updateAggregate(OperationN.java:99) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Select.updateAgg(Select.java:542) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Select.gatherGroup(Select.java:525) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Select.queryGroup(Select.java:491) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Select.queryWithoutCache(Select.java:886) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:211) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Query.query(Query.java:530) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.query.Query.query(Query.java:498) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.CommandContainer.query(CommandContainer.java:222) ~[h2-2.3.232.jar:2.3.232]
at org.h2.command.Command.executeQuery(Command.java:196) ~[h2-2.3.232.jar:2.3.232]
at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:130) ~[h2-2.3.232.jar:2.3.232]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
at org.babyfish.jimmer.sql.runtime.Selectors.lambda$select$0(Selectors.java:46) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.runtime.Internal.usingSqlDraftContext(Internal.java:117) ~[jimmer-core-0.9.48.jar:na]
at org.babyfish.jimmer.sql.runtime.Selectors.lambda$select$1(Selectors.java:43) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.runtime.DefaultExecutor.execute(DefaultExecutor.java:60) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.runtime.ExecutorForLog.simpleLog(ExecutorForLog.java:117) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.runtime.ExecutorForLog.execute(ExecutorForLog.java:47) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.runtime.Selectors.select(Selectors.java:31) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.ast.impl.query.ConfigurableRootQueryImpl.executeImpl(ConfigurableRootQueryImpl.java:285) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.spring.cfg.support.SpringConnectionManager.execute(SpringConnectionManager.java:31) ~[jimmer-spring-boot-starter-0.9.48.jar:na]
at org.babyfish.jimmer.sql.ast.impl.query.ConfigurableRootQueryImpl.execute(ConfigurableRootQueryImpl.java:275) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.ast.impl.query.ConfigurableRootQueryImpl.execute(ConfigurableRootQueryImpl.java:28) ~[jimmer-sql-0.9.48.jar:na]
at org.babyfish.jimmer.sql.ast.Executable.execute(Executable.java:12) ~[jimmer-sql-0.9.48.jar:na]
at top.yinzsw.gamespider.GameService.recommend(GameService.java:272) ~[main/:na]
at top.yinzsw.gamespider.GameController.recommend(GameController.java:30) ~[main/:na]
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1088) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:978) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.2.0.jar:6.2.0]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.33.jar:6.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.2.0.jar:6.2.0]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.33.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at java.base/java.lang.VirtualThread.run(VirtualThread.java:309) ~[na:na]
@yinzsw yinzsw added the bug Something isn't working label Jan 24, 2025
@babyfish-ct
Copy link
Owner

The expression used to group cannot contain literal values which will be translated to jdbc parameter ? automatially.

Please use constant expression, or hard code it in embedded SQL fragment.

@yinzsw
Copy link
Author

yinzsw commented Jan 26, 2025

The expression used to group cannot contain literal values which will be translated to jdbc parameter ? automatially.

Please use constant expression, or hard code it in embedded SQL fragment.

Yes, using SQL fragments can solve this problem; But why can the first two iterations in the iterator be executed normally. Suggest using friendly error prompts

@babyfish-ct
Copy link
Owner

This is a good suggestion, I will improve it in future.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@babyfish-ct @yinzsw