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

LP:1046882 - sub-subselect sometimes looses reference to enclosing table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      Trying to match a field with the out-most table from a EXISTS-sub-subquery sometimes silently fails.
      There is a combination which seemingly "repairs" this, but then again reproduceably breaks it - see below.
      We already tried to turn off all optimizer features and disabled query cache; both to no avail.

      To easily reproduce this, please use the contained 'mysql' database with the following example queries.

      the clients:

      CLIENT-A: mysql-cli
      Version: 15.1 Distrib 5.5.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1)
      Connected via: unix-socket

      CLIENT-B: php Application (phpmyadmin o.e. to execute Queries will do)
      Version: php5.3.3-7+squeeze14 (default debian package; tried both: mysql & mysqli)
      Connected via: unix-socket

      the queries:

      "QUERY-GOOD":
      SELECT a.* FROM mysql.user a WHERE ( EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );

      "QUERY-BAD":
      SELECT a.* FROM mysql.user a WHERE ( SELECT EXISTS ( SELECT 1 FROM mysql.user b WHERE b.user = a.user LIMIT 1 ) );

      Notice the "SELECT EXISTS" vs. "EXISTS" only.

      the bug:

      QUERY-GOOD works on CLIENT-A and CLIENT-B.
      QUERY-BAD works every time on MySQL 5.5.25a, MySQL 5.1.63 and MySQL 5.0.51a. Not so, in MariaDB 5.5.25 (5.5.25-MariaDB-mariadb1~squeeze-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)):

      If CLIENT-B executes QUERY-BAD the result-set is empty. (should not and is not in MySQL-5.*)
      If CLIENT-A afterwards executes QUERY-BAD the result-set is empty, too. (should not and is not in MySQL-5.*)
      If CLIENT-A now executes QUERY-GOOD there are results returned. (always)
      If CLIENT-A then executes QUERY-BAD again there are still results returned! (strange: initially the result was empty)
      it stays like this for CLIENT-A until...
      if CLIENT-B executes QUERY-BAD once: it gets the same results like CLIENT-A. ("healed")
      if CLIENT-B executes QUERY-BAD one more time: the result-set is once more empty ("broken again")
      if CLIENT-A now executes QUERY-BAD the result-set it is empty again and stays so, until QUERY-GOOD is re-executed.

      I don't think MariaDB should return empty result-sets when MySQL does not.
      I further assume queries originating from different clients, shouldn't interfere like this.
      Sorry, if this was overly complex.

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            danielheimann Daniel Heimann (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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