Bug #88109 subselect + pseudo column breaks table output format for select with rollup

• Submitted: 16 Oct 2017 11:10
• Modified: 16 Oct 2017 14:02
• Reporter: Arthur Tokarchuk
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.7.19
• OS: Any
• CPU Architecture: Any

[16 Oct 2017 11:10] Arthur Tokarchuk

Description:
If column title and values text width is less than 4 symbols, table output breaks under some conditions.

1) subselect is used,
2) expression rather than a table column is used.

How to repeat:
create table t1(a int);
insert into t1 values(1);

Right:

select
    a,
    count(*)
from
    t1
group by
    1
with
    rollup;

+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        1 |
| NULL |        1 |
+------+----------+
2 rows in set (0.00 sec)

Wrong:

select
    b,
    count(*)
from
    (
        select
            a,
            count(*) b
        from
            t1
        group by
            1
    ) t
group by
    1
with
    rollup;

+---+----------+
| b | count(*) |
+---+----------+
| 1 |        1 |
| NULL |        1 |
+---+----------+
2 rows in set (0.00 sec)

But subselect itself does not seem to affect this.

Right:

select
    a,
    count(*)
from
    (
        select
            a
        from
            t1
    ) t
group by
    1
with
    rollup;

+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        1 |
| NULL |        1 |
+------+----------+
2 rows in set (0.00 sec)

But if a constant is selected rather than a column, it breaks again:

select
    b,
    count(*)
from
    (
        select
            1 b
        from
            t1
    ) t
group by
    1
with
    rollup;

+---+----------+
| b | count(*) |
+---+----------+
| 1 |        1 |
| NULL |        1 |
+---+----------+
2 rows in set (0.00 sec)

A constant without subselect does not break formatting.

select
    1 b,
    count(*)
from
    t1
group by
    1
with
    rollup;
    
+------+----------+
| b    | count(*) |
+------+----------+
|    1 |        1 |
| NULL |        1 |
+------+----------+
2 rows in set (0.00 sec)

[16 Oct 2017 12:14] MySQL Verification Team

Hi!

Thank you for your bug report.

First of all, this is not a bug in the server, but if it is a bug, it is a bug in mysql CLI (command line interface).

We need two more informations before we can proceed. First, we need all the data from that table. Second, we would like to know whether these phenomena, that you observe, occur without ROLLUP being used.

Thanks in advance.

[16 Oct 2017 12:40] Arthur Tokarchuk

>First, we need all the data from that table. 

It's a table with one int column and one row, containing value '1'.

create table t1(a int);
insert into t1 values(1);

>Second, we would like to know whether these phenomena, that you observe, occur without ROLLUP being used.

No, it is not observed without ROLLUP being used.

[16 Oct 2017 14:02] MySQL Verification Team

I have repeated the behaviour and hence, I am verifying this bug:

mysql> create table bug88109 (a int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into bug88109 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select a, count(*) from bug88109 group by a with rollup;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        1 |
| NULL |        1 |
+------+----------+
2 rows in set (0.00 sec)

mysql> select b, count(*) from ( select a, count(*) b from  bug88109 group by a) t group by b  with rollup;
+---+----------+
| b | count(*) |
+---+----------+
| 1 |        1 |
| NULL |        1 |
+---+----------+
2 rows in set (0.00 sec)

mysql> select b, count(*) from ( select 1 b from  bug88109 group by a) t group by b  with rollup;
+---+----------+
| b | count(*) |
+---+----------+
| 1 |        1 |
| NULL |        1 |
+---+----------+
2 rows in set (0.00 sec)

mysql> select 1 b, count(*) from bug88109 group by b with rollup;
+------+----------+
| b    | count(*) |
+------+----------+
|    1 |        1 |
| NULL |        1 |
+------+----------+
2 rows in set (0.00 sec)