Bug #46482 JOIN broken when using views with enum data types

• Submitted: 30 Jul 2009 17:34
• Modified: 30 Jul 2009 18:31
• Reporter: Rahul Nabar
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 14.12 Distrib 5.0.45
• OS: Linux (Red Hat Enterprise)
• CPU Architecture: Any
• Tags: enum, FUNCTION, join, Views, wrong result

[30 Jul 2009 17:34] Rahul Nabar

Description:
JOIN gives *entirely wrong* result. Produces rows which are clearly wrong and have a mismatch on the field that was supposed to be joined. The query is correctly run by other mysql versions. 

#####Output produced############
+------+------------+------+------------+
| m1   | Prediction | m1   | Prediction |
+------+------------+------+------------+
| A    | Sandwich   | A    | Sandwich   | 
| B    | Sandwich   | B    | Overlayer  | 
| C    | Sandwich   | C    | NULL       | 
| D    | Overlayer  | D    | Sandwich   | 
| E    | NULL       | E    | Sandwich   | 
+------+------------+------+------------+

########Expected Output###########
+------+------------+------+------------+
| m1   | Prediction | m1   | Prediction |
+------+------------+------+------------+
| A    | Sandwich   | A    | Sandwich   |
| B    | Sandwich   | NULL | NULL       |
| C    | Sandwich   | NULL | NULL       |
| D    | Overlayer  | NULL | NULL       |
| E    | NULL       | NULL | NULL       |
+------+------------+------+------------+ 

My testing shows the following things are suspect when used together:
(a) a JOIN on two views
(b) a field returned by a function
(c) a ENUM data type returned

Might be related to another bug I opened yesterday:

http://bugs.mysql.com/bug.php?id=46463

How to repeat:
mysql -p -u root  < ~foouser/script.sql

#######################################################################
#####################################################################
####################script.sql#########################################

DROP DATABASE IF EXISTS test;

CREATE DATABASE test;
USE test;
######################################
DELIMITER //
CREATE FUNCTION prediction(x FLOAT ) RETURNS
ENUM('Overlayer','Sandwich') BEGIN

        IF  (x < 0)  THEN
                RETURN 'Overlayer';
        ELSEIF   (x > 0 )  THEN
                RETURN 'Sandwich';
        ELSE
                RETURN NULL;
        END IF;

END;
//
DELIMITER ;
##############################
CREATE TABLE  table1
(
        m1 varchar(6),
        x float,
        y float
);
################################
CREATE VIEW view1 AS
(
        SELECT
                m1,
                prediction( x) AS Prediction            
        FROM
                table1
);
##################################
CREATE VIEW view2 AS
(
SELECT
        m1,
        prediction( y) AS Prediction
FROM
        table1
);
####################################
INSERT INTO table1 values
('A',1,1),
('B',1,-1),
('C',1,0),
('D',-1,1),
('E',0,1)
;  
###################################
SELECT *
FROM
        view1
LEFT JOIN
        view2

        ON
        (      
                view1.m1 =view2.m1 AND
                view1.Prediction = view2.Prediction
        )
;
##################################### 

Suggested fix:
No fix known.

[30 Jul 2009 17:40] Valeriy Kravchuk

Thank you for the problem report. With 5.1.38 I've got the results you expected:

valeriy-kravchuks-macbook-pro:5.1 openxs$ 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 10
Server version: 5.1.38-debug Source distribution

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

mysql> DELIMITER //
mysql> CREATE FUNCTION prediction(x FLOAT ) RETURNS
    -> ENUM('Overlayer','Sandwich') BEGIN
    -> 
    ->         IF  (x < 0)  THEN
    ->                 RETURN 'Overlayer';
    ->         ELSEIF   (x > 0 )  THEN
    ->                 RETURN 'Sandwich';
    ->         ELSE
    ->                 RETURN NULL;
    ->         END IF;
    -> 
    -> END;
    -> //
DELIMITER ;
##############################
CREATE TABLE  table1
(
        m1 varchar(6),
        x float,
        y float
);
################################
CREATE VIEW view1 AS
(
        SELECT
                m1,
                prediction( x) AS Prediction            
        FROM
                table1
);
##################################
CREATE VIEW view2 AS
(
SELECT
        m1,
        prediction( y) AS Prediction
FROM
        table1
);
####################################
INSERT INTO table1 values
('A',1,1),
('B',1,-1),
('C',1,0),
('D',-1,1),
('E',0,1)
;  
###################################
SELECT *
FROM
        view1
LEFT JOIN
        view2

        ON
        (      
                view1.m1 =view2.m1 AND
                view1.Prediction = view2.Prediction
        )
;
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> ##############################
mysql> CREATE TABLE  table1
    -> (
    ->         m1 varchar(6),
    ->         x float,
    ->         y float
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> ################################
mysql> CREATE VIEW view1 AS
    -> (
    ->         SELECT
    ->                 m1,
    ->                 prediction( x) AS Prediction            
    ->         FROM
    ->                 table1
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> ##################################
mysql> CREATE VIEW view2 AS
    -> (
    -> SELECT
    ->         m1,
    ->         prediction( y) AS Prediction
    -> FROM
    ->         table1
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> ####################################
mysql> INSERT INTO table1 values
    -> ('A',1,1),
    -> ('B',1,-1),
    -> ('C',1,0),
    -> ('D',-1,1),
    -> ('E',0,1)
    -> ;  
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ###################################
mysql> SELECT *
    -> FROM
    ->         view1
    -> LEFT JOIN
    ->         view2
    -> 
    ->         ON
    ->         (      
    ->                 view1.m1 =view2.m1 AND
    ->                 view1.Prediction = view2.Prediction
    ->         )
    -> ;
+------+------------+------+------------+
| m1   | Prediction | m1   | Prediction |
+------+------------+------+------------+
| A    | Sandwich   | A    | Sandwich   | 
| B    | Sandwich   | NULL | NULL       | 
| C    | Sandwich   | NULL | NULL       | 
| D    | Overlayer  | NULL | NULL       | 
| E    | NULL       | NULL | NULL       | 
+------+------------+------+------------+
5 rows in set (0.02 sec)

[30 Jul 2009 17:42] Rahul Nabar

Thanks! Any chance you have access to version 5.045? Was this a bug in that version.

This is the default version shipped out by RedHat (as far as I know) Might have a larger impact.

[30 Jul 2009 17:44] Valeriy Kravchuk

You 5.0.45 is really old, and many bugs are fixed since that time. Looks like this bug is also fixed in current versions. Also not repeatable with 5.0.85:

valeriy-kravchuks-macbook-pro:5.0 openxs$ 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.0.85 Source distribution

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

mysql> DELIMITER //
CREATE FUNCTION prediction(x FLOAT ) RETURNS
ENUM('Overlayer','Sandwich') BEGIN

        IF  (x < 0)  THEN
                RETURN 'Overlayer';
        ELSEIF   (x > 0 )  THEN
                RETURN 'Sandwich';
        ELSE
                RETURN NULL;
        END IF;

END;
//
DELIMITER ;
##############################
CREATE TABLE  table1
(
        m1 varchar(6),
        x float,
        y float
);
################################
CREATE VIEW view1 AS
(
        SELECT
                m1,
                prediction( x) AS Prediction            
        FROM
                table1
);
##################################
CREATE VIEW view2 AS
(
SELECT
        m1,
        prediction( y) AS Prediction
FROM
        table1
);
####################################
INSERT INTO table1 values
('A',1,1),
('B',1,-1),
('C',1,0),
('D',-1,1),
('E',0,1)
;  
###################################
SELECT *
FROM
        view1
LEFT JOIN
        view2

        ON
        (      
   mysql> CREATE FUNCTION prediction(x FLOAT ) RETURNS
    -> ENUM('Overlayer','Sandwich') BEGIN
    -> 
    ->         IF  (x < 0)  THEN
    ->                 RETURN 'Overlayer';
    ->         ELSEIF   (x > 0 )  THEN
    ->                 RETURN 'Sandwich';
    ->         ELSE
    ->                 RETURN NULL;
    ->         END IF;
    -> 
    -> END;
    -> //
DELIMITER ;
##############################
CREATE TABLE  table1
(
        m1 varchar(6),
        x float,
        y float
);
################################
CREATE VIEW view1 AS
(
        SELECT
                m1,
                prediction( x) AS Prediction            
        FROM
                table1
);
##################################
CREATE VIEW view2 AS
(
SELECT
        m1,
        prediction( y) AS Prediction
FROM
        table1
);
####################################
INSERT INTO table1 values
('A',1,1),
('B',1,-1),
('C',1,0),
('D',-1,1),
('E',0,1)
;  
###################################
SELECT *
FROM
        view1
LEFT JOIN
        view2

        ON
        (      
                view1.m1 =view2.m1 AND
                  ew1.Prediction = view2.Prediction
        )
;
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> ##############################
mysql> CREATE TABLE  table1
    -> (
    ->         m1 varchar(6),
    ->         x float,
    ->         y float
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> ################################
mysql> CREATE VIEW view1 AS
    -> (
    ->         SELECT
    ->                 m1,
    ->                 prediction( x) AS Prediction            
    ->         FROM
    ->                 table1
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> ##################################
mysql> CREATE VIEW view2 AS
    -> (
    -> SELECT
    ->         m1,
    ->         prediction( y) AS Prediction
    -> FROM
    ->         table1
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> ####################################
mysql> INSERT INTO table1 values
    -> ('A',1,1),
    -> ('B',1,-1),
    -> ('C',1,0),
    -> ('D',-1,1),
    -> ('E',0,1)
    -> ;  
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM
    ->         view1
    -> LEFT JOIN
    ->         view2
    -> 
    ->         ON
    ->         (      
    ->                 view1.m1 =view2.m1 AND
    ->                 view1.Prediction = view2.Prediction
    ->         )
    -> ;
+------+------------+------+------------+
| m1   | Prediction | m1   | Prediction |
+------+------------+------+------------+
| A    | Sandwich   | A    | Sandwich   | 
| B    | Sandwich   | NULL | NULL       | 
| C    | Sandwich   | NULL | NULL       | 
| D    | Overlayer  | NULL | NULL       | 
| E    | NULL       | NULL | NULL       | 
+------+------------+------+------------+
5 rows in set (0.01 sec)

So, please, upgrade to 5.0.84 and check if the problem is still there.

[30 Jul 2009 18:03] Rahul Nabar

It isn't easy to upgrade right away. I am stuck with Red Hat and its old yum repo. 

Can you point me to a specific bug fix?  Or a bug report on this? That way I could look if there is  a patch for this issue.

[30 Jul 2009 18:31] Rahul Nabar

I sniffed up the old bug reports.

Do you think this is the bug I am facing:

http://bugs.mysql.com/bug.php?id=29604

[25 Aug 2009 6:14] Roel Van de Paar

FYI: Though it is fixed in a later release as pointed out above, 5.0.45 indeed has this bug:

+------+------------+------+------------+
| m1   | Prediction | m1   | Prediction |
+------+------------+------+------------+
| A    | Sandwich   | A    | Sandwich   | 
| B    | Sandwich   | B    | Overlayer  | 
| C    | Sandwich   | C    | NULL       | 
| D    | Overlayer  | D    | Sandwich   | 
| E    | NULL       | E    | Sandwich   | 
+------+------------+------+------------+