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

Query Error When Using VARCHAR Field in WHERE Condition with Dremio and SingleStore DB #1

Open
arjun-guptha opened this issue Mar 25, 2024 · 0 comments
Open

Query Error When Using VARCHAR Field in WHERE Condition with Dremio and SingleStore DB #1

arjun-guptha opened this issue Mar 25, 2024 · 0 comments

Comments

@arjun-guptha
Copy link

arjun-guptha commented Mar 25, 2024 •
edited
Loading

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)
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
Milestone
No milestone
Development

No branches or pull requests
1 participant
@arjun-guptha