=== modified file 'sql/field.h'

--- sql/field.h 2013-11-20 11:05:39 +0000

+++ sql/field.h 2014-02-25 16:16:22 +0000

@@ -599,25 +599,8 @@ class Field

   */

   virtual void sql_type(String &str) const =0;

   virtual uint size_of() const =0;             // For new field

-  inline bool is_null(my_ptrdiff_t row_offset= 0) const

-  {

-    /*

-      The table may have been marked as containing only NULL values

-      for all fields if it is a NULL-complemented row of an OUTER JOIN                                             

-      or if the query is an implicitly grouped query (has aggregate                                                

-      functions but no GROUP BY clause) with no qualifying rows. If                                                

-      this is the case (in which TABLE::null_row is true), the field                                               

-      is considered to be NULL.                                                                                    

-      Note that if a table->null_row is set then also all null_bits are                                            

-      set for the row.                                                                                             

-

-      Otherwise, if the field is NULLable, it has a valid null_ptr

-      pointer, and its NULLity is recorded in the "null_bit" bit of

-      null_ptr[row_offset].

-    */

-    return (table->null_row ? TRUE :

-            null_ptr ? test(null_ptr[row_offset] & null_bit) : 0);

-  }

+  inline bool is_null(my_ptrdiff_t row_offset= 0)

+  { return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) : table->null_row; }

   inline bool is_real_null(my_ptrdiff_t row_offset= 0) const

     { return null_ptr ? (null_ptr[row_offset] & null_bit ? 1 : 0) : 0; }

   inline bool is_null_in_record(const uchar *record) const

revid: sergey.glukhov@oracle.com-20130619102408-0a0bf6sl5d7tsysa

Bug#16620047 INCORRECT QUERY RESULT (AFTER SERVER UPGRADE)

Regression is introduced in Bug11760517. According to the

comment for the Bug11760517 fix, there is an additional cleanup

in field.h(Field::is_null() function):

...

jorgen.loland@oracle.com-20111220120431-pxmaax8e3effzhj0

BUG#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY 

              WITH CONST TABLE AND NO MATCHING ROWS

BUG#13422961: WRONG RESULTS FROM SELECT WITH AGGREGATES

              AND IMPLICIT GROUPING + MYISAM OR MEM

BUG#11760517: 52935: MIN/MAX FAILS TO EVALUATE HAVING 

              CONDITION, RETURNS INCORRECT NULL RESULT

When implicitly grouped queries do not have any rows matching

the join predicates, a row based on NULL values is returned

(if not filtered out by the HAVING clause). This means that

(a) non-aggregated fields should have the value NULL and (b)

aggregated fields should be calculated based on NULL.

For (a), this is achieved by marking all tables as containing

only NULL values by calling mark_as_null_row(). For (b),

this is achieved by calling Item::no_rows_in_result().

There were multiple bugs in this area:

 1) In locations where mark_as_null_row() was called, const

    tables were ignored. This has been fixed by storing the

    info mark_as_null_row() modifies for const tables, call 

    mark_as_null_row() for all tables including const tables,

    send the result and finally restore const table info.

    Note that save/restore is only required for subqueries 

    since the value will not be reused otherwise. This was 

    the case for end_send_group() and end_write_group()

 2) Item::no_rows_in_result() was not called everywhere where

    aggregate items needed to be calculated on NULL values. This

    was the case for do_select().

 3) mark_as_null_row() was not called everywhere non-aggregate

    items needed to be set to NULL. This was the case for

    do_select() and end_write_group().

 4) the having clause was evaluated after (a) but before (b).

    This was the case in return_zero_rows()

Additional cleanup: 

 * mark_as_null_row() no longer sets the NULL bits, only

   table->null_row and table->status

 * Field::is_null() now checks table->null_row before checking

   the NULL bits.

SELECT col_int_nokey, MAX( pk ) 

FROM ot 

WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );

col_int_nokey   MAX( pk )

NULL    NULL

SELECT col_int_nokey, MAX( pk ) 

FROM ot 

WHERE (8, 1) IN ( SELECT pk, COUNT( col_int_nokey ) FROM it );

col_int_nokey  MAX( pk )

8      NULL

#

# Bug#13430588: WRONG RESULT FROM IMPLICITLY GROUPED QUERY WITH

#               CONST TABLE AND NO MATCHING ROWS

#

CREATE TABLE t1 (i INT) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (j INT) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1),(2);

SELECT i, j, COUNT(i) FROM t1 JOIN t2 WHERE j=3;

i       j       COUNT(i)

1       NULL    0

--source include/have_innodb.inc

create table table_one (oidGroup int, oid int primary key) engine=InnoDB;

insert into table_one values (1,1),(1,2),(1,3),(1,4);

create table table_two (oid int primary key) engine=InnoDB;

insert into table_two values (3);

# Returns a value

select a.oidGroup, a.oid, b.oid from table_one a left join table_two b on a.oid=b.oid where a.oidGroup=1;

# Returns only NULLs

select a.oidGroup, a.oid, b.oid from table_one a left join table_two b on a.oid=b.oid where a.oidGroup=1 group by a.oid;