EF Core: Wrong SQL generated for InExpression for large Enumeration

[elvince]

Hi,

I have a large contains statement (1200 items). The generated SQL created use multiple OR to split the number of items in multiple bucket with less than 1000 items (oracle "IN" constraints).

The issue is that the OR statements are not included in 'parenthesis'. So as a consequence as soon as I had another criteria in the where statement, the results are wrong.

Expected Result:

Select *
from entities
where **(** id in ( ...) or id in (...) **)** and anothercolumn in (...);

Current Result:

Select *
from entities
where id in ( ...) or id in (...) and anothercolumn in (...);

If first case the statement is (A or B ) AND C In the second case it is A OR B AND C, in this case, Oracle is doing A OR ( B AND C). which is wrong.

thanks,

[elvince]

Here is the repro code :

namespace TestOracle
{
    class Program
    {
        //Demonstrates how to get started using Oracle Entity Framework Core 6 
        //Code connects to on-premises Oracle DB or walletless Oracle Autonomous DB

        public class BloggingContext : DbContext
        {
            public DbSet<Blog>? Blogs { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseOracle(@"User Id=xxx;Password=xxx;Data Source=DEV");
                optionsBuilder
                    .EnableSensitiveDataLogging()
                    .LogTo(Console.WriteLine);

            }
        }

        public class Blog
        {
            public int BlogId { get; set; }
            public int Check { get; set; }
        }

        static void Main(string[] args)
        {

            using (var db = new BloggingContext())
            {
                if (db.Blogs.Count(o => o.BlogId == 1) == 0)
                {
                    var blog = new Blog { BlogId = 1, Check = 1 };
                    db.Blogs!.Add(blog);
                }
                if (db.Blogs.Count(o => o.BlogId == 2) == 0)
                {
                    var blog2 = new Blog { BlogId = 2, Check = 2 };
                    db.Blogs!.Add(blog2);
                }

                db.SaveChanges();
            }

            var options = new List<int>() { 1, 5, 6 };

            // The next ouput should always be 1 as only blog ID 1 and Check = true should match.

            using (var db = new BloggingContext())
            {

                var longIdList = new List<int>();
                longIdList.Add(2);
                longIdList.Add(1);
                longIdList.AddRange(Enumerable.Range(1, 1500).Select(x => x * x));
                longIdList.Add(2);
                longIdList.Add(1);

                var blogsQuery = db.Blogs.Where(b => longIdList.Contains(b.BlogId));
                blogsQuery = blogsQuery.Where(b => options.Contains(b.Check));

                Console.WriteLine(blogsQuery.ToList().Count);
            }

            using (var db = new BloggingContext())
            {

                var longIdList = new List<int>();
                longIdList.Add(2);
                longIdList.Add(1);
                longIdList.AddRange(Enumerable.Range(1, 500).Select(x => x * x));
                longIdList.Add(2);
                longIdList.Add(1);

                var blogsQuery = db.Blogs.Where(b => longIdList.Contains(b.BlogId));
                blogsQuery = blogsQuery.Where(b => options.Contains(b.Check));

                Console.WriteLine(blogsQuery.ToList().Count);
            }
            Console.ReadLine();
        }
    }
}

it happens only when using contains clauses

[alexkeh]

What is the SQL that gets generated by a MS authored provider? The SQL Oracle provider is as you mentioned:

SELECT "b"."BlogId", "b"."Check" FROM "Blogs" "b" WHERE "b"."BlogId" IN (2, 1, 1,...) OR "b"."BlogId" IN (998001, ...) AND "b"."Check" IN (1, 5, 6)

[elvince]

I don't have SQL server available to make a test but I'm not sure that SQL Server have the 1000 items limitations of the IN statement. More if the second condition is another type of check like a bool value or string comparison, the parenthesis are properly set.

This is why I raised that as a bug, I think there is a edge case that cause the parenthesis to not being set properly.

Thanks,

[alexkeh]

Ok, I understand better about what you are showing in the sample code. I filed a bug (35474720) for the dev team to review and fix the issue. Thanks for reporting @elvince!

[elvince]

Thanks for the report.

I hope you will be able to provide a fix faster than #276 which was the same kind of bug. It's a show stopper for our application to move in production as we have no way to solve that our of reviewing all request 1 by 1 to be sure they are not impacted by this bug.

thanks,

[alexkeh]

If you need a fix faster when a bug has a big impact, I recommend opening a service request with Oracle Support to request a one-off fix. Typically, ODP.NET releases patches on a quarterly basis. Fixes have to be merged at an early enough date to make the quarterly patch merge cutoff. The delay from when a fix is done to when a customer can access it in a quarterly patch can be in the range of 1-4 months.

[elvince]

@alexkeh Thanks for the feedback.

I already set a SR. You anwsered and confirm the issue in 2 days, while SR is stucked waiting for Oracle feedback since days.

Sadly not the first time.. we spend hours to put repro code & explanation and it just fall into deep space without any feedback. The worst paid support I ever worked with.

Honestly 1 to 4 months to get a bug fix is pretty hard to hear those days. Big compagnies release new versions at quick pace, why Oracle is so slow to update their tools.?

Thanks

[alexkeh]

@elvince I'm sorry to hear about that experience. It looks like the Oracle .NET dev team was able to identify the bug root cause and fix it pretty quickly. Right now, the fix is scheduled to be delivered with ODP.NET EF Core 21.11. My expectation for when the 21.11 release will be delivered on NuGet is sometime in July/August.

If you need this fix sooner because it affects a production issue or blocks critical project delivery dates, request a one-off via the SR your organization filed. One-off patches can be delivered much more quickly. If you want to share your SR number, I can follow up as well.

[elvince]

@alexkeh Thanks again for the quick feedback. We are glad that the fix has been made.

FYI, the SR is : 3-33296329351. If you can help us to move forward on this.

Thanks,

[alexkeh]

@elvince I see that your SR is at severity level 3, which is typically not the level for problems blocking production. The support analyst may not have known earlier that your issue affects production. You can request your SR be bumped to a Severity 2 for faster responses.

In terms of next steps, request a one-off patch in your SR. Oracle Support can then start the process to get one created. I will let Oracle Support know that the one-off request will be coming.

[poke]

@alexkeh Hey, I just found that I am hitting this issue as well.

Do you happen to have any update on when a new version will be released? I am currently contemplating rewriting quite a lot of code to work around this issue, or waiting a bit longer until it get fixed upstream.

Also, do you know if a fix will also come to the 6.x version of Oracle.EntityFrameworkCore? I’m currently still on the LTS version of EF Core.

[alexkeh]

The fix will be for both Oracle EF Core 7 and 6.