Issue with Column Casting in Dremio-SingleStore Connector: SQL Syntax Error When Using Selected Column in WHERE Clause

1

Hi team,

I’m using Dremio version 25.0.5 and have built a Dremio-SingleStore connector. During testing, I encountered an issue where selecting a column and using the same column in a WHERE condition results in a “You have an error in your SQL syntax” message. Upon inspecting the query plan, I noticed that if a column is both selected and used in a WHERE condition, it gets cast in the SELECT clause. Could anyone provide guidance on why this casting occurs and how to resolve this issue?

Query :

SELECT Cust_Sub_Id FROM “6d_SingleStore”.“Voice_Seg_Fct” where Cust_Sub_Id=123456

Please find the profile query plan attached below
a6db7c8a-5772-4693-98bb-fd69d0000ec5.zip (22.5 KB)

2

Hi Team,

Any update on above issue?

3

You can try enabling the support option planner.verbose_profile to get a verbose query profile and see which phase in planning adds the CAST.

4

Hi @Benny_Chow

I enabled planner.verbose_profile and observed the plan. From my understanding, the Reduce Expressions phase in planning is responsible for adding the CAST in the query.Can you suggest me a solution for above mentioned issue.

5

Can you share the verbose profile?

6

Hi
Please find the profile query plan attached below
5d5f5bac-63c0-4e73-88a9-09fd4c5df612.zip (21.3 KB)

7

Looks like their CAST function doesn’t support BIGINT. Maybe there’s something you can configure in the ARP connector to avoid this…

8

Hi @Benny_Chow

I took the cast details from the MySQL ARP file and used them in the SingleStore ARP file. After building and testing the plugin, I found that selecting all columns and using an integer column in the WHERE condition works fine. However, it’s not working when using VARCHAR or DATE columns. Could you please suggest a solution? The ARP file used for building the SingleStore plugin is attached below.

singlestore_arp.zip (2.9 KB)

9

Hi team

Any update on this ?

10

Hey! I’m a member of the sources team, so I might be able to support with this.
To better understand what’s going on, I assume you based your connector off the community version of the SingleStore connector. Did you run into these errors before or after you made changes to the arp file?

11

Hi @Simon_Pannek

I built the SingleStore Dremio plugin using the same link you provided. Initially, I tried selecting columns by passing integer, bigint, date, and varchar columns in the WHERE condition, but I ran into an issue. I then took the cast details from the MySQL ARP file and applied them to the SingleStore ARP file. After building and testing the plugin, I found that selecting all columns and using integer and bigint columns in the WHERE condition worked correctly. However, it’s still not working when using VARCHAR or DATE columns.

12

Hi team,

Any update on this ?

Thanks in Advance

13

Hi,
no need for additional pings, we have an internal ticket reflecting your issue and are prioritizing accordingly.
Thank you for your clarification! Simply copying over cast details from MySQL won’t work since the pushdown function parameters supported by MySQL differ from the ones supported by SingleStore. I recommend looking at the Syntax described here and only include parameters in the ARP that are supported by SingleStore.

1 Like