bk commit into 5.0 tree (gluh:1.2047) BUG#13496

[gluh]

Below is the list of changes that have just been committed into a local
5.0 repository of gluh. When gluh does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2047 05/10/18 12:39:40 gluh@stripped +13 -0
  Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    added 'DUAL' table. 'DUAL' uses I_S algorithm. Difference between I_S tables and 
    'DUAL' is that 'DUAL' has ematy db name.

  mysql-test/t/dual.test
    1.1 05/10/18 12:39:29 gluh@stripped +45 -0

  mysql-test/r/dual.result
    1.1 05/10/18 12:39:29 gluh@stripped +49 -0

  sql/sql_yacc.yy
    1.435 05/10/18 12:39:29 gluh@stripped +16 -11
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      added praser rule for 'DUAL' table

  mysql-test/t/dual.test
    1.0 05/10/18 12:39:29 gluh@stripped +0 -0
    BitKeeper file /home/gluh/MySQL/Merge/5.0/mysql-test/t/dual.test

  mysql-test/r/dual.result
    1.0 05/10/18 12:39:29 gluh@stripped +0 -0
    BitKeeper file /home/gluh/MySQL/Merge/5.0/mysql-test/r/dual.result

  sql/sql_show.cc
    1.292 05/10/18 12:39:28 gluh@stripped +24 -0
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      added 'DUAL' table structures & functions

  sql/sql_select.cc
    1.384 05/10/18 12:39:28 gluh@stripped +7 -3
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      print 'DUAL' table name without db name and backquotes if it's 'DUAL' table

  sql/sql_parse.cc
    1.507 05/10/18 12:39:28 gluh@stripped +11 -5
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      processing of 'DUAL' table during addition to table list

  sql/sql_class.h
    1.271 05/10/18 12:39:28 gluh@stripped +5 -4
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      added 'dual_table' variable to Table_ident class

  sql/mysql_priv.h
    1.361 05/10/18 12:39:28 gluh@stripped +3 -0
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
      'DUAL' table

  mysql-test/t/view.test
    1.117 05/10/18 12:39:28 gluh@stripped +2 -3
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    test fixed

  mysql-test/t/sp.test
    1.158 05/10/18 12:39:28 gluh@stripped +1 -1
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    test fixed

  mysql-test/r/view.result
    1.125 05/10/18 12:39:28 gluh@stripped +2 -1
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    result fixed

  mysql-test/r/sp.result
    1.162 05/10/18 12:39:28 gluh@stripped +0 -12
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    result fixed

  mysql-test/r/func_gconcat.result
    1.48 05/10/18 12:39:28 gluh@stripped +1 -1
    Fix for bug#13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
    result fixed

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gluh
# Host:	eagle.intranet.mysql.r18.ru
# Root:	/home/gluh/MySQL/Merge/5.0

--- 1.360/sql/mysql_priv.h	Sat Oct  8 19:39:40 2005
+++ 1.361/sql/mysql_priv.h	Tue Oct 18 12:39:28 2005
@@ -859,6 +859,9 @@
 #define is_schema_db(X) \
   !my_strcasecmp(system_charset_info, information_schema_name.str, (X))
 
+/* DUAL table */
+extern ST_SCHEMA_TABLE dual_table;
+
 /* sql_prepare.cc */
 
 void mysql_stmt_prepare(THD *thd, const char *packet, uint packet_length);

--- 1.270/sql/sql_class.h	Fri Oct 14 02:04:48 2005
+++ 1.271/sql/sql_class.h	Tue Oct 18 12:39:28 2005
@@ -1922,21 +1922,22 @@
   LEX_STRING db;
   LEX_STRING table;
   SELECT_LEX_UNIT *sel;
+  bool dual_table;
   inline Table_ident(THD *thd, LEX_STRING db_arg, LEX_STRING table_arg,
 		     bool force)
-    :table(table_arg), sel((SELECT_LEX_UNIT *)0)
+    :table(table_arg), sel((SELECT_LEX_UNIT *)0), dual_table(FALSE)
   {
     if (!force && (thd->client_capabilities & CLIENT_NO_SCHEMA))
       db.str=0;
     else
       db= db_arg;
   }
-  inline Table_ident(LEX_STRING table_arg) 
-    :table(table_arg), sel((SELECT_LEX_UNIT *)0)
+  inline Table_ident(LEX_STRING table_arg, bool dual_table_arg= 0) 
+    :table(table_arg), sel((SELECT_LEX_UNIT *)0), dual_table(dual_table_arg)
   {
     db.str=0;
   }
-  inline Table_ident(SELECT_LEX_UNIT *s) : sel(s) 
+  inline Table_ident(SELECT_LEX_UNIT *s) : sel(s), dual_table(FALSE) 
   {
     /* We must have a table name here as this is used with add_table_to_list */
     db.str=0; table.str= internal_table_name; table.length=1;

--- 1.506/sql/sql_parse.cc	Thu Oct 13 14:12:07 2005
+++ 1.507/sql/sql_parse.cc	Tue Oct 18 12:39:28 2005
@@ -6126,8 +6126,9 @@
   if (!table)
     DBUG_RETURN(0);				// End of memory
   alias_str= alias ? alias->str : table->table.str;
-  if (check_table_name(table->table.str,table->table.length) ||
-      table->db.str && check_db_name(table->db.str))
+  if (!table->dual_table && 
+      (check_table_name(table->table.str,table->table.length) ||
+       table->db.str && check_db_name(table->db.str)))
   {
     my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str);
     DBUG_RETURN(0);
@@ -6151,7 +6152,7 @@
     ptr->db= table->db.str;
     ptr->db_length= table->db.length;
   }
-  else if (thd->db)
+  else if (thd->db && !table->dual_table)
   {
     ptr->db= thd->db;
     ptr->db_length= thd->db_length;
@@ -6175,8 +6176,13 @@
   ptr->force_index= test(table_options & TL_OPTION_FORCE_INDEX);
   ptr->ignore_leaves= test(table_options & TL_OPTION_IGNORE_LEAVES);
   ptr->derived=	    table->sel;
-  if (!my_strcasecmp(system_charset_info, ptr->db,
-                     information_schema_name.str))
+  if (table->dual_table && ptr->db[0] == 0)
+  {
+    ptr->schema_table_name= ptr->table_name;
+    ptr->schema_table= &dual_table;
+  }
+  else if (!my_strcasecmp(system_charset_info, ptr->db,
+                          information_schema_name.str))
   {
     ST_SCHEMA_TABLE *schema_table= find_schema_table(thd, ptr->table_name);
     if (!schema_table ||

--- 1.383/sql/sql_select.cc	Fri Oct 14 02:04:48 2005
+++ 1.384/sql/sql_select.cc	Tue Oct 18 12:39:28 2005
@@ -13798,15 +13798,19 @@
       // A normal table
 
       if (!(belong_to_view &&
-            belong_to_view->compact_view_format))
+            belong_to_view->compact_view_format)
+          && schema_table != &dual_table)
       {
         append_identifier(thd, str, db, db_length);
         str->append('.');
       }
       if (schema_table)
       {
-        append_identifier(thd, str, schema_table_name,
-                          strlen(schema_table_name));
+        if (schema_table == &dual_table)
+          str->append(schema_table_name);
+        else
+          append_identifier(thd, str, schema_table_name,
+                            strlen(schema_table_name));
         cmp_name= schema_table_name;
       }
       else

--- 1.291/sql/sql_show.cc	Thu Oct 13 20:23:47 2005
+++ 1.292/sql/sql_show.cc	Tue Oct 18 12:39:28 2005
@@ -4121,6 +4121,30 @@
 };
 
 
+/* DUAL table*/
+
+int fill_dual(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  DBUG_ENTER("fill_dual");
+  TABLE *table= tables->table;
+  restore_record(table, s->default_values);
+  table->field[0]->store(STRING_WITH_LEN("X"), system_charset_info);
+  if (schema_table_store_record(thd, table))
+    DBUG_RETURN(1);
+  DBUG_RETURN(0);
+}
+
+
+ST_FIELD_INFO dual_fields_info[]=
+{
+  {"DUMMY", 1, MYSQL_TYPE_STRING, 0, 0, 0}
+};
+
+
+ST_SCHEMA_TABLE dual_table=  {"DUAL", dual_fields_info, create_schema_table,
+                              fill_dual, 0, 0, -1, -1, 0};
+
+
 #ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
 template class List_iterator_fast<char>;
 template class List<char>;

