Bug #60719 Field info in EXPLAIN EXT./GROUP BY WITH ROLLUP says NOT NULL and contains NULL

• Submitted: 1 Apr 2011 8:40
• Modified: 11 Feb 2018 13:27
• Reporter: - -
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5.10, 5.5.9
• OS: Any
• CPU Architecture: Any
• Tags: explain, extended, null

[1 Apr 2011 8:40] - -

Description:
If we run the program from How to repeat, we'll get

Columns:
	server	[AllowDBNull=False]
	cnt	[AllowDBNull=False]
-----------------------------------
Rows:
	test1		1
	test2		2
	Null		2

i. e. column server cannot be null, but contains null. 

The following query 

EXPLAIN EXTENDED
          SELECT 'test1' server, 1 cnt
UNION ALL SELECT 'test2',        2;

will show similar problem, but for explain extended and column filtered. 

How to repeat:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Common;

namespace TestApp
{
    class Program
    {
        static void Main(string[] args)
        {
            object value = null;
            string sql = @"SELECT q.server, q.cnt
              FROM (
                            SELECT 'test1' server, 1 cnt
                  UNION ALL SELECT 'test2',        2
                ) q
              GROUP BY q.server WITH ROLLUP";

            MySqlConnection con = new MySqlConnection("Server = localhost; port = 3306; uid = root; pwd = root;");
            con.Open();

            MySqlCommand cmd = new MySqlCommand(sql, con);
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                DataTable schema = reader.GetSchemaTable();

                Console.WriteLine("Columns:");
                foreach (DataRow row in schema.Rows)
                    Console.WriteLine("\t{0}\t[AllowDBNull={1}]", row[SchemaTableColumn.ColumnName], row[SchemaTableColumn.AllowDBNull]);

                Console.WriteLine("-----------------------------------");
                Console.WriteLine("Rows:");
                while (reader.Read())
                {

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        value = reader[i];

                        if (value == DBNull.Value)
                            value = "Null";

                        Console.Write("\t" + value + "\t");
                    }

                    Console.WriteLine();
                }
            }

            Console.ReadKey(); ;
        }
    }
}

[1 Apr 2011 8:57] Valeriy Kravchuk

Verified on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT q.server, q.cnt
    ->               FROM (
    ->                             SELECT 'test1' server, 1 cnt
    ->                   UNION ALL SELECT 'test2',        2
    ->                 ) q
    ->               GROUP BY q.server WITH ROLLUP;
Field   1:  `server`
Catalog:    `def`
Database:   ``
Table:      `q`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     15
Max_length: 5
Decimals:   0
Flags:      NOT_NULL

Field   2:  `cnt`
Catalog:    `def`
Database:   ``
Table:      `q`
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 1
Decimals:   0
Flags:      NOT_NULL NUM

+--------+-----+
| server | cnt |
+--------+-----+
| test1  |   1 |
| test2  |   2 |
| NULL   |   2 |
+--------+-----+
3 rows in set (0.05 sec)

[11 Feb 2018 13:27] Roy Lyseng

Posted by developer:
 
Closing bug as it has been suspended for more than 6 years.