What does select * from table where column * 'value'; do?

Question

select * from table where column * 'value';

I accidentally wrote * for my where statement instead of =, and the query returned all rows in the table.

Made me wonder why an error wasn't thrown instead.

What does the * operator do in a where statement?

Answer 1

The SQL

WHERE column * value

is a numeric operation of multiplication, and when the value of the expression (column * value) isn't 0, that is equivalent to TRUE so will return the row.

Answer 2

SELECT * FROM <MY_TABLE> WHERE <COL_NAME> * 1;

Although this syntax works for MySQL 8.0 and SQLite 3.8 you will get an error on mostly all other RDBMS.

SQL Server 2017

An expression of non-boolean type specified in a context where a condition is expected, near ';'.

PostgreSQL 11

_{For integer columns:}

ERROR: argument of WHERE must be type boolean, not type integer

_{For text/varchar columns}

ERROR: operator does not exist: character varying * integer

Oracle 18c

ORA-00920: invalid relational operator