Bug #10554 Garbage value returned when executing the stmts without use of delimiter.

• Submitted: 11 May 2005 13:29
• Modified: 11 Apr 2006 9:10
• Reporter: Disha
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.5
• OS: Windows Server 2003
• Assigned to: Antony Curtis
• CPU Architecture: Any

[11 May 2005 13:29] Disha

Description:
Garbage value returned when executing the following statements without the use of a  delimiter between two different executable statements.

How to repeat:
1)delimiter //
2)drop table if exists t1 //
3)create table t1 (a integer, b integer) //
4)insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
select  distinct  sum(b) from (select 1  a,2 b union select 1,3) x group by a,b 
with rollup//

* Note : execute the 4th statement entirley including the semicolon(which is not the delimiter currently).

[11 May 2005 13:54] MySQL Verification Team

Are you tested this on Linux ?
the step:
4)insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);

expects the delimiter:
mysql> insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
    -> select  distinct  sum(b) from (select 1  a,2 b union select 1,3) x group by a,b

[25 May 2005 12:28] Vasily Kishkin

Ok. I found the garbage:

mysql> delimiter //
mysql> drop table if exists t1 //
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (a integer, b integer) //
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
    -> select  distinct  sum(b) from (select 1  a,2 b union select 1,3) x group by a,b
    -> with rollup//
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

+--------+
| sum(b) |
+--------+
| 2      |
| 3      |
| 5      |
+--------+
3 rows in set (0.05 sec)
sum(b)♠sum(b)♀?

mysql>

What are "sum(b)♠sum(b)♀"?

[11 Apr 2006 9:10] Valeriy Kravchuk

I was not able to repeat the behaviour described with 5.0.21-BK (ChangeSet@1.2143, 2006-04-10 08:29:24+02:00) on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> drop table if exists t1//
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int, b int)//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);
    -> select  distinct  sum(b) from (select 1  a,2 b union select 1,3) x group
    -> by a,b with rollup//
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

+--------+
| sum(b) |
+--------+
| 2      |
| 3      |
| 5      |
+--------+
3 rows in set (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> uname -a
Linux suse 2.6.11.4-20a-default #1 Wed Mar 23 21:52:37 UTC 2005 i686 i686 i386 GNU/Linux

So, looks like this bug is somehow fixed already.