--- 1.434/sql/sql_yacc.yy	Thu Oct 13 19:27:29 2005
+++ 1.435/sql/sql_yacc.yy	Tue Oct 18 12:39:29 2005
@@ -689,7 +689,7 @@
 	opt_table_alias
 
 %type <table>
-	table_ident table_ident_nodb references xid
+	table_ident table_ident_nodb table_ident_with_dual references xid
 
 %type <simple_string>
 	remember_name remember_end opt_ident opt_db text_or_password
@@ -4076,11 +4076,6 @@
 select_from:
 	  FROM join_table_list where_clause group_clause having_clause
 	       opt_order_clause opt_limit_clause procedure_clause
-        | FROM DUAL_SYM where_clause opt_limit_clause
-          /* oracle compatibility: oracle always requires FROM clause,
-             and DUAL is system table without fields.
-             Is "SELECT 1 FROM DUAL" any better than "SELECT 1" ?
-          Hmmm :) */
 	;
 
 select_options:
@@ -5360,7 +5355,7 @@
 	  sel->use_index_ptr=sel->ignore_index_ptr=0;
 	  sel->table_join_options= 0;
 	}
-        table_ident opt_table_alias opt_key_definition
+        table_ident_with_dual opt_table_alias opt_key_definition
 	{
 	  LEX *lex= Lex;
 	  SELECT_LEX *sel= lex->current_select;
@@ -6456,7 +6451,7 @@
 	| ENGINE_SYM storage_engines 
 	  { Lex->create_info.db_type= $2; }
 	  show_engine_param
-	| opt_full COLUMNS from_or_in table_ident opt_db wild_and_where
+	| opt_full COLUMNS from_or_in table_ident_with_dual opt_db wild_and_where
 	  {
  	    LEX *lex= Lex;
 	    lex->sql_command= SQLCOM_SELECT;
@@ -6489,7 +6484,7 @@
 	    LEX *lex= Lex;
 	    lex->sql_command= SQLCOM_SHOW_BINLOG_EVENTS;
           } opt_limit_clause_init
-        | keys_or_index from_or_in table_ident opt_db where_clause
+        | keys_or_index from_or_in table_ident_with_dual opt_db where_clause
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_SELECT;
@@ -6609,7 +6604,7 @@
 	    Lex->create_info.options=$3;
 	    Lex->name=$4.str;
 	  }
