MDEV-26844: DELETE returns ROW_NUMBER=1 for every row uponbb-10.7-row_number-MDEV-26844

ER_TRUNCATED_WRONG_VALUE

Part 2: Fix for DELETE with ORDER BY

Analysis: m_current_row_for_warning doesn't increment and assumes default
value which is then used by ROW_NUMBER.
Fix: Introduced a a new variable(modify_m_current_row_for_warning) which
when true increments counter. It is set to true when object of Filesort
class is created for DELETE and false everywhere else to avoid getting
strange results for other statements example SELECT.
This also fixes UPDATE with ORDER BY

9 files changed, 106 insertions, 10 deletions

diff --git a/mysql-test/main/get_diagnostics.result b/mysql-test/main/get_diagnostics.result
index 2e749fa21d7..bf194a88a33 100644
--- a/mysql-test/main/get_diagnostics.result
+++ b/mysql-test/main/get_diagnostics.result
@@ -1811,3 +1811,57 @@ SELECT @n;
 @n
 4
 DROP TABLE t;
+# With ORDER BY
+CREATE TABLE t (a VARCHAR(8));
+INSERT INTO t VALUES ('val1'),('val2'),('100'),('val4'), ('100'), ('val5');
+SELECT * FROM t;
+a
+val1
+val2
+100
+val4
+100
+val5
+DELETE FROM t WHERE a = 100 ORDER BY a;
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'val1'
+Warning	1292	Truncated incorrect DOUBLE value: 'val2'
+Warning	1292	Truncated incorrect DOUBLE value: 'val4'
+Warning	1292	Truncated incorrect DOUBLE value: 'val5'
+SHOW WARNINGS;
+Level	Code	Message
+Warning	1292	Truncated incorrect DOUBLE value: 'val1'
+Warning	1292	Truncated incorrect DOUBLE value: 'val2'
+Warning	1292	Truncated incorrect DOUBLE value: 'val4'
+Warning	1292	Truncated incorrect DOUBLE value: 'val5'
+GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER;
+SELECT @n;
+@n
+4
+DROP TABLE t;
+CREATE TABLE t (a VARCHAR(8), b tinyint);
+INSERT INTO t(a) VALUES ('val1'),('val2'),('100'),('val4'),('100');
+SELECT * FROM t;
+a	b
+val1	NULL
+val2	NULL
+100	NULL
+val4	NULL
+100	NULL
+SET SQL_MODE='';
+UPDATE t SET b=1234 WHERE a = 100 ORDER BY a;
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'val1'
+Warning	1292	Truncated incorrect DOUBLE value: 'val2'
+Warning	1292	Truncated incorrect DOUBLE value: 'val4'
+Warning	1264	Out of range value for column 'b' at row 1
+Warning	1264	Out of range value for column 'b' at row 2
+GET DIAGNOSTICS CONDITION 2 @n = ROW_NUMBER;
+SELECT @n;
+@n
+2
+GET DIAGNOSTICS CONDITION 5 @n = ROW_NUMBER;
+SELECT @n;
+@n
+2
+DROP TABLE t;
diff --git a/mysql-test/main/get_diagnostics.test b/mysql-test/main/get_diagnostics.test
index e8d81dca1e6..186c2391cfd 100644
--- a/mysql-test/main/get_diagnostics.test
+++ b/mysql-test/main/get_diagnostics.test
@@ -1687,3 +1687,35 @@ GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER;
 SELECT @n;
 
 DROP TABLE t;
+
+--echo # With ORDER BY
+
+CREATE TABLE t (a VARCHAR(8));
+
+INSERT INTO t VALUES ('val1'),('val2'),('100'),('val4'), ('100'), ('val5');
+
+SELECT * FROM t;
+
+DELETE FROM t WHERE a = 100 ORDER BY a;
+SHOW WARNINGS;
+
+GET DIAGNOSTICS CONDITION 3 @n = ROW_NUMBER;
+SELECT @n;
+
+DROP TABLE t;
+
+CREATE TABLE t (a VARCHAR(8), b tinyint);
+
+INSERT INTO t(a) VALUES ('val1'),('val2'),('100'),('val4'),('100');
+SELECT * FROM t;
+
+SET SQL_MODE='';
+
+UPDATE t SET b=1234 WHERE a = 100 ORDER BY a;
+GET DIAGNOSTICS CONDITION 2 @n = ROW_NUMBER;
+SELECT @n;
+
+GET DIAGNOSTICS CONDITION 5 @n = ROW_NUMBER;
+SELECT @n;
+
+DROP TABLE t;
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 6ca44311f45..6d1cb81c497 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -45,7 +45,8 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select,
                              IO_CACHE *buffer_file,
                              IO_CACHE *tempfile,
                              Bounded_queue<uchar, uchar> *pq,
