|
########### 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.
|
mariadb-bug.tgz