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

LP:777691 - Wrong result with subqery in select list and subquery cache=off in maria-5.3

    XMLWordPrintable

Details

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

    Description

      Not repeatable in maria-5.2 Repeatable in maria-5.3, maria-5.3-mwl89. The SUM part of following query:

      SELECT t1.f1, ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 ) FROM t1 JOIN t2 ON t2.f2 > 0;

      returns 2 different results for 2 rows where t1.f1 is the same, hence the SUM should also be the same.

      Subquery cache appears to mask the bug by caching the first value and returning it twice.

      Test case:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 ( f10 int NOT NULL) ;
      INSERT IGNORE INTO t1 VALUES (104),(119);

      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 ( f2 int NOT NULL ) ;
      INSERT IGNORE INTO t2 VALUES (8),(231);

      DROP TABLE IF EXISTS t3;
      CREATE TABLE t3 (f10 int NOT NULL ) ;
      INSERT IGNORE INTO t3 VALUES (112);

      SET SESSION optimizer_switch='subquery_cache=off';
      SELECT t1.f10, ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 ) FROM t1 JOIN t2 ON t2.f2 ;

      In maria-5.2:

      f10 ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 )
      104 112
      119 NULL
      104 112
      119 NULL

      in maria-5.3:

      f10 ( SELECT SUM( f10 ) FROM t3 WHERE f10 > t1.f10 )
      104 112
      119 NULL
      104 NULL <--------- notice NULL here
      119 NULL

      Explain in maria-5.3:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY t1 ALL NULL NULL NULL NULL 2
      1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
      2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            philipstoev Philip Stoev (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.