Is this a valid SQL conditional expression or a MySQL bug (feature)?

Question

Trying to debug some joins which seemed to returning cartesian products I typed the equivalent ON condition into selects.

In MySQL

select * 
from table 
where columnname

seems to behave as though I typed where columname is not null. In joins typing on table.columnname is accepted but returns LOTS of rows. MySQL does the right thing if I correct it to on table1.column=table2.column but surely my first version was incorrect and illegal.

Answer 1

The contexts you are talking about, the WHERE clause and the ON clause in a join, simply accept an expression.

SELECT ...
FROM table1 JOIN table2 ON <expr>
WHERE <expr>

Expressions can include comparison operators like = but expressions can also be as simple as a single column or a single constant value.

Compare with another context that accepts an expression: the select-list.

SELECT <expr>, <expr>, <expr>

It's normal to use a single column as the expression in this context.

Answer 2

Most programming languages consider all values other than null, 0, '', false as true. while(1); is an infinite loop irrespective of whether you give 1,2,'a',true and so on. I would say its a default expected behaviour and I have often used similar where clause