this probalby needs a new coercions role that is similar to IN impl. Also any_() all_() should accept any SELECT statement just like in_() does. Adding a method like .scalar() to the Values construct, with a datatype that's Tuple(<types>), would be a start.

Actually also the IN case does not work as expected. It's coerced to an hybrid between a tolumn valued and a subquery. I've updated the example above

if we want to use the same construct, and I'm not sure we should, I think .scalar should generate another type, that's not a from element.

Then I guess we can decide if in and any/all should coerce using this .scalar or raise

any_() / all_() should coerce to this .scalar() thing because that's what in_() does right now as well:

https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/coercions.py#L555

ok, but it does not really work that coercion in this case. I've corrected the sql above, but it renders this sql

SELECT bar
FROM t, (VALUES (:param_1), (:param_2), (:param_3))
WHERE bar IN (SELECT bar
FROM (VALUES (:param_4), (:param_5), (:param_6)))

we need a new coercion role to Do The Right Thing on this one.

I think I get the gist, I'll try giving a go after the two other regressions.

Values.scalar() will returns a new object that's called something like ScalarValue that is not a from element but a column/clause element. does it make sense?

yeah somehtiugn like. this one seemed like it didnt have any clear way to do it right now so if we get something up we can tweak it

I'm not sure this can be generalized.
On postgres at least this is very delicate: this query works

-- 1
SELECT bar
FROM t
WHERE bar IN (SELECT anon_2 FROM (VALUES (1), (2), (42)) as anon_1(anon_2));

SELECT bar
FROM t
WHERE bar = ANY(SELECT anon_2 FROM (VALUES (1), (2), (42)) as anon_1(anon_2)) -- or s/ANY/ALL/

this instead complain that it needs cast

-- 2
SELECT bar
FROM t
WHERE bar IN (SELECT anon_1 FROM (VALUES (1), (2), (42)) as anon_1);

SELECT bar
FROM t
WHERE bar = ANY(SELECT anon_1 FROM (VALUES (1), (2), (42)) as anon_1) -- or s/ANY/ALL/

any/all behave the same as in. so 1 ok, 2 not ok.

Calling scalar should instead render as:

SELECT bar
FROM t
WHERE bar IN (VALUES (1), (2), (42));

SELECT bar
FROM t
WHERE bar = ANY(VALUES (1), (2), (42)) -- or s/ANY/ALL/

but you mean to be using ALL/ANY in those sql examples right

they actually work the same with both. Updated examples above

Federico Caselli has proposed a fix for this issue in the main branch:

Implement ScalarValue https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4183