[MDEV-6417] Wrong results when using join and group - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.0.10, 10.0.12
Fix Version/s: 10.0.16
Component/s: Optimizer
Labels:
None
Environment:
Ubuntu 14.04
10.0.12-MariaDB-1~trusty-log - mariadb.org binary distribution

Description

I am using two tables joined together. If I use DISTINCT everything is OK, but I need unique rows. Using GROUP BY returns NULL. In old mysql 5.5 everything works fine.

Query 1 bad results:

SELECT items_6 . * , cm1.brand AS brand1, cm2.model AS model1

FROM items_6

LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand

LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model

GROUP BY items_6.id

Query 2 - good results, but not unique by items_6.id

SELECT DISTINCT items_6 . * , cm1.brand AS brand1, cm2.model AS model1

FROM items_6

LEFT JOIN cars_models AS cm1 ON cm1.brandid = items_6.brand

LEFT JOIN cars_models AS cm2 ON cm2.id = items_6.model

GROUP BY items_6.id

Export and results provided in attachment.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bug_interesting.txt
2014-07-04 00:01
1 kB
Tomasz
bugsql.txt
2014-07-03 23:52
5 kB
Tomasz
explain.txt
2014-07-03 23:57
5 kB
Tomasz
my.cnf
2014-07-03 23:52
5 kB
Tomasz

Issue Links

relates to

MDEV-5719 Wrong result with GROUP BY and LEFT OUTER JOIN

Closed

Activity

People

Assignee:: Sergei Petrunia

Reporter:: Tomasz

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2014-07-03 16:50

Updated:: 2015-02-18 13:50

Resolved:: 2015-02-18 13:50

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.