Skip occured exception After OrderBy... Case #1

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
ChangHyeon Lee
Posts: 11
Joined: Wed 14 Jun 2017 05:46

Skip occured exception After OrderBy... Case #1

Post by ChangHyeon Lee » Mon 25 Jun 2018 04:44

Hi...

I am currently applying 8.11.1172 to my project. (EF core 2.1.1)
It is applied to the actual product, so it is urgent to correct it. :(

But, if I call Skip () after OrderBy (), an error occurs.

Here is sample
==============================================
List< AnalysisRequest> test = await m_dbContext.AnalysisRequests.AsNoTracking()
.OrderBy(i => i.RegDate)
.Skip(1)
.ToListAsync();

Here is query generated by ef
==============================================
SELECT t.*
FROM (
SELECT t0.*
FROM (
SELECT i.REQUEST_ID, i.DEVICE_ID, i.REG_DATE, i.REG_ID, i.REG_NAME, i.SEARCH_END, i.SEARCH_START, i.STATUS, i.VIRTUAL_DEVICE_ID, i.REG_DATE AS REG_DATE0, i.REG_DATE AS REG_DATE00
FROM ANALYSIS_REQUESTS i
ORDER BY c
LIMIT 2147483647 OFFSET :p__p_0
) AS t0
ORDER BY t0.REG_DATE00
LIMIT 2147483647 OFFSET :p__p_0
) AS t
ORDER BY t.REG_DATE0
LIMIT :p__p_1 OFFSET :p__p_0

Here is stack traces..
==============================================

Microsoft.EntityFrameworkCore.Database.Command: [2018-06-25 13:39:49][ERROR][Microsoft.EntityFrameworkCore.Database.Command] - Failed executing DbCommand (4ms) [Parameters=[p__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT t.*
FROM (
SELECT t0.*
FROM (
SELECT i.REQUEST_ID, i.DEVICE_ID, i.REG_DATE, i.REG_ID, i.REG_NAME, i.SEARCH_END, i.SEARCH_START, i.STATUS, i.VIRTUAL_DEVICE_ID, i.REG_DATE AS REG_DATE0, i.REG_DATE AS REG_DATE00
FROM ANALYSIS_REQUESTS i
ORDER BY c
LIMIT 2147483647 OFFSET :p__p_0
) AS t0
ORDER BY t0.REG_DATE00
LIMIT 2147483647 OFFSET :p__p_0
) AS t
ORDER BY t.REG_DATE0
LIMIT 2147483647 OFFSET :p__p_0
예외 발생: 'Devart.Data.MySql.MySqlException'(System.Private.CoreLib.dll)
NNException: [2018-06-25 13:39:58][ERROR][NNException] - Unknown column 'c' in 'order clause'
Last edited by ChangHyeon Lee on Mon 25 Jun 2018 05:42, edited 1 time in total.

ChangHyeon Lee
Posts: 11
Joined: Wed 14 Jun 2017 05:46

Re: Skip occured exception After OrderBy... Case #1

Post by ChangHyeon Lee » Mon 25 Jun 2018 04:59

One more thing I worry about.

Looking at the query generated above ...

It seems that there is no need for an internal query.

1. no need internal query #1...I think

SELECT i.REQUEST_ID, i.DEVICE_ID, i.REG_DATE, i.REG_ID, i.REG_NAME, i.SEARCH_END, i.SEARCH_START, i.STATUS, i.VIRTUAL_DEVICE_ID, i.REG_DATE AS REG_DATE0, i.REG_DATE AS REG_DATE00
FROM ANALYSIS_REQUESTS i
ORDER BY c
LIMIT 2147483647 OFFSET :p__p_0

2. no need internal query #2...I think

SELECT t0.*
FROM (
SELECT i.REQUEST_ID, i.DEVICE_ID, i.REG_DATE, i.REG_ID, i.REG_NAME, i.SEARCH_END, i.SEARCH_START, i.STATUS, i.VIRTUAL_DEVICE_ID, i.REG_DATE AS REG_DATE0, i.REG_DATE AS REG_DATE00
FROM ANALYSIS_REQUESTS i
ORDER BY c
LIMIT 2147483647 OFFSET :p__p_0
) AS t0
ORDER BY t0.REG_DATE00
LIMIT 2147483647 OFFSET :p__p_0




In addition, from the inner query to the order by command, it seems to affect performance.

Is this a performance issue?

ChangHyeon Lee
Posts: 11
Joined: Wed 14 Jun 2017 05:46

Re: Skip occured exception After OrderBy... Case #1

Post by ChangHyeon Lee » Wed 27 Jun 2018 13:11

Is there anyone here?

Please let me know when I can receive updates.
Depending on the answer, I can decide whether to wait for an update or roll back the source.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Skip occured exception After OrderBy... Case #1

Post by Shalex » Wed 27 Jun 2018 18:30

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Skip occured exception After OrderBy... Case #1

Post by Shalex » Fri 13 Jul 2018 20:05

The bug with paging in EF Core 2.1 is fixed. The internal build with the fix: https://www.devart.com/pub/nuget_mysql_8_11_1186.zip.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Skip occured exception After OrderBy... Case #1

Post by Shalex » Fri 20 Jul 2018 11:21

dotConnect for MySQL 8.12 is released: viewtopic.php?f=2&t=37477.

Post Reply