-        | CREATE TABLE_SYM table_ident
+        | CREATE TABLE_SYM table_ident_with_dual
           {
             LEX *lex= Lex;
 	    lex->sql_command = SQLCOM_SHOW_CREATE;
@@ -6739,7 +6734,7 @@
 
 /* A Oracle compatible synonym for show */
 describe:
-	describe_command table_ident
+	describe_command table_ident_with_dual
 	{
           LEX *lex= Lex;
           lex->lock_option= TL_READ;
@@ -7392,6 +7387,16 @@
 
 table_ident_nodb:
 	ident			{ LEX_STRING db={(char*) any_db,3}; $$=new Table_ident(YYTHD, db,$1,0); }
+        ;
+
+table_ident_with_dual:
+        DUAL_SYM
+        {
+          LEX_STRING table;
+          make_lex_string(YYTHD, &table, "DUAL", 4, 0);
+          $$=new Table_ident(table, TRUE);
+        }
+        | table_ident {  $$= $1; }
         ;
 
 IDENT_sys:

--- 1.124/mysql-test/r/view.result	Wed Oct 12 02:59:46 2005
+++ 1.125/mysql-test/r/view.result	Tue Oct 18 12:39:28 2005
@@ -640,9 +640,10 @@
 drop view v1;
 drop table t1;
 CREATE VIEW v02 AS SELECT * FROM DUAL;
-ERROR HY000: No tables used
 SHOW TABLES;
 Tables_in_test
+v02
+DROP VIEW v02;
 CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2);
 select * from v1;
 EXISTS (SELECT 1 UNION SELECT 2)

--- 1.116/mysql-test/t/view.test	Wed Oct 12 02:59:47 2005
+++ 1.117/mysql-test/t/view.test	Tue Oct 18 12:39:28 2005
@@ -565,12 +565,11 @@
 drop table t1;
 
 #
-# error on preparation
+# View for DUAL
 #
--- error 1096
 CREATE VIEW v02 AS SELECT * FROM DUAL;
 SHOW TABLES;
-
+DROP VIEW v02;
 #
 # EXISTS with UNION VIEW
 #
--- New file ---
+++ mysql-test/r/dual.result	05/10/18 12:39:29
select * from dual;
DUMMY
X
select * from dual;
DUMMY
X
SELECT COUNT(*) col FROM dual WHERE 1=0;
col
0
SELECT COUNT(*) col FROM dual WHERE 1=1;
col
1
show fields from dual;
Field	Type	Null	Key	Default	Extra
DUMMY	varchar(1)	NO			
show keys from dual;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
show create table dual;
Table	Create Table
DUAL	CREATE TEMPORARY TABLE `DUAL` (
  `DUMMY` varchar(1) NOT NULL default ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
describe dual;
Field	Type	Null	Key	Default	Extra
DUMMY	varchar(1)	NO			
delete from dual;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual' at line 1
insert into dual values ("C");
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual values ("C")' at line 1
create database dual;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual' at line 1
create table dual (f1 int);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual (f1 int)' at line 1
create table t1(f1 int);
create view dual (c) as select f1 from t1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual (c) as select f1 from
t1' at line 1
CREATE PROCEDURE dual ()
BEGIN
SELECT 'foo' FROM t1;
END |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'dual ()
BEGIN
SELECT 'foo' FROM t1;
END' at line 1
drop table t1;
create table `dual`(f1 int);
select * from `dual`;
f1
drop table `dual`;

--- New file ---
+++ mysql-test/t/dual.test	05/10/18 12:39:29
#
# Bug #13496 SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
#
select * from dual;
connect (con5,localhost,root,,*NO-ONE*);
select * from dual;
connection default;
SELECT COUNT(*) col FROM dual WHERE 1=0;
SELECT COUNT(*) col FROM dual WHERE 1=1;

show fields from dual;
show keys from dual;
show create table dual;
describe dual;

#create view v1 (c) as select dummy from dual;
#select * from v1;
#show create view v1;
#drop view v1;


# Forbidden operations
--error 1064
delete from dual;
--error 1064
insert into dual values ("C");
--error 1064
create database dual;
--error 1064
create table dual (f1 int);
create table t1(f1 int);
--error 1064
create view dual (c) as select f1 from t1;
delimiter |;
--error 1064
CREATE PROCEDURE dual ()
BEGIN
  SELECT 'foo' FROM t1;
END |
delimiter ;|
drop table t1;

create table `dual`(f1 int);
select * from `dual`;
drop table `dual`;


--- 1.161/mysql-test/r/sp.result	Sun Oct 16 23:46:08 2005
+++ 1.162/mysql-test/r/sp.result	Tue Oct 18 12:39:28 2005
@@ -2887,19 +2887,7 @@
 close c;
 end|
 call bug10961()|
-x
-1
-x
-2
-x
-3
 call bug10961()|
-x
-1
-x
-2
-x
-3
 drop procedure bug10961|
 DROP PROCEDURE IF EXISTS bug6866|
 DROP VIEW IF EXISTS tv|

--- 1.157/mysql-test/t/sp.test	Sun Oct 16 23:44:14 2005
+++ 1.158/mysql-test/t/sp.test	Tue Oct 18 12:39:28 2005
@@ -3626,7 +3626,7 @@
 --disable_warnings
 drop procedure if exists bug10961|
 --enable_warnings
-# "select * from dual" results in an error, so the cursor will not open
+# "select * from dual" is executed without error, so the cursor will open
 create procedure bug10961()
 begin
   declare v char;

--- 1.47/mysql-test/r/func_gconcat.result	Fri Sep  9 12:44:06 2005
+++ 1.48/mysql-test/r/func_gconcat.result	Tue Oct 18 12:39:28 2005
@@ -566,7 +566,7 @@
 DROP TABLE t1,t2;
 select * from (select group_concat('c') from DUAL) t;
 group_concat('c')
-NULL
+c
 create table t1 ( a int not null default 0);
 select * from (select group_concat(a) from t1) t2;
 group_concat(a)