Bug #116033 TWO const VIEW right join's result incorrect
Submitted: 7 Sep 2024 7:01 Modified: 24 Oct 2024 11:53
Reporter: haizhen xue Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[7 Sep 2024 7:01] haizhen xue
Description:
Two view's define below:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t0_range_key_subpartition_sub_view` AS select 1 AS `c_id`,1 AS `c_d_id`,1 AS `c_w_id`,1 AS `c_first`,1 AS `c_middle`,1 AS `c_last`,1 AS `c_street_1`,1 AS `c_street_2`,1 AS `c_city`,1 AS `c_state`,1 AS `c_zip`,1 AS `c_phone`,1 AS `c_since`,1 AS `c_credit`,1 AS `c_credit_lim`,1 AS `c_discount`,1 AS `c_balance`,1 AS `c_ytd_payment`,1 AS `c_payment_cnt`,1 AS `c_delivery_cnt`,1 AS `c_data`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t0_t10_sub_1` AS select 1 AS `c_d_id`,1 AS `c_id`,1 AS `c_middle`,1 AS `c_street_1`,1 AS `c_credit`,1 AS `c_w_id`;

SQL statement's result is wrong, expect empty,but result is 0.
mysql> select 0 from(select 8 c0 from t0_range_key_subpartition_sub_view) subq_2 right join t0_t10_sub_1 on 0;
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.01 sec)

How to repeat:
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t0_range_key_subpartition_sub_view` AS select 1 AS `c_id`,1 AS `c_d_id`,1 AS `c_w_id`,1 AS `c_first`,1 AS `c_middle`,1 AS `c_last`,1 AS `c_street_1`,1 AS `c_street_2`,1 AS `c_city`,1 AS `c_state`,1 AS `c_zip`,1 AS `c_phone`,1 AS `c_since`,1 AS `c_credit`,1 AS `c_credit_lim`,1 AS `c_discount`,1 AS `c_balance`,1 AS `c_ytd_payment`,1 AS `c_payment_cnt`,1 AS `c_delivery_cnt`,1 AS `c_data`;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t0_t10_sub_1` AS select 1 AS `c_d_id`,1 AS `c_id`,1 AS `c_middle`,1 AS `c_street_1`,1 AS `c_credit`,1 AS `c_w_id`;
Query OK, 0 rows affected (0.01 sec)

mysql> select 0 from(select 8 c0 from t0_range_key_subpartition_sub_view) subq_2 right join t0_t10_sub_1 on 0;
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.01 sec)
[24 Oct 2024 11:53] Roy Lyseng
Posted by developer:
 
This is not a bug.
Here we have two views, each view returns one row.
For a RIGHT JOIN, and with no WHERE clause, a query will always
return at least one row for each row in the right-hand table,
regardless of the value of the join ON clause.