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

Wrong result (or questionable result and behavior) with aggregate function in uncorrelated SELECT subquery

    Details

    • Sprint:
      10.2.7-1

      Description

      Please consider the scenario below. I don't know whether my expectations are even correct, because MySQL, Oracle and PostgreSQL all behave differently in this case, and none does exactly what I expect.

      Output from the CLI

      MariaDB [test]> INSERT INTO t2 VALUES (3);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> --echo # This looks wrong. t1 has 2 rows, so the result set should have 2 rows too,
      MariaDB [test]> --echo # but it only has 1:
      MariaDB [test]> SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
      +----------------------------+
      | ( SELECT MAX(f1) FROM t2 ) |
      +----------------------------+
      |                          2 |
      +----------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> --echo # This also looks wrong. Now there are two rows in the result set,
      MariaDB [test]> --echo # but they are different, which should not happen, as we select MAX:
      MariaDB [test]> SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
      +----------------------------+
      | ( SELECT MAX(f1) FROM t2 ) |
      +----------------------------+
      |                          1 |
      |                          2 |
      +----------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> INSERT INTO t2 VALUES (4);
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [test]> 
      MariaDB [test]> --echo # This shouldn't even work, because the subquery returns more than 1 row,
      MariaDB [test]> --echo # but it works and returns wrong result, same as before:
      MariaDB [test]> SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
      +----------------------------+
      | ( SELECT MAX(f1) FROM t2 ) |
      +----------------------------+
      |                          1 |
      |                          2 |
      +----------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> --echo # This returns the error as expected:
      MariaDB [test]> SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
      ERROR 1242 (21000): Subquery returns more than 1 row
      

      Clean test case (for copy-paste)

      --disable_abort_on_error
       
      CREATE TABLE t1 (f1 INT);
      CREATE VIEW v1 AS SELECT * FROM t1;
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (f2 int);
       
      INSERT INTO t2 VALUES (3);
      --echo # This looks wrong. t1 has 2 rows, so the result set should have 2 rows too,
      --echo # but it only has 1:
      SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
       
      --echo # This also looks wrong. Now there are two rows in the result set,
      --echo # but they are different, which should not happen, as we select MAX:
      SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
       
      INSERT INTO t2 VALUES (4);
       
      --echo # This shouldn't even work, because the subquery returns more than 1 row,
      --echo # but it works and returns wrong result, same as before:
      SELECT ( SELECT MAX(f1) FROM t2 ) FROM v1;
       
      --echo # This returns the error as expected:
      SELECT ( SELECT MAX(f1) FROM t2 ) FROM t1;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sanja Oleksandr Byelkin
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: