Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.33a
-
None
-
None
-
Fedora 19
Description
########### PREFACE ################################
|
|
|
CREATE OR REPLACE VIEW invoiced_trades_view AS |
(
|
SELECT il.trade_origin, |
il.trade_id,
|
il.trade_line_id
|
FROM invoice_head ih, |
invoice_line il
|
WHERE ih.id = il.invoice_id |
AND ih.creditnote_id IS NULL |
);
|
|
|
select * from invoiced_trades_view where |
-> ( trade_id = 16123 and trade_line_id = 52350 ) |
-> or ( trade_id = 16129 and trade_line_id = 52370 ) |
-> or ( trade_id = 16977 and trade_line_id = 55162 ) |
-> or ( trade_id = 16984 and trade_line_id = 55185 ); |
+--------------+----------+---------------+ |
| trade_origin | trade_id | trade_line_id |
|
+--------------+----------+---------------+ |
| IWBMARKET | 16123 | 52350 |
|
| IWBMARKET | 16129 | 52370 |
|
| IWBMARKET | 16984 | 55185 |
|
| IWBMARKET | 16977 | 55162 |
|
+--------------+----------+---------------+ |
============ FAILING QUERY ==========================
|
|
|
SELECT * |
FROM
|
(
|
(
|
SELECT 'IWBMARKET-1' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON th.id = tl.trade_id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
AND it.trade_origin IS NULL |
)
|
UNION ALL |
(
|
SELECT 'IWBMARKET-2' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON tl.trade_id = th.id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
AND it.trade_origin IS NULL |
)
|
) t
|
where trader_id = 1488 |
;
|
>>>>>>>> EXPECTED RESULT <<<<<<<<<<
|
|
|
no columns selected
|
>>>>>>>> MariaDB 5.5.33a <<<<<<<<<<
|
|
|
+--------------+-----------------+----------+---------------+-----------+
|
| trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
|
+--------------+-----------------+----------+---------------+-----------+
|
| IWBMARKET-1 | NULL | 16977 | 55162 | 1488 |
|
| IWBMARKET-1 | NULL | 16984 | 55185 | 1488 |
|
| IWBMARKET-2 | NULL | 16977 | 55162 | 1488 |
|
| IWBMARKET-2 | NULL | 16984 | 55185 | 1488 |
|
+--------------+-----------------+----------+---------------+-----------+
|
>>>>>>>>>>> MySQL 5.5.32 <<<<<<<<<<<<<<<
|
|
|
Empty set (0.65 sec)
|
>>>>>>>>>>> MariaDB 5.5.33a explain <<<<<<<<<<<<<<<<<<<
|
|
|
+------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 32686 | Using where |
|
| 2 | DERIVED | th | ALL | PRIMARY | NULL | NULL | NULL | 16343 | Using where |
|
| 2 | DERIVED | tl | ref | idx1 | idx1 | 4 | iwbmarket_test.th.id | 1 | Using where |
|
| 2 | DERIVED | il | ref | invoice_id,idx1,idx2 | idx1 | 70 | const,iwbmarket_test.th.id,iwbmarket_test.tl.id | 1 | Using where |
|
| 2 | DERIVED | ih | eq_ref | PRIMARY | PRIMARY | 4 | iwbmarket_test.il.invoice_id | 1 | Using where |
|
| 3 | UNION | th | ALL | PRIMARY | NULL | NULL | NULL | 16343 | Using where |
|
| 3 | UNION | tl | ref | idx1 | idx1 | 4 | iwbmarket_test.th.id | 1 | Using where |
|
| 3 | UNION | il | ref | invoice_id,idx1,idx2 | idx1 | 70 | const,iwbmarket_test.tl.trade_id,iwbmarket_test.tl.id | 1 | Using where |
|
| 3 | UNION | ih | eq_ref | PRIMARY | PRIMARY | 4 | iwbmarket_test.il.invoice_id | 1 | Using where |
|
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+--------------+------------+--------+----------------------+---------+---------+-------------------------------------------------------+-------+-------------+
|
10 rows in set (0.00 sec)
|
>>>>>>>>>>> MySQL 5.5.32 explain <<<<<<<<<<<<<<<<<<
|
|
|
(i had to rename the database from iwbmarket_test to rudy)
|
|
|
+----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1734 | Using where |
|
| 2 | DERIVED | th | ALL | PRIMARY | NULL | NULL | NULL | 16574 | Using where |
|
| 2 | DERIVED | tl | ref | idx1 | idx1 | 4 | rudy.th.id | 1 | Using where |
|
| 2 | DERIVED | il | ref | invoice_id,idx1,idx2 | idx1 | 70 | rudy.th.id,rudy.tl.id | 1 | Using where |
|
| 2 | DERIVED | ih | eq_ref | PRIMARY | PRIMARY | 4 | rudy.il.invoice_id | 1 | |
|
| 3 | UNION | th | ALL | PRIMARY | NULL | NULL | NULL | 16574 | Using where |
|
| 3 | UNION | tl | ref | idx1 | idx1 | 4 | rudy.th.id | 1 | Using where |
|
| 3 | UNION | il | ref | invoice_id,idx1,idx2 | idx1 | 70 | rudy.tl.trade_id,rudy.tl.id | 1 | Using where |
|
| 3 | UNION | ih | eq_ref | PRIMARY | PRIMARY | 4 | rudy.il.invoice_id | 1 | |
|
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+----+--------------+------------+--------+----------------------+---------+---------+-----------------------------+-------+-------------+
|
10 rows in set (0.65 sec)
|
If I remove the "AND it.trade_origin IS NULL" restriction, this happens:
SELECT * |
FROM
|
(
|
(
|
SELECT 'IWBMARKET-1' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON th.id = tl.trade_id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
)
|
UNION ALL |
(
|
SELECT 'IWBMARKET-2' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON tl.trade_id = th.id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
)
|
) t
|
where trader_id = 1488 |
;
|
|
|
;+--------------+-----------------+----------+---------------+-----------+ |
| trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
|
+--------------+-----------------+----------+---------------+-----------+ |
| IWBMARKET-1 | IWBMARKET | 16123 | 52350 | 1488 |
|
| IWBMARKET-1 | IWBMARKET | 16129 | 52370 | 1488 |
|
| IWBMARKET-1 | NULL | 16977 | 55162 | 1488 | |
| IWBMARKET-1 | NULL | 16984 | 55185 | 1488 | |
| IWBMARKET-2 | IWBMARKET | 16123 | 52350 | 1488 |
|
| IWBMARKET-2 | IWBMARKET | 16129 | 52370 | 1488 |
|
| IWBMARKET-2 | NULL | 16977 | 55162 | 1488 | |
| IWBMARKET-2 | NULL | 16984 | 55185 | 1488 | |
+--------------+-----------------+----------+---------------+-----------+ |
8 rows in set (0.86 sec) |
And if I add a join to the primary key of another table, the rows (of the original query) double.
I am joining to company here, where company.id is the unique primary key.
SELECT * |
FROM
|
(
|
(
|
SELECT 'IWBMARKET-1' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON th.id = tl.trade_id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
JOIN company c ON c.id = tl.company_id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
AND it.trade_origin IS NULL |
)
|
UNION ALL |
(
|
SELECT 'IWBMARKET-2' trade_origin, |
it.trade_origin trade_origin_it,
|
th.id trade_id,
|
tl.id trade_line_id,
|
tl.trader_id trader_id
|
FROM trade_head th |
JOIN trade_line tl ON tl.trade_id = th.id |
LEFT JOIN invoiced_trades_view it ON it.trade_origin = 'IWBMARKET' |
AND it.trade_id = th.id |
AND it.trade_line_id = tl.id |
WHERE th.is_closed = 'Y' |
AND th.is_deleted = 'N' |
AND tl.is_deleted = 'N' |
AND tl.is_billable = 'Y' |
AND it.trade_origin IS NULL |
)
|
) t
|
where trader_id = 1488 |
;
|
|
|
+--------------+-----------------+----------+---------------+-----------+ |
| trade_origin | trade_origin_it | trade_id | trade_line_id | trader_id |
|
+--------------+-----------------+----------+---------------+-----------+ |
| IWBMARKET-1 | NULL | 16123 | 52350 | 1488 | |
| IWBMARKET-1 | NULL | 16129 | 52370 | 1488 | |
| IWBMARKET-1 | NULL | 16977 | 55162 | 1488 | |
| IWBMARKET-1 | NULL | 16984 | 55185 | 1488 | |
| IWBMARKET-2 | NULL | 16977 | 55162 | 1488 | |
| IWBMARKET-2 | NULL | 16984 | 55185 | 1488 | |
+--------------+-----------------+----------+---------------+-----------+ |
6 rows in set (0.77 sec) |
If you need more information, please let me know. I am sorry that I cannot provide you the full table dumps, as this is production data.