CTE not working

[billkiddo]

Version 1.3.1, trying to execute a query like this: ";WITH cte0 as ( SELECT 1 as xxx) SELECT 2 as xxx", throws

"Query was empty" at:  _System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__2.MoveNext() in /_/src/MySqlConnector/Core/ResultSet.cs:line 50
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 137
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__97.MoveNext() in /_/src/MySqlConnector/MySqlDataReader.cs:line 445_

[billkiddo]

It works just fine with MySql.Data. In our case is because ";"

[bgrainger]

I get a different error:

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 ';WITH cte0 as ( SELECT 1 as xxx) SELECT 2 as xxx' at line 1
--
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in F:\Code\Projects\MySqlConnector\src\MySqlConnector\Core\ResultSet.cs:line 50
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in F:\Code\Projects\MySqlConnector\src\MySqlConnector\MySqlDataReader.cs:line 135
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in F:\Code\Projects\MySqlConnector\src\MySqlConnector\MySqlDataReader.cs:line 444
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in F:\Code\Projects\MySqlConnector\src\MySqlConnector\Core\CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in F:\Code\Projects\MySqlConnector\src\MySqlConnector\MySqlCommand.cs:line 311
   at MySqlConnector.MySqlCommand.ExecuteReader() in F:\Code\Projects\MySqlConnector\src\MySqlConnector\MySqlCommand.cs:line 106

[bgrainger]

I tested the following queries:

Q1

; select 1;

Q2

/* test */; select 1;

Q3

               ← blank line
; select 1;

Q4

-- test
; select 1;

Q5

-- TODO: write SQL

Query	Workbench	MySql.Data	MySql.Data Prepared	MySqlConnector	MySqlConnector Prepared
1	❌	✔️	❌	❌	✔️
2	✔️	❌	❌	❌	✔️
3	✔️	✔️	❌	❌	✔️
4	✔️	❌	❌	❌	✔️
5	N/A	✔️	❌	✔️	❌

MySql.Data will also fail on queries such as SELECT 1;; SELECT 2;. It's not clear to me how much the MySQL ADO.NET ought to tweak the SQL before sending it to the server, versus just sending it as-is and letting the server parse it.

[bgrainger]

trying to execute a query like this: ";WITH cte0 as ( SELECT 1 as xxx) SELECT 2 as xxx",

Why are you beginning a SQL query with a leading ;?

[billkiddo]

trying to execute a query like this: ";WITH cte0 as ( SELECT 1 as xxx) SELECT 2 as xxx",

Why are you beginning a SQL query with a leading ;?

Not by my will :), it's some legacy generated code that bubbled up during a migration from MySql to SingleStore and spotlighted MySQlConnector as a more performant alternative; with the MySql.Data library the legacy code was working. Thx for the fast PR, I haven't had the time to do it myself.