Pomelo.EntityFrameworkCore.MySql: Incorrect queries generated by Pomelo after migrating from .net core 2.2 to 3.1

Hello, I have been using this library with my Asp.net core 2.2 on a linux server in the past and everything was working fine. Since today we decided to migrate from 2.2 to 3.1 and so we upgraded the Entityframework and also the Pomeleo package as well. I have seen the queries that were working earlier now has stopped working and as of my knowledge the queries are not being generated correcrtly. Linq Query


var query = (
                    from m in _context.Mail_Accounts
                    where m.access_type == "Shared" || (m.access_type == "Personal" && m.created_by == ti.UserId)
                    select new
                    {
                        id = m.id,
                        email = m.email,
                        unread_count = (from m1 in _context.Mails where m1.is_read == false && m1.incoming == true && m1.mail_account_id == m.id select m1.id).Count(),
                        folder = (from m2 in _context.Mail_Folders where m2.mail_account_id == m.id && m.is_deleted != true select new { m2.name, m2.id }).FirstOrDefault()
                    }
                ).ToList()

Exception:

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 '(
SELECT `m1`.`name`, `m1`.`id`, **1 AS `c`**, `m1`.`mail_account_id`
    FROM' at line 6

I don’t know why this is happening or we have to fall back to the old version.

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 31

Most upvoted comments

@hazhayder I can confirm, that the MySQL version seems to be in order here, because I can reproduce this issue on my end with MySQL 8.0.18.

To make context use the ILoggerFactory instance, you need to modify your ApplicationDbContext constructor, to include a DbContextOptions<ApplicationDbContext> parameters:

public ApplicationDbContext(
    DbContextOptions<ApplicationDbContext> options,
    ClaimsPrincipal c,
    ILoggerFactory factory)
    : base(options)
{
    // ...
}

Then, add some additional options, to get more detailed logging output:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySql(
        GetConnectionString(),
        mySqlOptionsAction => mySqlOptionsAction
            .ServerVersion(
                new Version(8, 0, 18),
                Pomelo.EntityFrameworkCore.MySql.Infrastructure.ServerType.MySql))
        .EnableDetailedErrors()
        .EnableSensitiveDataLogging();
}

With these settings, the following SQL is being logged:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (226ms) [Parameters=[@__ef_filter___tenant_id_1='1', @__ef_filter___tenant_id_3='1', @__ef_filter___tenant_id_2='1', @__ef_filter___tenant_id_0='1'], CommandType='Text', CommandTimeout='30']

SELECT `t`.`id`, `t`.`salutation`, `t`.`first_name`, `t`.`last_name`, `t`.`work_email`, `t`.`work_phone`, `t`.`work_email_type`, `t`.`work_phone_type`, `l`.`id`, `t1`.`id`, `t1`.`id0`, `t1`.`name`, `t1`.`value`, `t1`.`type`, `t1`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN (
    SELECT `c`.`id`, `c`.`company_id`, `c`.`created_by`, `c`.`created_date`, `c`.`first_name`, `c`.`is_company_primary`, `c`.`is_deleted`, `c`.`last_name`, `c`.`modified_by`, `c`.`modified_date`, `c`.`resp_user`, `c`.`salutation`, `c`.`tenant_id`, `c`.`work_email`, `c`.`work_email_type`, `c`.`work_phone`, `c`.`work_phone_type`
    FROM `Contacts` AS `c`
    WHERE (`c`.`tenant_id` = @__ef_filter___tenant_id_1) AND NOT (`c`.`is_deleted`)
) AS `t` ON `l`.`contact_id` = `t`.`id`
LEFT JOIN LATERAL (
    SELECT `t0`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `t0`.`value`, `c0`.`type`, `c0`.`options`
    FROM `Contact_Fields` AS `c0`
    LEFT JOIN (
        SELECT `c1`.`id`, `c1`.`contact_field_id`, `c1`.`contact_id`, `c1`.`created_by`, `c1`.`created_date`, `c1`.`is_deleted`, `c1`.`modified_by`, `c1`.`modified_date`, `c1`.`tenant_id`, `c1`.`value`
        FROM `Contact_Field_Values` AS `c1`
        WHERE (`c1`.`tenant_id` = @__ef_filter___tenant_id_3) AND NOT (`c1`.`is_deleted`)
    ) AS `t0` ON (`c0`.`id` = `t0`.`contact_field_id`) AND (`t`.`id` = `t0`.`contact_id`)
    WHERE (`c0`.`tenant_id` = @__ef_filter___tenant_id_2) AND NOT (`c0`.`is_deleted`)
) AS `t1` ON TRUE
WHERE ((`l`.`tenant_id` = @__ef_filter___tenant_id_0) AND NOT (`l`.`is_deleted`)) AND (`l`.`lead_id` = 1)
ORDER BY `l`.`id`, `t`.`id`, `t1`.`id0`

@mguinness You were right, there are global query filters at work here:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var type in GetEntityTypes())
    {
        var method = SetGlobalQueryMethod.MakeGenericMethod(type);
        method.Invoke(this, new object[] {modelBuilder});
    }

    this.RegisterSP(modelBuilder);
    base.OnModelCreating(modelBuilder);
}

static readonly MethodInfo SetGlobalQueryMethod = typeof(ApplicationDbContext).GetMethods(BindingFlags.Public | BindingFlags.Instance)
    .Single(t => t.IsGenericMethod && t.Name == "SetGlobalQuery");

