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