Bug #83707 Mysql left join with view of type merge returns view `ifnull` results instead of

• Submitted: 6 Nov 2016 16:08
• Modified: 2 Jan 2020 23:32
• Reporter: sinai yoktan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.5/5.6
• OS: Any
• CPU Architecture: Any
• Tags: view merge left-join ifnull

[6 Nov 2016 16:08] sinai yoktan

Description:
i encountered an abnormal behavior in mysql 5.6,

when using IFNULL in a view definition of type (algorithem) merge, the IFNULL clause will be calculated after processing the left join, this will generate abnormal results, (returning value on missing records)

the result is a situation where the view algorithm effects the output result.

How to repeat:
CREATE table test_table(a INT NOT NULL)
INSERT INTO test_table(a) values(1)

CREATE 
    ALGORITHM = MERGE 
VIEW `test_view` AS
    SELECT 
        IFNULL(`test`.`a`, 'why is it here') AS `b`
    FROM
        `test_table`
    WHERE
        FALSE

SELECT * FROM test a LEFT JOIN test_view b ON FALSE

Suggested fix:
a warning should be issued.

[6 Nov 2016 20:16] MySQL Verification Team

Results of 5.5/5.6/5.7/8.0

Attachment: bug83707.txt (text/plain), 4.46 KiB.

[7 Nov 2016 9:56] MySQL Verification Team

Thank you for the bug report. Only 5.5 and 5.6 affected. See attached file to correct the test case.

[2 Jan 2020 23:32] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.18