Press "Enter" to skip to content

Order of Operations and the Unary Negative Operator

Published 2020-05-19 by Kevin Feasel

Kenneth Fisher takes us through a weird problem:

Last but certainly not least our problem child.

SELECT -100.0/-100.0*10.0

In the absence of parenthesis and nothing but multiplication and division it should go left to right. Right? But that would give us -100.0/-100.0 returning 1.0 and finally 1.0*10.0 giving us 10.0. But that’s not what we are getting. Somehow we are ending up with 0.1.

Read the comments, where Joe Celko explains the reasoning behind this strange behavior.

I tried this out on dbfiddle and here were some quick results:

  • Postgres 12 — 10.0
  • Oracle 18c (had to query from dual) — 10
  • MySQL 8.0 — 10
  • SQLite 3.27 — 10
  • SQL Server — 0.1

So it’s definitely not the case everywhere. The thing I noticed for SQL Server is that they don’t call out unary operators in the operator precedence guide, as opposed to, say, Oracle.

Published in Syntax