Fix for bug lp:798597 Incorrect "Duplicate entry" error with views and GROUP BY

mysql-test/r/join.result:
  Test case for LP:798597
mysql-test/t/join.test:
  Test case for LP:798597
sql/sql_select.cc:
  In simplify_joins we reset table->maybe_null for outer join tables that can't ever be NULL.
  This caused a conflict between the previously calculated items and the group_buffer against the fields
  in the temporary table that are created as not null thanks to the optimization.
  The fix is to correct the group by items to also be not_null so that they match the used fields and keys.

3 files changed, 58 insertions, 0 deletions

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 96504df4b1e..dae3d16c781 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2,t3;
+drop view if exists v1,v2;
 CREATE TABLE t1 (S1 INT);
 CREATE TABLE t2 (S1 INT);
 INSERT INTO t1 VALUES (1);
@@ -1220,4 +1221,22 @@ f1
 2
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
+#
+# Bug LP:798597: Incorrect "Duplicate entry" error with views and
+#                GROUP BY
+#
+CREATE TABLE t1 ( f1 int NOT NULL , f2 int NOT NULL ) ;
+INSERT INTO t1 VALUES (214,0),(6,6);
+CREATE TABLE t2 ( f2 int) ;
+INSERT INTO t2 VALUES (88),(88);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0) ;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0 or t1.f2 is null) ;
+SELECT f1 , MIN(f2) FROM v1 GROUP BY f1;
+f1	MIN(f2)
+214	88
+SELECT f1 , MIN(f2) FROM v2 GROUP BY f1;
+f1	MIN(f2)
+214	88
+drop table t1,t2;
+drop view v1,v2;
 End of 5.1 tests
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 05d630edfb2..0a573e7cd4f 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -2,6 +2,7 @@
 # Initialization
 --disable_warnings
 drop table if exists t1,t2,t3;
+drop view if exists v1,v2;
 --enable_warnings
 
 #
@@ -921,4 +922,21 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug LP:798597: Incorrect "Duplicate entry" error with views and
+--echo #                GROUP BY
+--echo #
+
+CREATE TABLE t1 ( f1 int NOT NULL , f2 int NOT NULL ) ;
+INSERT INTO t1 VALUES (214,0),(6,6);
+CREATE TABLE t2 ( f2 int) ;
+INSERT INTO t2 VALUES (88),(88);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0) ;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT t1.f1, t2.f2 FROM (t2 LEFT JOIN t1 ON (t2.f2 <> t1.f1)) WHERE (t1.f2 <= 0 or t1.f2 is null) ;
+SELECT f1 , MIN(f2) FROM v1 GROUP BY f1;
+SELECT f1 , MIN(f2) FROM v2 GROUP BY f1;
+drop table t1,t2;
+drop view v1,v2;
+
+
 --echo End of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 91fc95a2909..0bd4e8ae647 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10637,15 +10637,30 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 	 (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 ||
 	 (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ?
 	0 : FIELDFLAG_BINARY;
+
       if (!using_unique_constraint)
       {
 	cur_group->buff=(char*) group_buff;
+
+        if (maybe_null & !field->null_bit)
+        {
+          /*
+            This can only happen in the unusual case where an outer join
+            table was found to be not-nullable by the optimizer and we
+            the item can't really be null.
+            We solve this by marking the item as !maybe_null to ensure
+            that the key,field and item definition match.
+          */
+          (*cur_group->item)->maybe_null= maybe_null= 0;
+        }
+
 	if (!(cur_group->field= field->new_key_field(thd->mem_root,table,
                                                      group_buff +
                                                      test(maybe_null),
                                                      field->null_ptr,
                                                      field->null_bit)))
 	  goto err; /* purecov: inspected */
+
 	if (maybe_null)
 	{
 	  /*
@@ -10667,6 +10682,12 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
       }
       keyinfo->key_length+=  key_part_info->length;
     }
+    /*
+      Ensure we didn't overrun the group buffer. The < is only true when
+      some maybe_null fields was changed to be not null fields.
+    */
+    DBUG_ASSERT(using_unique_constraint ||
+                group_buff <= param->group_buff + param->group_length);
   }
 
   if (distinct && field_count != param->hidden_field_count)