> In other words, it might be a correctness bug in other systems if they
run your query as a hash join.
Thanks for the reply. But for PostgreSQL, this query produces a nested-loop
join:
# explain select * from t11 join t22 on t11.id = floor(random() * 9) +
t22.id;
> Do you mean something like this:
> select * from t1 join (select floor(random()*9) + id as x from t2) m on
t1.id = m.x ?
> Yes, that works.
Yes.
> But that raise another question: theses two queries seem semantically
equivalent,
> yet we treat them differently: one raises an analysis
> For that, you can add a table subquery and do it in the select list.
Do you mean something like this:
select * from t1 join (select floor(random()*9) + id as x from t2) m on t1.id =
m.x ?
Yes, that works. But that raise another question: theses two queries seem
semantically equivalent, yet
Per SQL spec, I think your join query can only be run as a NestedLoopJoin
or CartesianProduct. See page 241 in SQL-99 (
http://web.cecs.pdx.edu/~len/sql1999.pdf).
In other words, it might be a correctness bug in other systems if they run
your query as a hash join.
> Here the purpose of adding a
