Posted about 1 month ago. Visible to the public. Repeats.

Don't sum up columns with + in a SQL query if NULL-values can be present.

Don't sum up columns with + in a sql-query if NULL-Values can be present.

MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL.

MySQL:

Copy
mysql> select 1 + 2 + 3; +-----------+ | 1 + 2 + 3 | +-----------+ | 6 | +-----------+ 1 row in set (0,00 sec) mysql> select 1 + NULL + 3; +--------------+ | 1 + NULL + 3 | +--------------+ | NULL | +--------------+ 1 row in set (0,00 sec)

Postgres:

Copy
test_database=# select 1 + 2 + 3; ?column? ---------- 6 (1 row) test_database=# select 1 + NULL + 3; ?column? ---------- (1 row)

If you want to do additions in the database:

  • use SUM if it's an option to sum up a column of a result set instead of expressions (SUM ignores NULL values)
  • wrap columns with: COALESCE(column, 0)(COALESCE takes the first non-null argument)

Does your version of Ruby on Rails still receive security updates?
Rails LTS provides security patches for old versions of Ruby on Rails (3.2 and 2.3).

Author of this card:

Avatar
Natalie Krehan
Last edit:
about 1 month ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Natalie Krehan to makandra dev