What is phpMyAdmin doing to my UNION ALL query?

Question

The following query returns 1 row:

SELECT `coach_id` FROM `table_a` WHERE `coach_id` = 8
UNION ALL
SELECT `coach_id` FROM `table_b` WHERE `coach_id` = 8

But SELECT coach_id FROM table_b WHERE coach_id = 8 returns 2 rows.

And SELECT coach_id FROM table_a WHERE coach_id = 8 returns 1 row.

I'm using UNION ALL to avoid the DISTINCT filtering, because I'm actually just interested in the total number of rows. Still it seems to behave like regular UNION a.k.a UNION DISTINCT.

Whats going on here? Query is executed in phpMyAdmin 4.5.2 interface on MariaDB 10.1.9 Server.

Update

I just discovered that the mysql command line client behaves like expected. So the failure has to be somewhere within my stack of nginx 1.8.0, PHP 5.6.16 mysqli and phpmyadmin.

Update 2

When I run the query from a php script (using mysqli) it also correctly returns 3 rows. I guess that leaves nothing but phpMyAdmin to cause this phenomenon. Thanks for the help so far and sorry that the question has been misleading. I didn't know better...

Answer 1

Is it possible you have dirty data? Spaces or some other character in with the ids? use LENGTH(coach_id), coach_id in your query. Then if it is more than one character it won't return those rows but you would know why.

Answer 2

schema

create schema testThenDrop123;
use testThenDrop123; -- use that database

create table table_a
(   id int auto_increment primary key,
    coach_id int not null
);

create table table_b
(   id int auto_increment primary key,
    coach_id int not null
);

insert table_a (coach_id) values (8);
insert table_b (coach_id) values (8),(8);

query

SELECT `coach_id` FROM `table_a` WHERE `coach_id` = 8 
UNION ALL 
SELECT `coach_id` FROM `table_b` WHERE `coach_id` = 8;
+----------+
| coach_id |
+----------+
|        8 |
|        8 |
|        8 |
+----------+

.

drop schema testThenDrop123; -- cleanup by dropping db

So I don't see what you are seeing.

Answer 3

This is a phpMyAdmin bug, which is fixed in v4.5.3.0 (2015-12-23).