Предложение HAVING не работает после обновления сервера
Мы только что сделали серьезное обновление в версии mysql с 5.0.51 до 5.6.22, и я только что заметил, что один из моих запросов больше не работает должным образом.
SELECT
p.id AS product_id,
p.code,
p.description,
p.unitofmeasure,
p.costprice,
p.packsize,
vc.rateinpercent,
CASE
WHEN Sum(sales.qty) IS NULL THEN 0
ELSE Sum(sales.qty)
END AS sold,
CASE
WHEN stock.stocklevel IS NULL THEN 0
ELSE stock.stocklevel
END AS stocklevel,
sum(sales.qty) - stock.stocklevel AS diff,
CEIL((sum(sales.qty) - stock.stocklevel) / p.packsize) AS amt
FROM products p
LEFT JOIN
( SELECT
col.product_id,
col.quantity AS qty
FROM customerorderlines col
LEFT JOIN customerorders co
ON co.id = col.customerorder_id
WHERE co.orderdate >= '2014-12-01 00:00:00'
AND co.orderdate <= '2015-02-09 23:59:59'
AND co.location_id IN (1,2,3,7)
) sales
ON sales.product_id = p.id
LEFT JOIN
( SELECT
product_id,
location_id,
Sum(stocklevel) AS stocklevel
FROM stock
WHERE location_id IN (1,2,3,7)
GROUP BY product_id
) stock
ON stock.product_id = p.id
LEFT JOIN vatcodes vc
ON vc.id = p.purchasevatcode_id
WHERE p.supplier_id IN (137)
AND p.currentstatus_v = 1
GROUP BY p.id
HAVING sold > stocklevel
ORDER BY sold DESC
На старом сервере предложение HAVING отфильтровывало все результаты с минусами, получая следующий результат:
Вместо этого я получаю следующий результат на новом сервере:
По сути, это отфильтровывает некоторые негативные результаты, но не все. (Наборы данных имеют несколько дней, поэтому количество и объем продаж Freeze Gel Spray и количество акций немного отличаются)
Оглядываясь назад, замечательно, но я не ожидал каких-либо серьезных изменений в запросах между обновлениями сервера, поэтому мне было все равно что-либо тестировать или проверять. К счастью, это один из двух или трех запросов, использующих HAVING, поэтому, если мне придется переписать пару запросов, пусть будет так. Есть идеи, почему это так? Если это не работает вообще, достаточно справедливо, но работать только частично?
Спасибо заранее за понимание,
р
1 ответ
Я полагаю, вы пытались объяснить запрос, чтобы выяснить, что он делает?
Попробуйте сделать вычисляемые имена полей уникальными из базовых имен полей, чтобы вы могли быть уверены в том, что вы фильтруете. Я видел некоторые странные результаты, когда вычисляемые поля имеют те же имена, что и базовые физические поля.
Если ваши подзапросы возвращают одинаковый формат результатов (т. Е. Оба суммированы / сгруппированы), это помогает увидеть, что происходит.
Я не проверял этот запрос, но он может помочь. Если вы публикуете структуры таблиц и, возможно, некоторые фальшивые данные, которые показывают ошибку, это поможет диагностировать
SELECT
p.id AS product_id,
p.code,
p.description,
p.unitofmeasure,
p.costprice,
p.packsize,
vc.rateinpercent,
sales.totalSold,
stock.totalStock,
sales.totalSold - stock.totalStock AS diff,
CEIL((sales.totalSold - stock.totalStock) / p.packsize) AS amt
FROM products p
LEFT JOIN (
SELECT
col.product_id,
IFNULL( SUM(col.quantity), 0) AS totalSold
FROM customerorderlines col
LEFT JOIN customerorders co
ON co.id = col.customerorder_id
WHERE co.orderdate >= '2014-12-01 00:00:00'
AND co.orderdate <= '2015-02-09 23:59:59'
AND co.location_id IN (1,2,3,7)
GROUP BY product_id
) sales
ON sales.product_id = p.id
LEFT JOIN (
SELECT
product_id,
IFNULL( SUM(stocklevel), 0) AS totalStock
FROM stock
WHERE location_id IN (1,2,3,7)
GROUP BY product_id
) stock
ON stock.product_id = p.id
LEFT JOIN vatcodes vc
ON vc.id = p.purchasevatcode_id
WHERE p.supplier_id IN (137)
AND p.currentstatus_v = 1
GROUP BY p.id
HAVING totalSold > totalStock
ORDER BY totalSold DESC