MDEV-18202: SQL_CALC_FOUND_ROWS do not work with UNION ALLbb-10.1-MDEV-18202

select_options (here used in conjunction with OPTION_FOUND_ROWS) is not set
correctly to check if the SELECT in union of SELECTs has SQL_CALC_FOUND_ROWS.
As a result, (select_options & OPTION_FOUND_ROWS) would give 0 and
send_records gets assigned 0 (number of rows sent to client in the query.
In case of LIMIT 0, no rows were sent to client, so send_records gets
assigned 0).

Since thd->limit_found_rows(the variable from where FOUND_ROWS() gets
its value from) gets its value from send_records, it also gets assigned 0.

Fix: The fix is made so that it is consistent with the tabled queries.
If we don’t have SQL_CALC_FOUND_ROWS in SELECT then we drop the flag.
If we have it in SELECT, we need to check for braces and LIMIT.
When there are no braces we keep the flag because we want the total number
of rows that are actually there in the table and not the number of rows
that were sent to the client (because even if we have LIMIT in this case,
it will be global LIMIT instead of local LIMIT). If we have braces
and LIMIT for a particular select, we don't want the total number of rows
that could have been sent to client if LIMIT was not present,
but we want the number of rows that were actually sent to client
(value returned by thd->get_sent_row_count()), irrespective if we have
SQL_CALC_FOUND_ROWS. Now send_records gets the value returned by
thd->get_sent_row_count() (number of rows that were sent to client)
if the flag is off, so we need to drop the flag. If we have braces
but no LIMIT, we need the number of rows that were sent to
client (1 for tabless queries). Now send_records is 1 when we have the
flag, so we don't drop the flag.

After setting the flag correctly, thd->limit_found_rows=send_records
needs to be done before send_eof() because send_eof() is incrementing
limit_found_rows and also assigning thd->limit_found_row = limit_found_rows
when current_select==last_select_lex.
Keeping thd->limit_found_rows = send_record after send_eof() will
reset the value of thd->limit_found_rows to 1 (because send_records is 1
for each union of select in case of tables queries) even when it
gets incremented in send_eof().

4 files changed, 201 insertions, 9 deletions

diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 7b38515cf70..7db4ad0a474 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -363,3 +363,132 @@ select found_rows();
 found_rows()
 5
 drop table t1;
+CREATE TABLE t1(id1 INT);
+CREATE TABLE t2(id2 INT);
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(2),(3);
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL SELECT "bar";
+foo
+foo
+bar
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL SELECT "bar" LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL SELECT id2 FROM t2;
+id1
+1
+2
+2
+3
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL SELECT id2 FROM t2 LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL (SELECT "bar");
+foo
+foo
+bar
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL (SELECT "bar") LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL (SELECT id2 FROM t2);
+id1
+1
+2
+2
+3
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar");
+foo
+foo
+bar
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar") LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2);
+id1
+1
+2
+2
+3
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL SELECT "bar";
+foo
+foo
+bar
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL SELECT "bar" LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL SELECT id2 FROM t2 LIMIT 0;
+id1
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL SELECT id2 FROM t2 LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar" LIMIT 0);
+foo
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar" LIMIT 0) LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+0
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2 LIMIT 0);
+id1
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2 LIMIT 0) LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+0
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar");
+foo
+bar
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar") LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+1
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2);
+id1
+2
+3
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar" LIMIT 0);
+foo
+foo
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar" LIMIT 0) LIMIT 0;
+foo
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+1
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2 LIMIT 0);
+id1
+1
+2
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2 LIMIT 0) LIMIT 0;
+id1
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+2
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test
index 33613697722..49395fbaebf 100644
--- a/mysql-test/t/select_found.test
+++ b/mysql-test/t/select_found.test
@@ -287,3 +287,61 @@ select found_rows();
 select sql_calc_found_rows * from t1 order by c1 limit 2,1;
 select found_rows();
 drop table t1;
+
+#
+#MDEV-18202 SQL_CALC_FOUND_ROWS do not work with UNION ALL
+#
+#
+# NO LIMIT IN BRACES
+#
+CREATE TABLE t1(id1 INT);
+CREATE TABLE t2(id2 INT);
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(2),(3);
+
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL SELECT "bar";
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL SELECT "bar" LIMIT 0;
+SELECT FOUND_ROWS();
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL SELECT id2 FROM t2;
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL SELECT id2 FROM t2 LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL (SELECT "bar");
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL (SELECT "bar") LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL (SELECT id2 FROM t2);
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+SELECT FOUND_ROWS();
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar");
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar") LIMIT 0;
+SELECT FOUND_ROWS();
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2);
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL SELECT "bar";
+(SELECT SQL_CALC_FOUND_ROWS "foo") UNION ALL SELECT "bar" LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL SELECT id2 FROM t2 LIMIT 0;
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1) UNION ALL SELECT id2 FROM t2 LIMIT 0;
+SELECT FOUND_ROWS();
+#
+#LIMIT IN BRACES
+#
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar" LIMIT 0);
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar" LIMIT 0) LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2 LIMIT 0);
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2 LIMIT 0) LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar");
+(SELECT SQL_CALC_FOUND_ROWS "foo" LIMIT 0) UNION ALL (SELECT "bar") LIMIT 0;
+SELECT FOUND_ROWS();
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2);
+(SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 LIMIT 0) UNION ALL (SELECT id2 FROM t2) LIMIT 0;
+SELECT FOUND_ROWS();
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar" LIMIT 0);
+SELECT SQL_CALC_FOUND_ROWS "foo" UNION ALL (SELECT "bar" LIMIT 0) LIMIT 0;
+SELECT FOUND_ROWS();
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2 LIMIT 0);
+SELECT SQL_CALC_FOUND_ROWS id1 FROM t1 UNION ALL (SELECT id2 FROM t2 LIMIT 0) LIMIT 0;
+SELECT FOUND_ROWS();
+DROP TABLE t1,t2;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 647dee80188..b964db4077d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2650,12 +2650,12 @@ void JOIN::exec_inner()
         send_records= 0;
       if (!error)
       {
+        /* Single select (without union) always returns 0 or 1 row */
+        thd->limit_found_rows= send_records;
         join_free();                      // Unlock all cursors
         error= (int) result->send_eof();
       }
     }
-    /* Single select (without union) always returns 0 or 1 row */
-    thd->limit_found_rows= send_records;
     thd->set_examined_row_count(0);
     DBUG_VOID_RETURN;
   }
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 38de2d592ed..d334bc9a694 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -767,10 +767,12 @@ bool st_select_lex_unit::optimize()
           we don't calculate found_rows() per union part.
           Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts.
         */
-        sl->join->select_options= 
-          (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
-          sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
-
+        sl->join->select_options=
+            (!found_rows_for_union) ? (sl->options & ~OPTION_FOUND_ROWS) :
+                                      ((sl->braces) ? ((select_limit_cnt==HA_POS_ERROR) ?
+                                                       (sl->options | found_rows_for_union) :
+                                                      (sl->options & ~OPTION_FOUND_ROWS)) :
+                                      (sl->options | found_rows_for_union));
 	saved_error= sl->join->optimize();
       }
 
@@ -848,9 +850,12 @@ bool st_select_lex_unit::exec()
           we don't calculate found_rows() per union part.
           Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts.
         */
-        sl->join->select_options= 
-          (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
-          sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
+        sl->join->select_options=
+            (!found_rows_for_union) ? (sl->options & ~OPTION_FOUND_ROWS) : 
+                                      ((sl->braces) ? ((select_limit_cnt==HA_POS_ERROR) ?
+                                                       (sl->options | found_rows_for_union) :
+                                                      (sl->options & ~OPTION_FOUND_ROWS)) :
+                                      (sl->options | found_rows_for_union));
 	saved_error= sl->join->optimize();
       }
       if (!saved_error)