INVALID SQL GENERATION

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
vlad.tryhub
Posts: 3
Joined: Thu 12 Jul 2018 16:35

INVALID SQL GENERATION

Post by vlad.tryhub » Fri 30 Nov 2018 18:37

Hi. As licensed users we are experiencing problems using dotConnect for MySQL v8.12.1278
Case 1:

Code: Select all

db.Set<RecordOne>()
.Select(x => x.RecordManyList.Select(y => y.str).FirstOrDefault() ?? "7")
.ToListAsync()

Code: Select all

SELECT COALESCE(SELECT y.string
FROM record_many y
WHERE x.id = y.record_one_id
LIMIT 1, '7')
FROM record_one x

Code: Select all

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 'SELECT y.string .....
Case 2:

Code: Select all

db.Set<RecordOne>()
.Select(x => new { x.Id, someField = x.RecordManyList.Any() })
.ToListAsync()

Code: Select all

      SELECT x.id, SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM record_many r
              WHERE x.id = r.record_one_id)
          THEN 1 ELSE 0
      END AS someField
      FROM record_one x

Code: Select all

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 'SELECT CASE .....

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

Re: INVALID SQL GENERATION

Post by Shalex » Wed 05 Dec 2018 18:44

Thank you for your report. We have reproduced both issues and are investigating them. We will notify you about the result.

Toshik
Posts: 5
Joined: Thu 19 Jul 2018 15:48

Re: INVALID SQL GENERATION

Post by Toshik » Fri 04 Jan 2019 16:12

Any idea when it will be fixed?
We were unable to use any driver release newer than 8.12.1202 due to different bugs introduced in each new release.

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

Re: INVALID SQL GENERATION

Post by Shalex » Mon 07 Jan 2019 12:35

The bug with generating a subselect within the SELECT list in EF Core 2 is fixed. You can download the internal build with the fix from https://www.devart.com/pub/nuget_mysql_8_12_1303.zip.

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

Re: INVALID SQL GENERATION

Post by Shalex » Thu 10 Jan 2019 17:44

New build of dotConnect for MySQL 8.12.1307 is available for download now: viewtopic.php?f=2&t=38261.

Post Reply