MariaDB 10.1 left outer join behaves like inner join -- caused by GROUP BY

Question

I have updated from MySQL 5.6 to MariaDB 10.1, I have some problems with getting my "LEFT JOIN" to work.

SELECT * FROM users;

Returns 19 rows, as expected

SELECT * FROM users LEFT JOIN HOURS ON users.id = hours.user;

Returns 24 rows?!?

SELECT  *
    FROM  users
    LEFT JOIN (
        SELECT  *
            FROM  HOURS
            WHERE  stop IS NULL
              ) as hours2
        ON users.id = hours2.user;

Returns 0 rows (all rows in hours have a stop value atm)

I would expect to get 19 rows in all 3 queries, I am probably doing something wrong, but was unable to google a solution.

query used on MySQL 5.6

SELECT  `users`.`initials` AS 'Initials', `users`.`name` AS 'Bruger' ,
        `projects`.`id` AS 'ProjectId', `projects`.`name` AS 'Project',
        `work_type`.`name` AS 'Arbejde'
    FROM  `users`
    LEFT JOIN  (
        SELECT  `hours`.`user`, `hours`.`work_type`, `hours`.`project`
            FROM  `hours`
            WHERE  `id` IN (
                SELECT  max(`id`) AS `id`
                    FROM  `hours`
                    WHERE  `stop` IS NULL
                    GROUP BY  `user`)
               ) AS `hours`  ON `hours`.`user` = `users`.`id`
    LEFT JOIN  `projects`   ON `projects`.`id` = `hours`.`project`
    LEFT JOIN  `work_type`  ON `work_type`.`id` = `hours`.`work_type`
    WHERE  `users`.`status` = 1
    ORDER BY  `users`.`name`;

This always returned 17 rows before the update (19 rows in users, where 17 have status = 1)

But now this query only returns users that have "stop" = null.

What is that you want to do? My guess is how many hours worked per user - but it's only a guess — P.Salmon, Nov 21 '17 at 11:32
What is your input dataset, and what is the expected output dataset? — tyteen4a03, Nov 21 '17 at 11:33
The third qyery in your question isn't syntactically valid. Subqueries need aliases and, and your subquery needs `SELECT * FROM`, not just `SELECT *`. — O. Jones, Nov 21 '17 at 11:35
@O.Jones I made a change to 3. query, but that is not really the problem, as I just made a simplified version of my query that works fine with MySQL 5.6. — Benjamin Karlog, Nov 21 '17 at 11:40
There is no difference in how these queries behave between mysql 5.6 and mariadb 10.1 if that's what you are asking. You may have oversimplified your examples. — P.Salmon, Nov 21 '17 at 11:41
@P.Salmon Then I do not understand why my query changed behavior. I will add the original query to the button of my question. — Benjamin Karlog, Nov 21 '17 at 11:42

score 0 · Answer 1 · answered Nov 21 '17 at 11:35

0

Your hours table surely has some rows with duplicate values of user. Either kind of join generates result-set rows consisting of all possible combinations of the two tables joined, that meet the ON criteria.

answered Nov 21 '17 at 11:35

O. Jones

103,626
17
118
172

score 0 · Answer 2 · answered Nov 21 '17 at 12:43

As far as I can tell without knowing the data model this query works exactly the same in 5.6 (as tested in sqlfiddle) and mariadb. given a model like this

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.1.14-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [sandbox]> select id,username,status from users where id < 4;
+----+----------+--------+
| id | username | status |
+----+----------+--------+
|  1 | John     |      1 |
|  2 | Jane     |      1 |
|  3 | Ali      |      1 |
+----+----------+--------+
3 rows in set (0.00 sec)

drop table if exists hours,projects,work_type;
create table hours(id int auto_increment primary key,`user` int,work_type int, project int, `stop` varchar(3) );

create table projects(id int, name varchar(5));

create table work_type (id int, name varchar(3));

truncate table hours;

insert into hours  (`user`,work_type,project,`stop`) values (1,1,1,'atm'),(1,1,1,null),(2,null,1,null);
insert into projects values (1,'abc');
insert into work_type values (1,'aaa'),(2,'bbb');

The query

