MySQL Bugs: #29745: LENGTH(binary_field) with select from (select table) as tmp

Bug #29745	LENGTH(binary_field) with select from (select table) as tmp_tbl don't working
Submitted:	12 Jul 2007 3:03	Modified:	29 Mar 2011 18:58
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S1 (Critical)
Version:	5.1.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
select *,length(valor) from(
SELECT datahora,
SUBSTRING(value,0*8+1,8) AS valor,
length(SUBSTRING(value,0*8+1,8)) AS valor2,
length(value)
FROM log_n_channels 
WHERE 
datahora<1184122800 AND 
SUBSTRING(value,0*8+1,8)!=(CHAR(128) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0)) AND 
LENGTH(value)>=0*8+8

) as tmp_tbl

length(valor) should be 8, but's 0, maybe collating is the problem, but field value from table log_n_channels is a longblob!

How to repeat:
select *,length(valor) from(
SELECT datahora,
SUBSTRING(value,0*8+1,8) AS valor,
length(SUBSTRING(value,0*8+1,8)) AS valor2,
length(value)
FROM log_n_channels 
WHERE 
datahora<1184122800 AND 
SUBSTRING(value,0*8+1,8)!=(CHAR(128) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0)) AND 
LENGTH(value)>=0*8+8

) as tmp_tbl

Suggested fix:
!!?

see that substring is ok
the problem is select * from (select sql) as tmp_tbl

when the "table" is create the type of substring isn't BYNARY, it's a text!

Thank you for a bug report. Sorry, but where is the bug here?

mysql> CREATE TABLE /*!32312 IF NOT EXISTS*/ `log_n_channels` (
    ->   `datahora` decimal(17,6) NOT NULL DEFAULT '0.000000',
    ->   `value` longblob NOT NULL,
    ->   PRIMARY KEY (`datahora`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> select *,length(valor) from(
    -> SELECT datahora,
    -> SUBSTRING(value,0*8+1,8) AS valor,
    -> length(SUBSTRING(value,0*8+1,8)) AS valor2,
    -> length(value)
    -> FROM log_n_channels
    -> WHERE
    -> datahora<1184122800 AND
    -> SUBSTRING(value,0*8+1,8)!=(CHAR(128) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) ||
    -> CHAR(0) || CHAR(0) || CHAR(0)) AND
    -> LENGTH(value)>=0*8+8
    ->
    -> ) as tmp_tbl;
Empty set (0.01 sec)

mysql> create table ttttt as select *,length(valor) from(
    -> SELECT datahora,
    -> SUBSTRING(value,0*8+1,8) AS valor,
    -> length(SUBSTRING(value,0*8+1,8)) AS valor2,
    -> length(value)
    -> FROM log_n_channels
    -> WHERE
    -> datahora<1184122800 AND
    -> SUBSTRING(value,0*8+1,8)!=(CHAR(128) || CHAR(0) || CHAR(0) || CHAR(0) || CHAR(0) ||
    -> CHAR(0) || CHAR(0) || CHAR(0)) AND
    -> LENGTH(value)>=0*8+8
    ->
    -> ) as tmp_tbl;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.1.21-beta |
+-------------+
1 row in set (0.00 sec)

mysql> show create table ttttt\G
*************************** 1. row ***************************
       Table: ttttt
Create Table: CREATE TABLE `ttttt` (
  `datahora` decimal(17,6) NOT NULL DEFAULT '0.000000',
  `valor` varbinary(8) NOT NULL DEFAULT '',
  `valor2` bigint(10) NOT NULL DEFAULT '0',
  `length(value)` bigint(10) NOT NULL DEFAULT '0',
  `length(valor)` int(10) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

So, making varbinary(8) out of SUBSTRING(value,0*8+1,8) where value is of type longblob is a bug? But you'll get the same without select * from (select sql) t. This looks like reasonable based on the manual, http://dev.mysql.com/doc/refman/5.0/en/blob.html:

"In most respects, you can regard a BLOB  column as a VARBINARY column that can be as large as you like."

"Thank you for a bug report. Sorry, but where is the bug here?"
check: 
if you do:

select * from (
select blob_field_with_binary_values_like_chr_0_and_others from table
) as tmp_tbl

you don't get value of blob
the blob is truncated at the first chr(0) 
since it's a blob, it couldn't be converted to a string (text), see what happen with strings:

select * from (select "123" || char(0) || "123") as asdf

this will return "123" and not the concat of "123" chr(0) and "123"

if you insert "123" || char(0) || "123" into a blob field and execute
select blob_field from table
you get "123" || char(0) || "123"

Sorry, but I still do not understand what are you complaning about. I tried to create a simple test based on your last comment. Please, check it:

openxs@linux:~/dbs/5.1> 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 1
Server version: 5.1.21-beta Source distribution

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

mysql> create table tblob (c1 blob);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tblob values(0x61626300616263);
Query OK, 1 row affected (0.01 sec)

mysql> select c1, length(c1) from tblob;
+---------+------------+
| c1      | length(c1) |
+---------+------------+
| abc abc |          7 |
+---------+------------+
1 row in set (0.00 sec)

mysql> select c1, length(c1) from (select * from tblob) tmp;
+---------+------------+
| c1      | length(c1) |
+---------+------------+
| abc abc |          7 |
+---------+------------+
1 row in set (0.01 sec)

Looks like I missed some details. Please, explain me what's wrong with my test above.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

nice working in 5.5