MySQL Bugs: #57015: LPad in view displays junk

Bug #57015	LPad in view displays junk
Submitted:	25 Sep 2010 8:10	Modified:	28 Sep 2010 19:12
Reporter:	Mallow Geno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, FUNCTION, lpad, Views

Description:

Using the Lpad on an int in a select query works great but if that same select query is put inside of a view, the Lpad displays 0x00 before the information. 

How to repeat:
# ---------------------Step One---------------------------
# Host:                         192.168.2.104
# Database:                     test
# Server version:               5.0.51a-3ubuntu5.4
# Server OS:                    debian-linux-gnu
# HeidiSQL version:             5.0.0.3272
# Date/time:                    2010-09-25 04:04:25
# --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
# Dumping database structure for test
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;

# Dumping structure for table test.numbers
DROP TABLE IF EXISTS `numbers`;
CREATE TABLE IF NOT EXISTS `numbers` (
  `n` int(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# Dumping data for table test.numbers: 21 rows
DELETE FROM `numbers`;
/*!40000 ALTER TABLE `numbers` DISABLE KEYS */;
INSERT INTO `numbers` (`n`) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
/*!40000 ALTER TABLE `numbers` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

############################################################# EOF
## Regular Select Query, look at the output column test is normal

SELECT `n`
, (lpad(`n`,5,'00000') as Test

from `numbers`

##################### Step Two ###########################3
create view `r` as
SELECT `n`
, (lpad(`n`,5,'00000') as Test

from `numbers`

##################### Step Two ###########################
If you look at the view data in Heidi you see things with '0x00' 
if you do a select * from r then you see various characters

Suggested fix:
lpad should function the same way regardless of whether it is a query or a view; 
Or behavior should be documented with the function.

Error in the create view I forgot to take off parentheses

alter view `r` as
SELECT `n`
, lpad(`n`,5,'00000') as Test

from `numbers`

It shows with 0x000 when you view it with heidisql but upon using a select query it displays correctly.

No it's a problem. I shouldn't have to do an arbitrary select * from view if the view is already created.

Please, check with a newer version of MySQL server, 5.0.91. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE IF NOT EXISTS `numbers` (
    ->   `n` int(10) default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO `numbers` (`n`) VALUES (0), (1), (2), (3), (4), (5), (6), (7)
, (8), (9),
    -> (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
Query OK, 21 rows affected (0.08 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql> SELECT `n`
    -> , (lpad(`n`,5,'00000') as Test
    ->
    -> from numbers;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'as Te
st

from numbers' at line 2
mysql> SELECT `n`
    -> , lpad(`n`,5,'00000') as Test
    -> from numbers;
+------+-------+
| n    | Test  |
+------+-------+
|    0 | 00000 |
|    1 | 00001 |
|    2 | 00002 |
|    3 | 00003 |
|    4 | 00004 |
|    5 | 00005 |
|    6 | 00006 |
|    7 | 00007 |
|    8 | 00008 |
|    9 | 00009 |
|   10 | 00010 |
|   11 | 00011 |
|   12 | 00012 |
|   13 | 00013 |
|   14 | 00014 |
|   15 | 00015 |
|   16 | 00016 |
|   17 | 00017 |
|   18 | 00018 |
|   19 | 00019 |
|   20 | 00020 |
+------+-------+
21 rows in set (0.08 sec)

mysql> create view `r`
    -> as SELECT `n`
    -> , lpad(`n`,5,'00000') as Test
    -> from numbers;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from r;
+------+-------+
| n    | Test  |
+------+-------+
|    0 | 00000 |
|    1 | 00001 |
|    2 | 00002 |
|    3 | 00003 |
|    4 | 00004 |
|    5 | 00005 |
|    6 | 00006 |
|    7 | 00007 |
|    8 | 00008 |
|    9 | 00009 |
|   10 | 00010 |
|   11 | 00011 |
|   12 | 00012 |
|   13 | 00013 |
|   14 | 00014 |
|   15 | 00015 |
|   16 | 00016 |
|   17 | 00017 |
|   18 | 00018 |
|   19 | 00019 |
|   20 | 00020 |
+------+-------+
21 rows in set (0.05 sec)

I see no problem above (other than extra '(' in your code). If you see the problem in HeidiSQL with current version of MySQL server, please, report bug to them.

The problem is that the variable is converted to varbinary. that is where the issue is at. That causes the issue.

I still do not see any difference of select 8 from view vs. initial select:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 --column-
type-info test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> SELECT `n`
    -> , lpad(`n`,5,'00000') as Test
    -> from `numbers` limit 1;
Field   1:  `n`
Catalog:    `def`
Database:   `test`
Table:      `numbers`
Org_table:  `numbers`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NUM

Field   2:  `Test`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     5
Max_length: 5
Decimals:   31
Flags:      BINARY

+------+-------+
| n    | Test  |
+------+-------+
|    0 | 00000 |
+------+-------+
1 row in set (0.06 sec)

mysql> select * from `r` limit 1;
Field   1:  `n`
Catalog:    `def`
Database:   `test`
Table:      `r`
Org_table:  `r`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NUM

Field   2:  `Test`
Catalog:    `def`
Database:   ``
Table:      `r`
Org_table:  `r`
Type:       VAR_STRING
Collation:  binary (63)
Length:     5
Max_length: 5
Decimals:   31
Flags:      BINARY

+------+-------+
| n    | Test  |
+------+-------+
|    0 | 00000 |
+------+-------+
1 row in set (0.02 sec)

Same data types and flags in both cases, at least from server version 5.0.91.

Thank you for trying to help.

It seems that when the field is Binary different programs interpret it as such and do not convert it back to string like mysql does when one uses the select from table. 

To remedy the issue use cast(field as char)