Bug #71286 Get DEFAULT values ​​of columns in a SELECT with Subquery
Submitted: 3 Jan 2014 17:46 Modified: 7 Jan 2014 10:43
Reporter: William Chiquito Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.15 OS:Any
Assigned to:
Tags: columns, DEFAULT, SELECT

[3 Jan 2014 17:46] William Chiquito
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)
[3 Jan 2014 17:47] William Chiquito
Change version.
[7 Jan 2014 10:43] Umesh Shastry
Hello William,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh