MySQL Bugs: #18243: View returns two different results for identical expressions in a concat

Bug #18243	View returns two different results for identical expressions in a concat
Submitted:	15 Mar 2006 1:50	Modified:	24 Jul 2006 17:15
Reporter:	Peter Brodersen (Candidate Quality Contributor)	Email Updates:
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.23-BK, 5.1.10+5.0.20 (user-compiled), 4.1.21	OS:	Linux (Linux)
Assigned to:	Igor Babaev	Target Version:

Description:
Server: 5.0.18-Debian_3.dotdeb.1-log
Client: 5.0.18

A specific expression for a field in a view of a table provides strange results when
passed through a bunch of functions.

I haven't quite been able to pinpoint where the strange result appears, but it is
reproducable.

(the problem don't show under an old 5.0.2-alpha-nt though - here the resultats are as
expected)

How to repeat:
1. Create table and enter content:
CREATE TABLE t1 (firstname text, surname text);
INSERT INTO t1 (firstname, surname) VALUES ("Bart","Simpson"),("Milhouse","van
Houten"),("Montgomery","Burns");

2. Create view with a concatenated field
CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
SELECT * FROM v1;

3. Create an expression:
SELECT
  RIGHT(name,INSTR(REVERSE(name)," ")-1) AS r, 
  LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")) AS l,
  CONCAT(
    RIGHT(name,INSTR(REVERSE(name)," ")-1),
    ", ",
    LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
  ) AS namesort
FROM v1
ORDER BY namesort

4. Test the exact same expression on the original table, just by replacing name with
CONCAT(firstname," ",surname)  - just as we did in the view:
SELECT
  RIGHT(CONCAT(firstname," ",surname),INSTR(REVERSE(CONCAT(firstname," ",surname)),"
")-1) AS r,
  LEFT(CONCAT(firstname," ",surname),LENGTH(CONCAT(firstname,"
",surname))-INSTR(REVERSE(CONCAT(firstname," ",surname))," ")) AS l,
  CONCAT(
    RIGHT(CONCAT(firstname," ",surname),INSTR(REVERSE(CONCAT(firstname," ",surname)),"
")-1),
    ", ",
    LEFT(CONCAT(firstname," ",surname),LENGTH(CONCAT(firstname,"
",surname))-INSTR(REVERSE(CONCAT(firstname," ",surname))," "))
  ) AS namesort
FROM t1
ORDER BY namesort

The result from #3 is:
+---------+--------------+----------------------+
| r       | l            | namesort             |
+---------+--------------+----------------------+
| Burns   | Montgomery   | Burns, snruB yrem    |
| Houten  | Milhouse van | Houten, netuoH nav e |
| Simpson | Bart         | Simpson, nosp        |
+---------+--------------+----------------------+

The result from #4 is:
+---------+--------------+----------------------+
| r       | l            | namesort             |
+---------+--------------+----------------------+
| Burns   | Montgomery   | Burns, Montgomery    |
| Houten  | Milhouse van | Houten, Milhouse van |
| Simpson | Bart         | Simpson, Bart        |
+---------+--------------+----------------------+

The result from #3 seems pretty wrong.

The spurious part is that the expressions for aliased field "r" and "l" has just been
concatenated to "namesort". It seems like the concat part messes up.

A more simple example:

SELECT
  CONCAT(
     LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
     LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
  ) AS f1
FROM v1

+--------------------------+
| f1                       |
+--------------------------+
| Bartnosp                 |
| Milhouse vannetuoH nav e |
| MontgomerysnruB yrem     |
+--------------------------+
3 rows in set (0.00 sec)

This is obviously wrong as we just concatenate two identical expressions.

Suggested fix:
Remove the weirdness :-)

Thank you for a problem report. Please, try to repeat with a MySQL's binary of 5.0.19, and
inform about the results. Here is what I got on 5.0.19 for the last your query:

mysql> SELECT
    ->   CONCAT(
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
    ->   ) AS f1
    -> FROM v1;
+----------------------+
| f1                   |
+----------------------+
| BartBart             |
| MilhouseMilhouse     |
| MontgomeryMontgomery |
+----------------------+
3 rows in set (0.00 sec)

I don't experience the problem under 5.0.19 under Windows, so I guess the problem was
already solved there.

I'll close the bug now.

I have upgraded to 5.0.19 (5.0.19-Debian_1.dotdeb.1-log) and I experience the problem here
as well:

mysql> SELECT
    ->   CONCAT(
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
    ->   ) AS f1
    -> FROM v1;
+--------------------------+
| f1                       |
+--------------------------+
| Bartnosp                 |
| Milhouse vannetuoH nav e |
| MontgomerysnruB yrem     |
+--------------------------+
3 rows in set (0.00 sec)

I created the table from scratch to make sure that the old table wasn't corrupted.

Please, try to repeat with MySQL's binaries of 5.0.19, not Debian's, and inform about the
results.

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".

I couldn't reproduce the bug with the binaries provided by MySQL.com - neither at 5.0.19
(Windows) or 5.1.7-beta (Windows). In both cases the queries ran as expected.

But I have experienced the bug at other places, where it is reproducable, e.g. creating
databases on www.db4free.net. The bug seems to be present under 5.0.20-max-log and
5.1.10-beta-log running at their servers.

Now, I would reckon that they have compiled mysqld themselves instead of using the
binaries provided by you. I understand that you might not be able to support or bugtest
user-compiled servers, but as the bug exists in different setups you might still be
interested in investigating the bug. Even though you recommend using your binaries I
don't suppose the result should differ between your binaries and some user-compiled
edition.

If they compiled themselves I need, at least, uname -a results, compiler (gcc?) version
used, glibc version used and, surely, exact environment and configure options used.
Without this information I can not verify this problem or reasonably state that it is not
repeatable.

In Windows with MySQL 5.0.21 and 5.1.9, I got the following output:

mysql> CREATE TABLE t1 (firstname text, surname text);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 (firstname, surname) VALUES ("Bart","Simpson"),("Milhouse"
,"van Houten"),("Montgomery","Burns");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT
    ->   CONCAT(
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
    ->   ) AS f1
    -> FROM v1;
+--------------------------+
| f1                       |
+--------------------------+
| BartBart                 |
| Milhouse vanMilhouse van |
| MontgomeryMontgomery     |
+--------------------------+
3 rows in set (0.01 sec)

In SuSE Linux 10.0 though, I get the same output as Peter Brodersen:

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

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

mysql> CREATE TABLE t1 (firstname text, surname text);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t1 (firstname, surname) VALUES ("Bart","Simpson"),("Milhouse"
,"van Houten"),("Montgomery","Burns");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> SELECT
    ->   CONCAT(
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
    ->      LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
    ->   ) AS f1
    -> FROM v1;
+--------------------------+
| f1                       |
+--------------------------+
| Bartnosp                 |
| Milhouse vannetuoH nav e |
| MontgomerysnruB yrem     |
+--------------------------+
3 rows in set (0.02 sec)

mysql>

By the way - the MySQL version that I use on SuSE Linux 10.0 is the RPM binary version for
x86_64 systems.

Verified just as described with 5.0.23-BK on Linux:

mysql> CREATE TABLE t1 (firstname text, surname text);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 (firstname, surname) VALUES
    -> ("Bart","Simpson"),("Milhouse"
    -> ,"van Houten"),("Montgomery","Burns");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT
    ->   CONCAT(
    ->     LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")),
    ->     LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))
    ->   ) AS f1
    -> FROM v1;
+--------------------------+
| f1                       |
+--------------------------+
| Bartnosp                 |
| Milhouse vannetuoH nav e |
| MontgomerysnruB yrem     |
+--------------------------+
3 rows in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_filesystem | binary                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/openxs/dbs/5.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.01 sec)

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

Weird bug, it seems.

This bug be demonstrated in 4.1 as well:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a varchar(10));
Query OK, 0 rows affected (0.48 sec)

mysql> INSERT INTO t1 VALUES ('abc'), ('xyz');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT a, CONCAT(a,' ',a) AS c FROM t1
    ->   HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a;
Empty set (0.00 sec)

mysql> SELECT a, CONCAT(a,' ',a) AS c FROM t1
    ->   HAVING LEFT(CONCAT(a,' ',a),
    ->               LENGTH(CONCAT(a,' ',a))-
    ->                      INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a;
+------+---------+
| a    | c       |
+------+---------+
| abc  | abc abc |
| xyz  | xyz xyz |
+------+---------+
2 rows in set (0.01 sec)

Apparently the output for both SELECT statements must be the same.

We observe a wrong result here because the function Item_func_reverse::val_str that
implements the REVERSE operation modifies its argument.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8731

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8850

The implementation of the method Item_func_reverse::val_str
for the REVERSE function modified the argument of the function.
This led to wrong results for expressions that contained
REVERSE(ref) if ref occurred somewhere else in the expressions.

Fixed in 4.1.22, 5.0.25

Noted in 4.1.22, 5.0.25 changelogs.