5.1.11升级到 5.3.2 时候，union语句 order by报错 #856
Description
在原来版本5.1.11时候 order by 拼接正常，但是在5.3.2的时候，order by 拼接在了里层，导致报错：SQL 错误 [1033] [S0001]: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
下面是还原SQL
在原来版本5.1.11时候 对下面SQL order by 是正常的
select ocnt_departure_date,
movement_number,
a.ocnt_mawb_number,
iif(sum(cast(a.pack_no AS int)) is null,0,sum(cast(a.pack_no AS int))) as packNo,
count(a.logistics_no) as logisticsCount,
sum(iif(orders_status not in (-1,0,4,100),1,0)) as orders_status,
sum(iif(logistics_status not in (-1,0,4,100),1,0)) as logistics_status,
sum(iif(receipts_status not in (-1,0,4,100),1,0)) as receipts_status,
sum(iif(orders_status in (2,120),1,0)) as orders_storage,
sum(iif(logistics_status in (2,120),1,0)) as logistics_storage,
sum(iif(receipts_status in (2,120),1,0)) as receipts_storage,
sum(iif(orders_status in (2,120),1,0)) as orders_success,
sum(iif(logistics_status in (2,120),1,0)) as logistics_success,
sum(iif(receipts_status in (2,120),1,0)) as receipts_success,
sum(temp.shipment_weight) as shipmentActualWeightSum
from import_data as a
join (select iif(cc.declare_weight = 0,id.shipment_weight,id.shipment_actual_weight) as shipment_weight,id.logistics_no
from import_data as id, corporate_customers as cc where id.shipper_account_number = cc.account and id.area = cc.area and cc.status=1) as temp on temp.logistics_no = a.logistics_no
inner join declare_status as b on a.logistics_no = b.logistics_no
inner join corporate_customers cc2 on cc2.account = a.shipper_account_number and cc2.area = a.area and cc2.status = 1
where
a.ocnt_mawb_number = b.ocnt_mawb_number
and a.area = #{dto.area,jdbcType=CHAR}
and cc2.declare_mode in (1,2,3)
group by a.ocnt_departure_date,a.movement_number,a.ocnt_mawb_number
having count(a.logistics_no) != sum(iif(logistics_status in (2,120),1,0))
union
select ocnt_departure_date,
movement_number,
a.ocnt_mawb_number,
iif(sum(cast(a.pack_no AS int)) is null,0,sum(cast(a.pack_no AS int))) as packNo,
count(a.logistics_no) as logisticsCount,
sum(iif(orders_status not in (-1,0,4,100),1,0)) as orders_status,
sum(iif(logistics_status not in (-1,0,4,100),1,0)) as logistics_status,
sum(iif(receipts_status not in (-1,0,4,100),1,0)) as receipts_status,
sum(iif(orders_status in (2,120),1,0)) as orders_storage,
sum(iif(logistics_status in (2,120),1,0)) as logistics_storage,
sum(iif(receipts_status in (2,120),1,0)) as receipts_storage,
sum(iif(orders_status in (2,120),1,0)) as orders_success,
sum(iif(logistics_status in (2,120),1,0)) as logistics_success,
sum(iif(receipts_status in (2,120),1,0)) as receipts_success,
sum(temp.shipment_weight) as shipmentActualWeightSum
from import_data as a
join (select iif(cc.declare_weight = 0,id.shipment_weight,id.shipment_actual_weight) as shipment_weight,id.logistics_no
from import_data as id, corporate_customers as cc where id.shipper_account_number = cc.account and id.area = cc.area and cc.status=1) as temp on temp.logistics_no = a.logistics_no
inner join declare_status as b on a.logistics_no = b.logistics_no
inner join corporate_customers cc2 on cc2.account = a.shipper_account_number and cc2.area = a.area and cc2.status = 1
where
a.ocnt_mawb_number = b.ocnt_mawb_number
and a.area = #{dto.area,jdbcType=CHAR}
and cc2.declare_mode = 0
group by a.ocnt_departure_date,a.movement_number,a.ocnt_mawb_number
having count(a.logistics_no) != sum(iif(orders_status in (2,120),1,0))
or count(a.logistics_no) != sum(iif(logistics_status in (2,120),1,0))
正常的order by SQL
SELECT
**ROW_NUMBER() OVER (
ORDER BY ocnt_departure_date DESC) PAGE_ROW_NUMBER,** -- 在此处拼接了order by
ocnt_departure_date,
movement_number,
ocnt_mawb_number,
packNo,
logisticsCount,
orders_status,
logistics_status,
receipts_status,
orders_storage,
logistics_storage,
receipts_storage,
orders_success,
logistics_success,
receipts_success,
shipmentActualWeightSum
FROM
(
SELECT
ocnt_departure_date,
movement_number,
ocnt_mawb_number,
packNo,
logisticsCount,
orders_status,
logistics_status,
receipts_status,
orders_storage,
logistics_storage,
receipts_storage,
orders_success,
logistics_success,
receipts_success,
shipmentActualWeightSum
FROM
(
SELECT
a.customize_date AS ocnt_departure_date,
a.customize_movement_number AS movement_number,
a.customize_mawb_number AS ocnt_mawb_number,
iif(sum(CAST(a.pack_no AS int)) IS NULL,
0,
sum(CAST(a.pack_no AS int))) AS packNo,
count(a.logistics_no) AS logisticsCount,
sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
sum(temp.shipment_weight) AS shipmentActualWeightSum
FROM
import_data AS a
JOIN (
SELECT
iif(cc.declare_weight = 0,
id.shipment_weight,
id.shipment_actual_weight) AS shipment_weight,
id.logistics_no
FROM
import_data AS id,
corporate_customers AS cc
WHERE
id.shipper_account_number = cc.account
AND id.area = cc.area
AND cc.status = 1) AS temp ON
temp.logistics_no = a.logistics_no
INNER JOIN declare_status AS b ON
a.logistics_no = b.logistics_no
INNER JOIN corporate_customers cc2 ON
cc2.account = a.shipper_account_number
AND cc2.area = a.area
AND cc2.status = 1
WHERE
a.area = 'CAN'
AND cc2.declare_mode IN (1, 2, 3)
AND a.customize_mawb_number != ''
AND a.customize_mawb_number IS NOT NULL
GROUP BY
a.customize_date,
a.customize_movement_number,
a.customize_mawb_number
HAVING
count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
UNION
SELECT
a.customize_date AS ocnt_departure_date,
a.customize_movement_number AS movement_number,
a.customize_mawb_number AS ocnt_mawb_number,
iif(sum(CAST(a.pack_no AS int)) IS NULL,
0,
sum(CAST(a.pack_no AS int))) AS packNo,
count(a.logistics_no) AS logisticsCount,
sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
sum(temp.shipment_weight) AS shipmentActualWeightSum
FROM
import_data AS a
JOIN (
SELECT
iif(cc.declare_weight = 0,
id.shipment_weight,
id.shipment_actual_weight) AS shipment_weight,
id.logistics_no
FROM
import_data AS id,
corporate_customers AS cc
WHERE
id.shipper_account_number = cc.account
AND id.area = cc.area
AND cc.status = 1) AS temp ON
temp.logistics_no = a.logistics_no
INNER JOIN declare_status AS b ON
a.logistics_no = b.logistics_no
INNER JOIN corporate_customers cc2 ON
cc2.account = a.shipper_account_number
AND cc2.area = a.area
AND cc2.status = 1
WHERE
a.area = 'CAN'
AND cc2.declare_mode = 0
AND a.customize_mawb_number != ''
AND a.customize_mawb_number IS NOT NULL
GROUP BY
a.customize_date,
a.customize_movement_number,
a.customize_mawb_number
HAVING
count(a.logistics_no) != sum(iif(orders_status IN (2, 120), 1, 0))
OR count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS升级版本后 5.3.2 之后 order by 变成了 这个样子
SELECT
TOP 10 ocnt_departure_date,
movement_number,
ocnt_mawb_number,
packNo,
logisticsCount,
orders_status,
logistics_status,
receipts_status,
orders_storage,
logistics_storage,
receipts_storage,
orders_success,
logistics_success,
receipts_success,
shipmentActualWeightSum
FROM
(
SELECT
ROW_NUMBER() OVER (
ORDER BY RAND()) PAGE_ROW_NUMBER,
ocnt_departure_date,
movement_number,
ocnt_mawb_number,
packNo,
logisticsCount,
orders_status,
logistics_status,
receipts_status,
orders_storage,
logistics_storage,
receipts_storage,
orders_success,
logistics_success,
receipts_success,
shipmentActualWeightSum
FROM
(
SELECT
ocnt_departure_date,
movement_number,
ocnt_mawb_number,
packNo,
logisticsCount,
orders_status,
logistics_status,
receipts_status,
orders_storage,
logistics_storage,
receipts_storage,
orders_success,
logistics_success,
receipts_success,
shipmentActualWeightSum
FROM
(
SELECT
a.customize_date AS ocnt_departure_date,
a.customize_movement_number AS movement_number,
a.customize_mawb_number AS ocnt_mawb_number,
iif(sum(CAST(a.pack_no AS int)) IS NULL,
0,
sum(CAST(a.pack_no AS int))) AS packNo,
count(a.logistics_no) AS logisticsCount,
sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
sum(temp.shipment_weight) AS shipmentActualWeightSum
FROM
import_data AS a
JOIN (
SELECT
iif(cc.declare_weight = 0,
id.shipment_weight,
id.shipment_actual_weight) AS shipment_weight,
id.logistics_no
FROM
import_data AS id,
corporate_customers AS cc
WHERE
id.shipper_account_number = cc.account
AND id.area = cc.area
AND cc.status = 1) AS temp
ON
temp.logistics_no = a.logistics_no
INNER JOIN declare_status AS b
ON
a.logistics_no = b.logistics_no
INNER JOIN corporate_customers cc2
ON
cc2.account = a.shipper_account_number
AND cc2.area = a.area
AND cc2.status = 1
WHERE
a.area = 'CAN'
AND cc2.declare_mode IN (1, 2, 3)
AND a.customize_mawb_number != ''
AND a.customize_mawb_number IS NOT NULL
AND a.customize_date BETWEEN '2022-06-01 00:00:00.0' AND '2025-06-07 00:00:00.0'
GROUP BY
a.customize_date,
a.customize_movement_number,
a.customize_mawb_number
HAVING
count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
UNION
SELECT
a.customize_date AS ocnt_departure_date,
a.customize_movement_number AS movement_number,
a.customize_mawb_number AS ocnt_mawb_number,
iif(sum(CAST(a.pack_no AS int)) IS NULL,
0,
sum(CAST(a.pack_no AS int))) AS packNo,
count(a.logistics_no) AS logisticsCount,
sum(iif(orders_status NOT IN (-1, 0, 4, 100), 1, 0)) AS orders_status,
sum(iif(logistics_status NOT IN (-1, 0, 4, 100), 1, 0)) AS logistics_status,
sum(iif(receipts_status NOT IN (-1, 0, 4, 100), 1, 0)) AS receipts_status,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_storage,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_storage,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_storage,
sum(iif(orders_status IN (2, 120), 1, 0)) AS orders_success,
sum(iif(logistics_status IN (2, 120), 1, 0)) AS logistics_success,
sum(iif(receipts_status IN (2, 120), 1, 0)) AS receipts_success,
sum(temp.shipment_weight) AS shipmentActualWeightSum
FROM
import_data AS a
JOIN (
SELECT
iif(cc.declare_weight = 0,
id.shipment_weight,
id.shipment_actual_weight) AS shipment_weight,
id.logistics_no
FROM
import_data AS id,
corporate_customers AS cc
WHERE
id.shipper_account_number = cc.account
AND id.area = cc.area
AND cc.status = 1) AS temp
ON
temp.logistics_no = a.logistics_no
INNER JOIN declare_status AS b
ON
a.logistics_no = b.logistics_no
INNER JOIN corporate_customers cc2
ON
cc2.account = a.shipper_account_number
AND cc2.area = a.area
AND cc2.status = 1
WHERE
a.area = 'CAN'
AND cc2.declare_mode = 0
AND a.customize_mawb_number != ''
AND a.customize_mawb_number IS NOT NULL
AND a.customize_date BETWEEN '2022-06-01 00:00:00.0' AND '2025-06-07 00:00:00.0'
GROUP BY
a.customize_date,
a.customize_movement_number,
a.customize_mawb_number
HAVING
count(a.logistics_no) != sum(iif(orders_status IN (2, 120), 1, 0))
OR count(a.logistics_no) != sum(iif(logistics_status IN (2, 120), 1, 0))
**ORDER BY
ocnt_departure_date DESC**) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS
WHERE
PAGE_ROW_NUMBER > 0
ORDER BY
PAGE_ROW_NUMBER;可以明显看到 order by 语句 （ORDER BY ocnt_departure_date DESC） 拼接到了后面，导致报错。这是为什么？如何修复？