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_dateFROM 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_dateWHERE 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_dateFROM 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_dateORDER 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:

Follow this topic

Posted on:

18 Mar 2023

Possible bug with latest contract filtering

0 answers ( 0 marked as helpful)