Description:
When I try to get the DEFAULT values of columns defined as NOT NULL in a table with no rows, via a subquery, I get NULL as a result.
How to repeat:
*** MySQL 5.5.35 ***
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`membership_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0,
`product_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=INNODB;
SELECT
DEFAULT(`users`.`membership_credits`) `membership_credits`,
DEFAULT(`users`.`product_credits`) `product_credits`
FROM (SELECT *, COUNT(0)
FROM `users`) `users`;
Result:
+--------------------+-----------------+
| membership_credits | product_credits |
+--------------------+-----------------+
| 0 | 0 |
+--------------------+-----------------+
1 rows in set (0.00 sec)
*** MySQL 5.6.15 ***
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`membership_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0,
`product_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=INNODB;
SELECT
DEFAULT(`users`.`membership_credits`) `membership_credits`,
DEFAULT(`users`.`product_credits`) `product_credits`
FROM (SELECT *, COUNT(0)
FROM `users`) `users`;
Result:
+--------------------+-----------------+
| membership_credits | product_credits |
+--------------------+-----------------+
| NULL | NULL |
+--------------------+-----------------+
1 rows in set (0.00 sec)
Description: When I try to get the DEFAULT values of columns defined as NOT NULL in a table with no rows, via a subquery, I get NULL as a result. How to repeat: *** MySQL 5.5.35 *** DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `membership_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0, `product_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=INNODB; SELECT DEFAULT(`users`.`membership_credits`) `membership_credits`, DEFAULT(`users`.`product_credits`) `product_credits` FROM (SELECT *, COUNT(0) FROM `users`) `users`; Result: +--------------------+-----------------+ | membership_credits | product_credits | +--------------------+-----------------+ | 0 | 0 | +--------------------+-----------------+ 1 rows in set (0.00 sec) *** MySQL 5.6.15 *** DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `membership_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0, `product_credits` INT(10) UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=INNODB; SELECT DEFAULT(`users`.`membership_credits`) `membership_credits`, DEFAULT(`users`.`product_credits`) `product_credits` FROM (SELECT *, COUNT(0) FROM `users`) `users`; Result: +--------------------+-----------------+ | membership_credits | product_credits | +--------------------+-----------------+ | NULL | NULL | +--------------------+-----------------+ 1 rows in set (0.00 sec)