maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

On Sun, Feb 21, 2010 at 09:32:26AM +0300, Sergey Petrunya wrote:
> At file:///home/psergey/dev/maria-5.3-subqueries-r7/
> 
> ------------------------------------------------------------
> revno: 2765
> revision-id: psergey@xxxxxxxxxxxx-20100221063223-h0f7u2low7rtjixc
> parent: psergey@xxxxxxxxxxxx-20100221033618-83dgm2h9ingzmhcc
> committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
> branch nick: maria-5.3-subqueries-r7
> timestamp: Sun 2010-02-21 08:32:23 +0200
> message:
>   Change Field_enumerator to enumerate Item_field-s not Field-s.
>   In Item_ref::fix_fields() do invoke mark_as_dependent() for outside 
>   references in all cases (see email for more details)

The problem occurs only when using --ps-protocol and manifests itself as
follows:

CURRENT_TEST: main.subselect
mysqltest: At line 258: query 'select numeropost as a FROM t1 ORDER BY (SELECT 1 FROM t1 HAVING a=1)' succeeded - should have failed with errno 1242...

The result from queries just before the failure was:
< snip >
CREATE TABLE `t1` (
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`maxnumrep` int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (`numeropost`),
UNIQUE KEY `maxnumrep` (`maxnumrep`)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1);
select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1);
ERROR 21000: Subquery returns more than 1 row


The lack of error is caused by "HAVING a=1" not being true, which itself is
caused by "a" having garbage, because the optimizer evaluates the subquery
before it starts exection of the upper select, which it does because it sees
that
  
  subselect_item->used_tables() == 0  (*)

which is wrong.

(*) occurs because during EXECUTE running fix_fields() for "HAVING a=1" does
not trigger any mark_as_dependent() calls. 

(I'm not sure why this happens in this case but not in others. Normally,
fix_fields() call for outer references will cause mark_as_dependent calls. 
There is a whole mark_select_range_as_dependent() function which is called 
only by EXECUTE (and not by regular non-PS execution).

Phyisically it looks as follows:


bool Item_ref::fix_fields(THD *thd, Item **reference)
{
  ...
  if (!ref || ref == not_found_item)
  {
     (All mark_as_dependent() or mark_select_range_as_dependent() calls are
     made from here)
  }
  DBUG_ASSERT(*ref);
  ...
}

For the example query, the if branch is taken during PREPARE command, but not 
in EXECUTE. This means that during EXECUTE branch we don't call
mark.*as_dependent(), which causes subselect's outside reference not to be
recorded, and then subselect_item->used_tables()==0 which leads to errors.

To avoid the problem, I've changed the code to be:

bool Item_ref::fix_fields(THD *thd, Item **reference)
{
  ...
  if (!ref || ref == not_found_item)
  {
     (All mark_as_dependent() or mark_select_range_as_dependent() calls are
     made from here)
  }
  else
  {
    (Enumerate all table field references in *ref and call mark_as_dependent()
    as necessary)
  }
  DBUG_ASSERT(*ref);
  ...
}


BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog