Bug in field creation when using CREATE TABLE with a derived table ?

Hi,

I'm wondering if the following are bug, and if not how fields
type/length/NULL/NOT NULL are chosen.

How-to-repeat :

CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;

mysql> SHOW CREATE TABLE s;
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
| s | CREATE TABLE `s` (
`a` bigint(1) NOT NULL default '0',
`(SELECT 1)` bigint(1) default NULL
) TYPE=MyISAM CHARSET=latin1 |
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
1 row in set (0.00 sec)

Why does the second field is defined as default NULL ?

CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT a)) a;

mysql> SHOW CREATE TABLE s;
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
| s | CREATE TABLE `s` (
`a` bigint(1) NOT NULL default '0',
`(SELECT a)` bigint(0) default NULL
) TYPE=MyISAM CHARSET=latin1 |
+-------+--------------------------------------------------- ----------------
----------------------------------------------------------+
1 row in set (0.00 sec)

Why in this case a bigint(0) (??) with default NULL

CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;

mysql> SHOW CREATE TABLE s;
+-------+--------------------------------------------------- ----------------
------------------------------------------------------------ -+
| Table | Create Table
|
+-------+--------------------------------------------------- ----------------
------------------------------------------------------------ -+
| s | CREATE TABLE `s` (
`a` bigint(1) NOT NULL default '0',
`(SELECT a+0)` bigint(17) default NULL
) TYPE=MyISAM CHARSET=latin1 |
+-------+--------------------------------------------------- ----------------
------------------------------------------------------------ -+
1 row in set (0.00 sec)

Why a bigint(17) this time ?

Regards,
Jocelyn


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13236@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in field creation when using CREATE TABLE with a derived table ?

Hi!

On Fri, Dec 06, 2002 at 08:40:44PM -0000, Jocelyn Fournier wrote:
> Hi,
>
> I'm wondering if the following are bug, and if not how fields
> type/length/NULL/NOT NULL are chosen.
>
> How-to-repeat :
>
> CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT 1)) a;
>
> mysql> SHOW CREATE TABLE s;
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> | Table | Create Table
> |
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> | s | CREATE TABLE `s` (
> `a` bigint(1) NOT NULL default '0',
> `(SELECT 1)` bigint(1) default NULL
> ) TYPE=MyISAM CHARSET=latin1 |
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> Why does the second field is defined as default NULL ?
>
> CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT a)) a;
>
> mysql> SHOW CREATE TABLE s;
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> | Table | Create Table
> |
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> | s | CREATE TABLE `s` (
> `a` bigint(1) NOT NULL default '0',
> `(SELECT a)` bigint(0) default NULL
> ) TYPE=MyISAM CHARSET=latin1 |
> +-------+--------------------------------------------------- ----------------
> ----------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> Why in this case a bigint(0) (??) with default NULL
>
> CREATE TABLE s SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a;
>
> mysql> SHOW CREATE TABLE s;
> +-------+--------------------------------------------------- ----------------
> ------------------------------------------------------------ -+
> | Table | Create Table
> |
> +-------+--------------------------------------------------- ----------------
> ------------------------------------------------------------ -+
> | s | CREATE TABLE `s` (
> `a` bigint(1) NOT NULL default '0',
> `(SELECT a+0)` bigint(17) default NULL
> ) TYPE=MyISAM CHARSET=latin1 |
> +-------+--------------------------------------------------- ----------------
> ------------------------------------------------------------ -+
> 1 row in set (0.00 sec)

Thank you for bug report. I fix this bug, patch is committed recently.

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13629@lists.mysql.com
To unsubscribe, e-mail