Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4126

MySQL:68254 - wrong result with non-deterministic GROUP BY in subquery

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.1, 5.5.29, 5.3.12
        • Fix Version/s: 5.3.13, 10.0, 5.5
        • Component/s: None
        • Labels:

          Description

          The bug was originally filed by a user at bugs.mysql.com. It's reproducible on MySQL 5.6 as well as MariaDB 5.3-10.0.

          See the original description, including the test case, and the suggested patch in http://bugs.mysql.com/bug.php?id=68254 (I don't know if I may copy-paste it).

          The major complaint is that if table t1 has rows

          a b
----
1 0
2 1
3 1
4 1

          then, while a query 

          SELECT a from t1 group by b

          returns

          1
2

          The query 

          SELECT x,y FROM t2 WHERE y IN (SELECT a FROM t1 GROUP BY b)

          returns rows with y IN (1,2,3,4).

          I've set it to Minor for now, because the query with GROUP BY is indeterministic according to MySQL documentation, so I don't see that much value in using it as a subquery; but it's still a bug since the subquery is supposed to return 2 values, not 4, even it can be any of (1,2), (1,3) or (1,4).

          MySQL has verified the bug, although I don't know if they're going to fix it, and how.

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #68254

                  Activity

                  Hide
                  Permalink
                  pomyk Patryk Pomykalski added a comment -

                  Strange, semijoin requirements have:

                  3. Subquery does not have GROUP BY or ORDER BY

                  in code:
                  !select_lex->group_list.elements && !join->order && // 3

                  but it still uses semijoin?

                  Show
                  pomyk Patryk Pomykalski added a comment - Strange, semijoin requirements have: 3. Subquery does not have GROUP BY or ORDER BY in code: !select_lex->group_list.elements && !join->order && // 3 but it still uses semijoin?

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      3 Start watching this issue

                      Dates

                      • Created:
                        Updated: