# Issue with Column Casting in Dremio-SingleStore Connector: SQL Syntax Error When Using Selected Column in WHERE Clause **URL:** \ **Category:** Uncategorized\ **Created:** [August 22, 2024, 4:35pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207 "2024-08-22T16:35:06Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [August 22, 2024, 4:35pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/1 "2024-08-22T16:35:07Z")
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](https://community.dremio.com/uploads/short-url/lKSHXh007VtSuTFXRgB75bjux28.zip) (22.5 KB) ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [August 23, 2024, 4:50am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/2 "2024-08-23T04:50:25Z")
Hi Team, Any update on above issue? ---
**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\ **Post date:** [August 24, 2024, 6:36pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/3 "2024-08-24T18:36:08Z")
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. ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [August 27, 2024, 6:31pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/4 "2024-08-27T18:31:07Z")
> [@Benny\_Chow](#): > > phase in planning adds the CAST 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. ---
**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\ **Post date:** [August 27, 2024, 6:59pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/5 "2024-08-27T18:59:24Z")
Can you share the verbose profile? ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [August 28, 2024, 4:28am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/6 "2024-08-28T04:28:55Z")
Hi Please find the profile query plan attached below [5d5f5bac-63c0-4e73-88a9-09fd4c5df612.zip](https://community.dremio.com/uploads/short-url/6WK13XzE5clUsnHNaQLr0RKjZ6U.zip) (21.3 KB) ---
**Author:** ![Benny\_Chow](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/benny_chow/32/5527_2.png) [@Benny\_Chow](https://community.dremio.com/u/Benny_Chow)\ **Post date:** [August 28, 2024, 4:59pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/7 "2024-08-28T16:59:43Z")
> **[CAST or CONVERT · SingleStore Documentation](https://docs.singlestore.com/cloud/reference/sql-reference/conditional-functions/cast-or-convert/)** > > SingleStore is a modern relational database for cloud and on-premises that delivers immediate insights for modern applications and analytical systems. Book a demo or trial today! Looks like their CAST function doesn’t support BIGINT. Maybe there’s something you can configure in the ARP connector to avoid this… ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [August 30, 2024, 7:42pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/8 "2024-08-30T19:42:51Z")
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](https://community.dremio.com/uploads/short-url/uDoXkltgxagEYb4TJcLgnOnkIq9.zip) (2.9 KB) ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 2, 2024, 5:40am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/9 "2024-09-02T05:40:58Z")
Hi team Any update on this ? ---
**Author:** ![Simon\_Pannek](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/simon_pannek/32/5910_2.png) [@Simon\_Pannek](https://community.dremio.com/u/Simon_Pannek)\ **Post date:** [September 2, 2024, 10:07am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/10 "2024-09-02T10:07:07Z")
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](https://github.com/memsql/dremio-singlestore/tree/master). Did you run into these errors before or after you made changes to the arp file? ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 3, 2024, 7:26am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/11 "2024-09-03T07:26:24Z")
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. ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 5, 2024, 4:39am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/12 "2024-09-05T04:39:09Z")
Hi team, Any update on this ? Thanks in Advance ---
**Author:** ![Simon\_Pannek](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/simon_pannek/32/5910_2.png) [@Simon\_Pannek](https://community.dremio.com/u/Simon_Pannek)\ **Post date:** [September 5, 2024, 10:05am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/13 "2024-09-05T10:05:10Z")
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](https://docs.singlestore.com/cloud/reference/sql-reference/conditional-functions/cast-or-convert/) and only include parameters in the ARP that are supported by SingleStore. ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 10, 2024, 2:38pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/14 "2024-09-10T14:38:01Z")
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? ---
**Author:** ![Simon\_Pannek](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/simon_pannek/32/5910_2.png) [@Simon\_Pannek](https://community.dremio.com/u/Simon_Pannek)\ **Post date:** [September 10, 2024, 3:04pm UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/15 "2024-09-10T15:04:28Z")
```auto - 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 ```auto - 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). ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 11, 2024, 4:14am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/16 "2024-09-11T04:14:04Z")
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. ---
**Author:** ![Simon\_Pannek](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/simon_pannek/32/5910_2.png) [@Simon\_Pannek](https://community.dremio.com/u/Simon_Pannek)\ **Post date:** [September 11, 2024, 10:30am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/17 "2024-09-11T10:30:02Z")
In that case, your best bet is probably to do the explicit function rewrites like I suggested in my previous message. ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 16, 2024, 7:44am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/18 "2024-09-16T07:44:16Z")
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? ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 17, 2024, 11:46am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/19 "2024-09-17T11:46:41Z")
Hi team, Any update on this please? Thanks in Advance ---
**Author:** ![JoiceJacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joicejacob/32/5715_2.png) [@JoiceJacob](https://community.dremio.com/u/JoiceJacob)\ **Post date:** [September 20, 2024, 4:50am UTC](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207/20 "2024-09-20T04:50:08Z")
Hi @Simon_Pannek Could you please assist me with the scenario mentioned above? Thanks in Advance [Next page](https://community.dremio.com/t/issue-with-column-casting-in-dremio-singlestore-connector-sql-syntax-error-when-using-selected-column-in-where-clause/12207.md?page=2)