Inconsistent behavior when use `.Any()` as an subquery in `Where(...)`, with/without an `Take()` or `Select(_=>1)`

[yyjdelete]

Describe what is not working as expected.

Some combilne lead to invalid sql or InvalidOperationException or ClientEvaluation.

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.0.0-preview.19074.3" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="2.2.0" />
  </ItemGroup>

</Project>

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;

namespace ConsoleApp15
{
    internal static class Program
    {
        public static List<T> ToListCatched<T>(this IQueryable<T> query, ILogger logger)
        {
            try
            {
                return query.ToList();
            }
            catch (Exception ex)
            {
                logger?.LogError(ex.ToString());
                return null;
            }
        }

        private static void Main(string[] args)
        {
            using (var ctx = new TestContext())
            {

                var needInit = false;
                try
                {
                    needInit = !(ctx.Test001.Any() && ctx.Test002.Select(dx => true).FirstOrDefault());
                }
                catch
                {
                    needInit = true;
                }
                if (needInit)
                {
                    #region init data
                    ctx.Database.EnsureDeleted();
                    ctx.Database.EnsureCreated();

                    ctx.Test001.Add(new Test001() { GroupByKey = 1, OrderByKey = 1 });
                    ctx.Test001.Add(new Test001() { GroupByKey = 1, OrderByKey = 2 });
                    ctx.Test001.Add(new Test001() { GroupByKey = 1, OrderByKey = 1 });
                    ctx.Test001.Add(new Test001() { GroupByKey = 2, OrderByKey = 2 });
                    ctx.Test001.Add(new Test001() { GroupByKey = 2, OrderByKey = 1 });
                    ctx.Test001.Add(new Test001() { GroupByKey = 2, OrderByKey = 2 });

                    ctx.Test002.Add(new Test002() { PK1 = 1, PK2 = 1, GroupByKey = 1, OrderByKey = 1 });
                    ctx.Test002.Add(new Test002() { PK1 = 1, PK2 = 2, GroupByKey = 2, OrderByKey = 2 });
                    ctx.Test002.Add(new Test002() { PK1 = 1, PK2 = 3, GroupByKey = 1, OrderByKey = 1 });
                    ctx.Test002.Add(new Test002() { PK1 = 2, PK2 = 1, GroupByKey = 1, OrderByKey = 2 });
                    ctx.Test002.Add(new Test002() { PK1 = 2, PK2 = 2, GroupByKey = 2, OrderByKey = 1 });
                    ctx.Test002.Add(new Test002() { PK1 = 2, PK2 = 3, GroupByKey = 2, OrderByKey = 2 });
                    ctx.Test002.Add(new Test002() { PK1 = 3, PK2 = 1, GroupByKey = 2, OrderByKey = 2 });
                    ctx.Test002.Add(new Test002() { PK1 = 3, PK2 = 2, GroupByKey = 1, OrderByKey = 1 });
                    ctx.Test002.Add(new Test002() { PK1 = 3, PK2 = 3, GroupByKey = 2, OrderByKey = 2 });
                    ctx.SaveChanges();
                    #endregion
                }
                //use after first query
                var loggerFactory = ctx.loggerFactory;
                var logger = loggerFactory.CreateLogger("Main");


                logger.LogInformation("-------------: begin");
                logger.LogInformation("-------------: test .Take(2).Where(...).Select(_=>1).Any()");
                Thread.Sleep(100);//Flush

                //ClientEvaluation
                logger.LogInformation("case_Any_Take1_Sel0: ");
                var q1 = ctx.Test001.Where(d1 => ctx.Test001
                    .Select(d2 => new { PK = d2.PK })
                    .OrderBy(d2 => d2.PK)
                    .Take(2)
                    //d1.PK
                    .Where(d2 => d2.PK == 1)
                    //.Select(d2 => 1)
                    .Any())
                     .ToList();
                Thread.Sleep(100);//Flush

                //good
                logger.LogInformation("case_Any_Take1_Sel1: ");
                var q2 = ctx.Test001.Where(d1 => ctx.Test001
                    .Select(d2 => new { PK = d2.PK })
                    .OrderBy(d2 => d2.PK)
                    .Take(2)
                    //d1.PK
                    .Where(d2 => d2.PK == 1)
                    .Select(d2 => 1)
                    .Any())
                     .ToList();
                Thread.Sleep(100);//Flush

                //good
                logger.LogInformation("case_Any_Take0_Sel0: ");
                var q3 = ctx.Test001.Where(d1 => ctx.Test001
                    .Select(d2 => new { PK = d2.PK })
                    .OrderBy(d2 => d2.PK)
                    //.Take(2)
                    .Where(d2 => d2.PK == 1)
                    //.Select(d2 => 1)
                    .Any())
                     .ToList();
                Thread.Sleep(100);//Flush

                //broken since 2.1.0 with `d2.PK == 1`, not happen when change to `d2.PK == 1` or `d2.PK == d1.PK`
                /*
                SELECT [d1].[PK], [d1].[GroupByKey], [d1].[OrderByKey]
                FROM [Test001] AS [d1]
                WHERE [d2].[PK] IN (
                    SELECT 1
                    FROM [Test001] AS [d2]
                )
                */
                logger.LogInformation("case_Any_Take0_Sel1: ");
                List<Test001> q4 = ctx.Test001.Where(d1 => ctx.Test001
                       .Select(d2 => new { PK = d2.PK })
                       .OrderBy(d2 => d2.PK)
                       //.Take(2)
                       .Where(d2 => d2.PK == 1)
                       .Select(d2 => 1)
                       .Any())
                       .ToListCatched(logger);
                Thread.Sleep(100);//Flush

                logger.LogInformation("-------------: test for .Take(2).Where(...).Select(_=>true).FirstOrDefault() with Test001");
                Thread.Sleep(100);//Flush

                logger.LogInformation("take0_select_true_first_key1: ");
                //good
                var f1 = ctx.Test001.Where(outer => ctx.Test001
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK })
                                .Where(inner => inner.PK == outer.PK)
                                .Select(inner => true)
                                .FirstOrDefault()
                                )
                                .ToList();
                Thread.Sleep(100);//Flush

                logger.LogInformation("take1_select_true_first_key1: ");
                //System.InvalidOperationException: No coercion operator is defined between types 'System.Int32' and 'System.Nullable`1[System.Boolean]'
                var f2 = ctx.Test001.Where(outer => ctx.Test001
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK })
                                .Take(2)
                                .Where(inner => inner.PK == outer.PK)
                                .Select(inner => true)
                                .FirstOrDefault()
                                )
                                .ToListCatched(logger);
                Thread.Sleep(100);//Flush

                logger.LogInformation("-------------: test for .Take(2).Where(...).Select(_=>true).FirstOrDefault() with Test002");
                Thread.Sleep(100);//Flush

                logger.LogInformation("take0_select_true_first_key2: ");
                var f3 = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })
                                //.Take(2)
                                .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                                .Select(inner => true)
                                .FirstOrDefault()
                                )
                                .ToList();
                Thread.Sleep(100);//Flush

                logger.LogInformation("take1_select_true_first_key2: ");
                //bad sql
                /*
                SELECT [outer].[PK1], [outer].[PK2], [outer].[GroupByKey], [outer].[OrderByKey]
                FROM [Test002] AS [outer]
                WHERE COALESCE((
                    SELECT TOP(1) [t].[PK1], [t].[PK2]
                    FROM (
                        SELECT TOP(2) [inner].[PK1], [inner].[PK2]
                        FROM [Test002] AS [inner]
                        WHERE [inner].[GroupByKey] = [outer].[GroupByKey]
                        ORDER BY [inner].[OrderByKey]
                    ) AS [t]
                    WHERE ([t].[PK1] = [outer].[PK1]) AND ([t].[PK2] = [outer].[PK2])
                ), 0) = 1
                 */
                var f4 = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })
                                .Take(2)
                                .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                                .Select(inner => true)
                                .FirstOrDefault()
                                )
                                .ToListCatched(logger);
                Thread.Sleep(100);//Flush

                logger.LogInformation("-------------: end");
                Thread.Sleep(100);//Flush
                loggerFactory.Dispose();
                return;
            }
        }
    }

    #region DbContext

    public partial class TestContext : DbContext
    {
        public LoggerFactory loggerFactory;
        public TestContext()
        {
        }

        public TestContext(DbContextOptions<TestContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Test001> Test001 { get; set; }

        public virtual DbSet<Test002> Test002 { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=Test_Database001;Trusted_Connection=True;");
                //optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.QueryClientEvaluationWarning));
            }
            loggerFactory = new LoggerFactory();
#pragma warning disable CS0618
            loggerFactory.AddConsole(LogLevel.Information);
#pragma warning restore CS0618
            optionsBuilder.UseLoggerFactory(loggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Test001>(entity =>
            {
                entity.HasKey(d => d.PK);
            });

            modelBuilder.Entity<Test002>(entity =>
            {
                entity.HasKey(d => new { d.PK1, d.PK2 });
            });
        }
    }

    public partial class Test002
    {
        public int PK1 { get; set; }

        public int PK2 { get; set; }

        public int GroupByKey { get; set; }

        public int OrderByKey { get; set; }
    }

    public partial class Test001
    {

        public int PK { get; set; }

        public int GroupByKey { get; set; }

        public int OrderByKey { get; set; }
    }

    #endregion DbContext
}

Further technical details

EF Core version: up to 3.0.0-preview.19074.3(case_Any_Take0_Sel1 used to work in 2.0.x, and the other 3 (take1_select_true_first_key1/take1_select_true_first_key2/case_Any_Take1_Sel0) always not work since 2.0.0)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: not affected
IDE: not affected

[maumar]

currently: case_Any_Take1_Sel0, case_Any_Take1_Sel1, case_Any_Take0_Sel0, case_Any_Take0_Sel1, take0_select_true_first_key1 work

take1_select_true_first_key1 fails - producing invalid sql:

SELECT [t].[PK], [t].[GroupByKey], [t].[OrderByKey]
FROM [Test001] AS [t]
WHERE (
    SELECT TOP(1) CAST(1 AS bit)
    FROM (
        SELECT TOP(2) [t0].[PK], [t0].[OrderByKey]
        FROM [Test001] AS [t0]
        WHERE [t0].[GroupByKey] = [t].[GroupByKey]
        ORDER BY [t0].[OrderByKey]
    ) AS [t1]
    WHERE [t1].[PK] = [t].[PK]
    ORDER BY [t1].[OrderByKey])

take0_select_true_first_key2 fails, invalid sql:

SELECT [t].[PK1], [t].[PK2], [t].[GroupByKey], [t].[OrderByKey]
FROM [Test002] AS [t]
WHERE (
    SELECT TOP(1) CAST(1 AS bit)
    FROM [Test002] AS [t0]
    WHERE ([t0].[GroupByKey] = [t].[GroupByKey]) AND (([t0].[PK1] = [t].[PK1]) AND ([t0].[PK2] = [t].[PK2]))
    ORDER BY [t0].[OrderByKey])

take1_select_true_first_key2 fails, invalid sql:

SELECT [t].[PK1], [t].[PK2], [t].[GroupByKey], [t].[OrderByKey]
FROM [Test002] AS [t]
WHERE (
    SELECT TOP(1) CAST(1 AS bit)
    FROM (
        SELECT TOP(2) [t0].[PK1], [t0].[PK2], [t0].[OrderByKey]
        FROM [Test002] AS [t0]
        WHERE [t0].[GroupByKey] = [t].[GroupByKey]
        ORDER BY [t0].[OrderByKey]
    ) AS [t1]
    WHERE ([t1].[PK1] = [t].[PK1]) AND ([t1].[PK2] = [t].[PK2])
    ORDER BY [t1].[OrderByKey])

error:

An expression of non-boolean type specified in a context where a condition is expected, near ')'.