Dremio cast issue with SingleStore

1

Hi Team,

I am using dremio-community-25.0.5.x with SingleStore 5.7.x, and have a table called cust_rec_data , When I am executing a query as,

image
image1262×288 15.8 KB

It’s push down as,

image
image1676×414 81.2 KB

The highlighted CAST syntax is supported in MySQL and Oracle, but not in SingleStore. I tried multiple ways it’s showing same, Please suggest a workaround. I am attaching the query profile and ARP file.

b47063c9-6836-487b-92af-0d011dfe82eb.zip (16.9 KB)
singlestore-arp.zip (6.3 KB)

ARP file was copied from MySQL since most of the SingleStore queries is compatible with MySQL.

Thanks,
Ajay Babu Maguluri.

2

Hi Team,

Any update?

Thanks,
Ajay Babu Maguluri.

3

@AjayBabuM Unfortunately SingleStore is not a source that we currently test on. I am not sure if all pushdowns to MySQL from the Dremio side would work on SingleStore.

Try running this pushdown on Singlestore and see where the issue is. That will narrow down of this is something that needs to fixed in ARP framework to SingleStore which you can try to implement

SELECT CAST(DATE '2024-09-30' AS DATE) AS `event_date`, `msisdn`, `rec_amount`
FROM (SELECT `cust_rec_data`.`event_date`, `cust_rec_data`.`msisdn`, `cust_rec_data`.`rec_amount`
FROM `cust_rec_data`) AS `cust_rec_data`
WHERE `event_date` = DATE '2024-09-30'
4

Hi @balaji.ramaswamy

I already executed query directly on Singlestore, As I highlighted in in my conversation DATE at cast and where clause was making issue. If I run without DATE like,

SELECT CAST('2024-09-30' AS DATE) AS `event_date`, `msisdn`, `rec_amount`
FROM (SELECT `cust_rec_data`.`event_date`, `cust_rec_data`.`msisdn`, `cust_rec_data`.`rec_amount`
FROM `cust_rec_data`) AS `cust_rec_data`
WHERE `event_date` = '2024-09-30'

It’s working fine. Is possible to handle it in ARP? actually I tried multiple changes in ARP but it’s pushdown as same. So please suggest want need to do.

Thanks,
Ajay Babu Maguluri.

5

@AjayBabuM Currently Singlestore is not in the roadmap. Even if this works, this query will not be efficient as compared to querying of Iceberg. Are there plans to move data to a lake in Iceberg format?

6

@balaji.ramaswamy As of now, there are no specific plans. We expect Dremio to provide a semantic interface for BI tools to interact with different datasets in the Singestore warehouse. It would be greatly appreciated if you could provide a workaround for this.

Thanks,
Ajay Babu Maguluri,

7

Hi Team,

Kindly help, It’s a showstopper to me. What changes required in ARP framework.

Thanks,
Ajay Babu Maguluri.

8

Hi Team,

Kindly update?

Thanks
Ajay Babu Maguluri.

9

@AjayBabuM Again, As explained earlier, Singlestore is currently not a supported source.

10

Hi @balaji.ramaswamy

I understand. I hope to use ARP to create custom relational connectors. However, I observed similar behavior in MySQL as well. Why is the date argument cast as…?

image
image1675×420 78.6 KB

Can we avoid cast it from ARP? please find attached query profile.

5a273686-a1fe-4641-a0e8-ce3b565d7bae.zip (15.0 KB)

Thanks,
Ajay Babu Maguluri.

11

@AjayBabuM Since the filter is just a string and actual data type is date it will use CAST

Can you try one of the 2 and see if that removes cast

  • Try using it as external query
  • in the where clause do something like event_date = {d ‘2024-01-01’}