Possible bug with latest contract filtering

Question

Let's look at the dept_employee subquery from the answer query. I've changed it a bit, moving from_date condition to ON block

SELECT
de.emp_no, de.dept_no, de.from_date, de.to_date
FROM
dept_emp de
JOIN
(SELECT
emp_no, MAX(from_date) AS from_date
FROM
dept_emp
GROUP BY emp_no) de1 ON de.emp_no = de1.emp_no AND de.from_date = de1.from_date
WHERE
de.to_date < '2002-01-01'
AND de.from_date > '2000-01-01'
order by de.emp_no, de.dept_no;

Now let's move the WHERE block inside the subquery

SELECT
de.emp_no, de.dept_no, de.from_date, de.to_date
FROM
dept_emp de
JOIN
(SELECT
emp_no, MAX(from_date) AS from_date
FROM
dept_emp
WHERE
from_date > '2000-01-01'
AND to_date < '2002-01-01'
GROUP BY emp_no) de1 ON de.emp_no = de1.emp_no AND de.from_date = de1.from_date
ORDER BY de.emp_no, de.dept_no;

As a result we're getting 676 and 806 rows respectively.

Why do the results differ?

To find out, let's look at the employee 13626. They are missing in the first result, while still being present in the second one.

Selecting from dept_emp gives us following results:

select * from dept_emp where emp_no = 13626;

Technically they had a contract "they have signed during the suggested time period". If you interpret the quoted line as "a contract started during the period", it's the second one. But it's getting picked up by the inner query and discarded by the outer WHERE. So, they do not show up in the results.

If you interpret the quoted line as "a contract started and ended during the period" (which is more likely looking at the code) , we have the first contract. But it's getting discarded by the inner MAX.

I consider this a bug since the employee actually have a contract for both cases and should not be discarded

I think you can achieve same conclusions with salaries table:

Answer 1

Hi Nick!

Thanks for reaching out.

What you are sharing is a great observation, thank you for doing that.

I am not sure we are talking about a bug but rather about a deeper understanding of how MySQL operates. Indeed, working with subqueries is, I think it is safe to say, a delicate matter, so where we place the conditions precisely alters the output. And since the code written abides by the MySQL syntax, Workbench will return a result in both cases so it is up to us, the user, to check the obtained data and ask the relative analytical and coding questions prior to concluding about the meaning of a certain output.

What happens "behind the scenes" is as you describe it, what I would add is to not forget about the order in which the SQL Optimizer will apply the tasks we've given it upon the execution of the given query. So, if a WHERE condition is left beyond the subquery, it will be applied prior to obtaining any particular dataset. I.e., referring to the first query, this will eliminate all rows from the dept_emp table beyond the provided condition even prior to joining the data from the subquery.

Finally, yes, the second query refers to the code we've provided namely because we don't need to eliminate rows by the use of the MAX function.

Hope this helps.
Best regards,
Martin

Answer 2

Actually, the first query is a slightly modified version of the subquery from the solution.

I meant that the solution has a bug, not MySQL or MySQL Workbench.

The second query from the thread is mine.

Answer 3

Hi Nick!

Thanks for your reply.

Can you please clarify what you mean by a "bug" - did the previous explanation help in understanding how MySQL functions?

You have obtained different from-dates meaning the mechanic used by SQL to retrieve the desired data is different - something you've achieved by using the MAX() function as you've described.

Also, I do obtain 676 and 806 rows as answers of the first and second query you've shared, respectively. Do you mean that you only obtain 2 or 3 rows? If that's the case, then we might be talking about a bug. If not, do you think you may have loaded a different database or be working with a shortened version of it? Or have you set any limits to the output obtained?

Looking forward to your answer.
Kind regards,
Martin

Answer 4

I do not consider it a MySQL bug. I don't think that the solution gives a right answer to the task. I've tried explaining it at the beggining.

Possible bug with latest contract filtering

Submit an answer