MDEV-14715: Assertion `!table || (!table->read_set... failed in Field_num::val_decimal

The assertion failure was caused by an incorrectly set read_set for
functions in the ORDER BY clause in part of a union, when we are using
a mergeable view and the order by clause can be skipped (removed).

An order by clause can be skipped if it's part of one part of the UNION as
the result set is not meaningful when multiple SELECT queries are UNIONed. The
server is aware of this optimization and tries to remove the order by
clause before JOIN::prepare. The problem is that we need to throw an
error when the ORDER BY clause contains invalid columns. To do this, we
attempt resolving the ORDER BY expressions, then subsequently drop them
if resolution succeeded. However, ORDER BY resolution had the side
effect of adding the expressions to the all_fields list, which is used
to construct temporary tables to store the result. We may be ignoring
the ORDER BY statement, but the tmp table still tried to compute the
values for the expressions, even if the columns are never used.

The assertion only shows itself if the order by clause contains members
which were not previously in the select list, and are part of a
function.

There is an additional question as to why this only manifests when using
VIEWS and not when using a regular table. The difference lies with the
"reset" of the read_set for the temporary table during
SELECT_LEX::update_used_tables() in JOIN::optimize(). The changes
introduced in fdf789a7eadf864ecc0e617f25f795fafda55026 cleared the
read_set when a mergeable view is encountered in the TABLE_LIST
defintion.

Upon initial order_list resolution, the table's read_set is updated
correctly. JOIN::optimize() will only reset the read_set if it
encounters a VIEW. Since we no longer have ORDER BY clause in
JOIN::optimize() we never get to correctly update the read_set again.

Other relevant commit by Timour, which first introduced the order
resolution when we "can_skip_sort_order":
883af99e7dac91e3f258135a2053e6b8e3c05fc3

Solution:
Don't add the resolved ORDER BY elements to all_fields. We only resolve
them to check if an error should be returned for the query. Ignore them
completely otherwise.

1 files changed, 37 insertions, 0 deletions

diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index fe2339db471..83d889b7b73 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1995,4 +1995,41 @@ avg(f)	sub
 31.5000	0
 1.5000	1
 drop table t1,t2,t3;
+#
+# MDEV-14715 Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))'
+# failed in Field_num::val_decimal
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1, NULL),(3, 4);
+(SELECT a, sum(a) AS f FROM v1 group by a ORDER BY b + sum(a))
+UNION
+(SELECT 2, 2);
+ERROR HY000: Invalid use of group function
+(SELECT a, sum(a) AS f FROM v1 group by a ORDER BY b + 1)
+UNION
+(SELECT 2, 2);
+a	f
+1	1
+3	3
+2	2
+SELECT a, b FROM t1
+UNION
+(SELECT a, VAR_POP(a) AS f FROM v1 GROUP BY a ORDER BY b/a );
+a	b
+1	NULL
+3	4
+1	0
+3	0
+DROP TABLE t1;
+(SELECT a, sum(a) AS f FROM v1 group by a ORDER BY b + 1)
+UNION
+(SELECT 2, 2);
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+DROP VIEW v1;
+(SELECT a, sum(a) AS f FROM v1 group by a ORDER BY b + 1)
+UNION
+(SELECT 2, 2);
+ERROR 42S02: Table 'test.v1' doesn't exist
 End of 5.5 tests