MySQL Forums
Forum List  »  Newbie

strange observation with "order by"
Posted by: Alex Yanovsky
Date: October 23, 2018 01:36PM

I am seeing such strange thing in MySQL Shell 8.0.13 talking to MySQL 5.7:

This is my simple table:

MySQL [/company] SQL> select * from testsimple;
+-----+
| a |
+-----+
| abc |
| def |
+-----+
2 rows in set (0.0004 sec)



Now I'm making such query:

MySQL [/company] SQL> select * from testsimple natural left join (select a, 1 mycol from testsimple where a = 'abc') part_test;
+-----+-------+
| a | mycol |
+-----+-------+
| abc | 1 |
| def | NULL |
+-----+-------+
2 rows in set (0.0005 sec)

The result above is what I expected.

Now I just add "order by a" to that query, and that is what I'm getting:

MySQL [10.143.17.55/company] SQL> select * from testsimple natural left join (select a, 1 mycol from testsimple where a = 'abc') part_test order by a;
+-----+-------+
| a | mycol |
+-----+-------+
| abc | 1 |
| def | 1 |
+-----+-------+
2 rows in set (0.0004 sec)

So, in the last row in mycol column I now have not NULL but 1.
Is this what I should be expecting ?

If yes, then why ?

Options: ReplyQuote


Subject
Written By
Posted
strange observation with "order by"
October 23, 2018 01:36PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.