[MDEV-23096] DEFAULT() doesn't behave as documented Created: 2020-07-05  Updated: 2020-08-17  Resolved: 2020-08-17

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.5.5
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None


 Description   

Documentation for default function says:

"If the column has no default value, NULL is returned."

https://mariadb.com/kb/en/default/

A test shows that:

  1. [snippet 1] If the column has no default and it is NULLable and the table is empty, no row is returned, even if we are also selecting another expression.
  2. [snippet 2] If the column has no default value and it's not NULLable, an error is returned.

MariaDB [test]> CREATE OR REPLACE TABLE t (x INT NULL, y INT NOT NULL);
 
Query OK, 0 rows affected (0.013 sec)
 
 
 
MariaDB [test]> SELECT DEFAULT(x), 1 FROM t;
 
Empty set (0.001 sec)

This looks like a bug to me.

MariaDB [test]> SELECT @@sql_mode;
 
+------------+
 
| @@sql_mode |
 
+------------+
 
|            |
 
+------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> SELECT DEFAULT(y) FROM t;
 
ERROR 1364 (HY000): Field 'y' doesn't have a default value

This makes sense to me because no default != NULL, but in that case the docs should be adjusted.

 Comments   
Comment by Ian Gilfillan [ 2020-08-17 ]

The first issue doesn't appear to be a bug, as even with a single expression, no values will be returned as there are no rows:

CREATE OR REPLACE TABLE t (x INT NULL, y INT NOT NULL);
 
Query OK, 0 rows affected (0.054 sec)
 
 
 
mysql [localhost] {msandbox} (test) > SELECT 1 FROM t;
 
Empty set (0.001 sec)

The 2nd issue has been fixed in the docs. It was changed 7 years ago, but the contributor did not distinguish between NULL and NOT NULL fields. I've expanded the examples to hopefully demonstrate this.

Generated at Thu Feb 08 09:19:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.