support CONVERT( ... USING ...)

[nanawel]

Introduction

It seems it's not possible to use the whole range of what MySQL syntax provides for queries. I stumbled upon a limitation with the statement below.

To Reproduce

SELECT CONVERT("a" USING latin1);

Actual behavior

SQLPage found a syntax error on line 5, character 16:
"a" USING latin1 ) ; 

Expected behavior

This query is perfectly valid on MySQL.

I thought that SQLPage was pretty agnostic to the actual content of the query, and that only the results mattered, but it doesn't seem to be the case and while a query can be perfectly valid for the database server, SQLPage might prevent you to use it.

Is there any other limitations of this kind? Is it a limitation of the database adapter which does not yet support some MySQL flavors?

Version information

• Database: MySQL 8
• SQLPage Version: 0.15.2

[lovasoa]

Hello! Indeed, SQLPage has to parse your queries in order to find which parameters to bind and which SQLPage functions to execute. The syntax covered by the parser is very large, and does include database specific dialects. However, it may happen that some non standard SQL fails to parse. When this happens, you should report it here, and we'll add it to the parser.

[lovasoa]

I reported the issue upstream in the parser. In the meantime, you should be able to use the CONVERT(str, encoding) syntax.

[nanawel]

Thanks!

[lovasoa]

I opened a pull request upstream, we should have support for CONVERT in sqlpage soon: https://github.com/sqlparser-rs/sqlparser-rs/pull/1048

[nanawel]

I reported the issue upstream in the parser. In the meantime, you should be able to use the CONVERT(str, encoding) syntax.

Nope, unfortunately this syntax is not valid with MySQL.

error returned from database: 1064 (42000): 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 'latin1) OR cae.xxx <> CONVERT(cae.xxx, latin1) OR cae.yyy <> CON' at line 1

I'll wait for the fix upstream.

[lovasoa]

You are right indeed, I was mistaken. I am not a MySQL expert, but what is the goal of cae.xxx <> CONVERT(cae.xxx, latin1) ? You have a column that is stored as unicode, and you want to filter out the values that have characters that cannot be encoded as latin1 ?

[nanawel]

It's a peculiar case but yes, after a migration we don't want non-latin1 characters in some columns of a table so we're getting rid of them and we want to check regularly that no new occurrences appear.

[lovasoa]

Just merged the upstream change, we have CONVERT( ... USING ...) on the main branch :tada:

You can try it on docker with lovasoa/sqlpage:main.

[nanawel]

Nice! Thanks! I should test it soon.