-                             ha_rows *found_rows);
+                             ha_rows *found_rows,
+                             bool modify_m_current_row_for_warning);
 static bool write_keys(Sort_param *param, SORT_INFO *fs_info,
                       uint count, IO_CACHE *buffer_file, IO_CACHE *tempfile);
 static uint make_sortkey(Sort_param *param, uchar *to, uchar *ref_pos,
@@ -359,7 +360,8 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort,
                           &buffpek_pointers,
                           &tempfile, 
                           pq.is_initialized() ? &pq : NULL,
-                          &sort->found_rows);
+                          &sort->found_rows,
+                          filesort->modify_m_current_row_for_warning);
   if (num_rows == HA_POS_ERROR)
     goto err;
 
@@ -833,7 +835,8 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select,
                              IO_CACHE *buffpek_pointers,
                              IO_CACHE *tempfile,
                              Bounded_queue<uchar, uchar> *pq,
-                             ha_rows *found_rows)
+                             ha_rows *found_rows,
+                             bool modify_m_current_row_for_warning)
 {
   int error, quick_select;
   uint idx, indexpos;
@@ -906,6 +909,8 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select,
 
   for (;;)
   {
+    if (modify_m_current_row_for_warning)
+      thd->get_stmt_da()->inc_current_row_for_warning();
     if (quick_select)
       error= select->quick->get_next();
     else					/* Not quick-select */
@@ -996,6 +1001,8 @@ static ha_rows find_all_keys(THD *thd, Sort_param *param, SQL_SELECT *select,
     if (!write_record)
       file->unlock_row();
   }
+  if (modify_m_current_row_for_warning)
+    thd->get_stmt_da()->reset_current_row_for_warning(0);
 
   if (!quick_select)
   {
diff --git a/sql/filesort.h b/sql/filesort.h
index c0fe2478bae..0ce4f70dc46 100644
--- a/sql/filesort.h
+++ b/sql/filesort.h
@@ -73,9 +73,10 @@ public:
   /* Unpack temp table columns to base table columns*/
   void (*unpack)(TABLE *);
 
+  bool modify_m_current_row_for_warning;
 
   Filesort(ORDER *order_arg, ha_rows limit_arg, bool sort_positions_arg,
-           SQL_SELECT *select_arg):
+           SQL_SELECT *select_arg, bool flag):
     order(order_arg),
     limit(limit_arg),
     sortorder(NULL),
@@ -86,7 +87,8 @@ public:
     sort_positions(sort_positions_arg),
     set_all_read_bits(false),
     sort_keys(NULL),
-    unpack(NULL)
+    unpack(NULL),
+    modify_m_current_row_for_warning(flag)
   {
     DBUG_ASSERT(order);
   };
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 0403d6e73c3..e33e0d772fb 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -657,7 +657,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (query_plan.using_filesort)
   {
     {
-      Filesort fsort(order, HA_POS_ERROR, true, select);
+      Filesort fsort(order, HA_POS_ERROR, true, select, true);
       DBUG_ASSERT(query_plan.index == MAX_KEY);
 
       Filesort_tracker *fs_tracker=
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 16ad11ba7c1..92520765088 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4128,7 +4128,7 @@ JOIN::add_sorting_to_table(JOIN_TAB *tab, ORDER *order)
 {
   tab->filesort= 
     new (thd->mem_root) Filesort(order, HA_POS_ERROR, tab->keep_current_rowid,
-                                 tab->select);
+                                 tab->select, false);
   if (!tab->filesort)
     return true;
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5127096657f..1f0fb45a2d5 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -10960,7 +10960,7 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to,
 
       THD_STAGE_INFO(thd, stage_sorting);
       Filesort_tracker dummy_tracker(false);
-      Filesort fsort(order, HA_POS_ERROR, true, NULL);
+      Filesort fsort(order, HA_POS_ERROR, true, NULL, false);
 
       if (thd->lex->first_select_lex()->setup_ref_array(thd, order_num) ||
           setup_order(thd, thd->lex->first_select_lex()->ref_pointer_array,
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 539bea958e8..96e6bf12400 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -793,7 +793,7 @@ int mysql_update(THD *thd,
 	to update
         NOTE: filesort will call table->prepare_for_position()
       */
-      Filesort fsort(order, limit, true, select);
+      Filesort fsort(order, limit, true, select, true);
 
       Filesort_tracker *fs_tracker= 
         thd->lex->explain->get_upd_del_plan()->filesort_tracker;
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index 17920519b41..f6e6893ecca 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -3083,7 +3083,8 @@ bool Window_funcs_sort::setup(THD *thd, SQL_SELECT *sel,
     order->field= join_tab->table->field[0];
     sort_order= order;
   }
-  filesort= new (thd->mem_root) Filesort(sort_order, HA_POS_ERROR, true, NULL);
+  filesort= new (thd->mem_root) Filesort(sort_order, HA_POS_ERROR, true, NULL,
+                                         false);
 
   /* Apply the same condition that the subsequent sort has. */
   filesort->select= sel;