MySQL Bugs: #74196: Extraneous / unnecessary column output in derived tables (i.e. `Filter1`, etc.)

Bug #74196	Extraneous / unnecessary column output in derived tables (i.e. `Filter1`, etc.)
Submitted:	2 Oct 2014 16:37	Modified:	29 Sep 2015 21:40
Reporter:	Ryan Griffith (OCA)	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.2	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Certain queries produce extraneous column output which is creating a compounding performance issue for me.

MySQL is not performing well when selecting from a derived / materialized table. This is exacerbated by the fact that the derived table contains ALL the columns from ALL tables within the projection. An example is below:

SELECT 
    `Distinct1`.`client_id`
FROM
    (SELECT DISTINCT
        `Filter1`.`client_id`
    FROM
        (SELECT 
        `Extent1`.`id`,
            `Extent1`.`tenure_id`,
            `Extent1`.`task_id`,
            `Extent1`.`client_id`,
            `Extent1`.`entry_date`,
            `Extent1`.`active`,
            /* Extent1 Columns omitted for brevity.... */

            `Extent2`.`div_id`,
            /* Extent2 Columns omitted for brevity.... */

            `Extent3`.`div_id` AS `DIV_ID1`
            /* Extent3 Columns omitted for brevity.... */

    FROM
        `time_entry` AS `Extent1`
    INNER JOIN `tenure` AS `Extent2` ON `Extent1`.`tenure_id` = `Extent2`.`id`
    INNER JOIN `tenure` AS `Extent3` ON `Extent1`.`tenure_id` = `Extent3`.`id`
    WHERE
        `Extent2`.`div_id` IN (2 , 3)) AS `Filter1`
    WHERE
        ((`Filter1`.`entry_date` >= @p__linq__0)
            AND (`Filter1`.`entry_date` <= @p__linq__1))
            AND (`Filter1`.`DIV_ID1` IS NOT NULL)) AS `Distinct1`

---------------------------------------------------
The exact same query performed against MS SQL / Microsoft's implementation is vastly different:
---------------------------------------------------
SELECT 
    [Distinct1].[client_id] AS [client_id]
    FROM ( SELECT DISTINCT 
        [Filter1].[client_id1] AS [client_id]
        FROM ( SELECT [Extent1].[client_id] AS [client_id1], [Extent1].[entry_date] AS [entry_date], [Extent3].[div_id] AS [div_id1]
            FROM   [itc].[time_entry] AS [Extent1]
            INNER JOIN [itc].[tenure] AS [Extent2] ON [Extent1].[tenure_id] = [Extent2].[id]
            INNER JOIN [itc].[tenure] AS [Extent3] ON [Extent1].[tenure_id] = [Extent3].[id]
            WHERE [Extent2].[div_id] IN (cast(2 as bigint), cast(3 as bigint))
        )  AS [Filter1]
        WHERE ([Filter1].[entry_date] >= @p__linq__0) AND ([Filter1].[entry_date] <= @p__linq__1) AND ([Filter1].[div_id1] IS NOT NULL)
    )  AS [Distinct1]

How to repeat:
Any question that produces a derived filter table.

Suggested fix:
Can we glean on some of the logic in Entity Framework / SQL Server's implementation of the the DbExpressionVisitor to figure out how to clean up this over-projection? I've dug deep into the code, but do not have enough domain knowledge to feel that I could tackle this as quickly as a core contributor.

Hi Fernando, do you know if this is something that will be worked on? I'm trying to figure out what direction to go with my software project because this issue is creating significant performance issues.

Thank you for your consideration

Hi Ryan, 

We are currently working on a few bugs in EF Sql Generation, including queries that are plain broken.

We are likely to release a 6.9.5 in a few weeks, but the fix for this one will not make it in that particular release (but a later one maybe).

Thanks

Thanks Fernando, I appreciate all your hard work.

Any word as to when you're going to get a source control system? Its my understand the only way to contribute is to send diffs.

Hello,

Just checking back into this bug. This causes issues for the query performance (significant issues) causing me to have to place all my common queries into flattened views so I can keep performance up.

Thanks Fernando.

Hi Ryan, I moved away from Connector/NET project, another member of the team will check it.

Thanks.