Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Join returns more columns then it should #13550

Closed
MaklaCof opened this issue Oct 9, 2018 · 0 comments · Fixed by #22897
Closed

Join returns more columns then it should #13550

MaklaCof opened this issue Oct 9, 2018 · 0 comments · Fixed by #22897
Assignees
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Milestone

Comments

@MaklaCof
Copy link

MaklaCof commented Oct 9, 2018

When using join query returns more columns then it should.

Steps to reproduce

This query:

            var temp = await this.Set<DbInvoiceState>().Join(
                    this.Set<DbInvoiceState>().GroupBy(t => t.InvoiceId).Select(t => new { t.Key, MaxInserted = t.Max(t2 => t2.InsertedDateTime) }),
                    t => new
                    {
                        t.InvoiceId,
                        t.InsertedDateTime,
                        states = states.Contains(t.DocumentState)
                    },
                    t => new
                    {
                        InvoiceId = t.Key,
                        InsertedDateTime = t.MaxInserted,
                        states = true
                    },
                    (t, t2) => t2.Key)
                .ToListAsync();

executes this query:

SELECT [t1].[Key], [t1].[MaxInserted]   //<--------- why this column?
FROM [Postman].[Invoice-States] AS [t]
INNER JOIN (
    SELECT [t0].[InvoiceId] AS [Key], MAX([t0].[InsertedDateTime]) AS [MaxInserted]
    FROM [Postman].[Invoice-States] AS [t0]
    GROUP BY [t0].[InvoiceId]
) AS [t1] ON (([t].[InvoiceId] = [t1].[Key]) AND ([t].[InsertedDateTime] = [t1].[MaxInserted])) AND [t].[DocumentState] IN (CAST(11 AS tinyint), CAST(12 AS tinyint), CAST(13 AS tinyint))

but it should be without [t1].[MaxInserted:

SELECT [t1].[Key]
FROM [Postman].[Invoice-States] AS [t]
INNER JOIN (
    SELECT [t0].[InvoiceId] AS [Key], MAX([t0].[InsertedDateTime]) AS [MaxInserted]
    FROM [Postman].[Invoice-States] AS [t0]
    GROUP BY [t0].[InvoiceId]
) AS [t1] ON (([t].[InvoiceId] = [t1].[Key]) AND ([t].[InsertedDateTime] = [t1].[MaxInserted])) AND [t].[DocumentState] IN (CAST(11 AS tinyint), CAST(12 AS tinyint), CAST(13 AS tinyint))

Further technical details

EF Core version: 2.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win-10-64bit
IDE: (Visual Studio 2017 15.8.3)

Also tried

this query

            var temp = await this.Set<DbInvoiceState>().Join(
                    this.Set<DbInvoiceState>().GroupBy(t => t.InvoiceId).Select(t => new { t.Key, MaxInserted = t.Max(t2 => t2.InsertedDateTime) }),
                    t => new
                    {
                        t.InvoiceId,
                        t.InsertedDateTime,
                        states = states.Contains(t.DocumentState)
                    },
                    t => new
                    {
                        InvoiceId = t.Key,
                        InsertedDateTime = t.MaxInserted,
                        states = true
                    },
                    (t, t2) => t.InvoiceId)       //   <--------   changed
                .ToListAsync();

but nothing is chaged: SELECT [t1].[Key], [t1].[MaxInserted], [t].[InvoiceId]

@ajcvickers ajcvickers added this to the Backlog milestone Oct 12, 2018
@smitpatel smitpatel added the verify-fixed This issue is likely fixed in new query pipeline. label Mar 16, 2020
@bricelam bricelam modified the milestones: Backlog, MQ Sep 11, 2020
@maumar maumar added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed verify-fixed This issue is likely fixed in new query pipeline. labels Sep 29, 2020
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 6, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 7, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
maumar added a commit that referenced this issue Oct 7, 2020
Resolves #10295
Resolves #12453
Resolves #13216
Resolves #13550
Resolves #13712
Resolves #13977
Resolves #15302
Resolves #17735
@maumar maumar closed this as completed in 2403579 Oct 7, 2020
@ajcvickers ajcvickers modified the milestones: MQ, 6.0.0 Nov 25, 2020
@ajcvickers ajcvickers modified the milestones: 6.0.0, 6.0.0-preview1 Jan 27, 2021
@ajcvickers ajcvickers modified the milestones: 6.0.0-preview1, 6.0.0 Nov 8, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported type-enhancement
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants