Union query error

1

hi,supporters:
I had found the problem when I used the dremio2.0.1 .can you help me ?
enviroment: dremio2.0.1 (1 master ,2 executer)
query SQL:

select u.* from (select * from “68”.csp.jmsb union select * from “68”.csp.stsb ) u

ERROR INFORMATION

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM csp.jmsb LIMIT 2000 OFFSET 0 UNION SELECT * FROM csp.stsb LIMIT 2000 OFFSET 0 plugin 68 SqlOperatorImpl JDBC_SUB_SCAN Location 1:0:4 SqlOperatorImpl JDBC_SUB_SCAN Location 1:0:4 Fragment 1:0 [Error Id: 4e2de7e1-9036-4ff0-8f83-74c558a5db8d on node2:-1] (java.sql.SQLException) (conn:127358) Incorrect usage of UNION and LIMIT org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get():179 org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException():118 org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue():247 org.mariadb.jdbc.MariaDbStatement.executeInternal():322 org.mariadb.jdbc.MariaDbStatement.executeQuery():492 com.dremio.exec.store.jdbc.JdbcRecordReader.setup():175 com.dremio.exec.store.CoercionReader.setup():109 com.dremio.sabot.op.scan.ScanOperator$1.run():188 com.dremio.sabot.op.scan.ScanOperator$1.run():184 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1807 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():184 com.dremio.sabot.op.scan.ScanOperator.setupReader():176 com.dremio.sabot.op.scan.ScanOperator.setup():162 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():560 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():530 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331 com.dremio.sabot.exec.fragment.FragmentExecutor.run():227 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577 com.dremio.sabot.task.AsyncTaskWrapper.run():92 com.dremio.sabot.task.slicing.SlicingThread.run():71 Caused By (java.sql.SQLException) Incorrect usage of UNION and LIMIT Query is: SELECT * FROM csp.jmsb LIMIT 2000 OFFSET 0 UNION SELECT * FROM csp.stsb LIMIT 2000 OFFSET 0 org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery():113 org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery():167 org.mariadb.jdbc.MariaDbStatement.executeInternal():316 org.mariadb.jdbc.MariaDbStatement.executeQuery():492 com.dremio.exec.store.jdbc.JdbcRecordReader.setup():175 com.dremio.exec.store.CoercionReader.setup():109 com.dremio.sabot.op.scan.ScanOperator$1.run():188 com.dremio.sabot.op.scan.ScanOperator$1.run():184 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1807 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():184 com.dremio.sabot.op.scan.ScanOperator.setupReader():176 com.dremio.sabot.op.scan.ScanOperator.setup():162 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():560 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():530 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331 com.dremio.sabot.exec.fragment.FragmentExecutor.run():227 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577 com.dremio.sabot.task.AsyncTaskWrapper.run():92 com.dremio.sabot.task.slicing.SlicingThread.run():71

2

the dataSource Type is mysql 5.7.20-18

3

Hi there,

Could you confirm whether “68”.csp.jmsb and “68”.csp.stsb are virtual or physical datasets in Dremio? If they are virtual, please share the definition of these VDSs.

Also could you share the profile of the failed query? See the link below how to do that.

4

hi jason
nice to see your reply. the “68”.csp.jmsb and the “68”.csp.jmsb are two physical table.
I supplement the problem when I click the “preview” button is report the error ,but when i
click the “run” , it can work normally.

5

There seems to be a SQL translation problem with the use of limit & union in your specific version of MySQL. When you use “preview” button, Dremio inserts a limit so that you can preview and see your data very quickly. When you use the “run” button, it runs the query on the full dataset so thereby doesn’t add a limit.

Unfortunately I am unable to repro this error with my own version of MySQL. In the interim, I would recommend just building a VDS off that union query and continuing workflow from there.