Bug #116033 TWO const VIEW right join's result incorrect

• Submitted: 7 Sep 7:01
• Modified: 9 Sep 17:22
• Reporter: haizhen xue
• Status: Analyzing
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[7 Sep 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)