search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.15k stars 2.93k forks source link

docs - how to convert array to string, want to search for text anywhere in the array or get length of characters in array? #8867

Open tooptoop4 opened 3 years ago

tooptoop4 commented 3 years ago

i basically want to count the number of characters (not elements) in an array and do cast(array as varchar) like '%somestring%'

my findings:

  1. prestodb has a array_to_string function https://github.com/prestodb/presto/pull/2600
  2. https://trino.io/docs/current/functions/array.html does not mention json_format(cast(MYARRAY as json)) but seems to be what i want
  3. array_join(MYARRAY,',','') gives below error: 
    io.prestosql.spi.PrestoException: Compiler failed
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitScanFilterAndProject(LocalExecutionPlanner.java:1326)
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitProject(LocalExecutionPlanner.java:1205)
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitProject(LocalExecutionPlanner.java:711)
at io.prestosql.sql.planner.plan.ProjectNode.accept(ProjectNode.java:82)
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitOutput(LocalExecutionPlanner.java:797)
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitOutput(LocalExecutionPlanner.java:711)
at io.prestosql.sql.planner.plan.OutputNode.accept(OutputNode.java:83)
at io.prestosql.sql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:462)
at io.prestosql.sql.planner.LocalExecutionPlanner.plan(LocalExecutionPlanner.java:384)
at io.prestosql.execution.SqlTaskExecutionFactory.create(SqlTaskExecutionFactory.java:75)
at io.prestosql.execution.SqlTask.updateTask(SqlTask.java:394)
at io.prestosql.execution.SqlTaskManager.updateTask(SqlTaskManager.java:383)
at io.prestosql.server.TaskResource.createOrUpdateTask(TaskResource.java:128)
at jdk.internal.reflect.GeneratedMethodAccessor594.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:76)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:148)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:191)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:200)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:103)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:493)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:415)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:104)
at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:277)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:272)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:268)
at org.glassfish.jersey.internal.Errors.process(Errors.java:316)
at org.glassfish.jersey.internal.Errors.process(Errors.java:298)
at org.glassfish.jersey.internal.Errors.process(Errors.java:268)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:289)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:256)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:703)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:416)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:370)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:389)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:342)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:229)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1617)
at io.prestosql.server.security.AuthenticationFilter.handleInsecureRequest(AuthenticationFilter.java:157)
at io.prestosql.server.security.AuthenticationFilter.doFilter(AuthenticationFilter.java:101)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at io.airlift.http.server.TraceTokenFilter.doFilter(TraceTokenFilter.java:63)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at io.airlift.http.server.TimingFilter.doFilter(TimingFilter.java:51)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:717)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1300)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1215)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:173)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:500)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:273)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:375)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: io.prestosql.spi.PrestoException: Input type row(revisionid bigint, changedate varchar, changinguser varchar, confirmed varchar, verified varchar, revisionnumber bigint, comments varchar) not supported
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051)
at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974)
at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958)
at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964)
at io.prestosql.sql.gen.PageFunctionCompiler.compileProjection(PageFunctionCompiler.java:169)
at io.prestosql.sql.gen.ExpressionCompiler.lambda$compilePageProcessor$3(ExpressionCompiler.java:107)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(Unknown Source)
at java.base/java.util.Collections$2.tryAdvance(Unknown Source)
at java.base/java.util.Collections$2.forEachRemaining(Unknown Source)
at java.base/java.util.stream.AbstractPipeline.copyInto(Unknown Source)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(Unknown Source)
at java.base/java.util.stream.AbstractPipeline.evaluate(Unknown Source)
at java.base/java.util.stream.ReferencePipeline.collect(Unknown Source)
at io.prestosql.sql.gen.ExpressionCompiler.compilePageProcessor(ExpressionCompiler.java:108)
at io.prestosql.sql.gen.ExpressionCompiler.compilePageProcessor(ExpressionCompiler.java:96)
at io.prestosql.sql.planner.LocalExecutionPlanner$Visitor.visitScanFilterAndProject(LocalExecutionPlanner.java:1309)
... 77 more
Caused by: io.prestosql.spi.PrestoException: Input type row(revisionid bigint, changedate varchar, changinguser varchar, confirmed varchar, verified varchar, revisionnumber bigint, comments varchar) not supported
at io.prestosql.operator.scalar.ArrayJoin.specializeArrayJoin(ArrayJoin.java:213)
at io.prestosql.operator.scalar.ArrayJoin$ArrayJoinWithNullReplacement.specialize(ArrayJoin.java:117)
at io.prestosql.metadata.FunctionRegistry.specializeScalarFunction(FunctionRegistry.java:812)
at io.prestosql.metadata.FunctionRegistry.lambda$getScalarFunctionInvoker$3(FunctionRegistry.java:799)
at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4876)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
at com.google.common.cache.LocalCache.get(LocalCache.java:3951)
at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4871)
at io.prestosql.metadata.FunctionRegistry.getScalarFunctionInvoker(FunctionRegistry.java:799)
at io.prestosql.metadata.MetadataManager.getScalarFunctionInvoker(MetadataManager.java:1603)
at io.prestosql.sql.gen.BytecodeUtils.generateFullInvocation(BytecodeUtils.java:240)
at io.prestosql.sql.gen.BytecodeGeneratorContext.generateFullCall(BytecodeGeneratorContext.java:99)
at io.prestosql.sql.gen.FunctionCallCodeGenerator.generateExpression(FunctionCallCodeGenerator.java:29)
at io.prestosql.sql.gen.RowExpressionCompiler$Visitor.visitCall(RowExpressionCompiler.java:94)
at io.prestosql.sql.gen.RowExpressionCompiler$Visitor.visitCall(RowExpressionCompiler.java:80)
at io.prestosql.sql.relational.CallExpression.accept(CallExpression.java:90)
at io.prestosql.sql.gen.RowExpressionCompiler.compile(RowExpressionCompiler.java:77)
at io.prestosql.sql.gen.RowExpressionCompiler.compile(RowExpressionCompiler.java:72)
at io.prestosql.sql.gen.PageFunctionCompiler.generateEvaluateMethod(PageFunctionCompiler.java:361)
at io.prestosql.sql.gen.PageFunctionCompiler.definePageProjectWorkClass(PageFunctionCompiler.java:245)
at io.prestosql.sql.gen.PageFunctionCompiler.compileProjectionInternal(PageFunctionCompiler.java:193)
at io.prestosql.sql.gen.PageFunctionCompiler.lambda$new$0(PageFunctionCompiler.java:127)
at com.google.common.cache.CacheLoader$FunctionToCacheLoader.load(CacheLoader.java:165)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
... 94 more
Caused by: io.prestosql.metadata.OperatorNotFoundException: CAST to varchar cannot be applied to row(revisionid bigint, changedate varchar, changinguser varchar, confirmed varchar, verified varchar, revisionnumber bigint, comments varchar)
at io.prestosql.metadata.MetadataManager.getCoercion(MetadataManager.java:1531)
at io.prestosql.metadata.Metadata.getCoercion(Metadata.java:474)
at io.prestosql.operator.scalar.ArrayJoin.specializeArrayJoin(ArrayJoin.java:171)
... 123 more

my column is revisions array(ROW(revisionid bigint, changedate varchar, changinguser varchar, confirmed varchar, verified varchar, revisionnumber bigint, comments varchar))

findepi commented 3 years ago

Marking as a bug, since OperatorNotFoundException: CAST to varchar cannot be applied to row(revisionid bigint, changedate varchar, changinguser varchar, confirmed varchar, verified varchar, revisionnumber bigint, comments varchar) should be realized earlier, during analysis. User should not get "Compiler failed" in a case like this.