Bug #13939 extension to SQL syntax: SELECT DEFAULT_IF_NULL
Submitted: 11 Oct 2005 20:55 Modified: 15 Dec 2010 11:15
Reporter: SweetAs Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:n/a OS:Any (n/a)
Assigned to: CPU Architecture:Any

[11 Oct 2005 20:55] SweetAs
at present, DEFAULT applies to insertion of data rather than on SELECT.

if you apply a new default value, to a col on a table that already contains data, the existing rows will not contain the default value.

an SELECT DEFAULT_IF_NULL would return the default value of a col in the instance that the field contained no data.

the same effect could be achieved with something like SELECT COALESCE(DEFAULT(colname),colname), but if i had a hundred cols i wanted this to apply too it would mean typing this out a hundred times.


How to repeat:

Suggested fix:
[15 Dec 2010 11:15] Valeriy Kravchuk
Makes some sense, when many columns are selected (or as SQL Server compatibility feature). But for most cases workarounds like mentioned or IFNULL(col1, DEFAULT(col1)) will work.