SQL query error

1

Hi!

We have an error in this SQL query which seems very straightforward.
The source is PostgreSQL 9.6.
Dremio 3.1.6-201903070042400578-fdcd3a8

It works well with psql or metabase for instance.
Any idea of what could be wrong ? Many thanks

select
bookings.deal_id,
(select loggables.created_at from loggables where loggables.loggable_id = bookings.id) as confirmation_date
from bookings limit 5

The following error raises:

      While invoking method 'public org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$Result org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Sort)'

Verbose:

 PLAN ERROR: While invoking method 'public org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$Result org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Sort)'

SQL Query select
bookings.deal_id,

 (select loggables.created_at from loggables where loggables.loggable_id = bookings.id) as confirmation_date

from bookings
limit 5



  (java.lang.RuntimeException) While invoking method 'public org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$Result org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Sort)'
    org.apache.calcite.util.ReflectUtil$2.invoke():527
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.reflect.InvocationTargetException) null
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.RuntimeException) While invoking method 'public org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$Result org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
    org.apache.calcite.util.ReflectUtil$2.invoke():527
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.reflect.InvocationTargetException) null
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.RuntimeException) While invoking method 'public org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$Result org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
    org.apache.calcite.util.ReflectUtil$2.invoke():527
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():698
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.processProjectChild():238
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():250
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.reflect.InvocationTargetException) null
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():698
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.processProjectChild():238
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():250
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    <truncated>
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.RuntimeException) While invoking method 'public org.apache.calcite.rel.rel2sql.SqlImplementor$Result org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Filter)'
    org.apache.calcite.util.ReflectUtil$2.invoke():527
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():249
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():698
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.processProjectChild():238
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():250
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.reflect.InvocationTargetException) null
    sun.reflect.GeneratedMethodAccessor601.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():249
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():698
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.processProjectChild():238
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():250
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
  Caused By (java.lang.ArrayIndexOutOfBoundsException) -1
    com.google.common.collect.RegularImmutableList.get():58
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioAliasContext.field():877
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():645
    org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql():711
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():742
    org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql():592
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.lambda$toSql$0():723
    com.dremio.exec.store.jdbc.dialect.arp.ArpDialect.decorateSqlNode():508
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():724
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit():179
    sun.reflect.GeneratedMethodAccessor601.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():249
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter$DremioContext.toSql():698
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.processProjectChild():238
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():250
    sun.reflect.GeneratedMethodAccessor506.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    org.apache.calcite.rel.rel2sql.DremioRelToSqlConverter.visit():351
    sun.reflect.GeneratedMethodAccessor504.invoke():-1
    sun.reflect.DelegatingMethodAccessorImpl.invoke():43
    java.lang.reflect.Method.invoke():498
    org.apache.calcite.util.ReflectUtil$2.invoke():524
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch():106
    org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild():112
    com.dremio.exec.store.jdbc.rel.JdbcPrel.<init>():122
    com.dremio.exec.store.jdbc.rel.JdbcIntermediatePrel.finalizeRel():60
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():30
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.StatelessRelShuttleImpl.visitChild():37
    org.apache.calcite.rel.RelShuttleImpl.visitChildren():61
    org.apache.calcite.rel.RelShuttleImpl.visit():123
    com.dremio.exec.planner.sql.handlers.PrelFinalizer.visit():32
    org.apache.calcite.rel.AbstractRelNode.accept():279
    com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():503
    com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
    com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
    com.dremio.exec.work.foreman.AttemptManager.run():288
    java.util.concurrent.ThreadPoolExecutor.runWorker():1149
    java.util.concurrent.ThreadPoolExecutor$Worker.run():624
    java.lang.Thread.run():748
2

Hi @mlb,

Can you provide the Dremio profile for this query?

Also, can you give the output of the “describe” table statement from Postgres? I believe the syntax would be something like \d+ bookings

3

Hi @ben,

Sure, please find attached.
describe statements are not complete but the relevant information is there:

\d+ bookings
                                                                   Table "public.bookings"
             Column             |            Type             |                       Modifiers                       | Storage  | Stats target | Description
--------------------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
 id                             | integer                     | not null default nextval('bookings_id_seq'::regclass) | plain    |              |
 deal_id                        | integer                     |                                                       | plain    |              |
 created_at                     | timestamp without time zone | not null                                              | plain    |              |
 updated_at                     | timestamp without time zone | not null                                              | plain    |              |
 state                          | character varying(255)      |                                                       | extended |              |
<...> 
Indexes:
    "bookings_pkey" PRIMARY KEY, btree (id)
    "<...>" btree (<...>)
    "<...>" btree (<...>)
Foreign-key constraints:
    "fk_rails_78d5bbca6a" FOREIGN KEY (<...>) REFERENCES <...>(id)
    "fk_rails_dfd7c1b556" FOREIGN KEY (<...>) REFERENCES <...>(id)
Referenced by:
    TABLE "<...>" CONSTRAINT "fk_rails_8e3e03adc8" FOREIGN KEY (booking_id) REFERENCES bookings(id)
    TABLE "<...>" CONSTRAINT "fk_rails_8e3ec99562" FOREIGN KEY (booking_id) REFERENCES bookings(id)


\d+ loggables
                                                             Table "public.loggables"
      Column       |            Type             |                       Modifiers                        | Storage  | Stats target | Description
-------------------+-----------------------------+--------------------------------------------------------+----------+--------------+-------------
 id                | integer                     | not null default nextval('loggables_id_seq'::regclass) | plain    |              |
 loggable_id       | integer                     |                                                        | plain    |              |
 loggable_type     | character varying           |                                                        | extended |              |
<...>
 created_at        | timestamp without time zone | not null                                               | plain    |              |
 updated_at        | timestamp without time zone | not null                                               | plain    |              |
Indexes:
    "loggables_pkey" PRIMARY KEY, btree (id)
    "index_loggables_on_loggable_id_and_loggable_type" btree (loggable_id, loggable_type)
<...>

profile_dremio.zip (6,8 Ko)

4

Hi!
Any hint given by the additional information provided ?
Many thanks,

5

FYI, this query works (different WHERE clause with no reference to the bookings table)

select
bookings.deal_id,
(select loggables.created_at from loggables where loggables.loggable_id = 1) as confirmation_date
from bookings limit 5
6

Apologies for the late response @mlb,

The error occurs during planning, while the query string is parsed to the relational syntax tree. The subquery is likely the problem:

select loggables.created_at from loggables where loggables.loggable_id = bookings.id

In Dremio 3.1.6 (which I believe you are using) this should have given an error:

Dremio doesn’t currently support non-scalar sub-queries used in an expression

But, for that matter, your other query should fail with the same message. What is the output of just the subquery?

select loggables.created_at from loggables where loggables.loggable_id = 1

7

Hi @ben, thanks for the follow-up

The result of the last subquery is 1 result, a date 2016-10-03 12:21:10.478

8

Would you advise to use a JOIN instead ?

Also, we have other complex queries with JOIN clauses that are fine with PostgreSQL / PowerBI which fail with Dremio - we are narrowing down the errors and will come back here.

9

@mlb, if the output of:

select loggables.created_at from loggables where loggables.loggable_id = bookings.id

is multiple values, then Dremio won’t handle this. You could try re-writing this as a JOIN

10

@ben, the output is indeed multiple values, but even with

select loggables.created_at from loggables where loggables.loggable_id = bookings.id limit 1
or
select max(loggables.created_at) from loggables where loggables.loggable_id = bookings.id

as a subquery, we encounter the same calcite/Sort error (and not the non scalar sub-queries error message).

Do you confirm subqueries are well handled generally with Dremio ?

Thanks,
Matthieu

1 Like