public void SetGlobalQuery<T>(ModelBuilder builder)
    where T : BaseEntity
{
    builder.Entity<T>()
        .HasKey(e => e.id);
    
    builder.Entity<T>()
        .HasQueryFilter(e => e.tenant_id == _tenant_id && !e.is_deleted);
}

This is also evident by the @__ef_filter___tenant_id_1 parameters shown in the logged query.

The query fails with the following exception:

MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 't.id' in 'on clause'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 't.id' in 'on clause'
    at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49

With the query filter removed, the query is simpler, but still fails:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (238ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
    SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
    FROM `Contact_Fields` AS `c0`
    LEFT JOIN `Contact_Field_Values` AS `c1` ON (`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`)
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`

The exception is basically the same:

MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'c.id' in 'on clause'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'c.id' in 'on clause'
    at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49

Looks like MySQL is unable to access the `c`.`id` field, though I expect it should be able to, due to the use of LATERAL.

I reported this issue a while ago on the MySQL Bugtracker as Outer reference in join condition isn’t allowed. Looks like this has been fixed for MySql 8.0.20.

This did not really work in Pomelo 2.2 either. EF Core 2.2 just gave up to translate the query, because it was to complicated and instead switch to client evaluation mode, meaning it just queried all the rows from the database (using multiple simple SELECT statements) and then executed the actual query in memory using LINQ. So you got a correct result, but a very inefficient one. You can verify this easily by logging the generated queries for EF Core 2.2.

I did check the original query in MySQL 8.0.20 and can confirm that it runs correct there.

We could implement a workaround for MySQL versions 8.0.14 to 8.0.19, where the ON clause is always true, and the actual condition is moved to the WHERE clause. But I am not convinced, that we need to implement this workaround in Pomelo, since anybody affected could just fix this issue by updating MySQL:

SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
    SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
    FROM `Contact_Fields` AS `c0`
    LEFT JOIN `Contact_Field_Values` AS `c1` ON TRUE
    WHERE `c1`.`id` IS NULL OR (`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`)
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`

If you cannot update to MySQL 8.0.20, you can change your query as follows, by manually applying the workaround I showed above:

var contacts = (from lc in _context.Lead_Contacts
    join c in _context.Contacts on lc.contact_id equals c.id
    where lc.lead_id == 1
    select new Lead_Contacts_DTO
    {
        id = c.id,
        salutation = c.salutation,
        first_name = c.first_name,
        last_name = c.last_name,
        work_email = c.work_email,
        work_phone = c.work_phone,
        work_email_type = c.work_email_type,
        work_phone_type = c.work_phone_type,
        unlink = false,
        Fields = (from cf in _context.Contact_Fields
            join cfv in _context.Contact_Field_Values on 1 equals 1 // <-- changed
            into cfv_v
            from cf1 in cfv_v.DefaultIfEmpty()
            where cf1 == null || // <-- added
                  cf.id == cf1.contact_field_id && c.id == cf1.contact_id // <-- added
            select new Lead_Contact_Field_Value
            {
                Contact_Field_Id = cf1.id,
                Field_Id = cf.id,
                Field_Name = cf.name,
                Field_Value = cf1.value,
                Field_Type = cf.type,
                Field_Options = cf.options,
                Field_Value_Arr = new List<string>()
            }).ToList()
    }).ToList();

It generates the following SQL, that should run on MySQL 8.0.18:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (24ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
    SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
    FROM `Contact_Fields` AS `c0`
    LEFT JOIN `Contact_Field_Values` AS `c1` ON 1 = 1
    WHERE `c1`.`id` IS NULL OR ((`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`))
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`

lauxjpn on May 1, 2020

Yeah, probably using a global query filter in which case they should also share their DbContext for completeness.

Good point!

They should also be aware of the many Breaking changes included in EF Core 3.0 if they haven’t already looked at those.

Yes, being aware about the fact, that the query pipeline was fundamentally rewritten between EF Core 2.2.6 and 3.0.0 is good. But since the query does work generally, as I have proven with the example code above, this is not the issue here.

The error appears to show LEFT JOIN LATERAL as the issue, so they should double check server is a MySQL 8.0.18 instance.

That was my first though as well. But he is very sure, that he is running the query against a MySQL 8.0.18 server.

@hazhayder Here is what you should do next:

To make us shut up about the MySQL version you are using, add the following lines to your code base on your production server app just before the failing LINQ query and then run the app. It needs to run in your production environment (as part of the real app that is failing). It will log your MySQL version number that is actually being used in your log. The result should be prove enough for us, that your MySQL version is high enough. The _logger field in the following code contains a constructor injected ILogger<MainController> instance:

using (var command = _context.Database.GetDbConnection().CreateCommand())
{
    _context.Database.OpenConnection();

    command.CommandText = "select @@version";
    var version = (string)command.ExecuteScalar();
    
    _logger.LogInformation($"Actual MySQL Version: {version}");
}

If the MySQL version is 8.0.14+, then post the SQL that got logged by EF Core for your failing LINQ query. As @mguinness suggested, you want to enable parameter logging as well. My example code does this too.
Post the code you use to create your DbContext, meaning the AddDbContext() call as well as the DbContext constructors and the OnConfiguring() method. This will address @mguinness point, that global query filters might be at work here.

lauxjpn on Apr 17, 2020