Using row_number() over (partition by) as a subquery in MySQL, conducting an outer join with other tables, no data is returned but "0 row(s) modified"

Question

The scenario is as follows: I need to query the table of active users and retrieve the data for each person ranked first. The SQL statement is provided below.

select
    au.id,
    p.user_code,
    au.user_code,
    p.user_code = au.user_code,
    au.row_num 
    -- The original SQL had additional select fields, but they were not important, so I removed them. These few fields were written for testing purposes, and of course, they can be replaced with other fields if needed.
from
    (
    select
        *
    from 
        (
        select
            *,
            row_number() over (partition by user_code
        order by
            status ) as row_num
        from
                 
                epm_eval_activity_user sr
        
        )aa
    where
        aa.row_num <= 1
    ) au
inner join epm_user p on
    au.user_code = p.user_code
where
    1 = 1
    and au.user_code = '764451'

However, the result is unexpected. The subquery returns data, and the outer user table also contains data. But when using inner join, no data is returned. To be precise, it returns "0 row(s) modified". It's not an empty table that typically indicates no results. I tried using EXPLAIN, but it also returned "0 row(s) modified".

Then I tried replacing "where au.user_code='xxxx'" with "where p.user_code='xxxx'", and the data was returned correctly. Why is this happening?

And the subquery can retrieve the correct data when executed separately, and it is also confirmed that there is corresponding data in epm_user.

-- can retrieve data
    select
        *
    from 
        (
        select
            *,
            row_number() over (partition by user_code
        order by
            status ) as row_num
        from
                 
                epm_eval_activity_user sr
        
        )aa
    where
        aa.row_num >= 0

Finally, I tried changing the filtering condition to "p.user_code", but even this couldn't explain why the previous SQL query couldn't retrieve the data correctly.

and the mysql version is 8.0.22;

---

This is the DDL statement of the two tables above(Omitted other unrelated fields.). If you are interested, you can try to reproduce this phenomenon yourself.

CREATE TABLE `epm_eval_activity_user` (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ID',
  `user_code` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'userCode',
  `user_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'name',
  `status` int DEFAULT '1' COMMENT '1=prepare,2=fail,3=ok,4=...',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='user eval activity';

-- evaluation.epm_user definition

CREATE TABLE `epm_user` (
  `id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'id',
  `user_code` varchar(120) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'userCode',
  `user_name` varchar(120) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'name',
  `creator` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_tm` datetime DEFAULT NULL,
  `updator` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_tm` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_epm_user_user_code` (`user_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='user';