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

控制台报错：WITHIN GROUP not allowed with group_concat function，但又没有什么影响？ #679

Closed
ArchangelLiang opened this issue Apr 22, 2022 · 3 comments
Closed

控制台报错：WITHIN GROUP not allowed with group_concat function，但又没有什么影响？ #679

ArchangelLiang opened this issue Apr 22, 2022 · 3 comments

Comments

@ArchangelLiang
Copy link

控制台报错：
2022-04-22 18:05:13,392[ERROR]io.mycat.DrdsSqlCompiler.getRelRoot:402sqlStatement:SELECT DISTINCT ROUTINE_SCHEMA AS ROUTINE_SCHEMA, ROUTINE_NAME AS ROUTINE_NAME, PARAMS.PARAMETER AS PARAMETER
FROM information_schema.ROUTINES
LEFT JOIN (
SELECT SPECIFIC_SCHEMA AS SPECIFIC_SCHEMA, SPECIFIC_NAME AS SPECIFIC_NAME
, group_concat(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') AS PARAMETER
, ROUTINE_TYPE AS ROUTINE_TYPE
FROM information_schema.PARAMETERS
GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE
) PARAMS
ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA
AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME
AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE
WHERE ROUTINE_SCHEMA = ?
ORDER BY ROUTINE_SCHEMA sqlNode:SELECT DISTINCT ROUTINE_SCHEMA AS ROUTINE_SCHEMA, ROUTINE_NAME AS ROUTINE_NAME, PARAMS.PARAMETER AS PARAMETER
FROM information_schema.ROUTINES
/*+ QB_NAME(ROUTINES) /
LEFT JOIN (SELECT
/+ QB_NAME(SEL$4) /
SPECIFIC_SCHEMA AS SPECIFIC_SCHEMA, SPECIFIC_NAME AS SPECIFIC_NAME, group_concat(concat(DATA_TYPE, _UTF-8' ', PARAMETER_NAME)) WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS PARAMETER, ROUTINE_TYPE AS ROUTINE_TYPE
FROM information_schema.PARAMETERS
/+ QB_NAME(PARAMETERS) */
GROUP BY PARAMETERS.SPECIFIC_SCHEMA, PARAMETERS.SPECIFIC_NAME, PARAMETERS.ROUTINE_TYPE) AS PARAMS ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE
WHERE ROUTINE_SCHEMA = ?
ORDER BY ROUTINE_SCHEMA validated:null root:null newRelNode:null
2022-04-22 18:05:13,396[ERROR]io.mycat.sqlhandler.ShardingSQLHandler.onExecute:61SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME, PARAMS.PARAMETER
FROM information_schema.ROUTINES
LEFT JOIN (
SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME
, GROUP_CONCAT(CONCAT(DATA_TYPE, ' ', PARAMETER_NAME) ORDER BY ORDINAL_POSITION SEPARATOR ', ') AS PARAMETER
, ROUTINE_TYPE
FROM information_schema.PARAMETERS
GROUP BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE
) PARAMS
ON ROUTINES.ROUTINE_SCHEMA = PARAMS.SPECIFIC_SCHEMA
AND ROUTINES.ROUTINE_NAME = PARAMS.SPECIFIC_NAME
AND ROUTINES.ROUTINE_TYPE = PARAMS.ROUTINE_TYPE
WHERE ROUTINE_SCHEMA = 'saas_headhunt'
ORDER BY ROUTINE_SCHEMA
org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: WITHIN GROUP not allowed with group_concat function
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:886)
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:871)
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5006)
at org.apache.calcite.sql.SqlWithinGroupOperator.validateCall(SqlWithinGroupOperator.java:64)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5465)
at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116)
at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:273)
at org.apache.calcite.sql.SqlAsOperator.validateCall(SqlAsOperator.java:106)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5465)
at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:116)
at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:273)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4222)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4197)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3437)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1066)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1040)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3168)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3150)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateJoin(SqlValidatorImpl.java:3215)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3159)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3424)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1066)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1040)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1015)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:723)
at io.mycat.DrdsSqlCompiler.getRelRoot(DrdsSqlCompiler.java:396)
at io.mycat.DrdsSqlCompiler.compileQuery(DrdsSqlCompiler.java:330)
at io.mycat.DrdsSqlCompiler.dispatch(DrdsSqlCompiler.java:219)
at io.mycat.DrdsSqlCompiler.dispatch(DrdsSqlCompiler.java:211)
at io.mycat.calcite.spm.MemPlanCache.add(MemPlanCache.java:182)
at io.mycat.calcite.spm.QueryPlanner.getAcceptedMycatRelList(QueryPlanner.java:69)
at io.mycat.calcite.spm.QueryPlanner.innerComputeMinCostCodeExecuterContext(QueryPlanner.java:28)
at io.mycat.calcite.DrdsRunnerHelper.getPlan(DrdsRunnerHelper.java:370)
at io.mycat.calcite.DrdsRunnerHelper.runOnDrds(DrdsRunnerHelper.java:358)
at io.mycat.sqlhandler.ShardingSQLHandler.onExecute(ShardingSQLHandler.java:58)
at io.mycat.sqlhandler.AbstractSQLHandler.execute(AbstractSQLHandler.java:64)
at io.mycat.commands.MycatdbCommand.lambda$execute$7(MycatdbCommand.java:491)
at io.vertx.core.impl.future.Composition.onSuccess(Composition.java:38)
at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
at io.vertx.core.impl.future.FutureImpl.addListener(FutureImpl.java:168)
at io.vertx.core.impl.future.FutureBase.compose(FutureBase.java:84)
at io.vertx.core.Future.compose(Future.java:199)
at io.vertx.core.Future.flatMap(Future.java:180)
at io.mycat.commands.MycatdbCommand.execute(MycatdbCommand.java:446)
at io.mycat.commands.MycatdbCommand$1.apply(MycatdbCommand.java:200)
at io.mycat.commands.MycatdbCommand$1.apply(MycatdbCommand.java:196)
at io.vertx.core.impl.future.Composition.onSuccess(Composition.java:38)
at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
at io.vertx.core.impl.future.SucceededFuture.addListener(SucceededFuture.java:88)
at io.vertx.core.impl.future.FutureBase.compose(FutureBase.java:84)
at io.vertx.core.impl.future.SucceededFuture.compose(SucceededFuture.java:27)
at io.vertx.core.Future.compose(Future.java:199)
at io.vertx.core.Future.flatMap(Future.java:180)
at io.mycat.commands.MycatdbCommand.executeQuery(MycatdbCommand.java:196)
at io.mycat.mycatmysql.MycatVertxMySQLHandler.handleQuery(MycatVertxMySQLHandler.java:525)
at io.mycat.mycatmysql.MycatVertxMySQLHandler.lambda$handle0$2(MycatVertxMySQLHandler.java:95)
at io.mycat.IOExecutor$1.lambda$executeBlocking$0(IOExecutor.java:75)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: WITHIN GROUP not allowed with group_concat function
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467)
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:560)
... 65 common frames omitted
@junwen12221
Copy link
Collaborator

不影响的.主要是navicat客户端一些sql,在mycat2里不兼容导致的,可以换成sqlyog

@ArchangelLiang
Copy link
Author

好的

@cmengs
Copy link

cmengs commented Jan 11, 2023

I also had this problem, and I configured the master-slave of the cluster and the database, and the status of the master-slave is also normal. It seems that this error was not reported when using sqlyog, but what is the reason why the table of the cluster database cannot be seen when the port 8066 is connected?

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

No branches or pull requests
3 participants
@junwen12221 @cmengs @ArchangelLiang