MySQL Bugs: #26560: VIEW with scalar correlated subquery returns wrong results

Bug #26560	VIEW with scalar correlated subquery returns wrong results
Submitted:	22 Feb 2007 10:33	Modified:	15 Mar 2007 15:17
Reporter:	Felix Geerinckx (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.27, 5.1.14	OS:	Any (All)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	qc

Description:
A VIEW with a scalar correlated subquery returns different (and wrong) results.

How to repeat:
USE test;
DROP TABLE IF EXISTS foo, lookup;
DROP TABLE IF EXISTS bar1, bar2;

CREATE TABLE lookup (
	lid INT UNSIGNED NOT NULL PRIMARY KEY,
	name CHAR(10) NOT NULL
);
INSERT INTO lookup (lid, name) VALUES (1, 'YES'), (2, 'NO');

CREATE TABLE foo (
    id INT UNSIGNED NOT NULL PRIMARY KEY, 
    gid INT UNSIGNED NOT NULL,
    lid INT UNSIGNED NOT NULL,
    dt DATE
);
INSERT INTO foo (id, gid, lid, dt) VALUES
(1, 1, 1, '2007-01-01'),
(2, 1, 2, '2007-01-02'),

(3, 2, 2, '2007-02-01'),
(4, 2, 1, '2007-02-02');

-- Basic SELECT query "get the latest lookup-name per gid"
-- returns correct results: NO for gid=1, YES for gid=2:

SELECT DISTINCT
    foo.gid AS lgid,
    (
        SELECT lookup.name
        FROM lookup, foo
        WHERE
            lookup.lid  = foo.lid
            AND foo.gid = lgid
        ORDER BY foo.dt DESC
        LIMIT 1
    ) as clid
FROM foo;

-- Putting the same exact SELECT in VIEW bar1 gives wrong
-- results: YES for gid=1, YES for gid=2:

CREATE OR REPLACE VIEW bar1 AS
SELECT  DISTINCT
    foo.gid AS lgid,
    (
        SELECT lookup.name
        FROM lookup, foo
        WHERE
            lookup.lid  = foo.lid
            AND foo.gid = lgid
        ORDER BY foo.dt DESC
        LIMIT 1
    ) as clid
FROM foo;

SELECT * FROM bar1;
        
-- Using a table alias in the main query solves the problem
-- in VIEW bar2:
 
CREATE OR REPLACE VIEW bar2 AS
SELECT  DISTINCT
    f1.gid  AS lgid,
    (
        SELECT lookup.name
        FROM lookup, foo
        WHERE
            lookup.lid  = foo.lid
            AND foo.gid = f1.gid
        ORDER BY foo.dt DESC, foo.lid
        LIMIT 1
    ) as clid
FROM foo f1;

SELECT * FROM bar2;

Suggested fix:
Defining the VIEW a bit different (as in bar2 above) seems to solve the problem.

Missed severity on first submission

Thank you for the report.

Verified as described.

All versions are affected.

If we look at the internal representation of the view bar1:

mysql> show create view bar1\G
*************************** 1. row ***************************
       View: bar1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `bar1` AS select distinct `foo`.`gid` AS `lgid`,(select `lookup`.`name` AS `name` from (`lookup` join `foo`) where ((`lookup`.`lid` = `foo`.`lid`) and (`foo`.`gid` = `foo`.`gid`)) order by `foo`.`dt` desc limit 1) AS `clid` from `foo`
1 row in set (0.01 sec)

we see that the outer reference lgid in the subquery was replaced for
 `foo`.`gid`. As foo is also the table referenced in the FROM list
of the subquery this replacement effectively changes the meaning of the
view.

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/21112

ChangeSet@1.2431, 2007-03-04 19:54:35-08:00, igor@olga.mysql.com +7 -0
  Fixed bug #26560.
  The flag alias_name_used was not set on for the outer references
  in subqueries. It resulted in replacement of any outer reference
  resolved against an alias for a full field name when the frm 
  representation of a view with a subquery was generated. 
  If the subquery and the outer query referenced the same table in
  their from lists this replacement effectively changed the meaning
  of the view and led to wrong results for selects from this view. 
  
  Modified several functions to ensure setting the right value of
  the alias_name_used flag for outer references resolved against
  aliases.

Pushed to 5.0.38, 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.

Views that used a scalar correlated subquery returned incorrect results.