MariaDB [sandbox]> SELECT `users`.`username` AS 'Bruger' , `projects`.`id` AS 'ProjectId', `projects`.`name` AS 'Project', `work_type`.`name` AS 'Arbejde'
    -> FROM `users`
    -> LEFT JOIN (SELECT `hours`.`user`, `hours`.`work_type`, `hours`.`project`
    -> FROM `hours`
    -> WHERE `id`
    ->            IN (SELECT max(`id`) AS `id`
    ->   FROM `hours`
    -> WHERE `stop` IS NULL
    -> GROUP BY `user`
    -> )
    ->   ) AS `hours` ON `hours`.`user` = `users`.`id`
    -> LEFT JOIN `projects` ON `projects`.`id` = `hours`.`project`
    -> LEFT JOIN `work_type` ON `work_type`.`id` = `hours`.`work_type`
    -> WHERE `users`.`status` = 1 and users.id < 4
    -> ORDER BY `users`.`username`;
+--------+-----------+---------+---------+
| Bruger | ProjectId | Project | Arbejde |
+--------+-----------+---------+---------+
| Ali    |      NULL | NULL    | NULL    |
| Jane   |         1 | abc     | NULL    |
| John   |         1 | abc     | aaa     |
+--------+-----------+---------+---------+
3 rows in set (0.00 sec)

They query you show is the behavior that I would expect, I have just created a "new" MySQL 5.6 and exported a copy of my data to that database. When I run my query there I will get your output, but when I run the same data on MariaDB10.1 I only get the once that have values in tableB (looks like it tries to make an inner join) — Benjamin Karlog, Nov 21 '17 at 16:45

score 0 · Accepted Answer · edited Nov 23 '17 at 06:31

0

Thanks everyone for the help.

I found the answer and there is a small difference between how MySQL 5.6 and MariaDB selects the data.

SELECT 
    *
FROM
    `users`
        LEFT JOIN
    (SELECT 
        `hours`.`user`, `hours`.`work_type`, `hours`.`project`
    FROM
        `hours`
    WHERE
        hours.id in (SELECT max(`id`) AS `id` from hours where hours.stop is null **GROUP BY hours.user**)  **GROUP BY hours.user**) AS `hours` ON `hours`.`user` = `users`.`id`
WHERE
    `users`.`status` = 1
ORDER BY `users`.`name`;

Works in both MySQL and MariaDB

where:

SELECT 
    *
FROM
    `users`
        LEFT JOIN
    (SELECT 
        `hours`.`user`, `hours`.`work_type`, `hours`.`project`
    FROM
        `hours`
    WHERE
        hours.id in (SELECT max(`id`) AS `id` from hours where hours.stop is null **GROUP BY hours.user**) ) AS `hours` ON `hours`.`user` = `users`.`id`
WHERE
    `users`.`status` = 1
ORDER BY `users`.`name`;

Only works in MySQL

The difference is the placement of the 'GROUP BY' it seems like MySQL have access to all the table data, where the MariaDB 'GROUP BY' only have access to 'SELECTED' columns.

edited Nov 23 '17 at 06:31

philipxy

14,867
6
39
83

answered Nov 21 '17 at 17:20

Benjamin Karlog

330
1
6
22

Hi. Format code by indnenting 4 spaces eg by clicking "{}". "> " is for quotes. You can do both. You can see your formated post below the edit window. – philipxy Nov 23 '17 at 06:33
One problem is [you are writing an illegal query that was allowed up to 5.6](https://stackoverflow.com/q/34115174/3404097). Read the manual re ONLY_FULL_GROUP_BY. Read re GROUP BY since you don't understand it. If you had run the illegal code in MariaDB you should've got an error message re this. Also: Why shouldn't JOIN have more rows that its left table? Seems HOURS.user is not unique so you got what you should expect. But your question & answer are unclear--it's not even clear what queries you ran with what DDL on what data with what expectation. Please read [mcve] then edit your question. – philipxy Nov 23 '17 at 06:46
@philipxy I do not agree, and will therefore not update my question. I found a solution that solves my needs, and have tried to make the answer as clear as I am able to. – Benjamin Karlog Nov 23 '17 at 11:54

MariaDB 10.1 left outer join behaves like inner join -- caused by GROUP BY

3 Answers3