Description:
Hi all：
recently，we find a issue , like this, 2 nodes mgr, do like:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select test.gr_member_in_primary_partition2();
+----------------------------------------+
| test.gr_member_in_primary_partition2() |
+----------------------------------------+
| YES |
+----------------------------------------+
1 row in set (0.01 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select test.gr_member_in_primary_partition2();
ERROR 1242 (21000): Subquery returns more than 1 row
function is:
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `gr_member_in_primary_partition2`() RETURNS varchar(3) CHARSET utf8mb4
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats rgms USING(member_id) where rgms.MEMBER_ID=@@SERVER_UUID);
END ;;
DELIMITER ;
we find this diff:
error scenario no join condition :
899 T@25: | | | | | | | | | | | | | | | >optimize_cond
900 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
901 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct
902 T@25: | | | | | | | | | | | | | | | | opt: condition: "WHERE"
903 T@25: | | | | | | | | | | | | | | | | opt: original_condition: "(`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4))"
904 T@25: | | | | | | | | | | | | | | | | opt: steps: starting struct
905 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
906 T@25: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation"
907 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
908 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
909 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
910 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
911 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
912 T@25: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation"
913 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
914 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
915 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
916 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
917
918 WHERE:(after const change) 0x7fff98459750 multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)
919 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct
920 T@25: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal"
921 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
922 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
923 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)"
924 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
925 T@25: | | | | | | | | | | | | | | | | opt: steps: ending struct
926 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct
927 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct
928 T@25: | | | | | | | | | | | | | | | <optimize_cond
normal scenario:
891 T@27: | | | | | | | | | | | | | | | >optimize_cond
892 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
893 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct
894 T@27: | | | | | | | | | | | | | | | | opt: condition: "WHERE"
895 T@27: | | | | | | | | | | | | | | | | opt: original_condition: "((`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4)) and (`performance_schema`.`replication_group_mem bers`.`MEMBER_ID` = `performance_schema`.`rgms`.`MEMBER_ID`))"
896 T@27: | | | | | | | | | | | | | | | | opt: steps: starting struct
897 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
898 T@27: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation"
899 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
900 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
901 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_ group_members`.`MEMBER_ID`))"
902 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
903 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
904 T@27: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation"
905 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
906 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
907 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_ group_members`.`MEMBER_ID`))"
908 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
909
910 WHERE:(after const change) 0xc478f10 (multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_group_members`.`MEMBER_ID`))
911 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct
912 T@27: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal"
913 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct
914 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct
915 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_g roup_members`.`MEMBER_ID`)"
916 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
917 T@27: | | | | | | | | | | | | | | | | opt: steps: ending struct
918 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct
919 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct
920 T@27: | | | | | | | | | | | | | | | <optimize_cond
And get_optimizable_join_conditions function table_list in error scenario is ：
table_name = 0x621c750 "", alias = 0x61cfa85 "(nest_last_join)"
normal scenario is :
"performance_schema", table_name = 0xca88820 "replication_group_member_stats"
Is The parse tree problem? I think flush tables relate "nest_last_join" "table".
Thanks !
How to repeat:
like Description
Description: Hi all： recently，we find a issue , like this, 2 nodes mgr, do like: mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select test.gr_member_in_primary_partition2(); +----------------------------------------+ | test.gr_member_in_primary_partition2() | +----------------------------------------+ | YES | +----------------------------------------+ 1 row in set (0.01 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select test.gr_member_in_primary_partition2(); ERROR 1242 (21000): Subquery returns more than 1 row function is: DELIMITER ;; CREATE DEFINER=`root`@`localhost` FUNCTION `gr_member_in_primary_partition2`() RETURNS varchar(3) CHARSET utf8mb4 BEGIN RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats rgms USING(member_id) where rgms.MEMBER_ID=@@SERVER_UUID); END ;; DELIMITER ; we find this diff: error scenario no join condition : 899 T@25: | | | | | | | | | | | | | | | >optimize_cond 900 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct 901 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct 902 T@25: | | | | | | | | | | | | | | | | opt: condition: "WHERE" 903 T@25: | | | | | | | | | | | | | | | | opt: original_condition: "(`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4))" 904 T@25: | | | | | | | | | | | | | | | | opt: steps: starting struct 905 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct 906 T@25: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation" 907 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 908 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 909 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)" 910 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct 911 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct 912 T@25: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation" 913 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 914 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 915 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)" 916 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct 917 918 WHERE:(after const change) 0x7fff98459750 multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`) 919 T@25: | | | | | | | | | | | | | | | | opt: (null): starting struct 920 T@25: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal" 921 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 922 T@25: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 923 T@25: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`)" 924 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct 925 T@25: | | | | | | | | | | | | | | | | opt: steps: ending struct 926 T@25: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct 927 T@25: | | | | | | | | | | | | | | | | opt: (null): ending struct 928 T@25: | | | | | | | | | | | | | | | <optimize_cond normal scenario: 891 T@27: | | | | | | | | | | | | | | | >optimize_cond 892 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct 893 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: starting struct 894 T@27: | | | | | | | | | | | | | | | | opt: condition: "WHERE" 895 T@27: | | | | | | | | | | | | | | | | opt: original_condition: "((`performance_schema`.`rgms`.`MEMBER_ID` = convert( using utf8mb4)) and (`performance_schema`.`replication_group_mem bers`.`MEMBER_ID` = `performance_schema`.`rgms`.`MEMBER_ID`))" 896 T@27: | | | | | | | | | | | | | | | | opt: steps: starting struct 897 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct 898 T@27: | | | | | | | | | | | | | | | | opt: transformation: "equality_propagation" 899 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 900 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 901 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_ group_members`.`MEMBER_ID`))" 902 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct 903 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct 904 T@27: | | | | | | | | | | | | | | | | opt: transformation: "constant_propagation" 905 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 906 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 907 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "(multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_ group_members`.`MEMBER_ID`))" 908 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct 909 910 WHERE:(after const change) 0xc478f10 (multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_group_members`.`MEMBER_ID`)) 911 T@27: | | | | | | | | | | | | | | | | opt: (null): starting struct 912 T@27: | | | | | | | | | | | | | | | | opt: transformation: "trivial_condition_removal" 913 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: starting struct 914 T@27: | | | | | | | | | | | | | | | | opt: subselect_evaluation: ending struct 915 T@27: | | | | | | | | | | | | | | | | opt: resulting_condition: "multiple equal(convert( using utf8mb4), `performance_schema`.`rgms`.`MEMBER_ID`, `performance_schema`.`replication_g roup_members`.`MEMBER_ID`)" 916 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct 917 T@27: | | | | | | | | | | | | | | | | opt: steps: ending struct 918 T@27: | | | | | | | | | | | | | | | | opt: condition_processing: ending struct 919 T@27: | | | | | | | | | | | | | | | | opt: (null): ending struct 920 T@27: | | | | | | | | | | | | | | | <optimize_cond And get_optimizable_join_conditions function table_list in error scenario is ： table_name = 0x621c750 "", alias = 0x61cfa85 "(nest_last_join)" normal scenario is : "performance_schema", table_name = 0xca88820 "replication_group_member_stats" Is The parse tree problem? I think flush tables relate "nest_last_join" "table". Thanks ! How to repeat: like Description