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
14

Hi @Simon_Pannek

    - args:
        - "date"
      return: "varchar"
      rewrite: "CAST({0} AS CHAR(10))"
     #Added
    - args:
        - "date"
      return: "date"
      rewrite: "CAST({0} AS DATE)"

After referring to the SingleStore documentation and using the above ARP configuration, adding the CAST function in the WHERE clause, such as in the query:

SELECT Msisdn, Event_date
FROM “6d_SingleStore”.“Voice_Seg_Fct”
WHERE CAST(Event_date AS CHAR(10)) < ‘2024-04-30’
AND CAST(Event_date AS CHAR(10)) > ‘2024-04-01’

allows the query to execute successfully. Is this the only viable solution, or is there a way to achieve the same result without using the CAST function in the WHERE clause?

15 
    - args:
        - "date"
      return: "date"
      rewrite: "CAST({0} AS DATE)"

Seems weird to me. Why would you want to cast a date value to a date value. Changing this to

    - args:
        - "varchar"
      return: "date"
      rewrite: "CAST({0} AS DATE)"

might solve this issue though.

Technically, if your ARP file correctly reflects the supported function parameters by SingleStore, explicit function rewrites like this shouldn’t be necessary. If you just want to get it working, ARP also supports function rewrites given certain parameter combinations (just look for the “rewrite” keyword in ARP files for examples).

16

Hi @Simon_Pannek

Thanks for your response

I also tried using the following configuration:

- args:
    - "varchar"
  return: "date"
  rewrite: "CAST({0} AS DATE)"

but it didn’t work for me.

17

In that case, your best bet is probably to do the explicit function rewrites like I suggested in my previous message.

18

Hi @Simon_Pannek

When I run the following query:

SELECT Event_date
FROM “SingleStore_DB”.“test”
WHERE Event_date = ‘2024-09-01’

The Dremio plugin generates the query as:

SELECT CAST(DATE ‘2024-09-01’ AS DATE) AS Event_date
FROM (SELECT test.Event_Date
FROM test) AS test
WHERE Event_Date = ‘2024-09-01’

To address this, I added the following to the dremio-singlestore ARP file under the CAST functionality:

- args:
	- "date"
  return: "varchar"
  rewrite: "CAST({0} AS CHAR(10))"


- args:
	- "varchar"
  return: "date"
  rewrite: "CAST({0} AS DATE)"

After building and testing, it still didn’t work. As a workaround, I removed the CAST functionality from the ARP file and redeployed it. However, the CAST is still being applied when running the query.
How that the CAST functionality is being handled internally ?. Could there be any other dependencies beyond the ARP file?

19

Hi team,

Any update on this please?

Thanks in Advance

20

Hi @Simon_Pannek

Could you please assist me with the scenario mentioned above?

Thanks in Advance