While integrating Dremio with SingleStore DB, I’ve successfully established a connection to the database. However, I’ve encountered an issue when passing a varchar field in the WHERE condition of a query, resulting in the following error.
Query : SELECT * from “test_db”.“Profile_Cdr” WHERE Line_Type=‘MobilePrepaid’
Dremio version : 23.1.0
2024-03-25 16:59:46,592 [19fe527c-f728-4a4a-2625-cef5a8a02100:execution-planning] WARN c.d.e.p.f.AssignFragmentPriorityVisitor - Assigned Priority not found for major fragment 0. Defaulting to 1
2024-03-25 16:59:46,606 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 19fe527c-f728-4a4a-2625-cef5a8a02100:0:0 with assigned weight 1 and scheduling weight 1
2024-03-25 16:59:46,607 [FABRIC-rpc-event-queue] INFO c.d.s.e.f.FragmentExecutorBuilder - Setting outStandingRPCsPerTunnel:3
2024-03-25 16:59:46,670 [e5 - 19fe527c-f728-4a4a-2625-cef5a8a02100:frag:0:0] INFO c.d.exec.store.jdbc.JdbcRecordReader - User Error Occurred [ErrorId: c2fa3d6d-f744-4957-a992-21571834acac]
com.dremio.common.exceptions.UserException: Source 'test_db' returned error '(conn=533734) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`' at line 1'
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:907)
at com.dremio.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:254)
at com.dremio.exec.store.CoercionReader.setup(CoercionReader.java:118)
at com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser(ScanOperator.java:343)
at com.dremio.sabot.op.scan.ScanOperator.setupReader(ScanOperator.java:334)
at com.dremio.sabot.op.scan.ScanOperator.setup(ScanOperator.java:298)
at com.dremio.sabot.driver.SmartOp$SmartProducer.setup(SmartOp.java:592)
at com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer(Pipe.java:79)
at com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer(Pipe.java:63)
at com.dremio.sabot.driver.SmartOp$SmartProducer.accept(SmartOp.java:562)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.StraightPipe.setup(StraightPipe.java:102)
at com.dremio.sabot.driver.Pipeline.setup(Pipeline.java:71)
at com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution(FragmentExecutor.java:598)
at com.dremio.sabot.exec.fragment.FragmentExecutor.run(FragmentExecutor.java:430)
at com.dremio.sabot.exec.fragment.FragmentExecutor.access$1700(FragmentExecutor.java:106)
at com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run(FragmentExecutor.java:973)
at com.dremio.sabot.task.AsyncTaskWrapper.run(AsyncTaskWrapper.java:121)
at com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop(SlicingThread.java:247)
at com.dremio.sabot.task.slicing.SlicingThread.run(SlicingThread.java:171)
Caused by: java.sql.SQLSyntaxErrorException: (conn=533734) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`' at line 1
at com.singlestore.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:269)
at com.singlestore.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:358)
at com.singlestore.jdbc.message.ClientMessage.readPacket(ClientMessage.java:175)
at com.singlestore.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:865)
at com.singlestore.jdbc.client.impl.StandardClient.readResults(StandardClient.java:806)
at com.singlestore.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:723)
at com.singlestore.jdbc.client.impl.StandardClient.executeInternal(StandardClient.java:647)
at com.singlestore.jdbc.client.impl.StandardClient.execute(StandardClient.java:618)
at com.singlestore.jdbc.Statement.executeInternal(Statement.java:989)
at com.singlestore.jdbc.Statement.executeQuery(Statement.java:155)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
at com.dremio.exec.store.jdbc.JdbcRecordReader.setup(JdbcRecordReader.java:230)
... 23 common frames omitted
2024-03-25 16:59:46,674 [e5 - 19fe527c-f728-4a4a-2625-cef5a8a02100:frag:0:0] ERROR com.dremio.sabot.driver.SmartOp - NullPointerException
com.dremio.common.exceptions.UserException: NullPointerException
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:907)
at com.dremio.sabot.driver.SmartOp.contextualize(SmartOp.java:161)
at com.dremio.sabot.driver.SmartOp$SmartSingleInput.close(SmartOp.java:237)
at com.dremio.common.AutoCloseables.close(AutoCloseables.java:139)
at com.dremio.sabot.driver.Pipeline.close(Pipeline.java:200)
at com.dremio.common.DeferredException.suppressingClose(DeferredException.java:217)
at com.dremio.sabot.exec.fragment.FragmentExecutor.retire(FragmentExecutor.java:662)
at com.dremio.sabot.exec.fragment.FragmentExecutor.finishRun(FragmentExecutor.java:621)
at com.dremio.sabot.exec.fragment.FragmentExecutor.run(FragmentExecutor.java:498)
at com.dremio.sabot.exec.fragment.FragmentExecutor.access$1700(FragmentExecutor.java:106)
at com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run(FragmentExecutor.java:973)
at com.dremio.sabot.task.AsyncTaskWrapper.run(AsyncTaskWrapper.java:121)
at com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop(SlicingThread.java:247)
at com.dremio.sabot.task.slicing.SlicingThread.run(SlicingThread.java:171)
Caused by: java.lang.NullPointerException: null
at com.dremio.sabot.exec.context.OperatorStats.setSlowIoInfosInProfile(OperatorStats.java:664)
at com.dremio.sabot.op.writer.WriterCommitterOperator.close(WriterCommitterOperator.java:224)
at com.dremio.sabot.driver.SmartOp$SmartSingleInput.close(SmartOp.java:235)
... 11 common frames omitted
2024-03-25 16:59:46,686 [Fabric-RPC-Offload16] INFO c.d.exec.maestro.FragmentTracker - Fragment 19fe527c-f728-4a4a-2625-cef5a8a02100:0:0 failed, cancelling remaining fragments.
2024-03-25 16:59:46,692 [Fabric-RPC-Offload16] INFO c.d.exec.work.foreman.AttemptManager - 19fe527c-f728-4a4a-2625-cef5a8a02100: State change requested RUNNING --> FAILED, Exception com.dremio.common.exceptions.UserRemoteException: DATA_READ ERROR: Source 'test_db' returned error '(conn=533734) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`' at line 1'
plugin test_db
sql SELECT `Profile_Cdr`.`Cust_Sub_Id`, `Profile_Cdr`.`Id_Sbsc`, `Profile_Cdr`.`Account_Number`, `Profile_Cdr`.`Subscr_No`, `Profile_Cdr`.`Arbor_Account_No`, `Profile_Cdr`.`Msisdn`, `Profile_Cdr`.`Business`, CAST('MobilePrepaid' AS VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`, `Profile_Cdr`.`Deactive_Date`, `Profile_Cdr`.`Id_Acct`, `Profile_Cdr`.`Document_Type`, `Profile_Cdr`.`Document_Id`, `Profile_Cdr`.`Name`, `Profile_Cdr`.`Email`, `Profile_Cdr`.`Age`, `Profile_Cdr`.`Subscriber_Dob`, `Profile_Cdr`.`Gender`, `Profile_Cdr`.`Nationality`, `Profile_Cdr`.`Acctexternalid`, `Profile_Cdr`.`Id_Cst`, `Profile_Cdr`.`Adsl_Login`, `Profile_Cdr`.`Other_Contact`, `Profile_Cdr`.`Other_Contact_2`, `Profile_Cdr`.`Other_Contact_3`, `Profile_Cdr`.`Preferred_language`, `Profile_Cdr`.`Credit_Rating`, `Profile_Cdr`.`Vip_Code`, `Profile_Cdr`.`Account_Category`, `Profile_Cdr`.`Sales_Code`, `Profile_Cdr`.`Sic_Code`, `Profile_Cdr`.`Current_Plan`, `Profile_Cdr`.`Current_Plan_Act_Date`, `Profile_Cdr`.`Handset_Manufacturer`, `Profile_Cdr`.`Handset_Model`, `Profile_Cdr`.`Handset_Generation`, `Profile_Cdr`.`Handset_Type`, `Profile_Cdr`.`Most_Used_Site_Id`, `Profile_Cdr`.`Most_Used_Site_Region`, `Profile_Cdr`.`Most_Used_Site_Technology`, `Profile_Cdr`.`Most_Used_Day_Site_Id`, `Profile_Cdr`.`Most_Used_Day_Site_Region`, `Profile_Cdr`.`Most_Used_Day_Site_Technology`, `Profile_Cdr`.`Most_Used_Night_Site_Id`, `Profile_Cdr`.`Most_Used_Night_Site_Region`, `Profile_Cdr`.`Most_Used_Night_Site_Technology`, `Profile_Cdr`.`Most_Used_Site_Id_90D`, `Profile_Cdr`.`Most_Used_Site_Region_90D`, `Profile_Cdr`.`Most_Used_Site_Technology_90D`, `Profile_Cdr`.`Most_Used_Day_Site_Id_90D`, `Profile_Cdr`.`Most_Used_Day_Site_Region_90D`, `Profile_Cdr`.`Most_Used_Day_Site_Technology_90D`, `Profile_Cdr`.`Most_Used_Night_Site_Id_90D`, `Profile_Cdr`.`Most_Used_Night_Site_Region_90D`, `Profile_Cdr`.`Most_Used_Night_Site_Technology_90D`, `Profile_Cdr`.`Vlr_Active_Status`, `Profile_Cdr`.`Activation_Business`, `Profile_Cdr`.`Activation_Csr_Id`, `Profile_Cdr`.`Activation_Csr_Name`, `Profile_Cdr`.`Activation_Csr_Outlet_Region`, `Profile_Cdr`.`Activation_Csr_Outlet_Dealer`, `Profile_Cdr`.`Activation_Plan`, `Profile_Cdr`.`Activation_Type`, `Profile_Cdr`.`Activation_Channel`, `Profile_Cdr`.`Mnp_Donor`, `Profile_Cdr`.`Act_Tech_Type`, `Profile_Cdr`.`Termination_Business`, `Profile_Cdr`.`Termination_Csr_Id`, `Profile_Cdr`.`Termination_Csr_Name`, `Profile_Cdr`.`Termination_Csr_Outlet_Region`, `Profile_Cdr`.`Termination_Csr_Outlet_Dealer`, `Profile_Cdr`.`Termination_Plan`, `Profile_Cdr`.`Termination_Type`, `Profile_Cdr`.`Termination_Channel`, `Profile_Cdr`.`Mnp_Recipient`, `Profile_Cdr`.`Dct_Tech_Type`, `Profile_Cdr`.`Last_Login_Date`, `Profile_Cdr`.`Card_Saved_Flag`, `Profile_Cdr`.`App_Last_Page_Visited`, `Profile_Cdr`.`Account_Categry`, `Profile_Cdr`.`New_Plan_Name`, `Profile_Cdr`.`New_Rate`, `Profile_Cdr`.`New_Tech`, `Profile_Cdr`.`Start_Date`, `Profile_Cdr`.`Contract_End_Date`, `Profile_Cdr`.`Old_Plan_Name`, `Profile_Cdr`.`Old_Rate`, `Profile_Cdr`.`Commitment_Old_Tech`, `Profile_Cdr`.`Old_Plan_Commitment_Start_Date`, `Profile_Cdr`.`Old_Plan_Commitment_End_Date`, `Profile_Cdr`.`Order_Date`, `Profile_Cdr`.`Completed_Date`, `Profile_Cdr`.`Is_Tv`, `Profile_Cdr`.`Account_No`, `Profile_Cdr`.`Plan_Migration_By_Service`, `Profile_Cdr`.`Plan_Movement_Direction`, `Profile_Cdr`.`Contract_Movement_Old_Contract`, `Profile_Cdr`.`Contract_Movement_New_Contract`, `Profile_Cdr`.`Csr`, `Profile_Cdr`.`Region`, `Profile_Cdr`.`Dealer`, `Profile_Cdr`.`Channel`, `Profile_Cdr`.`Order_Id`, `Profile_Cdr`.`S_Offerid`, `Profile_Cdr`.`Active_End_Date`, `Profile_Cdr`.`Barred_State`, `Profile_Cdr`.`Roam_Barred`, `Profile_Cdr`.`First_Call_Date`, `Profile_Cdr`.`Grace_End_Date`, `Profile_Cdr`.`Suspension_Date`, `Profile_Cdr`.`Suspension_End_Date`, `Profile_Cdr`.`Credit_Limit`, `Profile_Cdr`.`Balance`, `Profile_Cdr`.`Create_Date`, `Profile_Cdr`.`Aon`, `Profile_Cdr`.`App_User_Flag`
FROM `Profile_Cdr`
WHERE `Profile_Cdr`.`Line_Type` = 'MobilePrepaid'
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
Fragment 0:0
[Error Id: c2fa3d6d-f744-4957-a992-21571834acac on server35:0]
(java.sql.SQLSyntaxErrorException) (conn=533734) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`' at line 1
com.singlestore.jdbc.export.ExceptionFactory.createException():269
com.singlestore.jdbc.export.ExceptionFactory.create():358
com.singlestore.jdbc.message.ClientMessage.readPacket():175
com.singlestore.jdbc.client.impl.StandardClient.readPacket():865
com.singlestore.jdbc.client.impl.StandardClient.readResults():806
com.singlestore.jdbc.client.impl.StandardClient.readResponse():723
com.singlestore.jdbc.client.impl.StandardClient.executeInternal():647
com.singlestore.jdbc.client.impl.StandardClient.execute():618
com.singlestore.jdbc.Statement.executeInternal():989
com.singlestore.jdbc.Statement.executeQuery():155
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():230
com.dremio.exec.store.CoercionReader.setup():118
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():343
com.dremio.sabot.op.scan.ScanOperator.setupReader():334
com.dremio.sabot.op.scan.ScanOperator.setup():298
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():592
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():562
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.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.Pipeline.setup():71
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():598
com.dremio.sabot.exec.fragment.FragmentExecutor.run():430
com.dremio.sabot.exec.fragment.FragmentExecutor.access$1700():106
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():973
com.dremio.sabot.task.AsyncTaskWrapper.run():121
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():247
com.dremio.sabot.task.slicing.SlicingThread.run():171
2024-03-25 16:59:46,720 [Fabric-RPC-Offload15] WARN c.d.exec.work.foreman.AttemptManager - Dropping request to move to COMPLETED state as query 19fe527c-f728-4a4a-2625-cef5a8a02100 is already at FAILED state (which is terminal).
2024-03-25 16:59:46,752 [async-query-logger9] INFO query.logger - Query: 19fe527c-f728-4a4a-2625-cef5a8a02100; outcome: FAILED
2024-03-25 16:59:47,179 [grpc-default-executor-28] INFO c.d.service.jobs.JobResultsStore - User Error Occurred [ErrorId: 89c153a0-8f73-4fdd-b610-7927569ac845]
com.dremio.common.exceptions.UserException: Source 'test_db' returned error '(conn=533734) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR) AS `Line_Type`, `Profile_Cdr`.`Status`, `Profile_Cdr`.`Activation_Date`' at line 1'
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:907)
at com.dremio.service.jobs.JobResultsStore.loadJobData(JobResultsStore.java:152)
at com.dremio.service.jobs.JobResultsStore$LateJobLoader.load(JobResultsStore.java:331)
at com.dremio.service.jobs.JobDataImpl.range(JobDataImpl.java:50)
at com.dremio.service.jobs.LocalJobsService.getJobData(LocalJobsService.java:1128)
at com.dremio.service.jobs.JobsFlightProducer.getStream(JobsFlightProducer.java:78)
at com.dremio.dac.service.flight.CoordinatorFlightProducer.getStream(CoordinatorFlightProducer.java:63)
at org.apache.arrow.flight.FlightService.doGetCustom(FlightService.java:111)
at org.apache.arrow.flight.FlightBindingService$DoGetMethod.invoke(FlightBindingService.java:144)
at org.apache.arrow.flight.FlightBindingService$DoGetMethod.invoke(FlightBindingService.java:134)
at io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:182)
at io.grpc.PartialForwardingServerCallListener.onHalfClose(PartialForwardingServerCallListener.java:35)
at io.grpc.ForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:23)
at io.grpc.ForwardingServerCallListener$SimpleForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:40)
at io.grpc.Contexts$ContextualizedServerCallListener.onHalfClose(Contexts.java:86)
at io.grpc.PartialForwardingServerCallListener.onHalfClose(PartialForwardingServerCallListener.java:35)
at io.grpc.ForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:23)
at io.grpc.ForwardingServerCallListener$SimpleForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:40)
at io.opentracing.contrib.grpc.TracingServerInterceptor$2.onHalfClose(TracingServerInterceptor.java:231)
at io.grpc.PartialForwardingServerCallListener.onHalfClose(PartialForwardingServerCallListener.java:35)
at io.grpc.ForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:23)
at io.grpc.ForwardingServerCallListener$SimpleForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:40)
at io.grpc.util.TransmitStatusRuntimeExceptionInterceptor$1.onHalfClose(TransmitStatusRuntimeExceptionInterceptor.java:74)
at io.grpc.internal.ServerCallImpl$ServerStreamListenerImpl.halfClosed(ServerCallImpl.java:340)
at io.grpc.internal.ServerImpl$JumpToApplicationThreadServerStreamListener$1HalfClosed.runInContext(ServerImpl.java:866)
at io.grpc.internal.ContextRunnable.run(ContextRunnable.java:37)
at io.grpc.internal.SerializingExecutor.run(SerializingExecutor.java:133)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)