Querying for empty list fails with MySQL Server 5.6

[bgrainger]

MySQL Server 5.6.29 Dapper 1.50.1 MySql.Data 6.9.9

Execute the following code (query adapted from https://github.com/StackExchange/dapper-dot-net#list-support):

var csb = new MySqlConnectionStringBuilder { Server = "localhost", UserID = "root", Password = "..." };
using (var connection = new MySqlConnection(csb.ConnectionString))
{
    connection.Open();
    connection.Query<int>("select * from (select 1 as Id) as X where Id in @Ids", new { Ids = new int[0] }).ToList();
}

It throws a MySqlException: "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 'WHERE 1 = 0)' at line 1".

For this statement, Dapper sets the CommandText to select * from (select 1 as Id) as X where Id in (SELECT @Ids WHERE 1 = 0)

The MySql.Data connector interpolates the command parameters and rewrites this as select * from (select 1 as Id) as X where Id in (SELECT NULL WHERE 1 = 0) before sending it to MySQL Server.

MySQL Server 5.6 rejects this as invalid SQL syntax.

Upgrading to MySQL Server 5.7.11 fixes the exception, but may not always be practical. ~For MySQL Server 5.6, the following query would be valid (and selects 0 rows): select * from (select 1 as Id) as X where Id in (SELECT NULL).~ EDIT: deleted this suggested workaround because—although it selects zero rows—the inverse query WHERE Id NOT IN (SELECT NULL) also selects zero rows when the intent is to select all rows.

[bgrainger]

Another workaround is to check .Any() on the list parameter in the caller before executing the query (if having 0 items in the list would trivially return 0 rows from the query).

[mgravell]

@bgrainger that would be a perfectly sensible thing to do for the calling app-code to do; not really very easy for dapper to do, because it would have to parse and understand the intent - it isn't always the case that an empty input means an empty output

[bgrainger]

Yes, that's only a workaround for the caller (not Dapper) if they can infer that "0 items in list" == "0 rows returned". For a NOT IN @ids query, it wouldn't be applicable, for example.

[bgrainger]

Another workaround that works no matter the structure of the SQL you're trying to execute.

To make the example concrete, imagine a table of messages between users with columns FromId, ToId, Subject, MessageText where FromId and ToId are FKs to your Users table.

To find messages for a set of users, you want to execute the following SQL, but it could fail (if senders or recipients is empty; for simplicity, assuming non-null):

conn.Query("select * from Messages where FromId in @senders OR ToId in @recipients", new { senders, recipients });

Refactor it to:

var sendersSql = senders.Any() ? "@senders" : "(select null)";
var recipientsSql = recipients.Any() ? "@recipients" : "(select null)";
conn.Query($"select * from Messages where FromId in {sendersSql} OR ToId in {recipientsSql}", new { senders, recipients });

[iChaosren]

I'm having the same issue.

It works on my local machine just fine with an MySQL Server of Version: 5.7.14.

But when I publish my code to the testing server with MySQL Version: 5.5.32-0ubuntu0.12.04.1-log It fails and gives me the above syntax error. The work around above works, but I feel some investigation needs to be done to see why this is happening.

[xPaw]

I think this is the same issue I'm having. I am not entirely sure what causes this, but I've had my app crash a couple of times on this line: https://github.com/SteamDatabase/SteamDatabaseBackend/blob/846d9464e7023518919be995e522a802847f54c7/IRC/RSS.cs#L67

It produces this exception:

Exception: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE 1 = 0)' at line 1 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE 1 = 0)' at line 1

   at MySql.Data.Serialization.PayloadData.ThrowIfError()
   at MySql.Data.Serialization.MySqlSession.TryAsyncContinuation(Task`1 task)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySql.Data.MySqlClient.Results.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet)
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__65.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__64.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<QueryAsync>d__31`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 389
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at SteamDatabaseBackend.RSS.<ProcessFeed>d__7.MoveNext() in /home/steamdb/deploy/IRC/RSS.cs:line 67
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at SteamDatabaseBackend.RSS.<Tick>d__6.MoveNext() in /home/steamdb/deploy/IRC/RSS.cs:line 53
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadPoolWorkQueue.Dispatch()

I've found the same issue on Stack Overflow which didn't really get answered: https://stackoverflow.com/questions/11996639/c-sharp-dapper-query-using-where-in

[bgrainger]

@xPaw In your case, you should be able to work around this by changing https://github.com/SteamDatabase/SteamDatabaseBackend/blob/846d9464e7023518919be995e522a802847f54c7/IRC/RSS.cs#L60 to:

f (rssItems == null || rssItems.Count == 0)
{
    return;
}

The problem is passing an empty collection (as the Ids parameter) to QueryAsync on line 67.

[xPaw]

@bgrainger Yeah that's what I ended up doing, but is this still a valid issue on Dapper's side?

[bgrainger]

Yes, Dapper still creates queries that are incompatible with MySQL Server 5.6 (and earlier) and equivalent versions of MariaDB. (Since Dapper doesn't know which database it's being used with, and has to generate the same subquery for all DB servers, it's possible that fixing this could break a different database; I haven't looked into it.)

[bladeoflight16]

I believe this also applies to Oracle.

[Sarapas]

When making IN queries with empty list - Dapper makes something like this:

SELECT * FROM information_schema.tables WHERE VERSION IN (SELECT 1 WHERE 0 = 1);

Although SELECT 1 WHERE 0 = 1 was incorrect query on it's own due to missing FROM clause - it was allowed as a subquery before It is not allowed as a subquery anymore in new MySQL versions and it is the reason why it fails

[mgravell]

Well damn; any suggested workarounds?

On 5 Mar 2018 6:35 am, "Sarapas" [email protected] wrote:

When making IN queries with empty list - Dapper makes something like this:

SELECT * FROM information_schema.tables WHERE VERSION IN (SELECT 1 WHERE 0 = 1);

Although SELECT 1 WHERE 0 = 1 was incorrect query on it's own due to missing FROM clause - it was allowed as a subquery before It is not allowed as a subquery anymore in new MySQL versions and it is the reason why it fails

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/StackExchange/Dapper/issues/565#issuecomment-370438145, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsD3c6VRRuGiZ1k2ruZIw0AbVIHFyks5tbU1LgaJpZM4JRNAH .

[bgrainger]

SELECT * FROM information_schema.tables WHERE VERSION IN (SELECT 1 WHERE 0 = 1); It is not allowed as a subquery anymore in new MySQL versions and it is the reason why it fails

This query works fine for me in MySQL 8.0.4 RC. @Sarapas which version are you using where it is no longer working?

[bgrainger]

Here's a subquery that does work on both MySQL Server 5.6.39 and 8.0.4-rc: (SELECT 1 FROM (SELECT 1) a WHERE 0 = 1);, e.g.,

SELECT * FROM information_schema.tables WHERE VERSION IN (SELECT 1 FROM (SELECT 1) a WHERE 0 = 1);

(Of course, I haven't tested it on MSSQL, Oracle, Postgres, etc.)

[Sarapas]

@bgrainger I tested it on 5.6.39. I tried the same thing on 5.7.20 and it was working so it seems it was changed

[bgrainger]

OK, that's exactly what my original bug report said: Dapper's generated query fails in 5.6.x but works on 5.7.x. When you wrote "It is not allowed as a subquery anymore in new MySQL versions" above, I was confused, because it does work on 5.7 and later; perhaps you just meant new minor versions of 5.6.x.

[daikoz]

Same issue with MariaDB on debian 10: 10.3.17-MariaDB-0+deb10u1-log Debian 10

No error with debian 9: Maraidb: 10.1.41-0+deb9u1

[jehrenzweig-leagueapps]

Still running into this issue w/ MySQL 5.6; I have to conditionally add the WHERE IN ... clause to my SQL query before handing it to Dapper.