[debian-mysql] Order by / SQL engine

[László Gyimesi]

Hi,

I wrote an SQL query wherein the result set differs with or without "order
by" clause. I think it should not be so.

The correct result set, without order by:

+------+-----+-----+-----+
| vker | t4  | t8  | o4  |
+------+-----+-----+-----+
|    3 |   4 |  11 |   7 |
|    5 |  23 |  23 |   0 |
|    6 |  20 |  15 |  -5 |
|   13 |  13 |  14 |   1 |
|   24 |  -3 | -10 |  -7 |
|   25 |  21 |  23 |   2 |
|   26 |  24 |  16 |  -8 |
|   28 |   7 |  -2 |  -9 |
|   42 |   3 |  -2 |  -5 |
|   43 |   7 |  -6 | -13 |
|   46 |  -5 |  -7 |  -2 |
|   48 |   0 | -14 | -14 |
|   49 |  -1 |  -2 |  -1 |
|   53 |  -5 | -13 |  -8 |
|   67 |  10 |   8 |  -2 |
|   71 |  -3 | -11 |  -8 |
|   77 |   8 |   5 |  -3 |
|   88 |  23 |  16 |  -7 |
|   89 |   9 |   1 |  -8 |
|   91 | -10 | -23 | -13 |
|   94 |  13 |  -7 | -20 |
|   97 |   4 |  -2 |  -6 |
|  105 |   0 | -13 | -13 |
+------+-----+-----+-----+
23 rows in set (0.000 sec)

The wrong one with "order by o4" clause:

+------+----+----+----+
| vker | t4 | t8 | o4 |
+------+----+----+----+
|   26 | 24 | 16 |  0 |
|  105 |  0 |  0 |  0 |
|   28 |  7 |  0 |  0 |
|   42 |  3 |  0 |  0 |
|   43 |  7 |  0 |  0 |
|   46 |  0 |  0 |  0 |
|   48 |  0 |  0 |  0 |
|   49 |  0 |  0 |  0 |
|   53 |  0 |  0 |  0 |
|   67 | 10 |  8 |  0 |
|   71 |  0 |  0 |  0 |
|   77 |  8 |  5 |  0 |
|    5 | 23 | 23 |  0 |
|   88 | 23 | 16 |  0 |
|    6 | 20 | 15 |  0 |
|   89 |  9 |  1 |  0 |
|   91 |  0 |  0 |  0 |
|   24 |  0 |  0 |  0 |
|   94 | 13 |  0 |  0 |
|   97 |  4 |  0 |  0 |
|   13 | 13 | 14 |  1 |
|   25 | 21 | 23 |  2 |
|    3 |  4 | 11 |  7 |
+------+----+----+----+
23 rows in set (0.001 sec)

The SQL:

select 'Without order';
select t4.vker, (t4.ossz-t4.fid) as t4, (t8.ossz-t8.fid) as t8,
((t8.ossz-t8.fid) - (t4.ossz-t4.fid)) as o4
from ogyval as t4, ogyval as t8
where t4.val = '2014' and t8.val = '2018' and t4.vker = t8.vker
;

select 'With order';
select t4.vker, (t4.ossz-t4.fid) as t4, (t8.ossz-t8.fid) as t8,
((t8.ossz-t8.fid) - (t4.ossz-t4.fid)) as o4
from ogyval as t4, ogyval as t8
where t4.val = '2014' and t8.val = '2018' and t4.vker = t8.vker
order by o4;

The data are from parliamentary elections in Hungary in 2014 and 2018 in
some election districts ("vker" column, 23 districts of 106). Ell, fid and
ossz are percents. I want to compare district results between 2014 and
2018. Ell is max result of maverick parties, Ossz is sum result of maverick
parties, fid is the only governing party (Fidesz).

I attached the mysqldump of table ogyval. It contains version numbers. My
system is 10.9 debian, mariadb packages are from standard binary debian
repository:

deb http://deb.debian.org/debian buster main contrib non-free
deb http://deb.debian.org/debian buster-updates main contrib non-free
deb http://security.debian.org/ buster/updates main contrib non-free

Thank you,

Laszlo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/pkg-mysql-maint/attachments/20210409/d382a986/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ogyval.dump
Type: application/octet-stream
Size: 3231 bytes
Desc: not available
URL: <http://alioth-lists.debian.net/pipermail/pkg-mysql-maint/attachments/20210409/d382a986/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: p.sql
Type: application/sql
Size: 469 bytes
Desc: not available
URL: <http://alioth-lists.debian.net/pipermail/pkg-mysql-maint/attachments/20210409/d382a986/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: p.lst
Type: application/octet-stream
Size: 1699 bytes
Desc: not available
URL: <http://alioth-lists.debian.net/pipermail/pkg-mysql-maint/attachments/20210409/d382a986/attachment-0001.obj>