Why does selecting all columns change the order-CodePudding

I have 2 tables. The order of selecting with a select * is different than the order of selecting without the wildcard.

This issue is happening on a production environment.

I have tried to replicate this issue but have not succeeded.

What could be causing this issue in the production tables?

DROP TABLE IF EXISTS #table1
DROP TABLE IF EXISTS #table2

CREATE TABLE #table1 (id int, code varchar(10), carriercode varchar(10), maxvalue numeric(14,3))
CREATE TABLE #table2 (id int, carriercode varchar(10))

-- notice the maximum value is always 2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 1,'a','carrier_a',2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 2,'a','carrier_b',2000.000
INSERT INTO #table1 (id,code,carriercode, maxvalue) SELECT 3,'c','carrier_c',2000.000
INSERT INTO #table2 (id,carriercode) SELECT 1,'carrier_a'
INSERT INTO #table2 (id,carriercode) SELECT 2,'carrier_b'

This is the select without the wildcard

SELECT t1.id,t1.code,t1.parentcode,t1.carriercode 
FROM #table1 t1
LEFT JOIN #table2 t2 on t1.carriercode=t2.carriercode
WHERE (t1.parentcode = 'a')
AND (t1.maxvalue >= 830 OR t1.maxvalue is null)
ORDER BY t1.maxvalue DESC

And the result

id  code    parentcode  carriercode
1   a1  a   carrier_a
2   a2  a   carrier_b

Here the select with the wildcard

SELECT t1.id,t1.code,t1.parentcode,t1.carriercode,* 
FROM #table1 t1
LEFT JOIN #table2 t2 on t1.carriercode=t2.carriercode
WHERE (t1.parentcode = 'a')
AND (t1.maxvalue >= 830 OR t1.maxvalue is null)
ORDER BY t1.maxvalue DESC

And the second result

id  code    parentcode  carriercode id  code    parentcode  carriercode maxvalue    dt  id  carriercode
1   a1  a   carrier_a   1   a1  a   carrier_a   2000.000    2022-09-30 22:49:52.787 1   carrier_a
2   a2  a   carrier_b   2   a2  a   carrier_b   2000.000    2022-09-30 22:49:52.787 2   carrier_b

Notice that the order of table 1 id column is the same for both select statements. On the production tables the 2 select statements are ordered differently.

What I have tried

Rounding issues: CAST numeric to int -> order is still the same for both selects
Changed the order of the initial inserts -> order is still equal for both selects

CodePudding user response：

Because the order of rows that are tied based on your ORDER BY is undocumented and depends on the details of the execution plan.

To fix the order, ensure your ORDER BY includes enough columns to uniquely order the rows. EG

ORDER BY t1.maxvalue DESC, id