Unable to use result of a "subquery in select clause" in a "insert.. select.. on duplicate update" query

Possible Unanswered Duplicate Question: Insert select - using selected value in ON DUPLICATE KEY UPDATE

I am not able to use a subquery result column in the select clause as a value to update in a insert into ....(select... ) update on duplicate query.

Getting Error Code: 1054. Unknown column in 'field list' error.

Attached reduced and simple repro steps.

My original case involves 15 columns and too many complicated JSON_TABLEs. I have a work around that repeats the whole subquery again in the update clause, but that doubles the query execution time compared to omitting that column and updating.
drop table if exists t1;
create table t1 (a int primary key auto_increment, b int);
drop table if exists t2;
create table t2 (c int primary key auto_increment, d int);

insert into t1 (a, b) values (null, 0);
insert into t1 (a, b) values (null, 1);
insert into t1 (a, b) values (null, 2);
insert into t1 (a, b) values (null, 3);
insert into t1 (a, b) values (null, 4);
insert into t1 (a, b) values (null, 5);
insert into t1 (a, b) values (null, 6);
insert into t1 (a, b) values (null, 7);
insert into t1 (a, b) values (null, 8);
insert into t1 (a, b) values (null, 9);

insert into t2 (c, d) values (null, 0);

--doesnt work
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = t1.b1;

--doesnt work
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = values(b1);

--work around that takes twice as much time
insert into t2 (c, d)
select a, (select 1) as b1 from t1
on duplicate key update
d = (select 1);


insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = t.b1;

There are 2 tables in this query: t2 (in UPDATE clause) and t1 (in FROM clause). You refer to t.b1 - but there is no t table alias in the query text.

Correct variants:

insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
-- d = t1.b1; -- this should be used when the column is used
d = 1; -- literal is used as a source value, so it may be simply repeated

insert into t2 (c, d) 
select a, (select 1) as b1 from t1 AS t
on duplicate key update
-- d = t.b1;
d = 1;

insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = values(b1);

The description of the VALUES() explicitly tells:

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

I.e. an argument of the function is a name of a column which the value should be inserted into. The query is insert into t2 - so only the column of the table t2 can be an argument of VALUES(). You use values(b1) - but there is no b1 column in t2.

Correct variant:

insert into t2 (c, d) 
select a, (select 1) as b1 from t1
on duplicate key update
d = values(d);

StackExchange Database Administrators Q#319940, answer score: 3