diff options
106 files changed, 1749 insertions, 5305 deletions
diff --git a/VC++Files/client/mysqldump.dsp b/VC++Files/client/mysqldump.dsp index 527d1eb0a74..09f0df15ceb 100644 --- a/VC++Files/client/mysqldump.dsp +++ b/VC++Files/client/mysqldump.dsp @@ -129,5 +129,9 @@ SOURCE=.\mysqldump.c !ENDIF # End Source File +# Begin Source File + +SOURCE=..\sql-common\my_user.c +# End Source File # End Target # End Project diff --git a/VC++Files/client/mysqldump_ia64.dsp b/VC++Files/client/mysqldump_ia64.dsp index 79a7059cae5..360d16ea8e4 100644 --- a/VC++Files/client/mysqldump_ia64.dsp +++ b/VC++Files/client/mysqldump_ia64.dsp @@ -132,5 +132,9 @@ SOURCE=.\mysqldump.c !ENDIF # End Source File +# Begin Source File + +SOURCE=..\sql-common\my_user.c +# End Source File # End Target # End Project diff --git a/VC++Files/libmysqld/libmysqld.dsp b/VC++Files/libmysqld/libmysqld.dsp index 32c0aac5e54..53106e90338 100644 --- a/VC++Files/libmysqld/libmysqld.dsp +++ b/VC++Files/libmysqld/libmysqld.dsp @@ -504,6 +504,10 @@ SOURCE=..\sql\sql_load.cpp # End Source File # Begin Source File +SOURCE=..\sql\sql_locale.cpp +# End Source File +# Begin Source File + SOURCE=..\sql\sql_manager.cpp
# End Source File
# Begin Source File
diff --git a/VC++Files/libmysqld/libmysqld.vcproj b/VC++Files/libmysqld/libmysqld.vcproj index 52b4d4c4eba..828e069557d 100644 --- a/VC++Files/libmysqld/libmysqld.vcproj +++ b/VC++Files/libmysqld/libmysqld.vcproj @@ -3306,6 +3306,42 @@ </FileConfiguration> </File> <File + RelativePath="..\sql\sql_locale.cpp"> + <FileConfiguration + Name="Debug|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="0" + AdditionalIncludeDirectories="" + PreprocessorDefinitions="WIN32;_DEBUG;_WINDOWS;_MBCS;SAFEMALLOC;HAVE_BERKELEY_DB;USE_SYMDIR;SIGNAL_WITH_VIO_CLOSE;HAVE_DLOPEN;EMBEDDED_LIBRARY;HAVE_INNOBASE_DB;USE_TLS;__WIN__;$(NoInherit)" + BasicRuntimeChecks="3"/> + </FileConfiguration> + <FileConfiguration + Name="pro|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions="WIN32;USE_SYMDIR;SIGNAL_WITH_VIO_CLOSE;EMBEDDED_LIBRARY;USE_TLS;__WIN__;MYSQL_SERVER;LICENSE=Commercial;_MBCS;HAVE_DLOPEN;HAVE_INNOBASE_DB;DBUG_OFF;NDEBUG;_WINDOWS;_CONSOLE;$(NoInherit)"/> + </FileConfiguration> + <FileConfiguration + Name="Release|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_MBCS;SIGNAL_WITH_VIO_CLOSE;HAVE_DLOPEN;EMBEDDED_LIBRARY;HAVE_INNOBASE_DB;DBUG_OFF;USE_TLS;__WIN__;$(NoInherit)"/> + </FileConfiguration> + <FileConfiguration + Name="classic|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions="WIN32;_WINDOWS;USE_SYMDIR;SIGNAL_WITH_VIO_CLOSE;HAVE_DLOPEN;EMBEDDED_LIBRARY;USE_TLS;__WIN__;LICENSE=Commercial;DBUG_OFF;_MBCS;NDEBUG;$(NoInherit)"/> + </FileConfiguration> + </File> + <File RelativePath="..\sql\sql_manager.cpp"> <FileConfiguration Name="Debug|Win32"> diff --git a/VC++Files/libmysqld/libmysqld_ia64.dsp b/VC++Files/libmysqld/libmysqld_ia64.dsp index 9668193fc1d..6633274e781 100644 --- a/VC++Files/libmysqld/libmysqld_ia64.dsp +++ b/VC++Files/libmysqld/libmysqld_ia64.dsp @@ -454,6 +454,10 @@ SOURCE=..\sql\sql_load.cpp # End Source File # Begin Source File +SOURCE=..\sql\sql_locale.cpp +# End Source File +# Begin Source File + SOURCE=..\sql\sql_manager.cpp # End Source File # Begin Source File diff --git a/VC++Files/mysqldemb/mysqldemb.dsp b/VC++Files/mysqldemb/mysqldemb.dsp index 61a745ff7e8..8acc313181b 100644 --- a/VC++Files/mysqldemb/mysqldemb.dsp +++ b/VC++Files/mysqldemb/mysqldemb.dsp @@ -359,6 +359,10 @@ SOURCE=..\sql\sql_lex.cpp SOURCE=..\sql\sql_list.cpp
# End Source File
+# Begin Source File + +SOURCE=..\sql\sql_locale.cpp +# End Source File # Begin Source File
SOURCE=..\sql\sql_manager.cpp
diff --git a/VC++Files/mysqldemb/mysqldemb.vcproj b/VC++Files/mysqldemb/mysqldemb.vcproj index 1105e750ee7..8c052735661 100644 --- a/VC++Files/mysqldemb/mysqldemb.vcproj +++ b/VC++Files/mysqldemb/mysqldemb.vcproj @@ -2234,6 +2234,42 @@ </FileConfiguration> </File> <File + RelativePath="..\sql\sql_locale.cpp"> + <FileConfiguration + Name="classic|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="pro|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="Debug|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="0" + AdditionalIncludeDirectories="" + PreprocessorDefinitions="" + BasicRuntimeChecks="3"/> + </FileConfiguration> + <FileConfiguration + Name="Release|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + </File> + <File RelativePath="..\sql\sql_manager.cpp"> <FileConfiguration Name="classic|Win32"> diff --git a/VC++Files/mysqldemb/mysqldemb_ia64.dsp b/VC++Files/mysqldemb/mysqldemb_ia64.dsp index 5b54a7756e1..9d7367e4c0f 100644 --- a/VC++Files/mysqldemb/mysqldemb_ia64.dsp +++ b/VC++Files/mysqldemb/mysqldemb_ia64.dsp @@ -365,6 +365,10 @@ SOURCE=..\sql\sql_list.cpp # End Source File # Begin Source File +SOURCE=..\sql\sql_locale.cpp +# End Source File +# Begin Source File + SOURCE=..\sql\sql_manager.cpp # End Source File # Begin Source File diff --git a/VC++Files/mysys/mysys.vcproj b/VC++Files/mysys/mysys.vcproj index 3885e18cea8..3f2c5e755ac 100644 --- a/VC++Files/mysys/mysys.vcproj +++ b/VC++Files/mysys/mysys.vcproj @@ -22,7 +22,7 @@ Optimization="0" OptimizeForProcessor="2" AdditionalIncludeDirectories="../include,../zlib" - PreprocessorDefinitions="__NT__;_DEBUG;SAFEMALLOC;SAFE_MUTEX;_WINDOWS;USE_SYMDIR" + PreprocessorDefinitions="_DEBUG;SAFEMALLOC;SAFE_MUTEX;_WINDOWS;USE_SYMDIR" RuntimeLibrary="1" PrecompiledHeaderFile=".\debug/mysys.pch" AssemblerListingLocation=".\debug/" diff --git a/VC++Files/sql/mysqld.dsp b/VC++Files/sql/mysqld.dsp index 7d58e034e6b..259c044e390 100644 --- a/VC++Files/sql/mysqld.dsp +++ b/VC++Files/sql/mysqld.dsp @@ -1639,6 +1639,10 @@ SOURCE=.\sql_load.cpp # End Source File # Begin Source File +SOURCE=.\sql\sql_locale.cpp +# End Source File +# Begin Source File + SOURCE=.\sql_manager.cpp # End Source File # Begin Source File @@ -2057,5 +2061,9 @@ SOURCE=.\unireg.cpp !ENDIF # End Source File +# Begin Source File + +SOURCE=..\sql-common\my_user.c +# End Source File # End Target # End Project diff --git a/VC++Files/sql/mysqld.vcproj b/VC++Files/sql/mysqld.vcproj index d8cf5cedb2a..e18a6364c8b 100644 --- a/VC++Files/sql/mysqld.vcproj +++ b/VC++Files/sql/mysqld.vcproj @@ -43,7 +43,10 @@ OutputFile="../client_classic/mysqld-nt.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - AdditionalLibraryDirectories="" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_classic/mysqld-nt.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_classic/mysqld-nt.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -104,6 +107,10 @@ OutputFile="../client_release/mysqld-max.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_release/mysqld-max.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_release/mysqld-max.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -164,9 +171,10 @@ OutputFile="../client_release/mysqld-max-nt.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - ProgramDatabaseFile=".\max_nt/mysqld-max-nt.pdb" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_release/mysqld-max-nt.pdb" GenerateMapFile="TRUE" - MapFileName=".\max_nt/mysqld-max-nt.map" + MapFileName="../client_release/mysqld-max-nt.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -227,9 +235,10 @@ OutputFile="../client_release/mysqld-nt.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - ProgramDatabaseFile=".\nt/mysqld-nt.pdb" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_release/mysqld-nt.pdb" GenerateMapFile="TRUE" - MapFileName=".\nt/mysqld-nt.map" + MapFileName="../client_release/mysqld-nt.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -290,7 +299,10 @@ OutputFile="../client_pro/mysqld-nt.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - AdditionalLibraryDirectories="" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_pro/mysqld-nt.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_pro/mysqld-nt.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -350,7 +362,9 @@ LinkIncremental="1" SuppressStartupBanner="TRUE" GenerateDebugInformation="TRUE" - ProgramDatabaseFile=".\debug/mysqld-debug.pdb" + ProgramDatabaseFile="../client_debug/mysqld-debug.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_debug/mysqld-debug.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -411,7 +425,10 @@ OutputFile="../client_pro/mysqld.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - AdditionalLibraryDirectories="" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_pro/mysqld.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_pro/mysqld.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -472,7 +489,10 @@ OutputFile="../client_classic/mysqld.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" - AdditionalLibraryDirectories="" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_classic/mysqld.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_classic/mysqld.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -533,6 +553,10 @@ OutputFile="../client_release/mysqld.exe" LinkIncremental="1" SuppressStartupBanner="TRUE" + GenerateDebugInformation="TRUE" + ProgramDatabaseFile="../client_release/mysqld.pdb" + GenerateMapFile="TRUE" + MapFileName="../client_release/mysqld.map" SubSystem="1" TargetMachine="1"/> <Tool @@ -6834,6 +6858,82 @@ </FileConfiguration> </File> <File + RelativePath="sql_locale.cpp"> + <FileConfiguration + Name="classic nt|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="Max|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="Max nt|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="nt|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="pro nt|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="Debug|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="0" + OptimizeForProcessor="1" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="pro|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="classic|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + <FileConfiguration + Name="Release|Win32"> + <Tool + Name="VCCLCompilerTool" + Optimization="2" + AdditionalIncludeDirectories="" + PreprocessorDefinitions=""/> + </FileConfiguration> + </File> + <File RelativePath="sql_manager.cpp"> <FileConfiguration Name="classic nt|Win32"> diff --git a/VC++Files/sql/mysqld_ia64.dsp b/VC++Files/sql/mysqld_ia64.dsp index 310f48fcfb9..24c3ab4b8e1 100644 --- a/VC++Files/sql/mysqld_ia64.dsp +++ b/VC++Files/sql/mysqld_ia64.dsp @@ -1607,6 +1607,10 @@ SOURCE=.\sql_load.cpp # End Source File # Begin Source File +SOURCE=.\sql_locale.cpp +# End Source File +# Begin Source File + SOURCE=.\sql_manager.cpp # End Source File # Begin Source File @@ -2009,5 +2013,9 @@ SOURCE=.\unireg.cpp !ENDIF # End Source File +# Begin Source File + +SOURCE=..\sql-common\my_user.c +# End Source File # End Target # End Project diff --git a/VC++Files/sql/mysqldmax.dsp b/VC++Files/sql/mysqldmax.dsp index 2711b274da0..6e248c4a041 100644 --- a/VC++Files/sql/mysqldmax.dsp +++ b/VC++Files/sql/mysqldmax.dsp @@ -781,6 +781,22 @@ SOURCE=.\sql_load.cpp # End Source File # Begin Source File +SOURCE=.\sql_locale.cpp + +!IF "$(CFG)" == "mysqldmax - Win32 Release" + +!ELSEIF "$(CFG)" == "mysqldmax - Win32 Debug" + +# ADD CPP /G5 +# SUBTRACT CPP /YX /Yc /Yu + +!ELSEIF "$(CFG)" == "mysqldmax - Win32 nt" + +!ENDIF + +# End Source File +# Begin Source File + SOURCE=.\sql_manager.cpp # End Source File # Begin Source File diff --git a/VC++Files/sql/mysqldmax_ia64.dsp b/VC++Files/sql/mysqldmax_ia64.dsp index 9d79d224e4a..444b043b5c2 100644 --- a/VC++Files/sql/mysqldmax_ia64.dsp +++ b/VC++Files/sql/mysqldmax_ia64.dsp @@ -1195,6 +1195,31 @@ SOURCE=.\sql_load.cpp # End Source File # Begin Source File +SOURCE=.\sql_locale.cpp + +!IF "$(CFG)" == "mysqldmax - Win32 Release" + +!ELSEIF "$(CFG)" == "mysqldmax - Win32 Debug" + +# ADD CPP /G5 /Od +# SUBTRACT CPP /YX /Yc /Yu + +!ELSEIF "$(CFG)" == "mysqldmax - Win32 nt" + +!ELSEIF "$(CFG)" == "mysqldmax - WinIA64 Release" + +!ELSEIF "$(CFG)" == "mysqldmax - WinIA64 Debug" + +# ADD CPP /G5 /Od /G2 /EHsc /D"_IA64_" /Zi /D"WIN64" /D"WIN32" /D"_AFX_NO_DAO_SUPPORT" /Wp64 /Zm600 +# SUBTRACT CPP /YX /Yc /Yu + +!ELSEIF "$(CFG)" == "mysqldmax - WinIA64 nt" + +!ENDIF + +# End Source File +# Begin Source File + SOURCE=.\sql_manager.cpp # End Source File # Begin Source File diff --git a/client/mysqldump.c b/client/mysqldump.c index de35358ee42..56260733db5 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -2647,7 +2647,7 @@ static int init_dumping(char *database) /* length of table name * 2 (if name contains quotes), 2 quotes and 0 */ - char quoted_database_buf[64*2+3]; + char quoted_database_buf[NAME_LEN*2+3]; char *qdatabase= quote_name(database,quoted_database_buf,opt_quoted); if (opt_comments) { @@ -2796,7 +2796,18 @@ static my_bool dump_all_views_in_db(char *database) DB_error(sock, "when selecting the database"); return 1; } - + if (opt_databases || opt_alldbs) + { + char quoted_database_buf[NAME_LEN*2+3]; + char *qdatabase= quote_name(database,quoted_database_buf,opt_quoted); + if (opt_comments) + { + fprintf(md_result_file,"\n--\n-- Current Database: %s\n--\n", qdatabase); + check_io(md_result_file); + } + fprintf(md_result_file,"\nUSE %s;\n", qdatabase); + check_io(md_result_file); + } if (opt_xml) print_xml_tag1(md_result_file, "", "database name=", database, "\n"); if (lock_tables) @@ -3249,7 +3260,7 @@ static char *primary_key_fields(const char *table_name) MYSQL_RES *res = NULL; MYSQL_ROW row; /* SHOW KEYS FROM + table name * 2 (escaped) + 2 quotes + \0 */ - char show_keys_buff[15 + 64 * 2 + 3]; + char show_keys_buff[15 + NAME_LEN * 2 + 3]; uint result_length = 0; char *result = 0; diff --git a/configure.in b/configure.in index d4ee825a47f..3430bd8a5b5 100644 --- a/configure.in +++ b/configure.in @@ -2342,12 +2342,16 @@ then man_dirs="man" man1_files=`ls -1 $srcdir/man/*.1 | sed -e 's;^.*man/;;'` man1_files=`echo $man1_files` + man8_files=`ls -1 $srcdir/man/*.8 | sed -e 's;^.*man/;;'` + man8_files=`echo $man8_files` else man_dirs="" man1_files="" + man8_files="" fi AC_SUBST(man_dirs) AC_SUBST(man1_files) +AC_SUBST(man8_files) # Shall we build the bench code? AC_ARG_WITH(bench, diff --git a/extra/yassl/taocrypt/include/asn.hpp b/extra/yassl/taocrypt/include/asn.hpp index 90bc46a59fd..8bea2ae780b 100644 --- a/extra/yassl/taocrypt/include/asn.hpp +++ b/extra/yassl/taocrypt/include/asn.hpp @@ -103,7 +103,7 @@ enum Constants MAX_ALGO_SIZE = 9, MAX_DIGEST_SZ = 25, // SHA + enum(Bit or Octet) + length(4) DSA_SIG_SZ = 40, - NAME_MAX = 512 // max total of all included names + ASN_NAME_MAX = 512 // max total of all included names }; @@ -216,7 +216,7 @@ enum { SHA_SIZE = 20 }; // A Signing Authority class Signer { PublicKey key_; - char name_[NAME_MAX]; + char name_[ASN_NAME_MAX]; byte hash_[SHA_SIZE]; public: Signer(const byte* k, word32 kSz, const char* n, const byte* h); @@ -270,8 +270,8 @@ private: byte subjectHash_[SHA_SIZE]; // hash of all Names byte issuerHash_[SHA_SIZE]; // hash of all Names byte* signature_; - char issuer_[NAME_MAX]; // Names - char subject_[NAME_MAX]; // Names + char issuer_[ASN_NAME_MAX]; // Names + char subject_[ASN_NAME_MAX]; // Names char beforeDate_[MAX_DATE_SZ]; // valid before date char afterDate_[MAX_DATE_SZ]; // valid after date bool verify_; // Default to yes, but could be off diff --git a/extra/yassl/taocrypt/src/asn.cpp b/extra/yassl/taocrypt/src/asn.cpp index beb5490bb66..45fb1e60a0c 100644 --- a/extra/yassl/taocrypt/src/asn.cpp +++ b/extra/yassl/taocrypt/src/asn.cpp @@ -665,7 +665,7 @@ void CertDecoder::GetName(NameType nt) SHA sha; word32 length = GetSequence(); // length of all distinguished names - assert (length < NAME_MAX); + assert (length < ASN_NAME_MAX); length += source_.get_index(); char* ptr = (nt == ISSUER) ? issuer_ : subject_; diff --git a/extra/yassl/yassl.dsp b/extra/yassl/yassl.dsp index dc090512743..58c016da448 100644 --- a/extra/yassl/yassl.dsp +++ b/extra/yassl/yassl.dsp @@ -40,8 +40,8 @@ RSC=rc.exe # PROP Output_Dir "Release" # PROP Intermediate_Dir "Release" # PROP Target_Dir "" -# ADD BASE CPP /nologo /W3 /GX /O2 /D "WIN32" /D "NDEBUG" /D "_MBCS" /D "_LIB" /YX /FD /c -# ADD CPP /nologo /MT /W3 /O2 /I "include" /I "taocrypt\include" /I "mySTL" /D "WIN32" /D "NDEBUG" /D "_MBCS" /D "_LIB" /YX /FD /c +# ADD BASE CPP /nologo /W3 /GX /O2 /D "WIN32" /D "NDEBUG" /D "_MBCS" /D "_LIB" /D "YASSL_PREFIX" /YX /FD /c +# ADD CPP /nologo /MT /W3 /O2 /I "include" /I "taocrypt\include" /I "mySTL" /D "WIN32" /D "NDEBUG" /D "_MBCS" /D "_LIB" /D "YASSL_PREFIX" /YX /FD /c # ADD BASE RSC /l 0x409 /d "NDEBUG" # ADD RSC /l 0x409 /d "NDEBUG" BSC32=bscmake.exe @@ -63,8 +63,8 @@ LIB32=link.exe -lib # PROP Output_Dir "Debug" # PROP Intermediate_Dir "Debug" # PROP Target_Dir "" -# ADD BASE CPP /nologo /W3 /Gm /GX /ZI /Od /D "WIN32" /D "_DEBUG" /D "_MBCS" /D "_LIB" /YX /FD /GZ /c -# ADD CPP /nologo /MTd /W3 /Gm /ZI /Od /I "include" /I "taocrypt\include" /I "mySTL" /D "WIN32" /D "_DEBUG" /D "_MBCS" /D "_LIB" /FR /YX /FD /GZ /c +# ADD BASE CPP /nologo /W3 /Gm /GX /ZI /Od /D "WIN32" /D "_DEBUG" /D "_MBCS" /D "_LIB" /D "YASSL_PREFIX" /YX /FD /GZ /c +# ADD CPP /nologo /MTd /W3 /Gm /ZI /Od /I "include" /I "taocrypt\include" /I "mySTL" /D "WIN32" /D "_DEBUG" /D "_MBCS" /D "_LIB" /D "YASSL_PREFIX" /FR /YX /FD /GZ /c # ADD BASE RSC /l 0x409 /d "_DEBUG" # ADD RSC /l 0x409 /d "_DEBUG" BSC32=bscmake.exe diff --git a/include/my_base.h b/include/my_base.h index 05ba38e77eb..d8a0e15ccbe 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -356,8 +356,9 @@ enum ha_base_keytype { #define HA_ERR_NULL_IN_SPATIAL 158 /* NULLs are not supported in spatial index */ #define HA_ERR_TABLE_DEF_CHANGED 159 /* The table changed in storage engine */ #define HA_ERR_TABLE_NEEDS_UPGRADE 160 /* The table changed in storage engine */ +#define HA_ERR_TABLE_READONLY 161 /* The table is not writable */ -#define HA_ERR_LAST 160 /*Copy last error nr.*/ +#define HA_ERR_LAST 161 /*Copy last error nr.*/ /* Add error numbers before HA_ERR_LAST and change it accordingly. */ #define HA_ERR_ERRORS (HA_ERR_LAST - HA_ERR_FIRST + 1) diff --git a/innobase/btr/btr0cur.c b/innobase/btr/btr0cur.c index 3d7b6fffb52..d51a090be75 100644 --- a/innobase/btr/btr0cur.c +++ b/innobase/btr/btr0cur.c @@ -439,7 +439,7 @@ retry_page_get: if (UNIV_LIKELY_NULL(heap)) { mem_heap_free(heap); } - return; + goto func_exit; } /* Insert to the insert buffer did not succeed: @@ -555,6 +555,7 @@ retry_page_get: || mode != PAGE_CUR_LE); } +func_exit: if (has_search_latch) { rw_lock_s_lock(&btr_search_latch); diff --git a/innobase/ibuf/ibuf0ibuf.c b/innobase/ibuf/ibuf0ibuf.c index e4694ed52ae..b95f0a1832b 100644 --- a/innobase/ibuf/ibuf0ibuf.c +++ b/innobase/ibuf/ibuf0ibuf.c @@ -950,14 +950,11 @@ ibool ibuf_fixed_addr_page( /*=================*/ /* out: TRUE if a fixed address ibuf i/o page */ + ulint space, /* in: space id */ ulint page_no)/* in: page number */ { - if ((ibuf_bitmap_page(page_no)) - || (page_no == IBUF_TREE_ROOT_PAGE_NO)) { - return(TRUE); - } - - return(FALSE); + return((space == 0 && page_no == IBUF_TREE_ROOT_PAGE_NO) + || ibuf_bitmap_page(page_no)); } /*************************************************************************** @@ -981,7 +978,7 @@ ibuf_page( return(FALSE); } - if (ibuf_fixed_addr_page(page_no)) { + if (ibuf_fixed_addr_page(space, page_no)) { return(TRUE); } @@ -1029,7 +1026,7 @@ ibuf_page_low( return(FALSE); } #endif - if (ibuf_fixed_addr_page(page_no)) { + if (ibuf_fixed_addr_page(space, page_no)) { return(TRUE); } @@ -3052,7 +3049,7 @@ ibuf_merge_or_delete_for_page( return; } #endif - if (ibuf_fixed_addr_page(page_no) || fsp_descr_page(page_no) + if (ibuf_fixed_addr_page(space, page_no) || fsp_descr_page(page_no) || trx_sys_hdr_page(space, page_no)) { return; } diff --git a/innobase/include/sync0rw.ic b/innobase/include/sync0rw.ic index b1ae636010a..9e15475ae53 100644 --- a/innobase/include/sync0rw.ic +++ b/innobase/include/sync0rw.ic @@ -362,7 +362,7 @@ rw_lock_s_unlock_func( /* Reset the shared lock by decrementing the reader count */ - ut_ad(lock->reader_count > 0); + ut_a(lock->reader_count > 0); lock->reader_count--; #ifdef UNIV_SYNC_DEBUG diff --git a/libmysqld/lib_sql.cc b/libmysqld/lib_sql.cc index 2640910990e..40966be46a5 100644 --- a/libmysqld/lib_sql.cc +++ b/libmysqld/lib_sql.cc @@ -890,10 +890,14 @@ bool Protocol::send_fields(List<Item> *list, uint flags) } else { + uint max_char_len; /* With conversion */ client_field->charsetnr= thd_cs->number; - uint char_len= server_field.length / item->collation.collation->mbmaxlen; - client_field->length= char_len * thd_cs->mbmaxlen; + max_char_len= (server_field.type >= (int) MYSQL_TYPE_TINY_BLOB && + server_field.type <= (int) MYSQL_TYPE_BLOB) ? + server_field.length / item->collation.collation->mbminlen : + server_field.length / item->collation.collation->mbmaxlen; + client_field->length= max_char_len * thd_cs->mbmaxlen; } client_field->type= server_field.type; client_field->flags= server_field.flags; diff --git a/man/Makefile.am b/man/Makefile.am index 9702c4b2ace..5753259fd3d 100644 --- a/man/Makefile.am +++ b/man/Makefile.am @@ -18,7 +18,8 @@ ## Process this file with automake to create Makefile.in man1_MANS = @man1_files@ -EXTRA_DIST = $(man1_MANS) +man8_MANS = @man8_files@ +EXTRA_DIST = $(man1_MANS) $(man8_MANS) # Don't update the files from bitkeeper %::SCCS/s.% diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index f4e547dbc66..56f67cce4d6 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -39,10 +39,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL default '', `Min_value` varbinary(255) default NULL, `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', + `Min_length` int(11) NOT NULL default '0', + `Max_length` int(11) NOT NULL default '0', + `Empties_or_zeros` int(11) NOT NULL default '0', + `Nulls` int(11) NOT NULL default '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', `Std` varbinary(255) default NULL, `Optimal_fieldtype` varbinary(64) NOT NULL default '' @@ -58,10 +58,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL default '', `Min_value` varbinary(255) default NULL, `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', + `Min_length` int(11) NOT NULL default '0', + `Max_length` int(11) NOT NULL default '0', + `Empties_or_zeros` int(11) NOT NULL default '0', + `Nulls` int(11) NOT NULL default '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', `Std` varbinary(255) default NULL, `Optimal_fieldtype` varbinary(64) NOT NULL default '' @@ -81,10 +81,10 @@ t2 CREATE TABLE `t2` ( `Field_name` varbinary(255) NOT NULL default '', `Min_value` varbinary(255) default NULL, `Max_value` varbinary(255) default NULL, - `Min_length` bigint(11) NOT NULL default '0', - `Max_length` bigint(11) NOT NULL default '0', - `Empties_or_zeros` bigint(11) NOT NULL default '0', - `Nulls` bigint(11) NOT NULL default '0', + `Min_length` int(11) NOT NULL default '0', + `Max_length` int(11) NOT NULL default '0', + `Empties_or_zeros` int(11) NOT NULL default '0', + `Nulls` int(11) NOT NULL default '0', `Avg_value_or_avg_length` varbinary(255) NOT NULL default '', `Std` varbinary(255) default NULL, `Optimal_fieldtype` varbinary(64) NOT NULL default '' diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 3cdf4b17027..edc18319603 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -174,7 +174,7 @@ create table t1 select 1 as 'a'; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(1) NOT NULL default '0' + `a` int(1) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 select 9223372036854775809 as 'a'; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index c5b77ea4925..aa8c6d3d277 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -668,7 +668,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `b` int(11) NOT NULL, `a` varchar(12) character set utf8 collate utf8_bin NOT NULL, - `c` bigint(1) NOT NULL default '0', + `c` int(1) NOT NULL default '0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; @@ -681,7 +681,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `b` int(11) default NULL, `a` varchar(12) character set utf8 collate utf8_bin NOT NULL, - `c` bigint(1) NOT NULL default '0', + `c` int(1) NOT NULL default '0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 19325731d35..8c7e39e0e90 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -276,7 +276,7 @@ select * from t1; N M 3 0 delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N; -ERROR HY000: The target table p2 of the DELETE is not updatable +ERROR 42S02: Unknown table 'p2' in MULTI DELETE delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; ERROR 42S22: Unknown column 'aaa' in 'field list' drop table t1; diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index baf8641c9e9..454790f2df6 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1693,6 +1693,44 @@ id c1 c2 9 abc ppc drop table federated.t1, federated.t2; drop table federated.t1, federated.t2; +DROP TABLE IF EXISTS federated.test; +CREATE TABLE federated.test ( +`id` int(11) NOT NULL, +`val1` varchar(255) NOT NULL, +`val2` varchar(255) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +DROP TABLE IF EXISTS federated.test_local; +DROP TABLE IF EXISTS federated.test_remote; +CREATE TABLE federated.test_local ( +`id` int(11) NOT NULL, +`val1` varchar(255) NOT NULL, +`val2` varchar(255) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO federated.test_local VALUES (1, 'foo', 'bar'), +(2, 'bar', 'foo'); +CREATE TABLE federated.test_remote ( +`id` int(11) NOT NULL, +`val1` varchar(255) NOT NULL, +`val2` varchar(255) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=FEDERATED DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/test'; +insert into federated.test_remote select * from federated.test_local; +select * from federated.test_remote; +id val1 val2 +1 foo bar +2 bar foo +delete from federated.test_remote where id in (1,2); +insert into federated.test_remote select * from federated.test_local; +select * from federated.test_remote; +id val1 val2 +2 bar foo +1 foo bar +DROP TABLE federated.test_local; +DROP TABLE federated.test_remote; +DROP TABLE federated.test; drop table if exists federated.t1; create table federated.t1 (a int, b int, c int); drop table if exists federated.t1; @@ -1737,6 +1775,19 @@ id val 2 0 drop table t1; drop table t1; +create table t1 (a longblob not null); +create table t1 +(a longblob not null) engine=federated +connection='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +insert into t1 values (repeat('a',5000)); +select length(a) from t1; +length(a) +5000 +select length(a) from t1; +length(a) +5000 +drop table t1; +drop table t1; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 1042840fe04..aebf3596751 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1041,6 +1041,21 @@ select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test")); f1 f2 test a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 drop table t1; +CREATE TABLE t1 (a varchar(10)); +INSERT INTO t1 VALUES ('abc'), ('xyz'); +SELECT a, CONCAT(a,' ',a) AS c FROM t1 +HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a; +a c +abc abc abc +xyz xyz xyz +SELECT a, CONCAT(a,' ',a) AS c FROM t1 +HAVING LEFT(CONCAT(a,' ',a), +LENGTH(CONCAT(a,' ',a))- +INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a; +a c +abc abc abc +xyz xyz xyz +DROP TABLE t1; End of 4.1 tests create table t1 (d decimal default null); insert into t1 values (null); diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 699f0c9ec4b..c7db2fc6364 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -830,6 +830,13 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL +set time_zone='-6:00'; +create table t1(a timestamp); +insert into t1 values (19691231190001); +select * from t1; +a +1969-12-31 19:00:01 +drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 8dbfa906fe1..bf0c4ff1f42 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -54,6 +54,35 @@ c.c_id = 218 and expiredate is null; slai_id 12 drop table t1, t2; +CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; +CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), +UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; +INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; +INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t2 SELECT a + 1, b FROM t2; +DELETE FROM t2 WHERE a = 1 AND b < 2; +INSERT INTO t3 VALUES (1,1,1),(2,1,2); +INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; +INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE +t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) +ORDER BY t1.b LIMIT 2; +b a +1 1 +2 2 +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE +t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) +ORDER BY t1.b LIMIT 5; +b a +1 1 +2 2 +2 2 +3 3 +3 3 +DROP TABLE t1, t2, t3; create table t1m (a int) engine=myisam; create table t1i (a int) engine=innodb; create table t2m (a int) engine=myisam; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 49d9c3594db..3e2721fc09a 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -695,3 +695,103 @@ CREATE TABLE t2 (z int, y int); CREATE TABLE t3 (a int, b int); INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); DROP TABLE IF EXISTS t1,t2,t3; +CREATE DATABASE meow; +CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; +CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; +CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); +INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); +CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); +INSERT INTO table_countries VALUES ('YY','Entenhausen'); +CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); +CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); +INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); +GRANT SELECT ON table_source TO user20989@localhost; +GRANT SELECT ON table_countries TO user20989@localhost; +GRANT SELECT ON table_stations TO user20989@localhost; +GRANT SELECT ON view_stations TO user20989@localhost; +GRANT SELECT ON table_target TO user20989@localhost; +GRANT SELECT ON table_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost; +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); +ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target' +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); +ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2' +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); +ERROR HY000: View 'meow.view_target3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +GRANT INSERT,DELETE ON table_target TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost; +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); +REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); +ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2' +REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); +mexs_id messzeit +87654321 2006-07-12 07:50:00 +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); +SELECT * FROM table_target; +mexs_id messzeit +87654321 2006-07-12 07:50:00 +SELECT * FROM view_target2; +mexs_id messzeit +12X45Y78 2006-07-12 07:50:00 +87654321 2006-07-12 07:50:00 +SELECT * FROM view_target3; +mexs_id messzeit +87654321 2006-07-12 07:50:00 +DROP VIEW view_stations; +DROP TABLE table_source; +DROP TABLE table_countries; +DROP TABLE table_stations; +DROP TABLE table_target; +DROP TABLE table_target2; +DROP TABLE table_target3; +DROP VIEW view_target2; +DROP VIEW view_target3; +DROP USER user20989@localhost; +DROP DATABASE meow; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index b1abe16a091..b8fdd24be74 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -774,3 +774,15 @@ create table tm (b bit(1)) engine = merge union = (t1,t2); select * from tm; b drop table tm, t1, t2; +create table t1 (a int) insert_method = last engine = merge; +insert into t1 values (1); +ERROR HY000: Table 't1' is read only +create table t2 (a int) engine = myisam; +alter table t1 union (t2); +insert into t1 values (1); +alter table t1 insert_method = no; +insert into t1 values (1); +ERROR HY000: Table 't1' is read only +drop table t2; +drop table t1; +End of 5.0 tests diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index 55ed2a4868b..9e426b6df46 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -36,19 +36,19 @@ Tables_in_test t1 t2 t3 - +_ Test delimiter : from command line a 1 - +_ Test delimiter :; from command line a 1 - +_ Test 'go' command(vertical output) G *************************** 1. row *************************** a: 1 - +_ Test 'go' command g a 1 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 118079906bf..ff745021efb 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2470,6 +2470,8 @@ DROP TABLE IF EXISTS `v2`; `b` varchar(32), `c` varchar(32) ) */; + +USE `test`; /*!50001 DROP TABLE IF EXISTS `v0`*/; /*!50001 DROP VIEW IF EXISTS `v0`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ @@ -2789,6 +2791,8 @@ DROP TABLE IF EXISTS `v1`; /*!50001 CREATE TABLE `v1` ( `id` int(11) ) */; + +USE `mysqldump_test_db`; /*!50001 DROP TABLE IF EXISTS `v1`*/; /*!50001 DROP VIEW IF EXISTS `v1`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ @@ -2814,56 +2818,60 @@ create database mysqldump_views; use mysqldump_views; create view nasishnasifu as select mysqldump_tables.basetable.id from mysqldump_tables.basetable; -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; -/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; -/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; -/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; -/*!40103 SET TIME_ZONE='+00:00' */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; - CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqldump_tables` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysqldump_tables`; -DROP TABLE IF EXISTS `basetable`; CREATE TABLE `basetable` ( `id` bigint(20) unsigned NOT NULL auto_increment, `tag` varchar(64) default NULL, UNIQUE KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -LOCK TABLES `basetable` WRITE; -/*!40000 ALTER TABLE `basetable` DISABLE KEYS */; -/*!40000 ALTER TABLE `basetable` ENABLE KEYS */; -UNLOCK TABLES; - CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqldump_views` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `mysqldump_views`; -DROP TABLE IF EXISTS `nasishnasifu`; -/*!50001 DROP VIEW IF EXISTS `nasishnasifu`*/; /*!50001 CREATE TABLE `nasishnasifu` ( `id` bigint(20) unsigned ) */; -/*!50001 DROP TABLE IF EXISTS `nasishnasifu`*/; -/*!50001 DROP VIEW IF EXISTS `nasishnasifu`*/; + +USE `mysqldump_tables`; + +USE `mysqldump_views`; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `mysqldump_views`.`nasishnasifu` AS select `mysqldump_tables`.`basetable`.`id` AS `id` from `mysqldump_tables`.`basetable` */; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; -/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; -/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - drop view nasishnasifu; drop database mysqldump_views; drop table mysqldump_tables.basetable; drop database mysqldump_tables; +create database mysqldump_dba; +use mysqldump_dba; +create table t1 (f1 int, f2 int); +insert into t1 values (1,1); +create view v1 as select f1, f2 from t1; +create database mysqldump_dbb; +use mysqldump_dbb; +create table t1 (f1 int, f2 int); +insert into t1 values (2,2); +create view v1 as select f1, f2 from t1; +drop view v1; +drop table t1; +drop database mysqldump_dbb; +use mysqldump_dba; +drop view v1; +drop table t1; +drop database mysqldump_dba; +select * from mysqldump_dba.v1; +f1 f2 +1 1 +select * from mysqldump_dbb.v1; +f1 f2 +2 2 +use mysqldump_dba; +drop view v1; +drop table t1; +drop database mysqldump_dba; +use mysqldump_dbb; +drop view v1; +drop table t1; +drop database mysqldump_dbb; diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index 2d9d39393b1..5629d3dab33 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -14,3 +14,14 @@ explain select * from t1 where b is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE drop table t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (NULL); +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +a last_insert_id() +1 1 +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +a last_insert_id() +SELECT sql_no_cache a, last_insert_id() FROM t1; +a last_insert_id() +1 1 +DROP TABLE t1; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 225e306b3cf..28c1dc59540 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -620,8 +620,8 @@ CREATE VIEW v1 AS SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; DESC v1; Field Type Null Key Default Extra -a bigint(11) YES NULL -LENGTH(a) bigint(10) YES NULL +a int(11) YES 0 +LENGTH(a) int(10) YES NULL COUNT(*) bigint(21) NO 0 SELECT * FROM v1; a LENGTH(a) COUNT(*) diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 207d9ea7475..aa355067d1f 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -1775,7 +1775,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1805,7 +1805,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 13aa549949c..ea7ed9371e8 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -1758,7 +1758,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1788,7 +1788,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index a08dae945bd..fe4de89d6c7 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -1759,7 +1759,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1789,7 +1789,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 6682b085097..bee7b2400b8 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -1695,7 +1695,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1725,7 +1725,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 @@ -4709,7 +4709,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -4739,7 +4739,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result index dc3b984949d..d352ec9f9e2 100644 --- a/mysql-test/r/ps_6bdb.result +++ b/mysql-test/r/ps_6bdb.result @@ -1758,7 +1758,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1788,7 +1788,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result index 000a20da655..8ce4c624fbc 100644 --- a/mysql-test/r/ps_7ndb.result +++ b/mysql-test/r/ps_7ndb.result @@ -1758,7 +1758,7 @@ NULL as const12, @arg12 as param12, show create table t5 ; Table Create Table t5 CREATE TABLE `t5` ( - `const01` bigint(1) NOT NULL default '0', + `const01` int(1) NOT NULL default '0', `param01` bigint(20) default NULL, `const02` decimal(2,1) NOT NULL default '0.0', `param02` decimal(65,30) default NULL, @@ -1788,7 +1788,7 @@ t5 CREATE TABLE `t5` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t5 ; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def test t5 t5 const01 const01 8 1 1 N 32769 0 63 +def test t5 t5 const01 const01 3 1 1 N 32769 0 63 def test t5 t5 param01 param01 8 20 1 Y 32768 0 63 def test t5 t5 const02 const02 246 4 3 N 1 1 63 def test t5 t5 param02 param02 246 67 32 Y 0 30 63 diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index cd66e8727c1..3aa82bf1d63 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -74,6 +74,19 @@ SET FOREIGN_KEY_CHECKS=0; INSERT INTO t1 VALUES (1),(1); ERROR 23000: Duplicate entry '1' for key 1 drop table t1; +create table t1(a int auto_increment, key(a)); +create table t2(a int); +insert into t1 (a) values (null); +insert into t2 (a) select a from t1 where a is null; +insert into t2 (a) select a from t1 where a is null; +select * from t2; +a +1 +select * from t2; +a +1 +drop table t1; +drop table t2; drop function if exists bug15728; drop function if exists bug15728_insert; drop table if exists t1, t2; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index e6c590489a0..c2218585f7c 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3395,3 +3395,12 @@ a t1.b + 0 t1.c + 0 a t2.b + 0 c d 1 0 1 1 0 1 NULL 2 0 1 NULL NULL NULL NULL drop table t1,t2; +SELECT 0.9888889889 * 1.011111411911; +0.9888889889 * 1.011111411911 +0.9998769417899202067879 +prepare stmt from 'select 1 as " a "'; +Warnings: +Warning 1466 Leading spaces are removed from name ' a ' +execute stmt; +a +1 diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 96bf2f01f86..7807b7b52ce 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4921,7 +4921,7 @@ create table t3 as select * from v1| show create table t3| Table Create Table t3 CREATE TABLE `t3` ( - `j` bigint(11) default NULL + `j` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t3| j @@ -5057,4 +5057,19 @@ concat('data was: /', var1, '/') data was: /1/ drop table t3| drop procedure bug15217| +drop table if exists t3| +drop database if exists mysqltest1| +create table t3 (a int)| +insert into t3 (a) values (1), (2)| +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| +select database()| +database() +NULL +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +a +1 +use test| +drop table t3| drop table t1,t2; diff --git a/mysql-test/r/sp.result.orig b/mysql-test/r/sp.result.orig deleted file mode 100644 index 663204681f2..00000000000 --- a/mysql-test/r/sp.result.orig +++ /dev/null @@ -1,4853 +0,0 @@ -use test; -drop table if exists t1,t2,t3,t4; -create table t1 ( -id char(16) not null default '', -data int not null -); -create table t2 ( -s char(16), -i int, -d double -); -drop procedure if exists foo42; -create procedure foo42() -insert into test.t1 values ("foo", 42); -call foo42(); -select * from t1; -id data -foo 42 -delete from t1; -drop procedure foo42; -drop procedure if exists bar; -create procedure bar(x char(16), y int) -insert into test.t1 values (x, y); -call bar("bar", 666); -select * from t1; -id data -bar 666 -delete from t1; -drop procedure if exists empty| -create procedure empty() -begin -end| -call empty()| -drop procedure empty| -drop procedure if exists scope| -create procedure scope(a int, b float) -begin -declare b int; -declare c float; -begin -declare c int; -end; -end| -drop procedure scope| -drop procedure if exists two| -create procedure two(x1 char(16), x2 char(16), y int) -begin -insert into test.t1 values (x1, y); -insert into test.t1 values (x2, y); -end| -call two("one", "two", 3)| -select * from t1| -id data -one 3 -two 3 -delete from t1| -drop procedure two| -drop procedure if exists locset| -create procedure locset(x char(16), y int) -begin -declare z1, z2 int; -set z1 = y; -set z2 = z1+2; -insert into test.t1 values (x, z2); -end| -call locset("locset", 19)| -select * from t1| -id data -locset 21 -delete from t1| -drop procedure locset| -drop procedure if exists setcontext| -create procedure setcontext() -begin -declare data int default 2; -insert into t1 (id, data) values ("foo", 1); -replace t1 set data = data, id = "bar"; -update t1 set id = "kaka", data = 3 where t1.data = data; -end| -call setcontext()| -select * from t1| -id data -foo 1 -kaka 3 -delete from t1| -drop procedure setcontext| -create table t3 ( d date, i int, f double, s varchar(32) )| -drop procedure if exists nullset| -create procedure nullset() -begin -declare ld date; -declare li int; -declare lf double; -declare ls varchar(32); -set ld = null, li = null, lf = null, ls = null; -insert into t3 values (ld, li, lf, ls); -insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"), -((li is null), 1, "li is null"), -((li = 0), null, "li = 0"), -((lf is null), 1, "lf is null"), -((lf = 0), null, "lf = 0"), -((ls is null), 1, "ls is null"); -end| -call nullset()| -select * from t3| -d i f s -NULL NULL NULL NULL -NULL 1 1 ld is null -NULL 1 1 li is null -NULL NULL NULL li = 0 -NULL 1 1 lf is null -NULL NULL NULL lf = 0 -NULL 1 1 ls is null -drop table t3| -drop procedure nullset| -drop procedure if exists mixset| -create procedure mixset(x char(16), y int) -begin -declare z int; -set @z = y, z = 666, max_join_size = 100; -insert into test.t1 values (x, z); -end| -call mixset("mixset", 19)| -show variables like 'max_join_size'| -Variable_name Value -max_join_size 100 -select id,data,@z from t1| -id data @z -mixset 666 19 -delete from t1| -drop procedure mixset| -drop procedure if exists zip| -create procedure zip(x char(16), y int) -begin -declare z int; -call zap(y, z); -call bar(x, z); -end| -drop procedure if exists zap| -create procedure zap(x int, out y int) -begin -declare z int; -set z = x+1, y = z; -end| -call zip("zip", 99)| -select * from t1| -id data -zip 100 -delete from t1| -drop procedure zip| -drop procedure bar| -call zap(7, @zap)| -select @zap| -@zap -8 -drop procedure zap| -drop procedure if exists c1| -create procedure c1(x int) -call c2("c", x)| -drop procedure if exists c2| -create procedure c2(s char(16), x int) -call c3(x, s)| -drop procedure if exists c3| -create procedure c3(x int, s char(16)) -call c4("level", x, s)| -drop procedure if exists c4| -create procedure c4(l char(8), x int, s char(16)) -insert into t1 values (concat(l,s), x)| -call c1(42)| -select * from t1| -id data -levelc 42 -delete from t1| -drop procedure c1| -drop procedure c2| -drop procedure c3| -drop procedure c4| -drop procedure if exists iotest| -create procedure iotest(x1 char(16), x2 char(16), y int) -begin -call inc2(x2, y); -insert into test.t1 values (x1, y); -end| -drop procedure if exists inc2| -create procedure inc2(x char(16), y int) -begin -call inc(y); -insert into test.t1 values (x, y); -end| -drop procedure if exists inc| -create procedure inc(inout io int) -set io = io + 1| -call iotest("io1", "io2", 1)| -select * from t1| -id data -io2 2 -io1 1 -delete from t1| -drop procedure iotest| -drop procedure inc2| -drop procedure if exists incr| -create procedure incr(inout x int) -call inc(x)| -select @zap| -@zap -8 -call incr(@zap)| -select @zap| -@zap -9 -drop procedure inc| -drop procedure incr| -drop procedure if exists cbv1| -create procedure cbv1() -begin -declare y int default 3; -call cbv2(y+1, y); -insert into test.t1 values ("cbv1", y); -end| -drop procedure if exists cbv2| -create procedure cbv2(y1 int, inout y2 int) -begin -set y2 = 4711; -insert into test.t1 values ("cbv2", y1); -end| -call cbv1()| -select * from t1| -id data -cbv2 4 -cbv1 4711 -delete from t1| -drop procedure cbv1| -drop procedure cbv2| -insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| -drop procedure if exists sub1| -create procedure sub1(id char(16), x int) -insert into test.t1 values (id, x)| -drop procedure if exists sub2| -create procedure sub2(id char(16)) -begin -declare x int; -set x = (select sum(t.i) from test.t2 t); -insert into test.t1 values (id, x); -end| -drop procedure if exists sub3| -create function sub3(i int) returns int -return i+1| -call sub1("sub1a", (select 7))| -call sub1("sub1b", (select max(i) from t2))| -call sub1("sub1c", (select i,d from t2 limit 1))| -ERROR 21000: Operand should contain 1 column(s) -call sub1("sub1d", (select 1 from (select 1) a))| -call sub2("sub2")| -select * from t1| -id data -sub1a 7 -sub1b 3 -sub1d 1 -sub2 6 -select sub3((select max(i) from t2))| -sub3((select max(i) from t2)) -4 -drop procedure sub1| -drop procedure sub2| -drop function sub3| -delete from t1| -delete from t2| -drop procedure if exists a0| -create procedure a0(x int) -while x do -set x = x-1; -insert into test.t1 values ("a0", x); -end while| -call a0(3)| -select * from t1| -id data -a0 2 -a0 1 -a0 0 -delete from t1| -drop procedure a0| -drop procedure if exists a| -create procedure a(x int) -while x > 0 do -set x = x-1; -insert into test.t1 values ("a", x); -end while| -call a(3)| -select * from t1| -id data -a 2 -a 1 -a 0 -delete from t1| -drop procedure a| -drop procedure if exists b| -create procedure b(x int) -repeat -insert into test.t1 values (repeat("b",3), x); -set x = x-1; -until x = 0 end repeat| -call b(3)| -select * from t1| -id data -bbb 3 -bbb 2 -bbb 1 -delete from t1| -drop procedure b| -drop procedure if exists b2| -create procedure b2(x int) -repeat(select 1 into outfile 'b2'); -insert into test.t1 values (repeat("b2",3), x); -set x = x-1; -until x = 0 end repeat| -drop procedure b2| -drop procedure if exists c| -create procedure c(x int) -hmm: while x > 0 do -insert into test.t1 values ("c", x); -set x = x-1; -iterate hmm; -insert into test.t1 values ("x", x); -end while hmm| -call c(3)| -select * from t1| -id data -c 3 -c 2 -c 1 -delete from t1| -drop procedure c| -drop procedure if exists d| -create procedure d(x int) -hmm: while x > 0 do -insert into test.t1 values ("d", x); -set x = x-1; -leave hmm; -insert into test.t1 values ("x", x); -end while| -call d(3)| -select * from t1| -id data -d 3 -delete from t1| -drop procedure d| -drop procedure if exists e| -create procedure e(x int) -foo: loop -if x = 0 then -leave foo; -end if; -insert into test.t1 values ("e", x); -set x = x-1; -end loop foo| -call e(3)| -select * from t1| -id data -e 3 -e 2 -e 1 -delete from t1| -drop procedure e| -drop procedure if exists f| -create procedure f(x int) -if x < 0 then -insert into test.t1 values ("f", 0); -elseif x = 0 then -insert into test.t1 values ("f", 1); -else -insert into test.t1 values ("f", 2); -end if| -call f(-2)| -call f(0)| -call f(4)| -select * from t1| -id data -f 0 -f 1 -f 2 -delete from t1| -drop procedure f| -drop procedure if exists g| -create procedure g(x int) -case -when x < 0 then -insert into test.t1 values ("g", 0); -when x = 0 then -insert into test.t1 values ("g", 1); -else -insert into test.t1 values ("g", 2); -end case| -call g(-42)| -call g(0)| -call g(1)| -select * from t1| -id data -g 0 -g 1 -g 2 -delete from t1| -drop procedure g| -drop procedure if exists h| -create procedure h(x int) -case x -when 0 then -insert into test.t1 values ("h0", x); -when 1 then -insert into test.t1 values ("h1", x); -else -insert into test.t1 values ("h?", x); -end case| -call h(0)| -call h(1)| -call h(17)| -select * from t1| -id data -h0 0 -h1 1 -h? 17 -delete from t1| -drop procedure h| -drop procedure if exists i| -create procedure i(x int) -foo: -begin -if x = 0 then -leave foo; -end if; -insert into test.t1 values ("i", x); -end foo| -call i(0)| -call i(3)| -select * from t1| -id data -i 3 -delete from t1| -drop procedure i| -insert into t1 values ("foo", 3), ("bar", 19)| -insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| -drop procedure if exists sel1| -create procedure sel1() -begin -select * from t1; -end| -call sel1()| -id data -foo 3 -bar 19 -drop procedure sel1| -drop procedure if exists sel2| -create procedure sel2() -begin -select * from t1; -select * from t2; -end| -call sel2()| -id data -foo 3 -bar 19 -s i d -x 9 4.1 -y -1 19.2 -z 3 2.2 -drop procedure sel2| -delete from t1| -delete from t2| -drop procedure if exists into_test| -create procedure into_test(x char(16), y int) -begin -insert into test.t1 values (x, y); -select id,data into x,y from test.t1 limit 1; -insert into test.t1 values (concat(x, "2"), y+2); -end| -call into_test("into", 100)| -select * from t1| -id data -into 100 -into2 102 -delete from t1| -drop procedure into_test| -drop procedure if exists into_tes2| -create procedure into_test2(x char(16), y int) -begin -insert into test.t1 values (x, y); -select id,data into x,@z from test.t1 limit 1; -insert into test.t1 values (concat(x, "2"), y+2); -end| -call into_test2("into", 100)| -select id,data,@z from t1| -id data @z -into 100 100 -into2 102 100 -delete from t1| -drop procedure into_test2| -drop procedure if exists into_test3| -create procedure into_test3() -begin -declare x char(16); -declare y int; -select * into x,y from test.t1 limit 1; -insert into test.t2 values (x, y, 0.0); -end| -insert into t1 values ("into3", 19)| -call into_test3()| -call into_test3()| -select * from t2| -s i d -into3 19 0 -into3 19 0 -delete from t1| -delete from t2| -drop procedure into_test3| -drop procedure if exists into_test4| -create procedure into_test4() -begin -declare x int; -select data into x from test.t1 limit 1; -insert into test.t3 values ("into4", x); -end| -delete from t1| -create table t3 ( s char(16), d int)| -call into_test4()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from t3| -s d -into4 NULL -insert into t1 values ("i4", 77)| -call into_test4()| -select * from t3| -s d -into4 NULL -into4 77 -delete from t1| -drop table t3| -drop procedure into_test4| -drop procedure if exists into_outfile| -create procedure into_outfile(x char(16), y int) -begin -insert into test.t1 values (x, y); -select * into outfile "../tmp/spout" from test.t1; -insert into test.t1 values (concat(x, "2"), y+2); -end| -call into_outfile("ofile", 1)| -delete from t1| -drop procedure into_outfile| -drop procedure if exists into_dumpfile| -create procedure into_dumpfile(x char(16), y int) -begin -insert into test.t1 values (x, y); -select * into dumpfile "../tmp/spdump" from test.t1 limit 1; -insert into test.t1 values (concat(x, "2"), y+2); -end| -call into_dumpfile("dfile", 1)| -delete from t1| -drop procedure into_dumpfile| -drop procedure if exists create_select| -create procedure create_select(x char(16), y int) -begin -insert into test.t1 values (x, y); -create temporary table test.t3 select * from test.t1; -insert into test.t3 values (concat(x, "2"), y+2); -end| -call create_select("cs", 90)| -select * from t1, t3| -id data id data -cs 90 cs 90 -cs 90 cs2 92 -drop table t3| -delete from t1| -drop procedure create_select| -drop function if exists e| -create function e() returns double -return 2.7182818284590452354| -set @e = e()| -select e(), @e| -e() @e -2.718281828459 2.718281828459 -drop function if exists inc| -create function inc(i int) returns int -return i+1| -select inc(1), inc(99), inc(-71)| -inc(1) inc(99) inc(-71) -2 100 -70 -drop function if exists mul| -create function mul(x int, y int) returns int -return x*y| -select mul(1,1), mul(3,5), mul(4711, 666)| -mul(1,1) mul(3,5) mul(4711, 666) -1 15 3137526 -drop function if exists append| -create function append(s1 char(8), s2 char(8)) returns char(16) -return concat(s1, s2)| -select append("foo", "bar")| -append("foo", "bar") -foobar -drop function if exists fac| -create function fac(n int unsigned) returns bigint unsigned -begin -declare f bigint unsigned default 1; -while n > 1 do -set f = f * n; -set n = n - 1; -end while; -return f; -end| -select fac(1), fac(2), fac(5), fac(10)| -fac(1) fac(2) fac(5) fac(10) -1 2 120 3628800 -drop function if exists fun| -create function fun(d double, i int, u int unsigned) returns double -return mul(inc(i), fac(u)) / e()| -select fun(2.3, 3, 5)| -fun(2.3, 3, 5) -176.58213176229 -insert into t2 values (append("xxx", "yyy"), mul(4,3), e())| -insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| -select * from t2 where s = append("a", "b")| -s i d -ab 24 1324.36598821719 -select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| -s i d -xxxyyy 12 2.71828182845905 -ab 24 1324.36598821719 -select * from t2 where d = e()| -s i d -xxxyyy 12 2.71828182845905 -select * from t2| -s i d -xxxyyy 12 2.71828182845905 -ab 24 1324.36598821719 -delete from t2| -drop function e| -drop function inc| -drop function mul| -drop function append| -drop function fun| -drop procedure if exists hndlr1| -create procedure hndlr1(val int) -begin -declare x int default 0; -declare foo condition for 1136; -declare bar condition for sqlstate '42S98'; # Just for testing syntax -declare zip condition for sqlstate value '42S99'; # Just for testing syntax -declare continue handler for foo set x = 1; -insert into test.t1 values ("hndlr1", val, 2); # Too many values -if (x) then -insert into test.t1 values ("hndlr1", val); # This instead then -end if; -end| -call hndlr1(42)| -select * from t1| -id data -hndlr1 42 -delete from t1| -drop procedure hndlr1| -drop procedure if exists hndlr2| -create procedure hndlr2(val int) -begin -declare x int default 0; -begin -declare exit handler for sqlstate '21S01' set x = 1; -insert into test.t1 values ("hndlr2", val, 2); # Too many values -end; -insert into test.t1 values ("hndlr2", x); -end| -call hndlr2(42)| -select * from t1| -id data -hndlr2 1 -delete from t1| -drop procedure hndlr2| -drop procedure if exists hndlr3| -create procedure hndlr3(val int) -begin -declare x int default 0; -declare continue handler for sqlexception # Any error -begin -declare z int; -set z = 2 * val; -set x = 1; -end; -if val < 10 then -begin -declare y int; -set y = val + 10; -insert into test.t1 values ("hndlr3", y, 2); # Too many values -if x then -insert into test.t1 values ("hndlr3", y); -end if; -end; -end if; -end| -call hndlr3(3)| -select * from t1| -id data -hndlr3 13 -delete from t1| -drop procedure hndlr3| -create table t3 ( id char(16), data int )| -drop procedure if exists hndlr4| -create procedure hndlr4() -begin -declare x int default 0; -declare val int; # No default -declare continue handler for sqlstate '02000' set x=1; -select data into val from test.t3 where id='z' limit 1; # No hits -insert into test.t3 values ('z', val); -end| -call hndlr4()| -select * from t3| -id data -z NULL -drop table t3| -drop procedure hndlr4| -drop procedure if exists cur1| -create procedure cur1() -begin -declare a char(16); -declare b int; -declare c double; -declare done int default 0; -declare c cursor for select * from test.t2; -declare continue handler for sqlstate '02000' set done = 1; -open c; -repeat -fetch c into a, b, c; -if not done then -insert into test.t1 values (a, b+c); -end if; -until done end repeat; -close c; -end| -insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)| -call cur1()| -select * from t1| -id data -foo 40 -bar 15 -zap 663 -drop procedure cur1| -create table t3 ( s char(16), i int )| -drop procedure if exists cur2| -create procedure cur2() -begin -declare done int default 0; -declare c1 cursor for select id,data from test.t1; -declare c2 cursor for select i from test.t2; -declare continue handler for sqlstate '02000' set done = 1; -open c1; -open c2; -repeat -begin -declare a char(16); -declare b,c int; -fetch from c1 into a, b; -fetch next from c2 into c; -if not done then -if b < c then -insert into test.t3 values (a, b); -else -insert into test.t3 values (a, c); -end if; -end if; -end; -until done end repeat; -close c1; -close c2; -end| -call cur2()| -select * from t3| -s i -foo 40 -bar 3 -zap 663 -delete from t1| -delete from t2| -drop table t3| -drop procedure cur2| -drop procedure if exists chistics| -create procedure chistics() -language sql -modifies sql data -not deterministic -sql security definer -comment 'Characteristics procedure test' - insert into t1 values ("chistics", 1)| -show create procedure chistics| -Procedure sql_mode Create Procedure -chistics CREATE PROCEDURE `chistics`() - MODIFIES SQL DATA - COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) -call chistics()| -select * from t1| -id data -chistics 1 -delete from t1| -alter procedure chistics sql security invoker| -show create procedure chistics| -Procedure sql_mode Create Procedure -chistics CREATE PROCEDURE `chistics`() - MODIFIES SQL DATA - SQL SECURITY INVOKER - COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) -drop procedure chistics| -drop function if exists chistics| -create function chistics() returns int -language sql -deterministic -sql security invoker -comment 'Characteristics procedure test' - return 42| -show create function chistics| -Function sql_mode Create Function -chistics CREATE FUNCTION `chistics`() RETURNS int(11) - DETERMINISTIC - SQL SECURITY INVOKER - COMMENT 'Characteristics procedure test' -return 42 -select chistics()| -chistics() -42 -alter function chistics -no sql -comment 'Characteristics function test'| -show create function chistics| -Function sql_mode Create Function -chistics CREATE FUNCTION `chistics`() RETURNS int(11) - NO SQL - DETERMINISTIC - SQL SECURITY INVOKER - COMMENT 'Characteristics function test' -return 42 -drop function chistics| -insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| -set @@sql_mode = 'ANSI'| -drop procedure if exists modes$ -create procedure modes(out c1 int, out c2 int) -begin -declare done int default 0; -declare x int; -declare c cursor for select data from t1; -declare continue handler for sqlstate '02000' set done = 1; -select 1 || 2 into c1; -set c2 = 0; -open c; -repeat -fetch c into x; -if not done then -set c2 = c2 + 1; -end if; -until done end repeat; -close c; -end$ -set @@sql_mode = ''| -set sql_select_limit = 1| -call modes(@c1, @c2)| -set sql_select_limit = default| -select @c1, @c2| -@c1 @c2 -12 3 -delete from t1| -drop procedure modes| -create database sp_db1| -drop database sp_db1| -create database sp_db2| -use sp_db2| -create table t3 ( s char(4), t int )| -insert into t3 values ("abcd", 42), ("dcba", 666)| -use test| -drop database sp_db2| -create database sp_db3| -use sp_db3| -drop procedure if exists dummy| -create procedure dummy(out x int) -set x = 42| -use test| -drop database sp_db3| -select type,db,name from mysql.proc where db = 'sp_db3'| -type db name -drop procedure if exists rc| -create procedure rc() -begin -delete from t1; -insert into t1 values ("a", 1), ("b", 2), ("c", 3); -end| -call rc()| -select row_count()| -row_count() -3 -update t1 set data=42 where id = "b"; -select row_count()| -row_count() -1 -delete from t1| -select row_count()| -row_count() -3 -delete from t1| -select row_count()| -row_count() -0 -select * from t1| -id data -select row_count()| -row_count() --1 -drop procedure rc| -drop function if exists f0| -drop function if exists f1| -drop function if exists f2| -drop function if exists f3| -drop function if exists f4| -drop function if exists f5| -drop function if exists f6| -drop function if exists f7| -drop function if exists f8| -drop function if exists f9| -drop function if exists f10| -drop function if exists f11| -drop function if exists f12_1| -drop function if exists f12_2| -drop view if exists v0| -drop view if exists v1| -drop view if exists v2| -delete from t1| -delete from t2| -insert into t1 values ("a", 1), ("b", 2) | -insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | -create function f1() returns int -return (select sum(data) from t1)| -select f1()| -f1() -3 -select id, f1() from t1| -id f1() -a 3 -b 3 -create function f2() returns int -return (select data from t1 where data <= (select sum(data) from t1) limit 1)| -select f2()| -f2() -1 -select id, f2() from t1| -id f2() -a 1 -b 1 -create function f3() returns int -begin -declare n int; -declare m int; -set n:= (select min(data) from t1); -set m:= (select max(data) from t1); -return n < m; -end| -select f3()| -f3() -1 -select id, f3() from t1| -id f3() -a 1 -b 1 -select f1(), f3()| -f1() f3() -3 1 -select id, f1(), f3() from t1| -id f1() f3() -a 3 1 -b 3 1 -create function f4() returns double -return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| -select f4()| -f4() -2 -select s, f4() from t2| -s f4() -a 2 -b 2 -c 2 -create function f5(i int) returns int -begin -if i <= 0 then -return 0; -elseif i = 1 then -return (select count(*) from t1 where data = i); -else -return (select count(*) + f5( i - 1) from t1 where data = i); -end if; -end| -select f5(1)| -f5(1) -1 -select f5(2)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select f5(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -create function f6() returns int -begin -declare n int; -set n:= f1(); -return (select count(*) from t1 where data <= f7() and data <= n); -end| -create function f7() returns int -return (select sum(data) from t1 where data <= f1())| -select f6()| -f6() -2 -select id, f6() from t1| -id f6() -a 2 -b 2 -create view v1 (a) as select f1()| -select * from v1| -a -3 -select id, a from t1, v1| -id a -a 3 -b 3 -select * from v1, v1 as v| -a a -3 3 -create view v2 (a) as select a*10 from v1| -select * from v2| -a -30 -select id, a from t1, v2| -id a -a 30 -b 30 -select * from v1, v2| -a a -3 30 -create function f8 () returns int -return (select count(*) from v2)| -select *, f8() from v1| -a f8() -3 1 -drop function f1| -select * from v1| -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -create function f1() returns int -return (select sum(data) from t1) + (select sum(data) from v1)| -select f1()| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select * from v1| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select * from v2| -ERROR HY000: Recursive stored functions and triggers are not allowed. -drop function f1| -create function f1() returns int -return (select sum(data) from t1)| -create function f0() returns int -return (select * from (select 100) as r)| -select f0()| -f0() -100 -select *, f0() from (select 1) as t| -1 f0() -1 100 -create view v0 as select f0()| -select * from v0| -f0() -100 -select *, f0() from v0| -f0() f0() -100 100 -lock tables t1 read, t1 as t11 read| -select f3()| -f3() -1 -select id, f3() from t1 as t11| -id f3() -a 1 -b 1 -select f0()| -f0() -100 -select * from v0| -f0() -100 -select *, f0() from v0, (select 123) as d1| -f0() 123 f0() -100 123 100 -select id, f3() from t1| -ERROR HY000: Table 't1' was not locked with LOCK TABLES -select f4()| -ERROR HY000: Table 't2' was not locked with LOCK TABLES -unlock tables| -lock tables v2 read, mysql.proc read| -select * from v2| -a -30 -select * from v1| -a -3 -select * from v1, t1| -ERROR HY000: Table 't1' was not locked with LOCK TABLES -select f4()| -ERROR HY000: Table 't2' was not locked with LOCK TABLES -unlock tables| -create function f9() returns int -begin -declare a, b int; -drop temporary table if exists t3; -create temporary table t3 (id int); -insert into t3 values (1), (2), (3); -set a:= (select count(*) from t3); -set b:= (select count(*) from t3 t3_alias); -return a + b; -end| -select f9()| -f9() -6 -Warnings: -Note 1051 Unknown table 't3' -select f9() from t1 limit 1| -f9() -6 -create function f10() returns int -begin -drop temporary table if exists t3; -create temporary table t3 (id int); -insert into t3 select id from t4; -return (select count(*) from t3); -end| -select f10()| -ERROR 42S02: Table 'test.t4' doesn't exist -create table t4 as select 1 as id| -select f10()| -f10() -1 -create function f11() returns int -begin -drop temporary table if exists t3; -create temporary table t3 (id int); -insert into t3 values (1), (2), (3); -return (select count(*) from t3 as a, t3 as b); -end| -select f11()| -ERROR HY000: Can't reopen table: 'a' -select f11() from t1| -ERROR HY000: Can't reopen table: 'a' -create function f12_1() returns int -begin -drop temporary table if exists t3; -create temporary table t3 (id int); -insert into t3 values (1), (2), (3); -return f12_2(); -end| -create function f12_2() returns int -return (select count(*) from t3)| -drop temporary table t3| -select f12_1()| -ERROR 42S02: Table 'test.t3' doesn't exist -select f12_1() from t1 limit 1| -ERROR 42S02: Table 'test.t3' doesn't exist -drop function f0| -drop function f1| -drop function f2| -drop function f3| -drop function f4| -drop function f5| -drop function f6| -drop function f7| -drop function f8| -drop function f9| -drop function f10| -drop function f11| -drop function f12_1| -drop function f12_2| -drop view v0| -drop view v1| -drop view v2| -delete from t1 | -delete from t2 | -drop table t4| -drop table if exists t3| -create table t3 (n int unsigned not null primary key, f bigint unsigned)| -drop procedure if exists ifac| -create procedure ifac(n int unsigned) -begin -declare i int unsigned default 1; -if n > 20 then -set n = 20; # bigint overflow otherwise -end if; -while i <= n do -begin -insert into test.t3 values (i, fac(i)); -set i = i + 1; -end; -end while; -end| -call ifac(20)| -select * from t3| -n f -1 1 -2 2 -3 6 -4 24 -5 120 -6 720 -7 5040 -8 40320 -9 362880 -10 3628800 -11 39916800 -12 479001600 -13 6227020800 -14 87178291200 -15 1307674368000 -16 20922789888000 -17 355687428096000 -18 6402373705728000 -19 121645100408832000 -20 2432902008176640000 -drop table t3| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -drop procedure ifac| -drop function fac| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists t3| -create table t3 ( -i int unsigned not null primary key, -p bigint unsigned not null -)| -insert into t3 values -( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), -( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), -(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), -(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), -(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), -(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), -(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), -(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), -(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| -drop procedure if exists opp| -create procedure opp(n bigint unsigned, out pp bool) -begin -declare r double; -declare b, s bigint unsigned default 0; -set r = sqrt(n); -again: -loop -if s = 45 then -set b = b+200, s = 0; -else -begin -declare p bigint unsigned; -select t.p into p from test.t3 t where t.i = s; -if b+p > r then -set pp = 1; -leave again; -end if; -if mod(n, b+p) = 0 then -set pp = 0; -leave again; -end if; -set s = s+1; -end; -end if; -end loop; -end| -drop procedure if exists ip| -create procedure ip(m int unsigned) -begin -declare p bigint unsigned; -declare i int unsigned; -set i=45, p=201; -while i < m do -begin -declare pp bool default 0; -call opp(p, pp); -if pp then -insert into test.t3 values (i, p); -set i = i+1; -end if; -set p = p+2; -end; -end while; -end| -show create procedure opp| -Procedure sql_mode Create Procedure -opp CREATE PROCEDURE `opp`(n bigint unsigned, out pp bool) -begin -declare r double; -declare b, s bigint unsigned default 0; -set r = sqrt(n); -again: -loop -if s = 45 then -set b = b+200, s = 0; -else -begin -declare p bigint unsigned; -select t.p into p from test.t3 t where t.i = s; -if b+p > r then -set pp = 1; -leave again; -end if; -if mod(n, b+p) = 0 then -set pp = 0; -leave again; -end if; -set s = s+1; -end; -end if; -end loop; -end -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call ip(200)| -select * from t3 where i=45 or i=100 or i=199| -i p -45 211 -100 557 -199 1229 -drop table t3| -drop procedure opp| -drop procedure ip| -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists t3| -create table t3 ( f bigint unsigned not null )| -drop procedure if exists fib| -create procedure fib(n int unsigned) -begin -if n > 1 then -begin -declare x, y bigint unsigned; -declare c cursor for select f from t3 order by f desc limit 2; -open c; -fetch c into y; -fetch c into x; -close c; -insert into t3 values (x+y); -call fib(n-1); -end; -end if; -end| -set @@max_sp_recursion_depth= 20| -insert into t3 values (0), (1)| -call fib(3)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -delete from t3| -insert into t3 values (0), (1)| -call fib(10)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -3 -5 -8 -13 -21 -34 -55 -drop table t3| -drop procedure fib| -set @@max_sp_recursion_depth= 0| -drop procedure if exists bar| -create procedure bar(x char(16), y int) -comment "111111111111" sql security invoker -insert into test.t1 values (x, y)| -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 -alter procedure bar comment "2222222222" sql security definer| -alter procedure bar comment "3333333333"| -alter procedure bar| -show create procedure bar| -Procedure sql_mode Create Procedure -bar CREATE PROCEDURE `bar`(x char(16), y int) - COMMENT '3333333333' -insert into test.t1 values (x, y) -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 -drop procedure bar| -drop procedure if exists p1| -create procedure p1 () -select (select s1 from t3) from t3| -create table t3 (s1 int)| -call p1()| -(select s1 from t3) -insert into t3 values (1)| -call p1()| -(select s1 from t3) -1 -drop procedure p1| -drop table t3| -drop function if exists foo| -create function `foo` () returns int -return 5| -select `foo` ()| -`foo` () -5 -drop function `foo`| -drop function if exists t1max| -create function t1max() returns int -begin -declare x int; -select max(data) into x from t1; -return x; -end| -insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| -select t1max()| -t1max() -5 -drop function t1max| -create table t3 ( -v char(16) not null primary key, -c int unsigned not null -)| -create function getcount(s char(16)) returns int -begin -declare x int; -select count(*) into x from t3 where v = s; -if x = 0 then -insert into t3 values (s, 1); -else -update t3 set c = c+1 where v = s; -end if; -return x; -end| -select * from t1 where data = getcount("bar")| -id data -zap 1 -select * from t3| -v c -bar 4 -select getcount("zip")| -getcount("zip") -0 -select getcount("zip")| -getcount("zip") -1 -select * from t3| -v c -bar 4 -zip 2 -select getcount(id) from t1 where data = 3| -getcount(id) -0 -select getcount(id) from t1 where data = 5| -getcount(id) -1 -select * from t3| -v c -bar 4 -zip 3 -foo 1 -drop table t3| -drop function getcount| -drop table if exists t3| -drop procedure if exists h_ee| -drop procedure if exists h_es| -drop procedure if exists h_en| -drop procedure if exists h_ew| -drop procedure if exists h_ex| -drop procedure if exists h_se| -drop procedure if exists h_ss| -drop procedure if exists h_sn| -drop procedure if exists h_sw| -drop procedure if exists h_sx| -drop procedure if exists h_ne| -drop procedure if exists h_ns| -drop procedure if exists h_nn| -drop procedure if exists h_we| -drop procedure if exists h_ws| -drop procedure if exists h_ww| -drop procedure if exists h_xe| -drop procedure if exists h_xs| -drop procedure if exists h_xx| -create table t3 (a smallint primary key)| -insert into t3 (a) values (1)| -create procedure h_ee() -deterministic -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Outer (bad)' as 'h_ee'; -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Inner (good)' as 'h_ee'; -insert into t3 values (1); -end; -end| -create procedure h_es() -deterministic -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Outer (good)' as 'h_es'; -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' - select 'Inner (bad)' as 'h_es'; -insert into t3 values (1); -end; -end| -create procedure h_en() -deterministic -begin -declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA -select 'Outer (good)' as 'h_en'; -begin -declare x int; -declare continue handler for sqlstate '02000' -- no data -select 'Inner (bad)' as 'h_en'; -select a into x from t3 where a = 42; -end; -end| -create procedure h_ew() -deterministic -begin -declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE -select 'Outer (good)' as 'h_ew'; -begin -declare continue handler for sqlwarning -select 'Inner (bad)' as 'h_ew'; -insert into t3 values (123456789012); -end; -delete from t3; -insert into t3 values (1); -end| -create procedure h_ex() -deterministic -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Outer (good)' as 'h_ex'; -begin -declare continue handler for sqlexception -select 'Inner (bad)' as 'h_ex'; -insert into t3 values (1); -end; -end| -create procedure h_se() -deterministic -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' -select 'Outer (bad)' as 'h_se'; -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Inner (good)' as 'h_se'; -insert into t3 values (1); -end; -end| -create procedure h_ss() -deterministic -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' -select 'Outer (bad)' as 'h_ss'; -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' -select 'Inner (good)' as 'h_ss'; -insert into t3 values (1); -end; -end| -create procedure h_sn() -deterministic -begin --- Note: '02000' is more specific than NOT FOUND ; --- there might be other not found states -declare continue handler for sqlstate '02000' -- no data -select 'Outer (good)' as 'h_sn'; -begin -declare x int; -declare continue handler for not found -select 'Inner (bad)' as 'h_sn'; -select a into x from t3 where a = 42; -end; -end| -create procedure h_sw() -deterministic -begin --- data exception - numeric value out of range -declare continue handler for sqlstate '22003' - select 'Outer (good)' as 'h_sw'; -begin -declare continue handler for sqlwarning -select 'Inner (bad)' as 'h_sw'; -insert into t3 values (123456789012); -end; -delete from t3; -insert into t3 values (1); -end| -create procedure h_sx() -deterministic -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' -select 'Outer (good)' as 'h_sx'; -begin -declare continue handler for sqlexception -select 'Inner (bad)' as 'h_sx'; -insert into t3 values (1); -end; -end| -create procedure h_ne() -deterministic -begin -declare continue handler for not found -select 'Outer (bad)' as 'h_ne'; -begin -declare x int; -declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA -select 'Inner (good)' as 'h_ne'; -select a into x from t3 where a = 42; -end; -end| -create procedure h_ns() -deterministic -begin -declare continue handler for not found -select 'Outer (bad)' as 'h_ns'; -begin -declare x int; -declare continue handler for sqlstate '02000' -- no data -select 'Inner (good)' as 'h_ns'; -select a into x from t3 where a = 42; -end; -end| -create procedure h_nn() -deterministic -begin -declare continue handler for not found -select 'Outer (bad)' as 'h_nn'; -begin -declare x int; -declare continue handler for not found -select 'Inner (good)' as 'h_nn'; -select a into x from t3 where a = 42; -end; -end| -create procedure h_we() -deterministic -begin -declare continue handler for sqlwarning -select 'Outer (bad)' as 'h_we'; -begin -declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE -select 'Inner (good)' as 'h_we'; -insert into t3 values (123456789012); -end; -delete from t3; -insert into t3 values (1); -end| -create procedure h_ws() -deterministic -begin -declare continue handler for sqlwarning -select 'Outer (bad)' as 'h_ws'; -begin --- data exception - numeric value out of range -declare continue handler for sqlstate '22003' - select 'Inner (good)' as 'h_ws'; -insert into t3 values (123456789012); -end; -delete from t3; -insert into t3 values (1); -end| -create procedure h_ww() -deterministic -begin -declare continue handler for sqlwarning -select 'Outer (bad)' as 'h_ww'; -begin -declare continue handler for sqlwarning -select 'Inner (good)' as 'h_ww'; -insert into t3 values (123456789012); -end; -delete from t3; -insert into t3 values (1); -end| -create procedure h_xe() -deterministic -begin -declare continue handler for sqlexception -select 'Outer (bad)' as 'h_xe'; -begin -declare continue handler for 1062 -- ER_DUP_ENTRY -select 'Inner (good)' as 'h_xe'; -insert into t3 values (1); -end; -end| -create procedure h_xs() -deterministic -begin -declare continue handler for sqlexception -select 'Outer (bad)' as 'h_xs'; -begin --- integrity constraint violation -declare continue handler for sqlstate '23000' - select 'Inner (good)' as 'h_xs'; -insert into t3 values (1); -end; -end| -create procedure h_xx() -deterministic -begin -declare continue handler for sqlexception -select 'Outer (bad)' as 'h_xx'; -begin -declare continue handler for sqlexception -select 'Inner (good)' as 'h_xx'; -insert into t3 values (1); -end; -end| -call h_ee()| -h_ee -Inner (good) -call h_es()| -h_es -Outer (good) -call h_en()| -h_en -Outer (good) -call h_ew()| -h_ew -Outer (good) -call h_ex()| -h_ex -Outer (good) -call h_se()| -h_se -Inner (good) -call h_ss()| -h_ss -Inner (good) -call h_sn()| -h_sn -Outer (good) -call h_sw()| -h_sw -Outer (good) -call h_sx()| -h_sx -Outer (good) -call h_ne()| -h_ne -Inner (good) -call h_ns()| -h_ns -Inner (good) -call h_nn()| -h_nn -Inner (good) -call h_we()| -h_we -Inner (good) -call h_ws()| -h_ws -Inner (good) -call h_ww()| -h_ww -Inner (good) -call h_xe()| -h_xe -Inner (good) -call h_xs()| -h_xs -Inner (good) -call h_xx()| -h_xx -Inner (good) -drop table t3| -drop procedure h_ee| -drop procedure h_es| -drop procedure h_en| -drop procedure h_ew| -drop procedure h_ex| -drop procedure h_se| -drop procedure h_ss| -drop procedure h_sn| -drop procedure h_sw| -drop procedure h_sx| -drop procedure h_ne| -drop procedure h_ns| -drop procedure h_nn| -drop procedure h_we| -drop procedure h_ws| -drop procedure h_ww| -drop procedure h_xe| -drop procedure h_xs| -drop procedure h_xx| -drop procedure if exists bug822| -create procedure bug822(a_id char(16), a_data int) -begin -declare n int; -select count(*) into n from t1 where id = a_id and data = a_data; -if n = 0 then -insert into t1 (id, data) values (a_id, a_data); -end if; -end| -delete from t1| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| -select * from t1| -id data -foo 42 -bar 666 -delete from t1| -drop procedure bug822| -drop procedure if exists bug1495| -create procedure bug1495() -begin -declare x int; -select data into x from t1 order by id limit 1; -if x > 10 then -insert into t1 values ("less", x-10); -else -insert into t1 values ("more", x+10); -end if; -end| -insert into t1 values ('foo', 12)| -call bug1495()| -delete from t1 where id='foo'| -insert into t1 values ('bar', 7)| -call bug1495()| -delete from t1 where id='bar'| -select * from t1| -id data -less 2 -more 17 -delete from t1| -drop procedure bug1495| -drop procedure if exists bug1547| -create procedure bug1547(s char(16)) -begin -declare x int; -select data into x from t1 where s = id limit 1; -if x > 10 then -insert into t1 values ("less", x-10); -else -insert into t1 values ("more", x+10); -end if; -end| -insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| -select * from t1| -id data -foo 12 -bar 7 -less 2 -more 17 -delete from t1| -drop procedure bug1547| -drop table if exists t70| -create table t70 (s1 int,s2 int)| -insert into t70 values (1,2)| -drop procedure if exists bug1656| -create procedure bug1656(out p1 int, out p2 int) -select * into p1, p1 from t70| -call bug1656(@1, @2)| -select @1, @2| -@1 @2 -2 NULL -drop table t70| -drop procedure bug1656| -create table t3(a int)| -drop procedure if exists bug1862| -create procedure bug1862() -begin -insert into t3 values(2); -flush tables; -end| -call bug1862()| -call bug1862()| -select * from t3| -a -2 -2 -drop table t3| -drop procedure bug1862| -drop procedure if exists bug1874| -create procedure bug1874() -begin -declare x int; -declare y double; -select max(data) into x from t1; -insert into t2 values ("max", x, 0); -select min(data) into x from t1; -insert into t2 values ("min", x, 0); -select sum(data) into x from t1; -insert into t2 values ("sum", x, 0); -select avg(data) into y from t1; -insert into t2 values ("avg", 0, y); -end| -insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| -select * from t2| -s i d -max 9 0 -min 1 0 -sum 22 0 -avg 0 4.4 -delete from t1| -delete from t2| -drop procedure bug1874| -drop procedure if exists bug2260| -create procedure bug2260() -begin -declare v1 int; -declare c1 cursor for select data from t1; -declare continue handler for not found set @x2 = 1; -open c1; -fetch c1 into v1; -set @x2 = 2; -close c1; -end| -call bug2260()| -select @x2| -@x2 -2 -drop procedure bug2260| -drop procedure if exists bug2267_1| -create procedure bug2267_1() -begin -show procedure status; -end| -drop procedure if exists bug2267_2| -create procedure bug2267_2() -begin -show function status; -end| -drop procedure if exists bug2267_3| -create procedure bug2267_3() -begin -show create procedure bug2267_1; -end| -drop procedure if exists bug2267_4| -drop function if exists bug2267_4| -create procedure bug2267_4() -begin -show create function bug2267_4; -end| -create function bug2267_4() returns int return 100| -call bug2267_1()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_2()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_3()| -Procedure sql_mode Create Procedure -bug2267_1 CREATE PROCEDURE `bug2267_1`() -begin -show procedure status; -end -call bug2267_4()| -Function sql_mode Create Function -bug2267_4 CREATE FUNCTION `bug2267_4`() RETURNS int(11) -return 100 -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| -drop function bug2267_4| -drop procedure if exists bug2227| -create procedure bug2227(x int) -begin -declare y float default 2.6; -declare z char(16) default "zzz"; -select 1.3, x, y, 42, z; -end| -call bug2227(9)| -1.3 x y 42 z -1.3 9 2.6 42 zzz -drop procedure bug2227| -drop procedure if exists bug2614| -create procedure bug2614() -begin -drop table if exists t3; -create table t3 (id int default '0' not null); -insert into t3 select 12; -insert into t3 select * from t3; -end| -call bug2614()| -call bug2614()| -drop table t3| -drop procedure bug2614| -drop function if exists bug2674| -create function bug2674() returns int -return @@sort_buffer_size| -set @osbs = @@sort_buffer_size| -set @@sort_buffer_size = 262000| -select bug2674()| -bug2674() -262000 -drop function bug2674| -set @@sort_buffer_size = @osbs| -drop procedure if exists bug3259_1 | -create procedure bug3259_1 () begin end| -drop procedure if exists BUG3259_2 | -create procedure BUG3259_2 () begin end| -drop procedure if exists Bug3259_3 | -create procedure Bug3259_3 () begin end| -call BUG3259_1()| -call BUG3259_1()| -call bug3259_2()| -call Bug3259_2()| -call bug3259_3()| -call bUG3259_3()| -drop procedure bUg3259_1| -drop procedure BuG3259_2| -drop procedure BUG3259_3| -drop function if exists bug2772| -create function bug2772() returns char(10) character set latin2 -return 'a'| -select bug2772()| -bug2772() -a -drop function bug2772| -drop procedure if exists bug2776_1| -create procedure bug2776_1(out x int) -begin -declare v int; -set v = default; -set x = v; -end| -drop procedure if exists bug2776_2| -create procedure bug2776_2(out x int) -begin -declare v int default 42; -set v = default; -set x = v; -end| -set @x = 1| -call bug2776_1(@x)| -select @x| -@x -NULL -call bug2776_2(@x)| -select @x| -@x -42 -drop procedure bug2776_1| -drop procedure bug2776_2| -create table t3 (s1 smallint)| -insert into t3 values (123456789012)| -Warnings: -Warning 1264 Out of range value adjusted for column 's1' at row 1 -drop procedure if exists bug2780| -create procedure bug2780() -begin -declare exit handler for sqlwarning set @x = 1; -set @x = 0; -insert into t3 values (123456789012); -insert into t3 values (0); -end| -call bug2780()| -select @x| -@x -1 -select * from t3| -s1 -32767 -32767 -drop procedure bug2780| -drop table t3| -create table t3 (content varchar(10) )| -insert into t3 values ("test1")| -insert into t3 values ("test2")| -create table t4 (f1 int, rc int, t3 int)| -drop procedure if exists bug1863| -create procedure bug1863(in1 int) -begin -declare ind int default 0; -declare t1 int; -declare t2 int; -declare t3 int; -declare rc int default 0; -declare continue handler for 1065 set rc = 1; -drop temporary table if exists temp_t1; -create temporary table temp_t1 ( -f1 int auto_increment, f2 varchar(20), primary key (f1) -); -insert into temp_t1 (f2) select content from t3; -select f2 into t3 from temp_t1 where f1 = 10; -if (rc) then -insert into t4 values (1, rc, t3); -end if; -insert into t4 values (2, rc, t3); -end| -call bug1863(10)| -Warnings: -Note 1051 Unknown table 'temp_t1' -Warning 1329 No data - zero rows fetched, selected, or processed -call bug1863(10)| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from t4| -f1 rc t3 -2 0 NULL -2 0 NULL -drop procedure bug1863| -drop temporary table temp_t1; -drop table t3, t4| -create table t3 ( -OrderID int not null, -MarketID int, -primary key (OrderID) -)| -create table t4 ( -MarketID int not null, -Market varchar(60), -Status char(1), -primary key (MarketID) -)| -insert t3 (OrderID,MarketID) values (1,1)| -insert t3 (OrderID,MarketID) values (2,2)| -insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")| -insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")| -drop procedure if exists bug2656_1| -create procedure bug2656_1() -begin -select -m.Market -from t4 m JOIN t3 o -ON o.MarketID != 1 and o.MarketID = m.MarketID; -end | -drop procedure if exists bug2656_2| -create procedure bug2656_2() -begin -select -m.Market -from -t4 m, t3 o -where -m.MarketID != 1 and m.MarketID = o.MarketID; -end | -call bug2656_1()| -Market -MarketID Two -call bug2656_1()| -Market -MarketID Two -call bug2656_2()| -Market -MarketID Two -call bug2656_2()| -Market -MarketID Two -drop procedure bug2656_1| -drop procedure bug2656_2| -drop table t3, t4| -drop procedure if exists bug3426| -create procedure bug3426(in_time int unsigned, out x int) -begin -if in_time is null then -set @stamped_time=10; -set x=1; -else -set @stamped_time=in_time; -set x=2; -end if; -end| -call bug3426(1000, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -2 01-01-1970 03:16:40 -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -1 01-01-1970 03:00:10 -alter procedure bug3426 sql security invoker| -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -1 01-01-1970 03:00:10 -call bug3426(1000, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -2 01-01-1970 03:16:40 -drop procedure bug3426| -create table t3 ( -a int primary key, -ach char(1) -) engine = innodb| -create table t4 ( -b int primary key , -bch char(1) -) engine = innodb| -insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')| -Warnings: -Warning 1265 Data truncated for column 'ach' at row 1 -Warning 1265 Data truncated for column 'ach' at row 2 -insert into t4 values (1 , 'bCh1' )| -Warnings: -Warning 1265 Data truncated for column 'bch' at row 1 -drop procedure if exists bug3448| -create procedure bug3448() -select * from t3 inner join t4 on t3.a = t4.b| -select * from t3 inner join t4 on t3.a = t4.b| -a ach b bch -1 a 1 b -call bug3448()| -a ach b bch -1 a 1 b -call bug3448()| -a ach b bch -1 a 1 b -drop procedure bug3448| -drop table t3, t4| -create table t3 ( -id int unsigned auto_increment not null primary key, -title VARCHAR(200), -body text, -fulltext (title,body) -)| -insert into t3 (title,body) values -('MySQL Tutorial','DBMS stands for DataBase ...'), -('How To Use MySQL Well','After you went through a ...'), -('Optimizing MySQL','In this tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...')| -drop procedure if exists bug3734 | -create procedure bug3734 (param1 varchar(100)) -select * from t3 where match (title,body) against (param1)| -call bug3734('database')| -id title body -5 MySQL vs. YourSQL In the following database comparison ... -1 MySQL Tutorial DBMS stands for DataBase ... -call bug3734('Security')| -id title body -6 MySQL Security When configured properly, MySQL ... -drop procedure bug3734| -drop table t3| -drop procedure if exists bug3863| -create procedure bug3863() -begin -set @a = 0; -while @a < 5 do -set @a = @a + 1; -end while; -end| -call bug3863()| -select @a| -@a -5 -call bug3863()| -select @a| -@a -5 -drop procedure bug3863| -create table t3 ( -id int(10) unsigned not null default 0, -rid int(10) unsigned not null default 0, -msg text not null, -primary key (id), -unique key rid (rid, id) -)| -drop procedure if exists bug2460_1| -create procedure bug2460_1(in v int) -begin -( select n0.id from t3 as n0 where n0.id = v ) -union -( select n0.id from t3 as n0, t3 as n1 -where n0.id = n1.rid and n1.id = v ) -union -( select n0.id from t3 as n0, t3 as n1, t3 as n2 -where n0.id = n1.rid and n1.id = n2.rid and n2.id = v ); -end| -call bug2460_1(2)| -id -call bug2460_1(2)| -id -insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| -call bug2460_1(2)| -id -2 -1 -call bug2460_1(2)| -id -2 -1 -drop procedure if exists bug2460_2| -create procedure bug2460_2() -begin -drop table if exists t3; -create temporary table t3 (s1 int); -insert into t3 select 1 union select 1; -end| -call bug2460_2()| -call bug2460_2()| -select * from t3| -s1 -1 -drop procedure bug2460_1| -drop procedure bug2460_2| -drop table t3| -set @@sql_mode = ''| -drop procedure if exists bug2564_1| -create procedure bug2564_1() -comment 'Joe''s procedure' - insert into `t1` values ("foo", 1)| -set @@sql_mode = 'ANSI_QUOTES'| -drop procedure if exists bug2564_2| -create procedure bug2564_2() -insert into "t1" values ('foo', 1)| -set @@sql_mode = ''$ -drop function if exists bug2564_3$ -create function bug2564_3(x int, y int) returns int -return x || y$ -set @@sql_mode = 'ANSI'$ -drop function if exists bug2564_4$ -create function bug2564_4(x int, y int) returns int -return x || y$ -set @@sql_mode = ''| -show create procedure bug2564_1| -Procedure sql_mode Create Procedure -bug2564_1 CREATE PROCEDURE `bug2564_1`() - COMMENT 'Joe''s procedure' -insert into `t1` values ("foo", 1) -show create procedure bug2564_2| -Procedure sql_mode Create Procedure -bug2564_2 ANSI_QUOTES CREATE PROCEDURE "bug2564_2"() -insert into "t1" values ('foo', 1) -show create function bug2564_3| -Function sql_mode Create Function -bug2564_3 CREATE FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) -return x || y -show create function bug2564_4| -Function sql_mode Create Function -bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) -return x || y -drop procedure bug2564_1| -drop procedure bug2564_2| -drop function bug2564_3| -drop function bug2564_4| -drop function if exists bug3132| -create function bug3132(s char(20)) returns char(50) -return concat('Hello, ', s, '!')| -select bug3132('Bob') union all select bug3132('Judy')| -bug3132('Bob') -Hello, Bob! -Hello, Judy! -drop function bug3132| -drop procedure if exists bug3843| -create procedure bug3843() -analyze table t1| -call bug3843()| -Table Op Msg_type Msg_text -test.t1 analyze status OK -call bug3843()| -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -select 1+2| -1+2 -3 -drop procedure bug3843| -create table t3 ( s1 char(10) )| -insert into t3 values ('a'), ('b')| -drop procedure if exists bug3368| -create procedure bug3368(v char(10)) -begin -select group_concat(v) from t3; -end| -call bug3368('x')| -group_concat(v) -x,x -call bug3368('yz')| -group_concat(v) -yz,yz -drop procedure bug3368| -drop table t3| -create table t3 (f1 int, f2 int)| -insert into t3 values (1,1)| -drop procedure if exists bug4579_1| -create procedure bug4579_1 () -begin -declare sf1 int; -select f1 into sf1 from t3 where f1=1 and f2=1; -update t3 set f2 = f2 + 1 where f1=1 and f2=1; -call bug4579_2(); -end| -drop procedure if exists bug4579_2| -create procedure bug4579_2 () -begin -end| -call bug4579_1()| -call bug4579_1()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug4579_1()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -drop procedure bug4579_1| -drop procedure bug4579_2| -drop table t3| -drop procedure if exists bug2773| -create function bug2773() returns int return null| -create table t3 as select bug2773()| -show create table t3| -Table Create Table -t3 CREATE TABLE `t3` ( - `bug2773()` int(11) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t3| -drop function bug2773| -drop procedure if exists bug3788| -create function bug3788() returns date return cast("2005-03-04" as date)| -select bug3788()| -bug3788() -2005-03-04 -drop function bug3788| -create function bug3788() returns binary(1) return 5| -select bug3788()| -bug3788() -5 -drop function bug3788| -create table t3 (f1 int, f2 int, f3 int)| -insert into t3 values (1,1,1)| -drop procedure if exists bug4726| -create procedure bug4726() -begin -declare tmp_o_id INT; -declare tmp_d_id INT default 1; -while tmp_d_id <= 2 do -begin -select f1 into tmp_o_id from t3 where f2=1 and f3=1; -set tmp_d_id = tmp_d_id + 1; -end; -end while; -end| -call bug4726()| -call bug4726()| -call bug4726()| -drop procedure bug4726| -drop table t3| -drop procedure if exists bug4902| -create procedure bug4902() -begin -show charset like 'foo'; -show collation like 'foo'; -show column types; -show create table t1; -show create database test; -show databases like 'foo'; -show errors; -show columns from t1; -show grants for 'root'@'localhost'; -show keys from t1; -show open tables like 'foo'; -show privileges; -show status like 'foo'; -show tables like 'foo'; -show variables like 'foo'; -show warnings; -end| -call bug4902()| -Charset Description Default collation Maxlen -Collation Charset Id Default Compiled Sortlen -Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment -tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer -tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer -Table Create Table -t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Database Create Database -test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -Database (foo) -Level Code Message -Field Type Null Key Default Extra -id char(16) NO -data int(11) NO -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -Database Table In_use Name_locked -Privilege Context Comment -Alter Tables To alter the table -Alter routine Functions,Procedures To alter or drop stored functions/procedures -Create Databases,Tables,Indexes To create new databases and tables -Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE -Create temporary tables Databases To use CREATE TEMPORARY TABLE -Create view Tables To create new views -Create user Server Admin To create new users -Delete Tables To delete existing rows -Drop Databases,Tables To drop databases, tables, and views -Execute Functions,Procedures To execute stored routines -File File access on server To read and write files on the server -Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess -Index Tables To create or drop indexes -Insert Tables To insert data into tables -Lock tables Databases To use LOCK TABLES (together with SELECT privilege) -Process Server Admin To view the plain text of currently executing queries -References Databases,Tables To have references on tables -Reload Server Admin To reload or refresh tables, logs and privileges -Replication client Server Admin To ask where the slave or master servers are -Replication slave Server Admin To read binary log events from the master -Select Tables To retrieve rows from table -Show databases Server Admin To see all databases with SHOW DATABASES -Show view Tables To see views with SHOW CREATE VIEW -Shutdown Server Admin To shut down the server -Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. -Update Tables To update existing rows -Usage Server Admin No privileges - allow connect only -Variable_name Value -Tables_in_test (foo) -Variable_name Value -Level Code Message -call bug4902()| -Charset Description Default collation Maxlen -Collation Charset Id Default Compiled Sortlen -Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment -tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer -tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer -Table Create Table -t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Database Create Database -test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -Database (foo) -Level Code Message -Field Type Null Key Default Extra -id char(16) NO -data int(11) NO -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -Database Table In_use Name_locked -Privilege Context Comment -Alter Tables To alter the table -Alter routine Functions,Procedures To alter or drop stored functions/procedures -Create Databases,Tables,Indexes To create new databases and tables -Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE -Create temporary tables Databases To use CREATE TEMPORARY TABLE -Create view Tables To create new views -Create user Server Admin To create new users -Delete Tables To delete existing rows -Drop Databases,Tables To drop databases, tables, and views -Execute Functions,Procedures To execute stored routines -File File access on server To read and write files on the server -Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess -Index Tables To create or drop indexes -Insert Tables To insert data into tables -Lock tables Databases To use LOCK TABLES (together with SELECT privilege) -Process Server Admin To view the plain text of currently executing queries -References Databases,Tables To have references on tables -Reload Server Admin To reload or refresh tables, logs and privileges -Replication client Server Admin To ask where the slave or master servers are -Replication slave Server Admin To read binary log events from the master -Select Tables To retrieve rows from table -Show databases Server Admin To see all databases with SHOW DATABASES -Show view Tables To see views with SHOW CREATE VIEW -Shutdown Server Admin To shut down the server -Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. -Update Tables To update existing rows -Usage Server Admin No privileges - allow connect only -Variable_name Value -Tables_in_test (foo) -Variable_name Value -Level Code Message -drop procedure bug4902| -drop procedure if exists bug4902_2| -create procedure bug4902_2() -begin -show processlist; -end| -call bug4902_2()| -Id User Host db Command Time State Info -# root localhost test Query # NULL show processlist -call bug4902_2()| -Id User Host db Command Time State Info -# root localhost test Query # NULL show processlist -drop procedure bug4902_2| -drop procedure if exists bug4904| -create procedure bug4904() -begin -declare continue handler for sqlstate 'HY000' begin end; -create table t2 as select * from t3; -end| -call bug4904()| -ERROR 42S02: Table 'test.t3' doesn't exist -drop procedure bug4904| -create table t3 (s1 char character set latin1, s2 char character set latin2)| -drop procedure if exists bug4904| -create procedure bug4904 () -begin -declare continue handler for sqlstate 'HY000' begin end; -select s1 from t3 union select s2 from t3; -end| -call bug4904()| -drop procedure bug4904| -drop table t3| -drop procedure if exists bug336| -create procedure bug336(out y int) -begin -declare x int; -set x = (select sum(t.data) from test.t1 t); -set y = x; -end| -insert into t1 values ("a", 2), ("b", 3)| -call bug336(@y)| -select @y| -@y -5 -delete from t1| -drop procedure bug336| -drop procedure if exists bug3157| -create procedure bug3157() -begin -if exists(select * from t1) then -set @n= @n + 1; -end if; -if (select count(*) from t1) then -set @n= @n + 1; -end if; -end| -set @n = 0| -insert into t1 values ("a", 1)| -call bug3157()| -select @n| -@n -2 -delete from t1| -drop procedure bug3157| -drop procedure if exists bug5251| -create procedure bug5251() -begin -end| -select created into @c1 from mysql.proc -where db='test' and name='bug5251'| -alter procedure bug5251 comment 'foobar'| -select count(*) from mysql.proc -where db='test' and name='bug5251' and created = @c1| -count(*) -1 -drop procedure bug5251| -drop procedure if exists bug5251| -create procedure bug5251() -checksum table t1| -call bug5251()| -Table Checksum -test.t1 0 -call bug5251()| -Table Checksum -test.t1 0 -drop procedure bug5251| -drop procedure if exists bug5287| -create procedure bug5287(param1 int) -label1: -begin -declare c cursor for select 5; -loop -if param1 >= 0 then -leave label1; -end if; -end loop; -end| -call bug5287(1)| -drop procedure bug5287| -drop procedure if exists bug5307| -create procedure bug5307() -begin -end; set @x = 3| -call bug5307()| -select @x| -@x -3 -drop procedure bug5307| -drop procedure if exists bug5258| -create procedure bug5258() -begin -end| -drop procedure if exists bug5258_aux| -create procedure bug5258_aux() -begin -declare c, m char(19); -select created,modified into c,m from mysql.proc where name = 'bug5258'; -if c = m then -select 'Ok'; -else -select c, m; -end if; -end| -call bug5258_aux()| -Ok -Ok -drop procedure bug5258| -drop procedure bug5258_aux| -drop function if exists bug4487| -create function bug4487() returns char -begin -declare v char; -return v; -end| -select bug4487()| -bug4487() -NULL -drop function bug4487| -drop procedure if exists bug4941| -drop procedure if exists bug4941| -create procedure bug4941(out x int) -begin -declare c cursor for select i from t2 limit 1; -open c; -fetch c into x; -close c; -end| -insert into t2 values (null, null, null)| -set @x = 42| -call bug4941(@x)| -select @x| -@x -NULL -delete from t1| -drop procedure bug4941| -drop procedure if exists bug3583| -drop procedure if exists bug3583| -create procedure bug3583() -begin -declare c int; -select * from t1; -select count(*) into c from t1; -select c; -end| -insert into t1 values ("x", 3), ("y", 5)| -set @x = @@query_cache_size| -set global query_cache_size = 10*1024*1024| -flush status| -flush query cache| -show status like 'Qcache_hits'| -Variable_name Value -Qcache_hits 0 -call bug3583()| -id data -x 3 -y 5 -c -2 -show status like 'Qcache_hits'| -Variable_name Value -Qcache_hits 0 -call bug3583()| -id data -x 3 -y 5 -c -2 -call bug3583()| -id data -x 3 -y 5 -c -2 -show status like 'Qcache_hits'| -Variable_name Value -Qcache_hits 2 -set global query_cache_size = @x| -flush status| -flush query cache| -delete from t1| -drop procedure bug3583| -drop procedure if exists bug4905| -create table t3 (s1 int,primary key (s1))| -drop procedure if exists bug4905| -create procedure bug4905() -begin -declare v int; -declare continue handler for sqlstate '23000' set v = 5; -insert into t3 values (1); -end| -call bug4905()| -select row_count()| -row_count() -1 -call bug4905()| -select row_count()| -row_count() -0 -call bug4905()| -select row_count()| -row_count() -0 -select * from t3| -s1 -1 -drop procedure bug4905| -drop table t3| -drop procedure if exists bug6029| -drop procedure if exists bug6029| -create procedure bug6029() -begin -declare exit handler for 1136 select '1136'; -declare exit handler for sqlstate '23000' select 'sqlstate 23000'; -declare continue handler for sqlexception select 'sqlexception'; -insert into t3 values (1); -insert into t3 values (1,2); -end| -create table t3 (s1 int, primary key (s1))| -insert into t3 values (1)| -call bug6029()| -sqlstate 23000 -sqlstate 23000 -delete from t3| -call bug6029()| -1136 -1136 -drop procedure bug6029| -drop table t3| -drop procedure if exists bug8540| -create procedure bug8540() -begin -declare x int default 1; -select x as y, x+0 as z; -end| -call bug8540()| -y z -1 1 -drop procedure bug8540| -create table t3 (s1 int)| -drop procedure if exists bug6642| -create procedure bug6642() -select abs(count(s1)) from t3| -call bug6642()| -abs(count(s1)) -0 -call bug6642()| -abs(count(s1)) -0 -drop procedure bug6642| -insert into t3 values (0),(1)| -drop procedure if exists bug7013| -create procedure bug7013() -select s1,count(s1) from t3 group by s1 with rollup| -call bug7013()| -s1 count(s1) -0 1 -1 1 -NULL 2 -call bug7013()| -s1 count(s1) -0 1 -1 1 -NULL 2 -drop procedure bug7013| -drop table if exists t4| -create table t4 ( -a mediumint(8) unsigned not null auto_increment, -b smallint(5) unsigned not null, -c char(32) not null, -primary key (a) -) engine=myisam default charset=latin1| -insert into t4 values (1, 2, 'oneword')| -insert into t4 values (2, 2, 'anotherword')| -drop procedure if exists bug7743| -create procedure bug7743 ( searchstring char(28) ) -begin -declare var mediumint(8) unsigned; -select a into var from t4 where b = 2 and c = binary searchstring limit 1; -select var; -end| -call bug7743("oneword")| -var -1 -call bug7743("OneWord")| -var -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug7743("anotherword")| -var -2 -call bug7743("AnotherWord")| -var -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -drop procedure bug7743| -drop table t4| -delete from t3| -insert into t3 values(1)| -drop procedure if exists bug7992_1| -Warnings: -Note 1305 PROCEDURE bug7992_1 does not exist -drop procedure if exists bug7992_2| -Warnings: -Note 1305 PROCEDURE bug7992_2 does not exist -create procedure bug7992_1() -begin -declare i int; -select max(s1)+1 into i from t3; -end| -create procedure bug7992_2() -insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| -call bug7992_1()| -call bug7992_1()| -call bug7992_2()| -call bug7992_2()| -drop procedure bug7992_1| -drop procedure bug7992_2| -drop table t3| -create table t3 ( userid bigint(20) not null default 0 )| -drop procedure if exists bug8116| -create procedure bug8116(in _userid int) -select * from t3 where userid = _userid| -call bug8116(42)| -userid -call bug8116(42)| -userid -drop procedure bug8116| -drop table t3| -drop procedure if exists bug6857| -create procedure bug6857(counter int) -begin -declare t0, t1 int; -declare plus bool default 0; -set t0 = current_time(); -while counter > 0 do -set counter = counter - 1; -end while; -set t1 = current_time(); -if t1 > t0 then -set plus = 1; -end if; -select plus; -end| -drop procedure bug6857| -drop procedure if exists bug8757| -create procedure bug8757() -begin -declare x int; -declare c1 cursor for select data from t1 limit 1; -begin -declare y int; -declare c2 cursor for select i from t2 limit 1; -open c2; -fetch c2 into y; -close c2; -select 2,y; -end; -open c1; -fetch c1 into x; -close c1; -select 1,x; -end| -delete from t1| -delete from t2| -insert into t1 values ("x", 1)| -insert into t2 values ("y", 2, 0.0)| -call bug8757()| -2 y -2 2 -1 x -1 1 -delete from t1| -delete from t2| -drop procedure bug8757| -drop procedure if exists bug8762| -drop procedure if exists bug8762; create procedure bug8762() begin end| -drop procedure if exists bug8762; create procedure bug8762() begin end| -drop procedure bug8762| -drop function if exists bug5240| -create function bug5240 () returns int -begin -declare x int; -declare c cursor for select data from t1 limit 1; -open c; -fetch c into x; -close c; -return x; -end| -delete from t1| -insert into t1 values ("answer", 42)| -select id, bug5240() from t1| -id bug5240() -answer 42 -drop function bug5240| -drop function if exists bug5278| -create function bug5278 () returns char -begin -SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); -return 'okay'; -end| -select bug5278()| -ERROR 42000: Can't find any matching row in the user table -select bug5278()| -ERROR 42000: Can't find any matching row in the user table -drop function bug5278| -drop procedure if exists p1| -create table t3(id int)| -insert into t3 values(1)| -create procedure bug7992() -begin -declare i int; -select max(id)+1 into i from t3; -end| -call bug7992()| -call bug7992()| -drop procedure bug7992| -drop table t3| -create table t3 ( -lpitnumber int(11) default null, -lrecordtype int(11) default null -)| -create table t4 ( -lbsiid int(11) not null default '0', -ltradingmodeid int(11) not null default '0', -ltradingareaid int(11) not null default '0', -csellingprice decimal(19,4) default null, -primary key (lbsiid,ltradingmodeid,ltradingareaid) -)| -create table t5 ( -lbsiid int(11) not null default '0', -ltradingareaid int(11) not null default '0', -primary key (lbsiid,ltradingareaid) -)| -drop procedure if exists bug8849| -create procedure bug8849() -begin -insert into t5 -( -t5.lbsiid, -t5.ltradingareaid -) -select distinct t3.lpitnumber, t4.ltradingareaid -from -t4 join t3 on -t3.lpitnumber = t4.lbsiid -and t3.lrecordtype = 1 -left join t4 as price01 on -price01.lbsiid = t4.lbsiid and -price01.ltradingmodeid = 1 and -t4.ltradingareaid = price01.ltradingareaid; -end| -call bug8849()| -call bug8849()| -call bug8849()| -drop procedure bug8849| -drop tables t3,t4,t5| -drop procedure if exists bug8937| -create procedure bug8937() -begin -declare s,x,y,z int; -declare a float; -select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; -select s,x,y,z; -select avg(data) into a from t1; -select a; -end| -delete from t1| -insert into t1 (data) values (1), (2), (3), (4), (6)| -call bug8937()| -s x y z -16 3 1 6 -a -3.2 -drop procedure bug8937| -delete from t1| -drop procedure if exists bug6900| -drop procedure if exists bug9074| -drop procedure if exists bug6900_9074| -create table t3 (w char unique, x char)| -insert into t3 values ('a', 'b')| -create procedure bug6900() -begin -declare exit handler for sqlexception select '1'; -begin -declare exit handler for sqlexception select '2'; -insert into t3 values ('x', 'y', 'z'); -end; -end| -create procedure bug9074() -begin -declare x1, x2, x3, x4, x5, x6 int default 0; -begin -declare continue handler for sqlstate '23000' set x5 = 1; -insert into t3 values ('a', 'b'); -set x6 = 1; -end; -begin1_label: -begin -declare continue handler for sqlstate '23000' set x1 = 1; -insert into t3 values ('a', 'b'); -set x2 = 1; -begin2_label: -begin -declare exit handler for sqlstate '23000' set x3 = 1; -set x4= 1; -insert into t3 values ('a','b'); -set x4= 0; -end begin2_label; -end begin1_label; -select x1, x2, x3, x4, x5, x6; -end| -create procedure bug6900_9074(z int) -begin -declare exit handler for sqlstate '23000' select '23000'; -begin -declare exit handler for sqlexception select 'sqlexception'; -if z = 1 then -insert into t3 values ('a', 'b'); -else -insert into t3 values ('x', 'y', 'z'); -end if; -end; -end| -call bug6900()| -2 -2 -call bug9074()| -x1 x2 x3 x4 x5 x6 -1 1 1 1 1 1 -call bug6900_9074(0)| -sqlexception -sqlexception -call bug6900_9074(1)| -23000 -23000 -drop procedure bug6900| -drop procedure bug9074| -drop procedure bug6900_9074| -drop table t3| -drop procedure if exists avg| -create procedure avg () -begin -end| -call avg ()| -drop procedure avg| -drop procedure if exists bug6129| -set @old_mode= @@sql_mode; -set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; -create procedure bug6129() -select @@sql_mode| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode= "NO_ZERO_IN_DATE"| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode=@old_mode; -drop procedure bug6129| -drop procedure if exists bug9856| -create procedure bug9856() -begin -declare v int; -declare c cursor for select data from t1; -declare exit handler for sqlexception, not found select '16'; -open c; -fetch c into v; -select v; -end| -delete from t1| -call bug9856()| -16 -16 -call bug9856()| -16 -16 -drop procedure bug9856| -drop procedure if exists bug9674_1| -drop procedure if exists bug9674_2| -create procedure bug9674_1(out arg int) -begin -declare temp_in1 int default 0; -declare temp_fl1 int default 0; -set temp_in1 = 100; -set temp_fl1 = temp_in1/10; -set arg = temp_fl1; -end| -create procedure bug9674_2() -begin -declare v int default 100; -select v/10; -end| -call bug9674_1(@sptmp)| -call bug9674_1(@sptmp)| -select @sptmp| -@sptmp -10 -call bug9674_2()| -v/10 -10.0000 -call bug9674_2()| -v/10 -10.0000 -drop procedure bug9674_1| -drop procedure bug9674_2| -drop procedure if exists bug9598_1| -drop procedure if exists bug9598_2| -create procedure bug9598_1(in var_1 char(16), -out var_2 integer, out var_3 integer) -begin -set var_2 = 50; -set var_3 = 60; -end| -create procedure bug9598_2(in v1 char(16), -in v2 integer, -in v3 integer, -in v4 integer, -in v5 integer) -begin -select v1,v2,v3,v4,v5; -call bug9598_1(v1,@tmp1,@tmp2); -select v1,v2,v3,v4,v5; -end| -call bug9598_2('Test',2,3,4,5)| -v1 v2 v3 v4 v5 -Test 2 3 4 5 -v1 v2 v3 v4 v5 -Test 2 3 4 5 -select @tmp1, @tmp2| -@tmp1 @tmp2 -50 60 -drop procedure bug9598_1| -drop procedure bug9598_2| -drop procedure if exists bug9902| -create function bug9902() returns int(11) -begin -set @x = @x + 1; -return @x; -end| -set @qcs1 = @@query_cache_size| -set global query_cache_size = 100000| -set @x = 1| -insert into t1 values ("qc", 42)| -select bug9902() from t1| -bug9902() -2 -select bug9902() from t1| -bug9902() -3 -select @x| -@x -3 -set global query_cache_size = @qcs1| -delete from t1| -drop function bug9902| -drop function if exists bug9102| -create function bug9102() returns blob return 'a'| -select bug9102()| -bug9102() -a -drop function bug9102| -drop function if exists bug7648| -create function bug7648() returns bit(8) return 'a'| -select bug7648()| -bug7648() -a -drop function bug7648| -drop function if exists bug9775| -create function bug9775(v1 char(1)) returns enum('a','b') return v1| -select bug9775('a'),bug9775('b'),bug9775('c')| -bug9775('a') bug9775('b') bug9775('c') -a b -Warnings: -Warning 1265 Data truncated for column 'bug9775('c')' at row 1 -drop function bug9775| -create function bug9775(v1 int) returns enum('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3)| -bug9775(1) bug9775(2) bug9775(3) -a b -Warnings: -Warning 1265 Data truncated for column 'bug9775(3)' at row 1 -drop function bug9775| -create function bug9775(v1 char(1)) returns set('a','b') return v1| -select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')| -bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') -a b a -Warnings: -Warning 1265 Data truncated for column 'v1' at row 1 -Warning 1265 Data truncated for column 'bug9775('c')' at row 1 -drop function bug9775| -create function bug9775(v1 int) returns set('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| -bug9775(1) bug9775(2) bug9775(3) bug9775(4) -a b a,b -Warnings: -Warning 1265 Data truncated for column 'bug9775(4)' at row 1 -drop function bug9775| -drop function if exists bug8861| -create function bug8861(v1 int) returns year return v1| -select bug8861(05)| -bug8861(05) -2005 -set @x = bug8861(05)| -select @x| -@x -2005 -drop function bug8861| -drop procedure if exists bug9004_1| -drop procedure if exists bug9004_2| -create procedure bug9004_1(x char(16)) -begin -insert into t1 values (x, 42); -insert into t1 values (x, 17); -end| -create procedure bug9004_2(x char(16)) -call bug9004_1(x)| -call bug9004_1('12345678901234567')| -Warnings: -Warning 1265 Data truncated for column 'x' at row 1 -call bug9004_2('12345678901234567890')| -Warnings: -Warning 1265 Data truncated for column 'x' at row 1 -delete from t1| -drop procedure bug9004_1| -drop procedure bug9004_2| -drop procedure if exists bug7293| -insert into t1 values ('secret', 0)| -create procedure bug7293(p1 varchar(100)) -begin -if exists (select id from t1 where soundex(p1)=soundex(id)) then -select 'yes'; -end if; -end;| -call bug7293('secret')| -yes -yes -call bug7293 ('secrete')| -yes -yes -drop procedure bug7293| -delete from t1| -drop procedure if exists bug9841| -drop view if exists v1| -create view v1 as select * from t1, t2 where id = s| -create procedure bug9841 () -update v1 set data = 10| -call bug9841()| -drop view v1| -drop procedure bug9841| -drop procedure if exists bug5963| -create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;| -create table t3 (s1 int)| -insert into t3 values (5)| -call bug5963_1()| -v -5 -call bug5963_1()| -v -5 -drop procedure bug5963_1| -drop table t3| -create procedure bug5963_2 (cfk_value int) -begin -if cfk_value in (select cpk from t3) then -set @x = 5; -end if; -end; -| -create table t3 (cpk int)| -insert into t3 values (1)| -call bug5963_2(1)| -call bug5963_2(1)| -drop procedure bug5963_2| -drop table t3| -drop function if exists bug9559| -create function bug9559() -returns int -begin -set @y = -6/2; -return @y; -end| -select bug9559()| -bug9559() --3 -drop function bug9559| -drop procedure if exists bug10961| -create procedure bug10961() -begin -declare v char; -declare x int; -declare c cursor for select * from dual; -declare continue handler for sqlexception select x; -set x = 1; -open c; -set x = 2; -fetch c into v; -set x = 3; -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| -Warnings: -Note 1051 Unknown table 'test.tv' -DROP TABLE IF EXISTS tt1,tt2,tt3| -Warnings: -Note 1051 Unknown table 'tt1' -Note 1051 Unknown table 'tt2' -Note 1051 Unknown table 'tt3' -CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))| -CREATE TABLE tt2 (a2 int, data2 varchar(10))| -CREATE TABLE tt3 (a3 int, data3 varchar(10))| -INSERT INTO tt1 VALUES (1, 1, 4, 'xx')| -INSERT INTO tt2 VALUES (1, 'a')| -INSERT INTO tt2 VALUES (2, 'b')| -INSERT INTO tt2 VALUES (3, 'c')| -INSERT INTO tt3 VALUES (4, 'd')| -INSERT INTO tt3 VALUES (5, 'e')| -INSERT INTO tt3 VALUES (6, 'f')| -CREATE VIEW tv AS -SELECT tt1.*, tt2.data2, tt3.data3 -FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2 -LEFT JOIN tt3 ON tt1.a3 = tt3.a3 -ORDER BY tt1.a1, tt2.a2, tt3.a3| -CREATE PROCEDURE bug6866 (_a1 int) -BEGIN -SELECT * FROM tv WHERE a1 = _a1; -END| -CALL bug6866(1)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -CALL bug6866(1)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -CALL bug6866(1)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -DROP PROCEDURE bug6866; -DROP VIEW tv| -DROP TABLE tt1, tt2, tt3| -DROP PROCEDURE IF EXISTS bug10136| -create table t3 ( name char(5) not null primary key, val float not null)| -insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)| -create procedure bug10136() -begin -declare done int default 3; -repeat -select * from t3; -set done = done - 1; -until done <= 0 end repeat; -end| -call bug10136()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -call bug10136()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -call bug10136()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -drop procedure bug10136| -drop table t3| -drop procedure if exists bug11529| -create procedure bug11529() -begin -declare c cursor for select id, data from t1 where data in (10,13); -open c; -begin -declare vid char(16); -declare vdata int; -declare exit handler for not found begin end; -while true do -fetch c into vid, vdata; -end while; -end; -close c; -end| -insert into t1 values -('Name1', 10), -('Name2', 11), -('Name3', 12), -('Name4', 13), -('Name5', 14)| -call bug11529()| -call bug11529()| -delete from t1| -drop procedure bug11529| -drop procedure if exists bug6063| -drop procedure if exists bug7088_1| -drop procedure if exists bug7088_2| -drop procedure if exists bug9565_sub| -drop procedure if exists bug9565| -create procedure bug9565_sub() -begin -select * from t1; -end| -create procedure bug9565() -begin -insert into t1 values ("one", 1); -call bug9565_sub(); -end| -call bug9565()| -id data -one 1 -delete from t1| -drop procedure bug9565_sub| -drop procedure bug9565| -drop procedure if exists bug9538| -create procedure bug9538() -set @@sort_buffer_size = 1000000| -set @x = @@sort_buffer_size| -set @@sort_buffer_size = 2000000| -select @@sort_buffer_size| -@@sort_buffer_size -2000000 -call bug9538()| -select @@sort_buffer_size| -@@sort_buffer_size -1000000 -set @@sort_buffer_size = @x| -drop procedure bug9538| -drop procedure if exists bug8692| -create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))| -insert into t3 values ('', '', '', '', '', '', NULL)| -Warnings: -Warning 1265 Data truncated for column 'c3' at row 1 -create procedure bug8692() -begin -declare v1 VARCHAR(10); -declare v2 VARCHAR(10); -declare v3 VARCHAR(10); -declare v4 VARCHAR(10); -declare v5 VARCHAR(10); -declare v6 VARCHAR(10); -declare v7 VARCHAR(10); -declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3; -open c8692; -fetch c8692 into v1,v2,v3,v4,v5,v6,v7; -select v1, v2, v3, v4, v5, v6, v7; -end| -call bug8692()| -v1 v2 v3 v4 v5 v6 v7 - NULL -drop procedure bug8692| -drop table t3| -drop function if exists bug10055| -create function bug10055(v char(255)) returns char(255) return lower(v)| -select t.column_name, bug10055(t.column_name) -from information_schema.columns as t -where t.table_schema = 'test' and t.table_name = 't1'| -column_name bug10055(t.column_name) -id id -data data -drop function bug10055| -drop procedure if exists bug12297| -create procedure bug12297(lim int) -begin -set @x = 0; -repeat -insert into t1(id,data) -values('aa', @x); -set @x = @x + 1; -until @x >= lim -end repeat; -end| -call bug12297(10)| -drop procedure bug12297| -drop function if exists f_bug11247| -drop procedure if exists p_bug11247| -create function f_bug11247(param int) -returns int -return param + 1| -create procedure p_bug11247(lim int) -begin -declare v int default 0; -while v < lim do -set v= f_bug11247(v); -end while; -end| -call p_bug11247(10)| -drop function f_bug11247| -drop procedure p_bug11247| -drop procedure if exists bug12168| -drop table if exists t3, t4| -create table t3 (a int)| -insert into t3 values (1),(2),(3),(4)| -create table t4 (a int)| -create procedure bug12168(arg1 char(1)) -begin -declare b, c integer; -if arg1 = 'a' then -begin -declare c1 cursor for select a from t3 where a % 2; -declare continue handler for not found set b = 1; -set b = 0; -open c1; -c1_repeat: repeat -fetch c1 into c; -if (b = 1) then -leave c1_repeat; -end if; -insert into t4 values (c); -until b = 1 -end repeat; -end; -end if; -if arg1 = 'b' then -begin -declare c2 cursor for select a from t3 where not a % 2; -declare continue handler for not found set b = 1; -set b = 0; -open c2; -c2_repeat: repeat -fetch c2 into c; -if (b = 1) then -leave c2_repeat; -end if; -insert into t4 values (c); -until b = 1 -end repeat; -end; -end if; -end| -call bug12168('a')| -select * from t4| -a -1 -3 -truncate t4| -call bug12168('b')| -select * from t4| -a -2 -4 -truncate t4| -call bug12168('a')| -select * from t4| -a -1 -3 -truncate t4| -call bug12168('b')| -select * from t4| -a -2 -4 -truncate t4| -drop table t3, t4| -drop procedure if exists bug12168| -drop table if exists t3| -drop procedure if exists bug11333| -create table t3 (c1 char(128))| -insert into t3 values -('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')| -create procedure bug11333(i int) -begin -declare tmp varchar(128); -set @x = 0; -repeat -select c1 into tmp from t3 -where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; -set @x = @x + 1; -until @x >= i -end repeat; -end| -call bug11333(10)| -drop procedure bug11333| -drop table t3| -drop function if exists bug9048| -create function bug9048(f1 char binary) returns char binary -begin -set f1= concat( 'hello', f1 ); -return f1; -end| -drop function bug9048| -drop procedure if exists bug12849_1| -create procedure bug12849_1(inout x char) select x into x| -set @var='a'| -call bug12849_1(@var)| -select @var| -@var -a -drop procedure bug12849_1| -drop procedure if exists bug12849_2| -create procedure bug12849_2(inout foo varchar(15)) -begin -select concat(foo, foo) INTO foo; -end| -set @var='abcd'| -call bug12849_2(@var)| -select @var| -@var -abcdabcd -drop procedure bug12849_2| -drop procedure if exists bug131333| -drop function if exists bug131333| -create procedure bug131333() -begin -begin -declare a int; -select a; -set a = 1; -select a; -end; -begin -declare b int; -select b; -end; -end| -create function bug131333() -returns int -begin -begin -declare a int; -set a = 1; -end; -begin -declare b int; -return b; -end; -end| -call bug131333()| -a -NULL -a -1 -b -NULL -select bug131333()| -bug131333() -NULL -drop procedure bug131333| -drop function bug131333| -drop function if exists bug12379| -drop procedure if exists bug12379_1| -drop procedure if exists bug12379_2| -drop procedure if exists bug12379_3| -drop table if exists t3| -create table t3 (c1 char(1) primary key not null)| -create function bug12379() -returns integer -begin -insert into t3 values('X'); -insert into t3 values('X'); -return 0; -end| -create procedure bug12379_1() -begin -declare exit handler for sqlexception select 42; -select bug12379(); -END| -create procedure bug12379_2() -begin -declare exit handler for sqlexception begin end; -select bug12379(); -end| -create procedure bug12379_3() -begin -select bug12379(); -end| -select bug12379()| -ERROR 23000: Duplicate entry 'X' for key 1 -select 1| -1 -1 -call bug12379_1()| -bug12379() -42 -42 -select 2| -2 -2 -call bug12379_2()| -bug12379() -select 3| -3 -3 -call bug12379_3()| -ERROR 23000: Duplicate entry 'X' for key 1 -select 4| -4 -4 -drop function bug12379| -drop procedure bug12379_1| -drop procedure bug12379_2| -drop procedure bug12379_3| -drop table t3| -drop procedure if exists bug13124| -create procedure bug13124() -begin -declare y integer; -set @x=y; -end| -call bug13124()| -drop procedure bug13124| -drop procedure if exists bug12979_1| -create procedure bug12979_1(inout d decimal(5)) set d = d / 2| -set @bug12979_user_var = NULL| -call bug12979_1(@bug12979_user_var)| -drop procedure bug12979_1| -drop procedure if exists bug12979_2| -create procedure bug12979_2() -begin -declare internal_var decimal(5); -set internal_var= internal_var / 2; -select internal_var; -end| -call bug12979_2()| -internal_var -NULL -drop procedure bug12979_2| -drop table if exists t3| -drop procedure if exists bug6127| -create table t3 (s1 int unique)| -set @sm=@@sql_mode| -set sql_mode='traditional'| -create procedure bug6127() -begin -declare continue handler for sqlstate '23000' - begin -declare continue handler for sqlstate '22003' - insert into t3 values (0); -insert into t3 values (1000000000000000); -end; -insert into t3 values (1); -insert into t3 values (1); -end| -call bug6127()| -select * from t3| -s1 -0 -1 -call bug6127()| -ERROR 23000: Duplicate entry '0' for key 1 -select * from t3| -s1 -0 -1 -set sql_mode=@sm| -drop table t3| -drop procedure bug6127| -drop procedure if exists bug12589_1| -drop procedure if exists bug12589_2| -drop procedure if exists bug12589_3| -create procedure bug12589_1() -begin -declare spv1 decimal(3,3); -set spv1= 123.456; -set spv1 = 'test'; -create temporary table tm1 as select spv1; -show create table tm1; -drop temporary table tm1; -end| -create procedure bug12589_2() -begin -declare spv1 decimal(6,3); -set spv1= 123.456; -create temporary table tm1 as select spv1; -show create table tm1; -drop temporary table tm1; -end| -create procedure bug12589_3() -begin -declare spv1 decimal(6,3); -set spv1= -123.456; -create temporary table tm1 as select spv1; -show create table tm1; -drop temporary table tm1; -end| -call bug12589_1()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(3,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Warnings: -Warning 1264 Out of range value adjusted for column 'spv1' at row 1 -Warning 1366 Incorrect decimal value: 'test' for column 'spv1' at row 1 -call bug12589_2()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(6,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -call bug12589_3()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(6,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop procedure bug12589_1| -drop procedure bug12589_2| -drop procedure bug12589_3| -drop table if exists t3| -drop procedure if exists bug7049_1| -drop procedure if exists bug7049_2| -drop procedure if exists bug7049_3| -drop procedure if exists bug7049_4| -drop function if exists bug7049_1| -drop function if exists bug7049_2| -create table t3 ( x int unique )| -create procedure bug7049_1() -begin -insert into t3 values (42); -insert into t3 values (42); -end| -create procedure bug7049_2() -begin -declare exit handler for sqlexception -select 'Caught it' as 'Result'; -call bug7049_1(); -select 'Missed it' as 'Result'; -end| -create procedure bug7049_3() -call bug7049_1()| -create procedure bug7049_4() -begin -declare exit handler for sqlexception -select 'Caught it' as 'Result'; -call bug7049_3(); -select 'Missed it' as 'Result'; -end| -create function bug7049_1() -returns int -begin -insert into t3 values (42); -insert into t3 values (42); -return 42; -end| -create function bug7049_2() -returns int -begin -declare x int default 0; -declare continue handler for sqlexception -set x = 1; -set x = bug7049_1(); -return x; -end| -call bug7049_2()| -Result -Caught it -select * from t3| -x -42 -delete from t3| -call bug7049_4()| -Result -Caught it -select * from t3| -x -42 -select bug7049_2()| -bug7049_2() -1 -drop table t3| -drop procedure bug7049_1| -drop procedure bug7049_2| -drop procedure bug7049_3| -drop procedure bug7049_4| -drop function bug7049_1| -drop function bug7049_2| -drop function if exists bug13941| -drop procedure if exists bug13941| -create function bug13941(p_input_str text) -returns text -begin -declare p_output_str text; -set p_output_str = p_input_str; -set p_output_str = replace(p_output_str, 'xyzzy', 'plugh'); -set p_output_str = replace(p_output_str, 'test', 'prova'); -set p_output_str = replace(p_output_str, 'this', 'questo'); -set p_output_str = replace(p_output_str, ' a ', 'una '); -set p_output_str = replace(p_output_str, 'is', ''); -return p_output_str; -end| -create procedure bug13941(out sout varchar(128)) -begin -set sout = 'Local'; -set sout = ifnull(sout, 'DEF'); -end| -select bug13941('this is a test')| -bug13941('this is a test') -questo una prova -call bug13941(@a)| -select @a| -@a -Local -drop function bug13941| -drop procedure bug13941| -DROP PROCEDURE IF EXISTS bug13095; -DROP TABLE IF EXISTS bug13095_t1; -DROP VIEW IF EXISTS bug13095_v1; -CREATE PROCEDURE bug13095(tbl_name varchar(32)) -BEGIN -SET @str = -CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))"); -SELECT @str; -PREPARE stmt FROM @str; -EXECUTE stmt; -SET @str = -CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" ); -SELECT @str; -PREPARE stmt FROM @str; -EXECUTE stmt; -SET @str = -CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name); -SELECT @str; -PREPARE stmt FROM @str; -EXECUTE stmt; -SELECT * FROM bug13095_v1; -SET @str = -"DROP VIEW bug13095_v1"; -SELECT @str; -PREPARE stmt FROM @str; -EXECUTE stmt; -END| -CALL bug13095('bug13095_t1'); -@str -CREATE TABLE bug13095_t1(stuff char(15)) -@str -INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3') -@str -CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1 -c1 -row1 -row2 -row3 -@str -DROP VIEW bug13095_v1 -DROP PROCEDURE IF EXISTS bug13095; -DROP VIEW IF EXISTS bug13095_v1; -DROP TABLE IF EXISTS bug13095_t1; -drop procedure if exists bug14210| -set @@session.max_heap_table_size=16384| -select @@session.max_heap_table_size| -@@session.max_heap_table_size -16384 -create table t3 (a char(255)) engine=InnoDB| -create procedure bug14210_fill_table() -begin -declare table_size, max_table_size int default 0; -select @@session.max_heap_table_size into max_table_size; -delete from t3; -insert into t3 (a) values (repeat('a', 255)); -repeat -insert into t3 select a from t3; -select count(*)*255 from t3 into table_size; -until table_size > max_table_size*2 end repeat; -end| -call bug14210_fill_table()| -drop procedure bug14210_fill_table| -create table t4 like t3| -create procedure bug14210() -begin -declare a char(255); -declare done int default 0; -declare c cursor for select * from t3; -declare continue handler for sqlstate '02000' set done = 1; -open c; -repeat -fetch c into a; -if not done then -insert into t4 values (upper(a)); -end if; -until done end repeat; -close c; -end| -call bug14210()| -select count(*) from t4| -count(*) -256 -drop table t3, t4| -drop procedure bug14210| -set @@session.max_heap_table_size=default| -drop function if exists bug14723| -drop procedure if exists bug14723| -/*!50003 create function bug14723() -returns bigint(20) -main_loop: begin -return 42; -end */;; -show create function bug14723;; -Function sql_mode Create Function -bug14723 CREATE FUNCTION `bug14723`() RETURNS bigint(20) -main_loop: begin -return 42; -end -select bug14723();; -bug14723() -42 -/*!50003 create procedure bug14723() -main_loop: begin -select 42; -end */;; -show create procedure bug14723;; -Procedure sql_mode Create Procedure -bug14723 CREATE PROCEDURE `bug14723`() -main_loop: begin -select 42; -end -call bug14723();; -42 -42 -drop function bug14723| -drop procedure bug14723| -create procedure bug14845() -begin -declare a char(255); -declare done int default 0; -declare c cursor for select count(*) from t1 where 1 = 0; -declare continue handler for sqlstate '02000' set done = 1; -open c; -repeat -fetch c into a; -if not done then -select a; -end if; -until done end repeat; -close c; -end| -call bug14845()| -a -0 -drop procedure bug14845| -drop procedure if exists bug13549_1| -drop procedure if exists bug13549_2| -CREATE PROCEDURE `bug13549_2`() -begin -call bug13549_1(); -end| -CREATE PROCEDURE `bug13549_1`() -begin -declare done int default 0; -set done= not done; -end| -CALL bug13549_2()| -drop procedure bug13549_2| -drop procedure bug13549_1| -drop function if exists bug10100f| -drop procedure if exists bug10100p| -drop procedure if exists bug10100t| -drop procedure if exists bug10100pt| -drop procedure if exists bug10100pv| -drop procedure if exists bug10100pd| -drop procedure if exists bug10100pc| -create function bug10100f(prm int) returns int -begin -if prm > 1 then -return prm * bug10100f(prm - 1); -end if; -return 1; -end| -create procedure bug10100p(prm int, inout res int) -begin -set res = res * prm; -if prm > 1 then -call bug10100p(prm - 1, res); -end if; -end| -create procedure bug10100t(prm int) -begin -declare res int; -set res = 1; -call bug10100p(prm, res); -select res; -end| -create table t3 (a int)| -insert into t3 values (0)| -create view v1 as select a from t3; -create procedure bug10100pt(level int, lim int) -begin -if level < lim then -update t3 set a=level; -FLUSH TABLES; -call bug10100pt(level+1, lim); -else -select * from t3; -end if; -end| -create procedure bug10100pv(level int, lim int) -begin -if level < lim then -update v1 set a=level; -FLUSH TABLES; -call bug10100pv(level+1, lim); -else -select * from v1; -end if; -end| -prepare stmt2 from "select * from t3;"; -create procedure bug10100pd(level int, lim int) -begin -if level < lim then -select level; -prepare stmt1 from "update t3 set a=a+2"; -execute stmt1; -FLUSH TABLES; -execute stmt1; -FLUSH TABLES; -execute stmt1; -FLUSH TABLES; -deallocate prepare stmt1; -execute stmt2; -select * from t3; -call bug10100pd(level+1, lim); -else -execute stmt2; -end if; -end| -create procedure bug10100pc(level int, lim int) -begin -declare lv int; -declare c cursor for select a from t3; -open c; -if level < lim then -select level; -fetch c into lv; -select lv; -update t3 set a=level+lv; -FLUSH TABLES; -call bug10100pc(level+1, lim); -else -select * from t3; -end if; -close c; -end| -set @@max_sp_recursion_depth=4| -select @@max_sp_recursion_depth| -@@max_sp_recursion_depth -4 -select bug10100f(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select bug10100f(6)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -call bug10100t(5)| -res -120 -call bug10100pt(1,5)| -a -4 -call bug10100pv(1,5)| -a -4 -update t3 set a=1| -call bug10100pd(1,5)| -level -1 -a -7 -a -7 -level -2 -a -13 -a -13 -level -3 -a -19 -a -19 -level -4 -a -25 -a -25 -a -25 -select * from t3| -a -25 -update t3 set a=1| -call bug10100pc(1,5)| -level -1 -lv -1 -level -2 -lv -2 -level -3 -lv -4 -level -4 -lv -7 -a -11 -select * from t3| -a -11 -set @@max_sp_recursion_depth=0| -select @@max_sp_recursion_depth| -@@max_sp_recursion_depth -0 -select bug10100f(5)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -call bug10100t(5)| -ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p -set @@max_sp_recursion_depth=255| -set @var=1| -call bug10100p(255, @var)| -call bug10100pt(1,255)| -call bug10100pv(1,255)| -call bug10100pd(1,255)| -call bug10100pc(1,255)| -set @@max_sp_recursion_depth=0| -deallocate prepare stmt2| -drop function bug10100f| -drop procedure bug10100p| -drop procedure bug10100t| -drop procedure bug10100pt| -drop procedure bug10100pv| -drop procedure bug10100pd| -drop procedure bug10100pc| -drop view v1| -drop procedure if exists bug13729| -drop table if exists t3| -create table t3 (s1 int, primary key (s1))| -insert into t3 values (1),(2)| -create procedure bug13729() -begin -declare continue handler for sqlexception select 55; -update t3 set s1 = 1; -end| -call bug13729()| -55 -55 -select * from t3| -s1 -1 -2 -drop procedure bug13729| -drop table t3| -drop procedure if exists bug14643_1| -drop procedure if exists bug14643_2| -create procedure bug14643_1() -begin -declare continue handler for sqlexception select 'boo' as 'Handler'; -begin -declare v int default undefined_var; -if v = 1 then -select 1; -else -select v, isnull(v); -end if; -end; -end| -create procedure bug14643_2() -begin -declare continue handler for sqlexception select 'boo' as 'Handler'; -case undefined_var -when 1 then -select 1; -else -select 2; -end case; -select undefined_var; -end| -call bug14643_1()| -Handler -boo -v isnull(v) -NULL 1 -call bug14643_2()| -Handler -boo -Handler -boo -drop procedure bug14643_1| -drop procedure bug14643_2| -drop procedure if exists bug14304| -drop table if exists t3, t4| -create table t3(a int primary key auto_increment)| -create table t4(a int primary key auto_increment)| -create procedure bug14304() -begin -insert into t3 set a=null; -insert into t4 set a=null; -insert into t4 set a=null; -insert into t4 set a=null; -insert into t4 set a=null; -insert into t4 set a=null; -insert into t4 select null as a; -insert into t3 set a=null; -insert into t3 set a=null; -select * from t3; -end| -call bug14304()| -a -1 -2 -3 -drop procedure bug14304| -drop table t3, t4| -drop procedure if exists bug14376| -create procedure bug14376() -begin -declare x int default x; -end| -call bug14376()| -ERROR 42S22: Unknown column 'x' in 'field list' -drop procedure bug14376| -create procedure bug14376() -begin -declare x int default 42; -begin -declare x int default x; -select x; -end; -end| -call bug14376()| -x -42 -drop procedure bug14376| -create procedure bug14376(x int) -begin -declare x int default x; -select x; -end| -call bug14376(4711)| -x -4711 -drop procedure bug14376| -drop procedure if exists bug5967| -drop table if exists t3| -create table t3 (a varchar(255))| -insert into t3 (a) values ("a - table column")| -create procedure bug5967(a varchar(255)) -begin -declare i varchar(255); -declare c cursor for select a from t3; -select a; -select a from t3 into i; -select i as 'Parameter takes precedence over table column'; open c; -fetch c into i; -close c; -select i as 'Parameter takes precedence over table column in cursors'; -begin -declare a varchar(255) default 'a - local variable'; -declare c1 cursor for select a from t3; -select a as 'A local variable takes precedence over parameter'; -open c1; -fetch c1 into i; -close c1; -select i as 'A local variable takes precedence over parameter in cursors'; -begin -declare a varchar(255) default 'a - local variable in a nested compound statement'; -declare c2 cursor for select a from t3; -select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; -select a from t3 into i; -select i as 'A local variable in a nested compound statement takes precedence over table column'; -open c2; -fetch c2 into i; -close c2; -select i as 'A local variable in a nested compound statement takes precedence over table column in cursors'; -end; -end; -end| -call bug5967("a - stored procedure parameter")| -a -a - stored procedure parameter -Parameter takes precedence over table column -a - stored procedure parameter -Parameter takes precedence over table column in cursors -a - stored procedure parameter -A local variable takes precedence over parameter -a - local variable -A local variable takes precedence over parameter in cursors -a - local variable -A local variable in a nested compound statement takes precedence over a local variable in the outer statement -a - local variable in a nested compound statement -A local variable in a nested compound statement takes precedence over table column -a - local variable in a nested compound statement -A local variable in a nested compound statement takes precedence over table column in cursors -a - local variable in a nested compound statement -drop procedure bug5967| -drop procedure if exists bug13012| -create procedure bug13012() -BEGIN -REPAIR TABLE t1; -BACKUP TABLE t1 to '../tmp'; -DROP TABLE t1; -RESTORE TABLE t1 FROM '../tmp'; -END| -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -Table Op Msg_type Msg_text -test.t1 backup status OK -Table Op Msg_type Msg_text -test.t1 restore status OK -drop procedure bug13012| -create view v1 as select * from t1| -create procedure bug13012() -BEGIN -REPAIR TABLE t1,t2,t3,v1; -OPTIMIZE TABLE t1,t2,t3,v1; -ANALYZE TABLE t1,t2,t3,v1; -END| -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -drop procedure bug13012| -drop view v1; -select * from t1| -id data -aa 0 -aa 1 -aa 2 -aa 3 -aa 4 -aa 5 -aa 6 -aa 7 -aa 8 -aa 9 -drop schema if exists mysqltest1| -Warnings: -Note 1008 Can't drop database 'mysqltest1'; database doesn't exist -drop schema if exists mysqltest2| -Warnings: -Note 1008 Can't drop database 'mysqltest2'; database doesn't exist -drop schema if exists mysqltest3| -Warnings: -Note 1008 Can't drop database 'mysqltest3'; database doesn't exist -create schema mysqltest1| -create schema mysqltest2| -create schema mysqltest3| -use mysqltest3| -create procedure mysqltest1.p1 (out prequestid varchar(100)) -begin -call mysqltest2.p2('call mysqltest3.p3(1, 2)'); -end| -create procedure mysqltest2.p2(in psql text) -begin -declare lsql text; -set @lsql= psql; -prepare lstatement from @lsql; -execute lstatement; -deallocate prepare lstatement; -end| -create procedure mysqltest3.p3(in p1 int) -begin -select p1; -end| -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -drop schema if exists mysqltest1| -drop schema if exists mysqltest2| -drop schema if exists mysqltest3| -use test| -drop table if exists t3| -drop procedure if exists bug15441| -create table t3 (id int not null primary key, county varchar(25))| -insert into t3 (id, county) values (1, 'York')| -create procedure bug15441(c varchar(25)) -begin -update t3 set id=2, county=values(c); -end| -call bug15441('county')| -ERROR 42S22: Unknown column 'c' in 'field list' -drop procedure bug15441| -create procedure bug15441(county varchar(25)) -begin -declare c varchar(25) default "hello"; -insert into t3 (id, county) values (1, county) -on duplicate key update county= values(county); -select * from t3; -update t3 set id=2, county=values(id); -select * from t3; -end| -call bug15441('Yale')| -id county -1 Yale -id county -2 NULL -drop table t3| -drop procedure bug15441| -drop procedure if exists bug14498_1| -drop procedure if exists bug14498_2| -drop procedure if exists bug14498_3| -drop procedure if exists bug14498_4| -drop procedure if exists bug14498_5| -create procedure bug14498_1() -begin -declare continue handler for sqlexception select 'error' as 'Handler'; -if v then -select 'yes' as 'v'; -else -select 'no' as 'v'; -end if; -select 'done' as 'End'; -end| -create procedure bug14498_2() -begin -declare continue handler for sqlexception select 'error' as 'Handler'; -while v do -select 'yes' as 'v'; -end while; -select 'done' as 'End'; -end| -create procedure bug14498_3() -begin -declare continue handler for sqlexception select 'error' as 'Handler'; -repeat -select 'maybe' as 'v'; -until v end repeat; -select 'done' as 'End'; -end| -create procedure bug14498_4() -begin -declare continue handler for sqlexception select 'error' as 'Handler'; -case v -when 1 then -select '1' as 'v'; -when 2 then -select '2' as 'v'; -else -select '?' as 'v'; -end case; -select 'done' as 'End'; -end| -create procedure bug14498_5() -begin -declare continue handler for sqlexception select 'error' as 'Handler'; -case -when v = 1 then -select '1' as 'v'; -when v = 2 then -select '2' as 'v'; -else -select '?' as 'v'; -end case; -select 'done' as 'End'; -end| -call bug14498_1()| -Handler -error -End -done -call bug14498_2()| -Handler -error -End -done -call bug14498_3()| -v -maybe -Handler -error -End -done -call bug14498_4()| -Handler -error -End -done -call bug14498_5()| -Handler -error -End -done -drop procedure bug14498_1| -drop procedure bug14498_2| -drop procedure bug14498_3| -drop procedure bug14498_4| -drop procedure bug14498_5| -drop table if exists t3| -drop procedure if exists bug15231_1| -drop procedure if exists bug15231_2| -drop procedure if exists bug15231_3| -drop procedure if exists bug15231_4| -create table t3 (id int not null)| -create procedure bug15231_1() -begin -declare xid integer; -declare xdone integer default 0; -declare continue handler for not found set xdone = 1; -set xid=null; -call bug15231_2(xid); -select xid, xdone; -end| -create procedure bug15231_2(inout ioid integer) -begin -select "Before NOT FOUND condition is triggered" as '1'; -select id into ioid from t3 where id=ioid; -select "After NOT FOUND condtition is triggered" as '2'; -if ioid is null then -set ioid=1; -end if; -end| -create procedure bug15231_3() -begin -declare exit handler for sqlwarning -select 'Caught it (wrong)' as 'Result'; -call bug15231_4(); -end| -create procedure bug15231_4() -begin -declare x decimal(2,1); -set x = 'zap'; -select 'Missed it (correct)' as 'Result'; -end| -call bug15231_1()| -1 -Before NOT FOUND condition is triggered -2 -After NOT FOUND condtition is triggered -xid xdone -1 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug15231_3()| -Result -Missed it (correct) -Warnings: -Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 -drop table if exists t3| -drop procedure if exists bug15231_1| -drop procedure if exists bug15231_2| -drop procedure if exists bug15231_3| -drop procedure if exists bug15231_4| -drop procedure if exists bug15011| -create table t3 (c1 int primary key)| -insert into t3 values (1)| -create procedure bug15011() -deterministic -begin -declare continue handler for 1062 -select 'Outer' as 'Handler'; -begin -declare continue handler for 1062 -select 'Inner' as 'Handler'; -insert into t3 values (1); -end; -end| -call bug15011()| -Handler -Inner -drop procedure bug15011| -drop table t3| -drop table t1,t2; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 07630ffee0f..ae929cf9c2e 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1087,24 +1087,24 @@ CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT 1)) a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(1) NOT NULL default '0', - `(SELECT 1)` bigint(1) NOT NULL default '0' + `a` int(1) NOT NULL default '0', + `(SELECT 1)` int(1) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a)) a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(1) NOT NULL default '0', - `(SELECT a)` bigint(1) NOT NULL default '0' + `a` int(1) NOT NULL default '0', + `(SELECT a)` int(1) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT * FROM (SELECT 1 as a,(SELECT a+0)) a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` bigint(1) NOT NULL default '0', - `(SELECT a+0)` bigint(3) NOT NULL default '0' + `a` int(1) NOT NULL default '0', + `(SELECT a+0)` int(3) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a; @@ -2854,6 +2854,40 @@ a 3 4 DROP TABLE t1,t2,t3; +purge master logs before (select adddate(current_timestamp(), interval -4 day)); +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2); +f1 +1 +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0); +f1 +1 +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0); +f1 +1 +DROP TABLE t1, t2; +select 1 from dual where 1 < any (select 2); +1 +1 +select 1 from dual where 1 < all (select 2); +1 +1 +select 1 from dual where 2 > any (select 1); +1 +1 +select 1 from dual where 2 > all (select 1); +1 +1 +select 1 from dual where 1 < any (select 2 from dual); +1 +1 +select 1 from dual where 1 < all (select 2 from dual where 1!=1); +1 +1 create table t1 (df decimal(5,1)); insert into t1 values(1.1); insert into t1 values(2.2); @@ -3204,3 +3238,84 @@ i 10000000000000000000 DROP TABLE t1; DROP TABLE t2; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +name varchar(255) NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO t1 VALUES +(1, 'Balazs'), (2, 'Joe'), (3, 'Frank'); +CREATE TABLE t2 ( +id bigint(20) unsigned NOT NULL auto_increment, +mid bigint(20) unsigned NOT NULL, +date date NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO t2 VALUES +(1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), +(4, 2, '2006-04-20'), (5, 1, '2006-05-01'); +SELECT *, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 0, 1) AS date_last, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last +FROM t1; +id name date_last date_next_to_last +1 Balazs 2006-05-01 NULL +2 Joe 2006-04-20 NULL +3 Frank 2006-04-13 NULL +SELECT *, +(SELECT COUNT(*) FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 1, 1) AS date_count +FROM t1; +id name date_count +1 Balazs NULL +2 Joe NULL +3 Frank NULL +SELECT *, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 0, 1) AS date_last, +(SELECT date FROM t2 WHERE mid = t1.id +ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last +FROM t1; +id name date_last date_next_to_last +1 Balazs 2006-05-01 2006-03-30 +2 Joe 2006-04-20 2006-04-06 +3 Frank 2006-04-13 NULL +DROP TABLE t1,t2; +CREATE TABLE t1 ( +i1 int(11) NOT NULL default '0', +i2 int(11) NOT NULL default '0', +t datetime NOT NULL default '0000-00-00 00:00:00', +PRIMARY KEY (i1,i2,t) +); +INSERT INTO t1 VALUES +(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), +(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), +(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'), +(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'), +(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'), +(24,2,'2005-05-27 12:40:06'); +CREATE TABLE t2 ( +i1 int(11) NOT NULL default '0', +i2 int(11) NOT NULL default '0', +t datetime default NULL, +PRIMARY KEY (i1) +); +INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40'); +EXPLAIN +SELECT * FROM t1,t2 +WHERE t1.t = (SELECT t1.t FROM t1 +WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 +ORDER BY t1.t DESC LIMIT 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index +2 DEPENDENT SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index +SELECT * FROM t1,t2 +WHERE t1.t = (SELECT t1.t FROM t1 +WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 +ORDER BY t1.t DESC LIMIT 1); +i1 i2 t i1 i2 t +24 1 2005-05-27 12:40:30 24 1 2006-06-20 12:29:40 +DROP TABLE t1, t2; diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index cfbe11b975e..ec8211dc4bf 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -276,7 +276,7 @@ create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, r show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment auto bigint(12) unsigned NULL NO PRI 0 # -t1 bigint(1) NULL NO 0 # +t1 int(1) NULL NO 0 # t2 varchar(1) latin1_swedish_ci NO # t3 varchar(256) latin1_swedish_ci NO # t4 varbinary(256) NULL NO # @@ -304,7 +304,7 @@ show full columns from t3; Field Type Collation Null Key Default Extra Privileges Comment c1 int(11) NULL YES NULL # c2 int(11) NULL YES NULL # -const bigint(1) NULL NO 0 # +const int(1) NULL NO 0 # drop table t1,t2,t3; create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield)); drop table t1; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 72cffb9531c..7d2ab63ca77 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2735,4 +2735,42 @@ m e 4 a 1 b DROP VIEW v1; -DROP TABLE IF EXISTS t1,t2; +DROP TABLE t1,t2; +CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); +CREATE VIEW v1 AS SELECT a, b FROM t1; +INSERT INTO v1 (b) VALUES (2); +Warnings: +Warning 1423 Field of view 'test.v1' underlying table doesn't have a default value +SET SQL_MODE = STRICT_ALL_TABLES; +INSERT INTO v1 (b) VALUES (4); +ERROR HY000: Field of view 'test.v1' underlying table doesn't have a default value +SET SQL_MODE = ''; +SELECT * FROM t1; +a b +0 2 +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (firstname text, surname text); +INSERT INTO t1 VALUES +("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); +CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; +SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), +LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 +FROM v1; +f1 +BartBart +Milhouse vanMilhouse van +MontgomeryMontgomery +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (i int, j int); +CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; +DESCRIBE v1; +Field Type Null Key Default Extra +COALESCE(i,j) int(11) YES NULL +CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; +DESCRIBE t2; +Field Type Null Key Default Extra +COALESCE(i,j) int(11) YES NULL +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index f9006ebca37..59be512d5a6 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -173,6 +173,13 @@ alter table t1 engine=isam; Warnings: Warning 1266 Using storage engine MyISAM for table 't1' drop table t1; +create table t1 (id int) engine=merge; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't1' +alter table t1 engine=merge; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't1' +drop table t1; create table t1 (id int) type=heap; Warnings: Warning 1287 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 33b06e9bc11..3ad33dddcbe 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -157,7 +157,7 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; select * from t1; --replace_result P2 p2 ---error 1288 +--error ER_UNKNOWN_TABLE delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; -- error 1054 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index 773c9121af0..38beab605fd 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1365,6 +1365,62 @@ drop table federated.t1, federated.t2; connection slave; drop table federated.t1, federated.t2; +# +# BUG #18764: Delete conditions causing inconsistencies in Federated tables +# +connection slave; +--disable_warnings +DROP TABLE IF EXISTS federated.test; +--enable_warnings +CREATE TABLE federated.test ( + `id` int(11) NOT NULL, + `val1` varchar(255) NOT NULL, + `val2` varchar(255) NOT NULL, + PRIMARY KEY (`id`) + ) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +connection master; +--disable_warnings +DROP TABLE IF EXISTS federated.test_local; +DROP TABLE IF EXISTS federated.test_remote; +--enable_warnings +CREATE TABLE federated.test_local ( + `id` int(11) NOT NULL, + `val1` varchar(255) NOT NULL, + `val2` varchar(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + +INSERT INTO federated.test_local VALUES (1, 'foo', 'bar'), +(2, 'bar', 'foo'); + +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE federated.test_remote ( + `id` int(11) NOT NULL, + `val1` varchar(255) NOT NULL, + `val2` varchar(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=FEDERATED DEFAULT CHARSET=latin1 +CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/test'; + +insert into federated.test_remote select * from federated.test_local; + +select * from federated.test_remote; + +delete from federated.test_remote where id in (1,2); + +insert into federated.test_remote select * from federated.test_local; + +select * from federated.test_remote; +--disable_warnings +DROP TABLE federated.test_local; +DROP TABLE federated.test_remote; +--enable_warnings +connection slave; +--disable_warnings +DROP TABLE federated.test; +--enable_warnings + # # Additional test for bug#18437 "Wrong values inserted with a before # update trigger on NDB table". SQL-layer didn't properly inform @@ -1425,4 +1481,22 @@ drop table t1; connection master; drop table t1; +# +# Bug #17608: String literals lost during INSERT query on FEDERATED table +# +connection slave; +create table t1 (a longblob not null); +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table t1 + (a longblob not null) engine=federated + connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; +insert into t1 values (repeat('a',5000)); +select length(a) from t1; +connection slave; +select length(a) from t1; +drop table t1; +connection master; +drop table t1; + source include/federated_cleanup.inc; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 56a03283d3a..0fb866cf370 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -690,6 +690,23 @@ select * from t1 where f1='test' and (f2= sha("test") or f2= sha("TEST")); select * from t1 where f1='test' and (f2= sha("TEST") or f2= sha("test")); drop table t1; +# +# Bug#18243: REVERSE changes its argument +# + +CREATE TABLE t1 (a varchar(10)); +INSERT INTO t1 VALUES ('abc'), ('xyz'); + +SELECT a, CONCAT(a,' ',a) AS c FROM t1 + HAVING LEFT(c,LENGTH(c)-INSTR(REVERSE(c)," ")) = a; + +SELECT a, CONCAT(a,' ',a) AS c FROM t1 + HAVING LEFT(CONCAT(a,' ',a), + LENGTH(CONCAT(a,' ',a))- + INSTR(REVERSE(CONCAT(a,' ',a))," ")) = a; + +DROP TABLE t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 83b877e725c..c95d3e3319a 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -407,6 +407,16 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); # +# Bug #16327: problem with timestamp < 1970 +# + +set time_zone='-6:00'; +create table t1(a timestamp); +insert into t1 values (19691231190001); +select * from t1; +drop table t1; + +# # Bug#16377 result of DATE/TIME functions were compared as strings which # can lead to a wrong result. # diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index c7d2e2c0acd..4b512ccce1d 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -59,6 +59,38 @@ where drop table t1, t2; # +# Bug#17212: results not sorted correctly by ORDER BY when using index +# (repeatable only w/innodb because of index props) +# +CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB; +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB; +CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), + UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB; + +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; +INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; + +INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); +INSERT INTO t2 SELECT a + 1, b FROM t2; +DELETE FROM t2 WHERE a = 1 AND b < 2; + +INSERT INTO t3 VALUES (1,1,1),(2,1,2); +INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; +INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; + +# demonstrate a problem when a must-use-sort table flag +# (sort_by_table=1) is being neglected. +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 2; + +# demonstrate the problem described in the bug report +SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE + t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) + ORDER BY t1.b LIMIT 5; +DROP TABLE t1, t2, t3; +# # Bug #12882 min/max inconsistent on empty table # diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 05953a1fd49..b6b94d07e87 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -247,3 +247,141 @@ CREATE TABLE t2 (z int, y int); CREATE TABLE t3 (a int, b int); INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1); DROP TABLE IF EXISTS t1,t2,t3; + +# +# Bug #20989: View '(null).(null)' references invalid table(s)... on +# SQL SECURITY INVOKER +# +# this is really the fact that REPLACE ... SELECT required additional +# INSERT privs (on tables that are part of a view) over the related +# REPLACE, SELECT +# + +CREATE DATABASE meow; + +connect (root,localhost,root,,meow); +connection root; + +CREATE TABLE table_target ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target2 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE TABLE table_target3 ( mexs_id CHAR(8), messzeit TIMESTAMP, PRIMARY KEY (mexs_id)); +CREATE VIEW view_target2 AS SELECT mexs_id,messzeit FROM table_target2; +CREATE SQL SECURITY INVOKER VIEW view_target3 AS SELECT mexs_id,messzeit FROM table_target3; + +CREATE TABLE table_stations ( mexs_id VARCHAR(8), icao VARCHAR(4), country CHAR(2), PRIMARY KEY (mexs_id), UNIQUE KEY icao (icao), KEY country (country), CONSTRAINT stations_ibfk_8 FOREIGN KEY (country) REFERENCES countries (country) ON UPDATE CASCADE); +INSERT INTO table_stations VALUES ('87654321','XXXX','YY'); + +CREATE TABLE table_countries ( country CHAR(2), iso_short_en VARCHAR(64), PRIMARY KEY (country)); +INSERT INTO table_countries VALUES ('YY','Entenhausen'); + +CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW view_stations AS select table_stations.mexs_id AS mexs_id, table_stations.icao AS icao, table_stations.country AS landescode from (table_stations join table_countries on((table_stations.country = table_countries.country))); + +CREATE TABLE table_source ( id varchar(4), datetime TIMESTAMP, PRIMARY KEY (id)); +INSERT INTO table_source VALUES ('XXXX','2006-07-12 07:50:00'); + +GRANT SELECT ON table_source TO user20989@localhost; +GRANT SELECT ON table_countries TO user20989@localhost; +GRANT SELECT ON table_stations TO user20989@localhost; +GRANT SELECT ON view_stations TO user20989@localhost; +GRANT SELECT ON table_target TO user20989@localhost; +GRANT SELECT ON table_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target3 TO user20989@localhost; + +connect (user20989,localhost,user20989,,meow); +connection user20989; + +--error 1142 +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); + +--error 1142 +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +--error 1356 +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); + +connection root; +disconnect user20989; + +GRANT INSERT,DELETE ON table_target TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON view_target2 TO user20989@localhost; +GRANT INSERT,DELETE,SELECT ON table_target3 TO user20989@localhost; + +connect (user20989,localhost,user20989,,meow); +connection user20989; + +REPLACE INTO table_target +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN table_target AS old +USING (mexs_id); + +--error 1142 +REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); +REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); + +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +REPLACE INTO view_target2 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target2 AS old +USING (mexs_id); + +REPLACE INTO view_target3 +SELECT stations.mexs_id AS mexs_id, datetime AS messzeit +FROM table_source +INNER JOIN view_stations AS stations +ON table_source.id = stations.icao +LEFT JOIN view_target3 AS old +USING (mexs_id); + +connection root; +disconnect user20989; + +SELECT * FROM table_target; +SELECT * FROM view_target2; +SELECT * FROM view_target3; + +DROP VIEW view_stations; +DROP TABLE table_source; +DROP TABLE table_countries; +DROP TABLE table_stations; +DROP TABLE table_target; +DROP TABLE table_target2; +DROP TABLE table_target3; +DROP VIEW view_target2; +DROP VIEW view_target3; +DROP USER user20989@localhost; + +disconnect root; + +connection default; + +DROP DATABASE meow; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 639129e1393..93fbc631680 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -389,4 +389,19 @@ create table tm (b bit(1)) engine = merge union = (t1,t2); select * from tm; drop table tm, t1, t2; -# End of 5.0 tests +# +# Bug #17766: The server accepts to create MERGE tables which cannot work +# +create table t1 (a int) insert_method = last engine = merge; +--error ER_OPEN_AS_READONLY +insert into t1 values (1); +create table t2 (a int) engine = myisam; +alter table t1 union (t2); +insert into t1 values (1); +alter table t1 insert_method = no; +--error ER_OPEN_AS_READONLY +insert into t1 values (1); +drop table t2; +drop table t1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 10c07c931ab..9d412c43bb5 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -20,16 +20,16 @@ insert into t1 values(1); --disable_query_log # Test delimiter : supplied on the command line -select "Test delimiter : from command line" as " "; +select "Test delimiter : from command line" as "_"; --exec $MYSQL test --delimiter=":" -e "select * from t1:" # Test delimiter :; supplied on the command line -select "Test delimiter :; from command line" as " "; +select "Test delimiter :; from command line" as "_"; --exec $MYSQL test --delimiter=":;" -e "select * from t1:;" # Test 'go' command (vertical output) \G -select "Test 'go' command(vertical output) \G" as " "; +select "Test 'go' command(vertical output) \G" as "_"; --exec $MYSQL test -e "select * from t1\G" # Test 'go' command \g -select "Test 'go' command \g" as " "; +select "Test 'go' command \g" as "_"; --exec $MYSQL test -e "select * from t1\g" --enable_query_log drop table t1; diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index c2cd15c3f0f..0691cb7c76b 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -42,21 +42,21 @@ select "--- Local --" as ""; # --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000001 # this should not fail but shouldn't produce any working statements --disable_query_log select "--- Broken LOAD DATA --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000002 2> /dev/null # this should show almost nothing --disable_query_log select "--- --database --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --database=nottest $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --database=nottest $MYSQLTEST_VARDIR/log/master-bin.000001 2> /dev/null # this test for position option --disable_query_log @@ -81,14 +81,14 @@ select "--- Remote --" as ""; select "--- Broken LOAD DATA --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 2> /dev/null # And this too ! (altough it is documented) --disable_query_log select "--- --database --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --database=nottest master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --database=nottest master-bin.000001 2> /dev/null # Strangely but this works --disable_query_log diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 858d8910781..9508846f71a 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -610,7 +610,7 @@ drop database db1; # BUG#15328 Segmentation fault occured if my.cnf is invalid for escape sequence # ---exec $MYSQL_MY_PRINT_DEFAULTS --defaults-file=$MYSQL_TEST_DIR/std_data/bug15328.cnf mysqldump +--exec $MYSQL_MY_PRINT_DEFAULTS --config-file=$MYSQL_TEST_DIR/std_data/bug15328.cnf mysqldump # @@ -1222,9 +1222,47 @@ create database mysqldump_views; use mysqldump_views; create view nasishnasifu as select mysqldump_tables.basetable.id from mysqldump_tables.basetable; ---exec $MYSQL_DUMP --skip-comments --databases mysqldump_tables mysqldump_views; +--exec $MYSQL_DUMP --skip-comments --compact --databases mysqldump_tables mysqldump_views; drop view nasishnasifu; drop database mysqldump_views; drop table mysqldump_tables.basetable; drop database mysqldump_tables; + +# Bug20221 Dumping of multiple databases containing view(s) yields maleformed dumps + +create database mysqldump_dba; +use mysqldump_dba; +create table t1 (f1 int, f2 int); +insert into t1 values (1,1); +create view v1 as select f1, f2 from t1; + +create database mysqldump_dbb; +use mysqldump_dbb; +create table t1 (f1 int, f2 int); +insert into t1 values (2,2); +create view v1 as select f1, f2 from t1; + +--exec $MYSQL_DUMP --skip-comments --add-drop-database --databases mysqldump_dba mysqldump_dbb > $MYSQLTEST_VARDIR/tmp/bug20221_backup; + +drop view v1; +drop table t1; +drop database mysqldump_dbb; +use mysqldump_dba; +drop view v1; +drop table t1; +drop database mysqldump_dba; + +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug20221_backup; + +select * from mysqldump_dba.v1; +select * from mysqldump_dbb.v1; + +use mysqldump_dba; +drop view v1; +drop table t1; +drop database mysqldump_dba; +use mysqldump_dbb; +drop view v1; +drop table t1; +drop database mysqldump_dbb; diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test index d4b6fc35e74..6a754bb32a7 100644 --- a/mysql-test/t/odbc.test +++ b/mysql-test/t/odbc.test @@ -21,4 +21,14 @@ select * from t1 where a is null; explain select * from t1 where b is null; drop table t1; +# +# Bug #14553: NULL in WHERE resets LAST_INSERT_ID +# +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (NULL); +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL; +SELECT sql_no_cache a, last_insert_id() FROM t1; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 90a123cf5dc..fa66306aaa6 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -77,6 +77,24 @@ sync_slave_with_master; connection master; drop table t1; sync_slave_with_master; + +# +# Bug#14553: NULL in WHERE resets LAST_INSERT_ID +# +connection master; +create table t1(a int auto_increment, key(a)); +create table t2(a int); +insert into t1 (a) values (null); +insert into t2 (a) select a from t1 where a is null; +insert into t2 (a) select a from t1 where a is null; +select * from t2; +sync_slave_with_master; +connection slave; +select * from t2; +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; # End of 4.1 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index b75d0dd8bb6..592e366f835 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2901,3 +2901,14 @@ from t1 left outer join t2 on t1.a = t2.c and t2.b <> 1 where t1.b <> 1 order by t1.a; drop table t1,t2; + +# +# Bug #20569: Garbage in DECIMAL results from some mathematical functions +# +SELECT 0.9888889889 * 1.011111411911; + +# +# Bug #10977: No warning issued if a column name is truncated +# +prepare stmt from 'select 1 as " a "'; +execute stmt; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 25c96042e6f..9a0003bab9c 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5963,6 +5963,31 @@ drop table t3| drop procedure bug15217| # +# Bug#21002 "Derived table not selecting from a "real" table fails in JOINs" +# +# A regression caused by the fix for Bug#18444: for derived tables we should +# set an empty string as the current database. They do not belong to any +# database and must be usable even if there is no database +# selected. +--disable_warnings +drop table if exists t3| +drop database if exists mysqltest1| +--enable_warnings +create table t3 (a int)| +insert into t3 (a) values (1), (2)| + +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| + +# No current database +select database()| + +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +use test| +drop table t3| + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 8916a5cec6d..8bf8337714f 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -1821,6 +1821,36 @@ SELECT * FROM t1 DROP TABLE t1,t2,t3; +# +# BUG #10308: purge log with subselect +# + +purge master logs before (select adddate(current_timestamp(), interval -4 day)); + + +# +# Bug#18503: Queries with a quantified subquery returning empty set may +# return a wrong result. +# +CREATE TABLE t1 (f1 INT); +CREATE TABLE t2 (f2 INT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE 1=0); +INSERT INTO t2 VALUES (1); +INSERT INTO t2 VALUES (2); +SELECT * FROM t1 WHERE f1 > ALL (SELECT f2 FROM t2 WHERE f2=0); +DROP TABLE t1, t2; + +# +# Bug#16302: Quantified subquery without any tables gives wrong results +# +select 1 from dual where 1 < any (select 2); +select 1 from dual where 1 < all (select 2); +select 1 from dual where 2 > any (select 1); +select 1 from dual where 2 > all (select 1); +select 1 from dual where 1 < any (select 2 from dual); +select 1 from dual where 1 < all (select 2 from dual where 1!=1); # End of 4.1 tests # @@ -2131,3 +2161,81 @@ SELECT t1.i FROM t1 WHERE t1.i = CAST((SELECT MAX(i) FROM t2) AS UNSIGNED); DROP TABLE t1; DROP TABLE t2; + +# +# Bug#20519: subselect with LIMIT M, N +# + +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL auto_increment, + name varchar(255) NOT NULL, + PRIMARY KEY (id) +); +INSERT INTO t1 VALUES + (1, 'Balazs'), (2, 'Joe'), (3, 'Frank'); + +CREATE TABLE t2 ( + id bigint(20) unsigned NOT NULL auto_increment, + mid bigint(20) unsigned NOT NULL, + date date NOT NULL, + PRIMARY KEY (id) +); +INSERT INTO t2 VALUES + (1, 1, '2006-03-30'), (2, 2, '2006-04-06'), (3, 3, '2006-04-13'), + (4, 2, '2006-04-20'), (5, 1, '2006-05-01'); + +SELECT *, + (SELECT date FROM t2 WHERE mid = t1.id + ORDER BY date DESC LIMIT 0, 1) AS date_last, + (SELECT date FROM t2 WHERE mid = t1.id + ORDER BY date DESC LIMIT 3, 1) AS date_next_to_last + FROM t1; +SELECT *, + (SELECT COUNT(*) FROM t2 WHERE mid = t1.id + ORDER BY date DESC LIMIT 1, 1) AS date_count + FROM t1; +SELECT *, + (SELECT date FROM t2 WHERE mid = t1.id + ORDER BY date DESC LIMIT 0, 1) AS date_last, + (SELECT date FROM t2 WHERE mid = t1.id + ORDER BY date DESC LIMIT 1, 1) AS date_next_to_last + FROM t1; +DROP TABLE t1,t2; + +# +# Bug#20869: subselect with range access by DESC +# + +CREATE TABLE t1 ( + i1 int(11) NOT NULL default '0', + i2 int(11) NOT NULL default '0', + t datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (i1,i2,t) +); +INSERT INTO t1 VALUES +(24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'), +(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'), +(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'), +(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'), +(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'), +(24,2,'2005-05-27 12:40:06'); + +CREATE TABLE t2 ( + i1 int(11) NOT NULL default '0', + i2 int(11) NOT NULL default '0', + t datetime default NULL, + PRIMARY KEY (i1) +); +INSERT INTO t2 VALUES (24,1,'2006-06-20 12:29:40'); + +EXPLAIN +SELECT * FROM t1,t2 + WHERE t1.t = (SELECT t1.t FROM t1 + WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 + ORDER BY t1.t DESC LIMIT 1); +SELECT * FROM t1,t2 + WHERE t1.t = (SELECT t1.t FROM t1 + WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1 + ORDER BY t1.t DESC LIMIT 1); + +DROP TABLE t1, t2; diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index f3be08c8537..e0c2493c616 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -1,6 +1,6 @@ --source include/have_udf.inc # -# To run this tests the "sql/udf_example.cc" need to be compiled into +# To run this tests the "sql/udf_example.c" need to be compiled into # udf_example.so and LD_LIBRARY_PATH should be setup to point out where # the library are. # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index a1c1e9b2ad1..88a4d489039 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2595,4 +2595,51 @@ CREATE TABLE t2 SELECT * FROM v1; SELECT * FROM t2; DROP VIEW v1; -DROP TABLE IF EXISTS t1,t2; +DROP TABLE t1,t2; + +# +# Bug#16110: insert permitted into view col w/o default value +# +CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); +CREATE VIEW v1 AS SELECT a, b FROM t1; + +INSERT INTO v1 (b) VALUES (2); + +SET SQL_MODE = STRICT_ALL_TABLES; +--error 1423 +INSERT INTO v1 (b) VALUES (4); +SET SQL_MODE = ''; + +SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug #18243: expression over a view column that with the REVERSE function +# + +CREATE TABLE t1 (firstname text, surname text); +INSERT INTO t1 VALUES + ("Bart","Simpson"),("Milhouse","van Houten"),("Montgomery","Burns"); + +CREATE VIEW v1 AS SELECT CONCAT(firstname," ",surname) AS name FROM t1; +SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), + LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," "))) AS f1 + FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug #19714: wrong type of a view column specified by an expressions over ints +# + +CREATE TABLE t1 (i int, j int); +CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; +DESCRIBE v1; +CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; +DESCRIBE t2; + +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/warnings-master.opt b/mysql-test/t/warnings-master.opt index 3b8baeddb14..e924a22e2ce 100644 --- a/mysql-test/t/warnings-master.opt +++ b/mysql-test/t/warnings-master.opt @@ -1 +1 @@ ---skip-isam +--skip-isam --skip-merge diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 71e847d1d8c..4768c7574e5 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -117,6 +117,10 @@ create table t1 (id int) engine=isam; alter table t1 engine=isam; drop table t1; +create table t1 (id int) engine=merge; +alter table t1 engine=merge; +drop table t1; + # # Test for deprecated TYPE= syntax # diff --git a/mysys/my_bitmap.c b/mysys/my_bitmap.c index 49c23aaeae5..25ff2651e90 100644 --- a/mysys/my_bitmap.c +++ b/mysys/my_bitmap.c @@ -159,7 +159,7 @@ uint bitmap_set_next(MY_BITMAP *map) { uchar *bitmap=map->bitmap; uint bit_found = MY_BIT_NONE; - uint bitmap_size=map->bitmap_size*8; + uint bitmap_size=map->bitmap_size; uint i; DBUG_ASSERT(map->bitmap); @@ -445,7 +445,7 @@ uint bitmap_get_first(const MY_BITMAP *map) { uchar *bitmap=map->bitmap; uint bit_found = MY_BIT_NONE; - uint bitmap_size=map->bitmap_size*8; + uint bitmap_size=map->bitmap_size; uint i; DBUG_ASSERT(map->bitmap); diff --git a/ndb/test/ndbapi/Makefile.am b/ndb/test/ndbapi/Makefile.am index 19d3c4902a8..b639c71d07a 100644 --- a/ndb/test/ndbapi/Makefile.am +++ b/ndb/test/ndbapi/Makefile.am @@ -37,6 +37,10 @@ testBitfield \ DbCreate DbAsyncGenerator \ testSRBank +EXTRA_PROGRAMS = \ + test_event \ + test_event_merge \ + test_event_multi_table #flexTimedAsynch #testBlobs #flex_bench_mysql diff --git a/scripts/make_binary_distribution.sh b/scripts/make_binary_distribution.sh index c344cf3e93a..d9e3ea27895 100644 --- a/scripts/make_binary_distribution.sh +++ b/scripts/make_binary_distribution.sh @@ -96,7 +96,7 @@ mkdir $BASE $BASE/bin $BASE/docs \ if [ $BASE_SYSTEM != "netware" ] ; then mkdir $BASE/share/mysql $BASE/tests $BASE/sql-bench $BASE/man \ - $BASE/man/man1 $BASE/data $BASE/data/mysql $BASE/data/test + $BASE/man/man1 $BASE/man/man8 $BASE/data $BASE/data/mysql $BASE/data/test chmod o-rwx $BASE/data $BASE/data/* fi @@ -219,6 +219,7 @@ if [ $BASE_SYSTEM != "netware" ] ; then fi if [ -d man ] ; then $CP man/*.1 $BASE/man/man1 + $CP man/*.8 $BASE/man/man8 fi fi @@ -309,7 +310,6 @@ else fi # Make safe_mysqld a symlink to mysqld_safe for backwards portability -# To be removed in MySQL 4.1 if [ $BASE_SYSTEM != "netware" ] ; then (cd $BASE/bin ; ln -s mysqld_safe safe_mysqld ) fi diff --git a/scripts/make_win_src_distribution.sh b/scripts/make_win_src_distribution.sh index c677db6a537..d9333540ab8 100644 --- a/scripts/make_win_src_distribution.sh +++ b/scripts/make_win_src_distribution.sh @@ -203,7 +203,7 @@ copy_dir_files() print_debug "Creating directory '$arg'" mkdir $BASE/$arg fi - for i in *.c *.cpp *.h *.ih *.i *.ic *.asm *.def *.hpp *.yy \ + for i in *.c *.cpp *.h *.ih *.i *.ic *.asm *.def *.hpp *.yy *dsp *.dsw \ README INSTALL* LICENSE AUTHORS NEWS ChangeLog \ *.inc *.test *.result *.pem Moscow_leap des_key_file \ *.vcproj *.sln *.dat *.000001 *.require *.opt diff --git a/server-tools/instance-manager/mysqlmanager.vcproj b/server-tools/instance-manager/mysqlmanager.vcproj index bbcb94fa221..d835e242eb1 100644 --- a/server-tools/instance-manager/mysqlmanager.vcproj +++ b/server-tools/instance-manager/mysqlmanager.vcproj @@ -37,7 +37,7 @@ OutputFile="../../client_debug/mysqlmanager.exe" LinkIncremental="2" GenerateDebugInformation="TRUE" - ProgramDatabaseFile="$(OutDir)/mysqlmanager.pdb" + ProgramDatabaseFile="../../client_debug/mysqlmanager.pdb" SubSystem="1" TargetMachine="1"/> <Tool diff --git a/sql/Makefile.am b/sql/Makefile.am index 416f0faf1a6..8428d6401b5 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -116,7 +116,7 @@ DEFS = -DMYSQL_SERVER \ @DEFS@ BUILT_SOURCES = sql_yacc.cc sql_yacc.h lex_hash.h -EXTRA_DIST = udf_example.cc $(BUILT_SOURCES) +EXTRA_DIST = $(BUILT_SOURCES) DISTCLEANFILES = lex_hash.h AM_YFLAGS = -d @@ -155,7 +155,7 @@ lex_hash.h: gen_lex_hash$(EXEEXT) # For testing of udf_example.so noinst_LTLIBRARIES= udf_example.la -udf_example_la_SOURCES= udf_example.cc +udf_example_la_SOURCES= udf_example.c udf_example_la_LDFLAGS= -module -rpath $(pkglibdir) diff --git a/sql/field.cc b/sql/field.cc index 31184a9f08c..510e3bd259f 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1551,104 +1551,6 @@ Field *Field::new_key_field(MEM_ROOT *root, struct st_table *new_table, } -/* - SYNOPSIS - Field::quote_data() - unquoted_string Pointer pointing to the value of a field - - DESCRIPTION - Simple method that passes the field type to the method "type_quote" - To get a true/false value as to whether the value in string1 needs - to be enclosed with quotes. This ensures that values in the final - sql statement to be passed to the remote server will be quoted properly - - RETURN_VALUE - void Immediately - if string doesn't need quote - void Upon prepending/appending quotes on each side of variable - -*/ - -bool Field::quote_data(String *unquoted_string) -{ - char escaped_string[IO_SIZE]; - DBUG_ENTER("Field::quote_data"); - - if (!needs_quotes()) - DBUG_RETURN(0); - - // this is the same call that mysql_real_escape_string() calls - if (escape_string_for_mysql(&my_charset_bin, (char *)escaped_string, - sizeof(escaped_string), unquoted_string->ptr(), - unquoted_string->length()) == (ulong)~0) - DBUG_RETURN(1); - - // reset string, then re-append with quotes and escaped values - unquoted_string->length(0); - if (unquoted_string->append('\'') || - unquoted_string->append((char *)escaped_string) || - unquoted_string->append('\'')) - DBUG_RETURN(1); - DBUG_RETURN(0); -} - - -/* - Quote a field type if needed - - SYNOPSIS - Field::type_quote - - DESCRIPTION - Simple method to give true/false whether a field should be quoted. - Used when constructing INSERT and UPDATE queries to the remote server - see write_row and update_row - - RETURN VALUE - 0 if value is of type NOT needing quotes - 1 if value is of type needing quotes -*/ - -bool Field::needs_quotes(void) -{ - DBUG_ENTER("Field::type_quote"); - - switch (type()) { - //FIX this when kernel is fixed - case MYSQL_TYPE_VARCHAR : - case FIELD_TYPE_STRING : - case FIELD_TYPE_VAR_STRING : - case FIELD_TYPE_YEAR : - case FIELD_TYPE_NEWDATE : - case FIELD_TYPE_TIME : - case FIELD_TYPE_TIMESTAMP : - case FIELD_TYPE_DATE : - case FIELD_TYPE_DATETIME : - case FIELD_TYPE_TINY_BLOB : - case FIELD_TYPE_BLOB : - case FIELD_TYPE_MEDIUM_BLOB : - case FIELD_TYPE_LONG_BLOB : - case FIELD_TYPE_GEOMETRY : - case FIELD_TYPE_BIT: - DBUG_RETURN(1); - - case FIELD_TYPE_DECIMAL : - case FIELD_TYPE_TINY : - case FIELD_TYPE_SHORT : - case FIELD_TYPE_INT24 : - case FIELD_TYPE_LONG : - case FIELD_TYPE_FLOAT : - case FIELD_TYPE_DOUBLE : - case FIELD_TYPE_LONGLONG : - case FIELD_TYPE_NULL : - case FIELD_TYPE_SET : - case FIELD_TYPE_ENUM : - DBUG_RETURN(0); - default: - DBUG_RETURN(0); - } -} - - /**************************************************************************** Field_null, a field that always return NULL ****************************************************************************/ @@ -4704,7 +4606,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) thd->time_zone_used= 1; temp= time_tmp.year % 100; - if (temp < YY_PART_YEAR) + if (temp < YY_PART_YEAR - 1) { *to++= '2'; *to++= '0'; diff --git a/sql/field.h b/sql/field.h index 3b33d3651e3..09638b9a979 100644 --- a/sql/field.h +++ b/sql/field.h @@ -251,8 +251,6 @@ public: ptr= old_ptr; return str; } - bool quote_data(String *unquoted_string); - bool needs_quotes(void); virtual bool send_binary(Protocol *protocol); virtual char *pack(char* to, const char *from, uint max_length=~(uint) 0) { diff --git a/sql/ha_federated.cc b/sql/ha_federated.cc index e2988df1619..2267c2b5d79 100644 --- a/sql/ha_federated.cc +++ b/sql/ha_federated.cc @@ -124,11 +124,6 @@ ha_federated::write_row - <for every field/column> - Field::quote_data - Field::quote_data - </for every field/column> - ha_federated::reset (UPDATE) @@ -138,20 +133,10 @@ ha_federated::index_init ha_federated::index_read ha_federated::index_read_idx - Field::quote_data ha_federated::rnd_next ha_federated::convert_row_to_internal_format ha_federated::update_row - <quote 3 cols, new and old data> - Field::quote_data - Field::quote_data - Field::quote_data - Field::quote_data - Field::quote_data - Field::quote_data - </quote 3 cols, new and old data> - ha_federated::extra ha_federated::extra ha_federated::extra @@ -1151,7 +1136,7 @@ bool ha_federated::create_where_from_key(String *to, Field *field= key_part->field; uint store_length= key_part->store_length; uint part_length= min(store_length, length); - needs_quotes= field->needs_quotes(); + needs_quotes= 1; DBUG_DUMP("key, start of loop", (char *) ptr, length); if (key_part->null_bit) @@ -1559,10 +1544,6 @@ inline uint field_in_record_is_null(TABLE *table, int ha_federated::write_row(byte *buf) { - bool has_fields= FALSE; - uint all_fields_have_same_query_id= 1; - ulong current_query_id= 1; - ulong tmp_query_id= 1; char insert_buffer[FEDERATED_QUERY_BUFFER_SIZE]; char values_buffer[FEDERATED_QUERY_BUFFER_SIZE]; char insert_field_value_buffer[STRING_BUFFER_USUAL_SIZE]; @@ -1580,24 +1561,12 @@ int ha_federated::write_row(byte *buf) insert_string.length(0); insert_field_value_string.length(0); DBUG_ENTER("ha_federated::write_row"); - DBUG_PRINT("info", - ("table charset name %s csname %s", - table->s->table_charset->name, - table->s->table_charset->csname)); statistic_increment(table->in_use->status_var.ha_write_count, &LOCK_status); if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT) table->timestamp_field->set_time(); /* - get the current query id - the fields that we add to the insert - statement to send to the foreign will not be appended unless they match - this query id - */ - current_query_id= table->in_use->query_id; - DBUG_PRINT("info", ("current query id %d", current_query_id)); - - /* start both our field and field values strings */ insert_string.append(FEDERATED_INSERT); @@ -1610,63 +1579,39 @@ int ha_federated::write_row(byte *buf) values_string.append(FEDERATED_OPENPAREN); /* - Even if one field is different, all_fields_same_query_id can't remain - 0 if it remains 0, then that means no fields were specified in the query - such as in the case of INSERT INTO table VALUES (val1, val2, valN) - - */ - for (field= table->field; *field; field++) - { - if (field > table->field && tmp_query_id != (*field)->query_id) - all_fields_have_same_query_id= 0; - - tmp_query_id= (*field)->query_id; - } - /* loop through the field pointer array, add any fields to both the values list and the fields list that match the current query id - - You might ask "Why an index variable (has_fields) ?" My answer is that - we need to count how many fields we actually need */ for (field= table->field; *field; field++) { - /* if there is a query id and if it's equal to the current query id */ - if (((*field)->query_id && (*field)->query_id == current_query_id) - || all_fields_have_same_query_id) + if ((*field)->is_null()) + insert_field_value_string.append(FEDERATED_NULL); + else { - /* - There are some fields. This will be used later to determine - whether to chop off commas and parens. - */ - has_fields= TRUE; - - if ((*field)->is_null()) - insert_field_value_string.append(FEDERATED_NULL); - else - { - (*field)->val_str(&insert_field_value_string); - /* quote these fields if they require it */ - (*field)->quote_data(&insert_field_value_string); - } - /* append the field name */ - insert_string.append((*field)->field_name); + (*field)->val_str(&insert_field_value_string); + values_string.append('\''); + insert_field_value_string.print(&values_string); + values_string.append('\''); - /* append the value */ - values_string.append(insert_field_value_string); insert_field_value_string.length(0); - - /* append commas between both fields and fieldnames */ - /* - unfortunately, we can't use the logic - if *(fields + 1) to make the following - appends conditional because we may not append - if the next field doesn't match the condition: - (((*field)->query_id && (*field)->query_id == current_query_id) - */ - insert_string.append(FEDERATED_COMMA); - values_string.append(FEDERATED_COMMA); } + /* append the field name */ + insert_string.append((*field)->field_name); + + /* append the value */ + values_string.append(insert_field_value_string); + insert_field_value_string.length(0); + + /* append commas between both fields and fieldnames */ + /* + unfortunately, we can't use the logic + if *(fields + 1) to make the following + appends conditional because we may not append + if the next field doesn't match the condition: + (((*field)->query_id && (*field)->query_id == current_query_id) + */ + insert_string.append(FEDERATED_COMMA); + values_string.append(FEDERATED_COMMA); } /* @@ -1678,7 +1623,7 @@ int ha_federated::write_row(byte *buf) AND, we don't want to chop off the last char '(' insert will be "INSERT INTO t1 VALUES ();" */ - if (has_fields) + if (table->s->fields) { /* chops off leading commas */ values_string.length(values_string.length() - strlen(FEDERATED_COMMA)); @@ -1861,8 +1806,9 @@ int ha_federated::update_row(const byte *old_data, byte *new_data) { /* otherwise = */ (*field)->val_str(&field_value); - (*field)->quote_data(&field_value); - update_string.append(field_value); + update_string.append('\''); + field_value.print(&update_string); + update_string.append('\''); field_value.length(0); } @@ -1873,8 +1819,9 @@ int ha_federated::update_row(const byte *old_data, byte *new_data) where_string.append(FEDERATED_EQ); (*field)->val_str(&field_value, (char*) (old_data + (*field)->offset())); - (*field)->quote_data(&field_value); - where_string.append(field_value); + where_string.append('\''); + field_value.print(&where_string); + where_string.append('\''); field_value.length(0); } @@ -1944,17 +1891,17 @@ int ha_federated::delete_row(const byte *buf) if (cur_field->is_null()) { - delete_string.append(FEDERATED_IS); - data_string.append(FEDERATED_NULL); + delete_string.append(FEDERATED_ISNULL); } else { delete_string.append(FEDERATED_EQ); cur_field->val_str(&data_string); - cur_field->quote_data(&data_string); + delete_string.append('\''); + data_string.print(&delete_string); + delete_string.append('\''); } - delete_string.append(data_string); delete_string.append(FEDERATED_AND); } delete_string.length(delete_string.length()-5); // Remove trailing AND diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc index 9780f163634..0b6e05fcbd4 100644 --- a/sql/ha_myisammrg.cc +++ b/sql/ha_myisammrg.cc @@ -132,6 +132,10 @@ int ha_myisammrg::close(void) int ha_myisammrg::write_row(byte * buf) { statistic_increment(table->in_use->status_var.ha_write_count,&LOCK_status); + + if (file->merge_insert_method == MERGE_INSERT_DISABLED || !file->tables) + return (HA_ERR_TABLE_READONLY); + if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT) table->timestamp_field->set_time(); if (table->next_number_field && buf == table->record[0]) diff --git a/sql/handler.cc b/sql/handler.cc index 9c03a9ef88f..b0051b02d91 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -302,7 +302,9 @@ handler *get_new_handler(TABLE *table, MEM_ROOT *alloc, enum db_type db_type) #endif case DB_TYPE_MRG_MYISAM: case DB_TYPE_MRG_ISAM: - return new (alloc) ha_myisammrg(table); + if (have_merge_db == SHOW_OPTION_YES) + return new (alloc) ha_myisammrg(table); + return NULL; #ifdef HAVE_BERKELEY_DB case DB_TYPE_BERKELEY_DB: if (have_berkeley_db == SHOW_OPTION_YES) @@ -426,6 +428,7 @@ static int ha_init_errors(void) SETMSG(HA_ERR_NO_CONNECTION, "Could not connect to storage engine"); SETMSG(HA_ERR_TABLE_DEF_CHANGED, ER(ER_TABLE_DEF_CHANGED)); SETMSG(HA_ERR_TABLE_NEEDS_UPGRADE, ER(ER_TABLE_NEEDS_UPGRADE)); + SETMSG(HA_ERR_TABLE_READONLY, ER(ER_OPEN_AS_READONLY)); /* Register the error messages for use with my_error(). */ return my_error_register(errmsgs, HA_ERR_FIRST, HA_ERR_LAST); @@ -1858,6 +1861,9 @@ void handler::print_error(int error, myf errflag) case HA_ERR_TABLE_NEEDS_UPGRADE: textno=ER_TABLE_NEEDS_UPGRADE; break; + case HA_ERR_TABLE_READONLY: + textno= ER_OPEN_AS_READONLY; + break; default: { /* The error was "unknown" to this function. diff --git a/sql/item.cc b/sql/item.cc index 511ea1ffb44..ad8b79182d4 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -573,6 +573,7 @@ void Item::set_name(const char *str, uint length, CHARSET_INFO *cs) } if (cs->ctype) { + uint orig_len= length; /* This will probably need a better implementation in the future: a function in CHARSET_INFO structure. @@ -582,6 +583,11 @@ void Item::set_name(const char *str, uint length, CHARSET_INFO *cs) length--; str++; } + if (orig_len != length && !is_autogenerated_name) + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_REMOVED_SPACES, ER(ER_REMOVED_SPACES), + str + length - orig_len); + } if (!my_charset_same(cs, system_charset_info)) { diff --git a/sql/item.h b/sql/item.h index b7b9f972f67..0f49145082f 100644 --- a/sql/item.h +++ b/sql/item.h @@ -697,9 +697,16 @@ public: Any new item which can be NULL must implement this call. */ virtual bool is_null() { return 0; } + /* - it is "top level" item of WHERE clause and we do not need correct NULL - handling + Inform the item that there will be no distinction between its result + being FALSE or NULL. + + NOTE + This function will be called for eg. Items that are top-level AND-parts + of the WHERE clause. Items implementing this function (currently + Item_cond_and and subquery-related item) enable special optimizations + when they are "top level". */ virtual void top_level_item() {} /* diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index a524e4e3056..c63a89351c6 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -752,44 +752,47 @@ String *Item_func_reverse::val_str(String *str) { DBUG_ASSERT(fixed == 1); String *res = args[0]->val_str(str); - char *ptr,*end; + char *ptr, *end, *tmp; if ((null_value=args[0]->null_value)) return 0; /* An empty string is a special case as the string pointer may be null */ if (!res->length()) return &my_empty_string; - res=copy_if_not_alloced(str,res,res->length()); - ptr = (char *) res->ptr(); - end=ptr+res->length(); + if (tmp_value.alloced_length() < res->length() && + tmp_value.realloc(res->length())) + { + null_value= 1; + return 0; + } + tmp_value.length(res->length()); + tmp_value.set_charset(res->charset()); + ptr= (char *) res->ptr(); + end= ptr + res->length(); + tmp= (char *) tmp_value.ptr() + tmp_value.length(); #ifdef USE_MB if (use_mb(res->charset())) { - String tmpstr; - tmpstr.copy(*res); - char *tmp = (char *) tmpstr.ptr() + tmpstr.length(); register uint32 l; while (ptr < end) { - if ((l=my_ismbchar(res->charset(), ptr,end))) - tmp-=l, memcpy(tmp,ptr,l), ptr+=l; + if ((l= my_ismbchar(res->charset(),ptr,end))) + { + tmp-= l; + memcpy(tmp,ptr,l); + ptr+= l; + } else - *--tmp=*ptr++; + *--tmp= *ptr++; } - memcpy((char *) res->ptr(),(char *) tmpstr.ptr(), res->length()); } else #endif /* USE_MB */ { - char tmp; while (ptr < end) - { - tmp=*ptr; - *ptr++=*--end; - *end=tmp; - } + *--tmp= *ptr++; } - return res; + return &tmp_value; } diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 13d32765c21..7abbe232249 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -102,6 +102,7 @@ public: class Item_func_reverse :public Item_str_func { + String tmp_value; public: Item_func_reverse(Item *a) :Item_str_func(a) {} String *val_str(String *); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 318d3fcaad5..5ca2b602c25 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -802,7 +802,8 @@ Item_in_subselect::single_value_transformer(JOIN *join, if (!select_lex->group_list.elements && !select_lex->having && !select_lex->with_sum_func && - !(select_lex->next_select())) + !(select_lex->next_select()) && + select_lex->table_list.elements) { Item_sum_hybrid *item; nesting_map save_allow_sum_func; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index c9ae743addd..089bc965c8c 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1323,6 +1323,10 @@ extern handlerton ndbcluster_hton; extern SHOW_COMP_OPTION have_ndbcluster; #endif +/* MRG_MYISAM handler is always built, but may be skipped */ +extern handlerton myisammrg_hton; +#define have_merge_db myisammrg_hton.state + extern SHOW_COMP_OPTION have_isam; extern SHOW_COMP_OPTION have_raid, have_openssl, have_symlink, have_dlopen; extern SHOW_COMP_OPTION have_query_cache; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index b5b6ac1449f..57ee7971c8d 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -300,7 +300,7 @@ static bool lower_case_table_names_used= 0; static bool volatile select_thread_in_use, signal_thread_in_use; static bool volatile ready_to_exit; static my_bool opt_debugging= 0, opt_external_locking= 0, opt_console= 0; -static my_bool opt_bdb, opt_isam, opt_ndbcluster; +static my_bool opt_bdb, opt_isam, opt_ndbcluster, opt_merge; static my_bool opt_short_log_format= 0; static uint kill_cached_threads, wake_thread; static ulong killed_threads, thread_created; @@ -4653,7 +4653,8 @@ enum options_mysqld OPT_OLD_STYLE_USER_LIMITS, OPT_LOG_SLOW_ADMIN_STATEMENTS, OPT_TABLE_LOCK_WAIT_TIMEOUT, - OPT_PORT_OPEN_TIMEOUT + OPT_PORT_OPEN_TIMEOUT, + OPT_MERGE }; @@ -5106,6 +5107,9 @@ master-ssl", #endif /* HAVE_REPLICATION */ {"memlock", OPT_MEMLOCK, "Lock mysqld in memory.", (gptr*) &locked_in_memory, (gptr*) &locked_in_memory, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"merge", OPT_MERGE, "Enable Merge storage engine. Disable with \ +--skip-merge.", + (gptr*) &opt_merge, (gptr*) &opt_merge, 0, GET_BOOL, NO_ARG, 1, 0, 0, 0, 0}, {"myisam-recover", OPT_MYISAM_RECOVER, "Syntax: myisam-recover[=option[,option...]], where option can be DEFAULT, BACKUP, FORCE or QUICK.", (gptr*) &myisam_recover_options_str, (gptr*) &myisam_recover_options_str, 0, @@ -6888,6 +6892,11 @@ get_one_option(int optid, const struct my_option *opt __attribute__((unused)), global_system_variables.tx_isolation= (type-1); break; } + case OPT_MERGE: + if (opt_merge) + have_merge_db= SHOW_OPTION_YES; + else + have_merge_db= SHOW_OPTION_DISABLED; #ifdef HAVE_BERKELEY_DB case OPT_BDB_NOSYNC: /* Deprecated option */ diff --git a/sql/opt_range.h b/sql/opt_range.h index cdb00ea7d0c..9474f2d469f 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -668,7 +668,7 @@ private: #ifdef NOT_USED bool test_if_null_range(QUICK_RANGE *range, uint used_key_parts); #endif - int reset(void) { next=0; rev_it.rewind(); return 0; } + int reset(void) { rev_it.rewind(); return QUICK_RANGE_SELECT::reset(); } List<QUICK_RANGE> rev_ranges; List_iterator<QUICK_RANGE> rev_it; }; diff --git a/sql/set_var.cc b/sql/set_var.cc index 8e3fda0a164..9f3886bca4d 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -870,6 +870,7 @@ struct show_var_st init_vars[]= { {"have_innodb", (char*) &have_innodb, SHOW_HAVE}, {"have_isam", (char*) &have_isam, SHOW_HAVE}, {"have_ndbcluster", (char*) &have_ndbcluster, SHOW_HAVE}, + {"have_merge_engine", (char*) &have_merge_db, SHOW_HAVE}, {"have_openssl", (char*) &have_openssl, SHOW_HAVE}, {"have_query_cache", (char*) &have_query_cache, SHOW_HAVE}, {"have_raid", (char*) &have_raid, SHOW_HAVE}, diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index 9b20c37ece2..5c967ba19bd 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5621,3 +5621,5 @@ ER_TABLE_CANT_HANDLE_SPKEYS eng "The used table type doesn't support SPATIAL indexes" ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA eng "Triggers can not be created on system tables" +ER_REMOVED_SPACES + eng "Leading spaces are removed from name '%s'" diff --git a/sql/slave.cc b/sql/slave.cc index b284f4a6a16..90e95e812bd 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -3717,15 +3717,12 @@ err: write_ignored_events_info_to_relay_log(thd, mi); thd->proc_info = "Waiting for slave mutex on exit"; pthread_mutex_lock(&mi->run_lock); - mi->slave_running = 0; - mi->io_thd = 0; /* Forget the relay log's format */ delete mi->rli.relay_log.description_event_for_queue; mi->rli.relay_log.description_event_for_queue= 0; // TODO: make rpl_status part of MASTER_INFO change_rpl_status(RPL_ACTIVE_SLAVE,RPL_IDLE_SLAVE); - mi->abort_slave = 0; // TODO: check if this is needed DBUG_ASSERT(thd->net.buff != 0); net_end(&thd->net); // destructor will not free it, because net.vio is 0 close_thread_tables(thd, 0); @@ -3733,8 +3730,11 @@ err: THD_CHECK_SENTRY(thd); delete thd; pthread_mutex_unlock(&LOCK_thread_count); - pthread_cond_broadcast(&mi->stop_cond); // tell the world we are done + mi->abort_slave= 0; + mi->slave_running= 0; + mi->io_thd= 0; pthread_mutex_unlock(&mi->run_lock); + pthread_cond_broadcast(&mi->stop_cond); // tell the world we are done #ifndef DBUG_OFF if (abort_slave_event_count && !events_till_abort) goto slave_begin; diff --git a/sql/sp.cc b/sql/sp.cc index 553465ebff8..a7078da2f50 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1846,7 +1846,6 @@ sp_use_new_db(THD *thd, LEX_STRING new_db, LEX_STRING *old_db, bool no_access_check, bool *dbchangedp) { int ret; - static char empty_c_string[1]= {0}; /* used for not defined db */ DBUG_ENTER("sp_use_new_db"); DBUG_PRINT("enter", ("newdb: %s", new_db.str)); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 5c8bd797e7c..4cb9cfc53c6 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -43,6 +43,7 @@ table name */ char internal_table_name[2]= "*"; +char empty_c_string[1]= {0}; /* used for not defined db */ const char * const THD::DEFAULT_WHERE= "field list"; @@ -269,6 +270,7 @@ THD::THD() tablespace_op=FALSE; ulong tmp=sql_rnd_with_mutex(); randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id); + substitute_null_with_insert_id = FALSE; thr_lock_info_init(&lock_info); /* safety: will be reset after start */ thr_lock_owner_init(&main_lock_id, &lock_info); } diff --git a/sql/sql_class.h b/sql/sql_class.h index 9adfc4ec31e..53a95a89b51 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -41,6 +41,7 @@ enum enum_check_fields { CHECK_FIELD_IGNORE, CHECK_FIELD_WARN, CHECK_FIELD_ERROR_FOR_NULL }; extern char internal_table_name[2]; +extern char empty_c_string[1]; extern const char **errmesg; #define TC_LOG_PAGE_SIZE 8192 @@ -1324,6 +1325,8 @@ public: bool no_errors, password, is_fatal_error; bool query_start_used, rand_used, time_zone_used; bool last_insert_id_used,insert_id_used, clear_next_insert_id; + /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */ + bool substitute_null_with_insert_id; bool in_lock_tables; bool query_error, bootstrap, cleanup_done; bool tmp_table_used; @@ -1455,6 +1458,7 @@ public: { last_insert_id= id_arg; insert_id_used=1; + substitute_null_with_insert_id= TRUE; } inline ulonglong insert_id(void) { @@ -1980,11 +1984,21 @@ public: { db.str=0; } + /* + This constructor is used only for the case when we create a derived + table. A derived table has no name and doesn't belong to any database. + Later, if there was an alias specified for the table, it will be set + by add_table_to_list. + */ inline Table_ident(SELECT_LEX_UNIT *s) : sel(s) { /* 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; + db.str= empty_c_string; /* a subject to casedn_str */ + db.length= 0; + table.str= internal_table_name; + table.length=1; } + bool is_derived_table() const { return test(sel); } inline void change_db(char *db_name) { db.str= db_name; db.length= (uint) strlen(db_name); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index eaa7d3a72db..57805da608b 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -755,6 +755,7 @@ static bool check_view_insertability(THD * thd, TABLE_LIST *view) uint used_fields_buff_size= (table->s->fields + 7) / 8; uchar *used_fields_buff= (uchar*)thd->alloc(used_fields_buff_size); MY_BITMAP used_fields; + bool save_set_query_id= thd->set_query_id; DBUG_ENTER("check_key_in_view"); if (!used_fields_buff) @@ -767,15 +768,26 @@ static bool check_view_insertability(THD * thd, TABLE_LIST *view) bitmap_clear_all(&used_fields); view->contain_auto_increment= 0; + /* + we must not set query_id for fields as they're not + really used in this context + */ + thd->set_query_id= 0; /* check simplicity and prepare unique test of view */ for (trans= trans_start; trans != trans_end; trans++) { if (!trans->item->fixed && trans->item->fix_fields(thd, &trans->item)) - return TRUE; + { + thd->set_query_id= save_set_query_id; + DBUG_RETURN(TRUE); + } Item_field *field; /* simple SELECT list entry (field without expression) */ if (!(field= trans->item->filed_for_view_update())) + { + thd->set_query_id= save_set_query_id; DBUG_RETURN(TRUE); + } if (field->field->unireg_check == Field::NEXT_NUMBER) view->contain_auto_increment= 1; /* prepare unique test */ @@ -785,6 +797,7 @@ static bool check_view_insertability(THD * thd, TABLE_LIST *view) */ trans->item= field; } + thd->set_query_id= save_set_query_id; /* unique test */ for (trans= trans_start; trans != trans_end; trans++) { @@ -823,11 +836,18 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, bool insert_into_view= (table_list->view != 0); DBUG_ENTER("mysql_prepare_insert_check_table"); + /* + first table in list is the one we'll INSERT into, requires INSERT_ACL. + all others require SELECT_ACL only. the ACL requirement below is for + new leaves only anyway (view-constituents), so check for SELECT rather + than INSERT. + */ + if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context, &thd->lex->select_lex.top_join_list, table_list, where, &thd->lex->select_lex.leaf_tables, - select_insert, INSERT_ACL)) + select_insert, SELECT_ACL)) DBUG_RETURN(TRUE); if (insert_into_view && !fields.elements) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index e37bc7cc3f7..28ed3e25d57 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6118,8 +6118,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, 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 (check_table_name(table->table.str, table->table.length)) { my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str); DBUG_RETURN(0); @@ -6140,6 +6139,11 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, DBUG_RETURN(0); /* purecov: inspected */ if (table->db.str) { + if (table->is_derived_table() == FALSE && check_db_name(table->db.str)) + { + my_error(ER_WRONG_DB_NAME, MYF(0), table->db.str); + DBUG_RETURN(0); + } ptr->db= table->db.str; ptr->db_length= table->db.length; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 400342854a6..20512563f37 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1209,6 +1209,11 @@ int JOIN::reinit() { DBUG_ENTER("JOIN::reinit"); + + unit->offset_limit_cnt= (ha_rows)(select_lex->offset_limit ? + select_lex->offset_limit->val_uint() : + ULL(0)); + first_record= 0; if (exec_tmp_table1) @@ -4126,14 +4131,14 @@ greedy_search(JOIN *join, double read_time= 0.0; uint idx= join->const_tables; // index into 'join->best_ref' uint best_idx; - uint rem_size; // cardinality of remaining_tables + uint size_remain; // cardinality of remaining_tables POSITION best_pos; JOIN_TAB *best_table; // the next plan node to be added to the curr QEP DBUG_ENTER("greedy_search"); /* number of tables that remain to be optimized */ - rem_size= my_count_bits(remaining_tables); + size_remain= my_count_bits(remaining_tables); do { /* Find the extension of the current QEP with the lowest cost */ @@ -4141,7 +4146,7 @@ greedy_search(JOIN *join, best_extension_by_limited_search(join, remaining_tables, idx, record_count, read_time, search_depth, prune_level); - if (rem_size <= search_depth) + if (size_remain <= search_depth) { /* 'join->best_positions' contains a complete optimal extension of the @@ -4177,7 +4182,7 @@ greedy_search(JOIN *join, read_time+= join->positions[idx].read_time; remaining_tables&= ~(best_table->table->map); - --rem_size; + --size_remain; ++idx; DBUG_EXECUTE("opt", print_plan(join, join->tables, @@ -5482,6 +5487,7 @@ make_join_readinfo(JOIN *join, uint options) uint i; bool statistics= test(!(join->select_options & SELECT_DESCRIBE)); + bool ordered_set= 0; DBUG_ENTER("make_join_readinfo"); for (i=join->const_tables ; i < join->tables ; i++) @@ -5491,6 +5497,22 @@ make_join_readinfo(JOIN *join, uint options) tab->read_record.table= table; tab->read_record.file=table->file; tab->next_select=sub_select; /* normal select */ + + /* + Determine if the set is already ordered for ORDER BY, so it can + disable join cache because it will change the ordering of the results. + Code handles sort table that is at any location (not only first after + the const tables) despite the fact that it's currently prohibited. + */ + if (!ordered_set && + (table == join->sort_by_table && + (!join->order || join->skip_sort_order || + test_if_skip_sort_order(tab, join->order, join->select_limit, + 1)) + ) || + (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)) + ordered_set= 1; + switch (tab->type) { case JT_SYSTEM: // Only happens with left join table->status=STATUS_NO_RECORD; @@ -5561,10 +5583,11 @@ make_join_readinfo(JOIN *join, uint options) case JT_ALL: /* If previous table use cache + If the incoming data set is already sorted don't use cache. */ table->status=STATUS_NO_RECORD; if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) && - tab->use_quick != 2 && !tab->first_inner) + tab->use_quick != 2 && !tab->first_inner && !ordered_set) { if ((options & SELECT_DESCRIBE) || !join_init_cache(join->thd,join->join_tab+join->const_tables, @@ -7854,7 +7877,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) Field *field=((Item_field*) args[0])->field; if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null && (thd->options & OPTION_AUTO_IS_NULL) && - thd->insert_id()) + thd->insert_id() && thd->substitute_null_with_insert_id) { #ifdef HAVE_QUERY_CACHE query_cache_abort(&thd->net); @@ -7873,7 +7896,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value) */ cond->fix_fields(thd, &cond); } - thd->insert_id(0); // Clear for next request + thd->substitute_null_with_insert_id= FALSE; // Clear for next request } /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */ else if (((field->type() == FIELD_TYPE_DATE) || @@ -8074,8 +8097,13 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table, item->name, table, item->decimals); break; case INT_RESULT: - new_field=new Field_longlong(item->max_length, maybe_null, - item->name, table, item->unsigned_flag); + /* Select an integer type with the minimal fit precision */ + if (item->max_length > 11) + new_field=new Field_longlong(item->max_length, maybe_null, + item->name, table, item->unsigned_flag); + else + new_field=new Field_long(item->max_length, maybe_null, + item->name, table, item->unsigned_flag); break; case STRING_RESULT: DBUG_ASSERT(item->collation.collation); @@ -8414,13 +8442,15 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, param->group_length : 0, NullS)) { - bitmap_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_clear_bit(&temp_pool, temp_pool_slot); DBUG_RETURN(NULL); /* purecov: inspected */ } /* Copy_field belongs to TMP_TABLE_PARAM, allocate it in THD mem_root */ if (!(param->copy_field= copy= new (thd->mem_root) Copy_field[field_count])) { - bitmap_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_clear_bit(&temp_pool, temp_pool_slot); free_root(&own_root, MYF(0)); /* purecov: inspected */ DBUG_RETURN(NULL); /* purecov: inspected */ } @@ -8944,7 +8974,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, err: thd->mem_root= mem_root_save; free_tmp_table(thd,table); /* purecov: inspected */ - bitmap_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_clear_bit(&temp_pool, temp_pool_slot); DBUG_RETURN(NULL); /* purecov: inspected */ } @@ -9232,7 +9263,8 @@ free_tmp_table(THD *thd, TABLE *entry) (*ptr)->free(); free_io_cache(entry); - bitmap_clear_bit(&temp_pool, entry->temp_pool_slot); + if (entry->temp_pool_slot != MY_BIT_NONE) + bitmap_clear_bit(&temp_pool, entry->temp_pool_slot); free_root(&own_root, MYF(0)); /* the table is allocated in its own root */ thd->proc_info=save_proc_info; @@ -10608,8 +10640,13 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), { if (!join->first_record) { + List_iterator_fast<Item> it(*join->fields); + Item *item; /* No matching rows for group function */ join->clear(); + + while ((item= it++)) + item->no_rows_in_result(); } if (join->having && join->having->val_int() == 0) error= -1; // Didn't satisfy having diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a730e2533b1..6d560d3d250 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4060,8 +4060,8 @@ select_item: YYABORT; if ($4.str) { - $2->set_name($4.str, $4.length, system_charset_info); $2->is_autogenerated_name= FALSE; + $2->set_name($4.str, $4.length, system_charset_info); } else if (!$2->name) { char *str = $1; @@ -4936,8 +4936,8 @@ udf_expr: { if ($4.str) { - $2->set_name($4.str, $4.length, system_charset_info); $2->is_autogenerated_name= FALSE; + $2->set_name($4.str, $4.length, system_charset_info); } else $2->set_name($1, (uint) ($3 - $1), YYTHD->charset()); diff --git a/sql/udf_example.cc b/sql/udf_example.c index 6ad066eacc2..62995085599 100644 --- a/sql/udf_example.cc +++ b/sql/udf_example.c @@ -127,7 +127,7 @@ typedef long long longlong; #else #include <my_global.h> #include <my_sys.h> -#include <m_string.h> // To get strmov() +#include <m_string.h> /* To get strmov() */ #endif #include <mysql.h> #include <ctype.h> @@ -138,7 +138,6 @@ static pthread_mutex_t LOCK_hostname; /* These must be right or mysqld will not find the symbol! */ -extern "C" { my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void metaphon_deinit(UDF_INIT *initid); char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, @@ -159,7 +158,6 @@ void avgcost_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error void avgcost_clear( UDF_INIT* initid, char* is_null, char *error ); void avgcost_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ); double avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ); -} /************************************************************************* @@ -221,7 +219,7 @@ my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message) ****************************************************************************/ -void metaphon_deinit(UDF_INIT *initid) +void metaphon_deinit(UDF_INIT *initid __attribute__((unused))) { } @@ -267,23 +265,25 @@ static char codes[26] = { #define NOGHTOF(x) (codes[(x) - 'A'] & 16) /* BDH */ -char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *length, char *is_null, char *error) +char *metaphon(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args, char *result, unsigned long *length, + char *is_null, char *error __attribute__((unused))) { const char *word=args->args[0]; - if (!word) // Null argument + const char *w_end; + char *org_result; + char *n, *n_start, *n_end; /* pointers to string */ + char *metaph_end; /* pointers to end of metaph */ + char ntrans[32]; /* word with uppercase letters */ + int KSflag; /* state flag for X to KS */ + + if (!word) /* Null argument */ { *is_null=1; return 0; } - const char *w_end=word+args->lengths[0]; - char *org_result=result; - - char *n, *n_start, *n_end; /* pointers to string */ - char *metaph, *metaph_end; /* pointers to metaph */ - char ntrans[32]; /* word with uppercase letters */ - char newm[8]; /* new metaph for comparison */ - int KSflag; /* state flag for X to KS */ + w_end=word+args->lengths[0]; + org_result=result; /*-------------------------------------------------------- * Copy word to internal buffer, dropping non-alphabetic @@ -519,6 +519,8 @@ char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, my_bool myfunc_double_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { + uint i; + if (!args->arg_count) { strcpy(message,"myfunc_double must have at least one argument"); @@ -528,27 +530,28 @@ my_bool myfunc_double_init(UDF_INIT *initid, UDF_ARGS *args, char *message) ** As this function wants to have everything as strings, force all arguments ** to strings. */ - for (uint i=0 ; i < args->arg_count; i++) + for (i=0 ; i < args->arg_count; i++) args->arg_type[i]=STRING_RESULT; - initid->maybe_null=1; // The result may be null - initid->decimals=2; // We want 2 decimals in the result - initid->max_length=6; // 3 digits + . + 2 decimals + initid->maybe_null=1; /* The result may be null */ + initid->decimals=2; /* We want 2 decimals in the result */ + initid->max_length=6; /* 3 digits + . + 2 decimals */ return 0; } -double myfunc_double(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +double myfunc_double(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null, char *error __attribute__((unused))) { unsigned long val = 0; unsigned long v = 0; + uint i, j; - for (uint i = 0; i < args->arg_count; i++) + for (i = 0; i < args->arg_count; i++) { if (args->args[i] == NULL) continue; val += args->lengths[i]; - for (uint j=args->lengths[i] ; j-- > 0 ;) + for (j=args->lengths[i] ; j-- > 0 ;) v += args->args[i][j]; } if (val) @@ -575,22 +578,25 @@ double myfunc_double(UDF_INIT *initid, UDF_ARGS *args, char *is_null, /* This function returns the sum of all arguments */ -longlong myfunc_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +longlong myfunc_int(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null __attribute__((unused)), + char *error __attribute__((unused))) { longlong val = 0; - for (uint i = 0; i < args->arg_count; i++) + uint i; + + for (i = 0; i < args->arg_count; i++) { if (args->args[i] == NULL) continue; switch (args->arg_type[i]) { - case STRING_RESULT: // Add string lengths + case STRING_RESULT: /* Add string lengths */ val += args->lengths[i]; break; - case INT_RESULT: // Add numbers + case INT_RESULT: /* Add numbers */ val += *((longlong*) args->args[i]); break; - case REAL_RESULT: // Add numers as longlong + case REAL_RESULT: /* Add numers as longlong */ val += (longlong) *((double*) args->args[i]); break; default: @@ -604,7 +610,9 @@ longlong myfunc_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null, At least one of _init/_deinit is needed unless the server is started with --allow_suspicious_udfs. */ -my_bool myfunc_int_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +my_bool myfunc_int_init(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args __attribute__((unused)), + char *message __attribute__((unused))) { return 0; } @@ -622,7 +630,7 @@ my_bool sequence_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 1; } if (args->arg_count) - args->arg_type[0]= INT_RESULT; // Force argument to int + args->arg_type[0]= INT_RESULT; /* Force argument to int */ if (!(initid->ptr=(char*) malloc(sizeof(longlong)))) { @@ -646,8 +654,9 @@ void sequence_deinit(UDF_INIT *initid) free(initid->ptr); } -longlong sequence(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +longlong sequence(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null __attribute__((unused)), + char *error __attribute__((unused))) { ulonglong val=0; if (args->arg_count) @@ -670,7 +679,6 @@ longlong sequence(UDF_INIT *initid, UDF_ARGS *args, char *is_null, #include <arpa/inet.h> #include <netdb.h> -extern "C" { my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void lookup_deinit(UDF_INIT *initid); char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, @@ -679,7 +687,6 @@ my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void reverse_lookup_deinit(UDF_INIT *initid); char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *null_value, char *error); -} /**************************************************************************** @@ -705,20 +712,26 @@ my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 0; } -void lookup_deinit(UDF_INIT *initid) +void lookup_deinit(UDF_INIT *initid __attribute__((unused))) { #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST) (void) pthread_mutex_destroy(&LOCK_hostname); #endif } -char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *res_length, char *null_value, char *error) +char *lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *result, unsigned long *res_length, char *null_value, + char *error __attribute__((unused))) { uint length; + char name_buff[256]; + struct hostent *hostent; +#if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) int tmp_errno; - char name_buff[256],hostname_buff[2048]; - struct hostent tmp_hostent,*hostent; + char hostname_buff[2048]; + struct hostent tmp_hostent; +#endif + struct in_addr in; if (!args->args[0] || !(length=args->lengths[0])) { @@ -746,7 +759,6 @@ char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, } VOID(pthread_mutex_unlock(&LOCK_hostname)); #endif - struct in_addr in; memcpy_fixed((char*) &in,(char*) *hostent->h_addr_list, sizeof(in.s_addr)); *res_length= (ulong) (strmov(result, inet_ntoa(in)) - result); return result; @@ -780,18 +792,23 @@ my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 0; } -void reverse_lookup_deinit(UDF_INIT *initid) +void reverse_lookup_deinit(UDF_INIT *initid __attribute__((unused))) { #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST) (void) pthread_mutex_destroy(&LOCK_hostname); #endif } -char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *res_length, char *null_value, char *error) +char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *result, unsigned long *res_length, + char *null_value, char *error __attribute__((unused))) { +#if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) char name_buff[256]; struct hostent tmp_hostent; +#endif + struct hostent *hp; + unsigned long taddr; uint length; if (args->arg_count == 4) @@ -808,8 +825,8 @@ char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, (int) *((longlong*) args->args[3])); } else - { // string argument - if (!args->args[0]) // Return NULL for NULL values + { /* string argument */ + if (!args->args[0]) /* Return NULL for NULL values */ { *null_value=1; return 0; @@ -821,13 +838,12 @@ char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, result[length]=0; } - unsigned long taddr = inet_addr(result); + taddr = inet_addr(result); if (taddr == (unsigned long) -1L) { *null_value=1; return 0; } - struct hostent *hp; #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) int tmp_errno; if (!(hp=gethostbyaddr_r((char*) &taddr,sizeof(taddr), AF_INET, @@ -902,11 +918,15 @@ avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) /*args->arg_type[0] = REAL_RESULT; args->arg_type[1] = REAL_RESULT;*/ - initid->maybe_null = 0; // The result may be null - initid->decimals = 4; // We want 4 decimals in the result - initid->max_length = 20; // 6 digits + . + 10 decimals + initid->maybe_null = 0; /* The result may be null */ + initid->decimals = 4; /* We want 4 decimals in the result */ + initid->max_length = 20; /* 6 digits + . + 10 decimals */ - data = new struct avgcost_data; + if (!(data = (struct avgcost_data*) malloc(sizeof(struct avgcost_data)))) + { + strmov(message,"Couldn't allocate memory"); + return 1; + } data->totalquantity = 0; data->totalprice = 0.0; @@ -918,7 +938,7 @@ avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) void avgcost_deinit( UDF_INIT* initid ) { - delete initid->ptr; + free(initid->ptr); } @@ -933,7 +953,8 @@ avgcost_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) /* This is needed to get things to work in MySQL 4.1.1 and above */ void -avgcost_clear(UDF_INIT* initid, char* is_null, char* message) +avgcost_clear(UDF_INIT* initid, char* is_null __attribute__((unused)), + char* message __attribute__((unused))) { struct avgcost_data* data = (struct avgcost_data*)initid->ptr; data->totalprice= 0.0; @@ -943,7 +964,9 @@ avgcost_clear(UDF_INIT* initid, char* is_null, char* message) void -avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) +avgcost_add(UDF_INIT* initid, UDF_ARGS* args, + char* is_null __attribute__((unused)), + char* message __attribute__((unused))) { if (args->args[0] && args->args[1]) { @@ -963,7 +986,7 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) if ( ((quantity < 0) && (newquantity < 0)) || ((quantity > 0) && (newquantity > 0)) ) { - data->totalprice = price * double(newquantity); + data->totalprice = price * (double)newquantity; } /* ** sub q if totalq > 0 @@ -971,15 +994,15 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) */ else { - price = data->totalprice / double(data->totalquantity); - data->totalprice = price * double(newquantity); + price = data->totalprice / (double)data->totalquantity; + data->totalprice = price * (double)newquantity; } data->totalquantity = newquantity; } else { data->totalquantity += quantity; - data->totalprice += price * double(quantity); + data->totalprice += price * (double)quantity; } if (data->totalquantity == 0) @@ -989,7 +1012,8 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) double -avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error ) +avgcost( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)), + char* is_null, char* error __attribute__((unused))) { struct avgcost_data* data = (struct avgcost_data*)initid->ptr; if (!data->count || !data->totalquantity) @@ -999,16 +1023,14 @@ avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error ) } *is_null = 0; - return data->totalprice/double(data->totalquantity); + return data->totalprice/(double)data->totalquantity; } -extern "C" { my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, char *message); char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *null_value, char *error); -} my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, char *message) @@ -1024,16 +1046,17 @@ my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, return 0; } -char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *length, char *null_value, - char *error) +char *myfunc_argument_name(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args, char *result, + unsigned long *length, char *null_value, + char *error __attribute__((unused))) { if (!args->attributes[0]) { null_value= 0; return 0; } - (*length)--; // space for ending \0 (for debugging purposes) + (*length)--; /* space for ending \0 (for debugging purposes) */ if (*length > args->attribute_lengths[0]) *length= args->attribute_lengths[0]; memcpy(result, args->attributes[0], *length); diff --git a/strings/decimal.c b/strings/decimal.c index 8786a513945..5a0bc0968b6 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -171,6 +171,7 @@ static const dec1 frac_max[DIG_PER_DEC1-1]={ do \ { \ dec1 a=(from1)+(from2)+(carry); \ + DBUG_ASSERT((carry) <= 1); \ if (((carry)= a >= DIG_BASE)) /* no division here! */ \ a-=DIG_BASE; \ (to)=a; \ @@ -179,7 +180,7 @@ static const dec1 frac_max[DIG_PER_DEC1-1]={ #define ADD2(to, from1, from2, carry) \ do \ { \ - dec1 a=(from1)+(from2)+(carry); \ + dec2 a=((dec2)(from1))+(from2)+(carry); \ if (((carry)= a >= DIG_BASE)) \ a-=DIG_BASE; \ if (unlikely(a >= DIG_BASE)) \ @@ -187,7 +188,7 @@ static const dec1 frac_max[DIG_PER_DEC1-1]={ a-=DIG_BASE; \ carry++; \ } \ - (to)=a; \ + (to)=(dec1) a; \ } while(0) #define SUB(to, from1, from2, carry) /* to=from1-from2 */ \ @@ -1998,7 +1999,13 @@ int decimal_mul(decimal_t *from1, decimal_t *from2, decimal_t *to) ADD2(*buf0, *buf0, lo, carry); carry+=hi; } - for (; carry; buf0--) + if (carry) + { + if (buf0 < to->buf) + return E_DEC_OVERFLOW; + ADD2(*buf0, *buf0, 0, carry); + } + for (buf0--; carry; buf0--) { if (buf0 < to->buf) return E_DEC_OVERFLOW; diff --git a/strings/strtod.c b/strings/strtod.c index e0910205d2f..7171a6e0801 100644 --- a/strings/strtod.c +++ b/strings/strtod.c @@ -26,8 +26,7 @@ */ -#include "my_base.h" /* Defines EOVERFLOW on Windows */ -#include "my_global.h" /* Includes errno.h */ +#include "my_base.h" /* Includes errno.h + EOVERFLOW */ #include "m_ctype.h" #define MAX_DBL_EXP 308 diff --git a/support-files/mysql.spec.sh b/support-files/mysql.spec.sh index 5a0963e4f93..b4942072e5d 100644 --- a/support-files/mysql.spec.sh +++ b/support-files/mysql.spec.sh @@ -567,15 +567,18 @@ fi %doc %attr(644, root, root) %{_infodir}/mysql.info* +%doc %attr(644, root, man) %{_mandir}/man1/myisam_ftdump.1* %doc %attr(644, root, man) %{_mandir}/man1/myisamchk.1* %doc %attr(644, root, man) %{_mandir}/man1/myisamlog.1* %doc %attr(644, root, man) %{_mandir}/man1/myisampack.1* -%doc %attr(644, root, man) %{_mandir}/man1/mysqld.1* +%doc %attr(644, root, man) %{_mandir}/man1/mysql_explain_log.1* +%doc %attr(644, root, man) %{_mandir}/man8/mysqld.8* %doc %attr(644, root, man) %{_mandir}/man1/mysqld_multi.1* %doc %attr(644, root, man) %{_mandir}/man1/mysqld_safe.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql_fix_privilege_tables.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql_upgrade.1* %doc %attr(644, root, man) %{_mandir}/man1/mysqlhotcopy.1* +%doc %attr(644, root, man) %{_mandir}/man1/mysqlman.1* %doc %attr(644, root, man) %{_mandir}/man1/mysqlmanager.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql.server.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql_zap.1* @@ -662,7 +665,6 @@ fi %files ndb-management %defattr(-,root,root,0755) %attr(755, root, root) %{_sbindir}/ndb_mgmd -%attr(755, root, root) %{_bindir}/ndb_mgm %files ndb-tools %defattr(-,root,root,0755) diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index b81c0aef506..5ee63cb8738 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -15029,6 +15029,49 @@ static void test_bug20152() /* + Bug#21206: memory corruption when too many cursors are opened at once + + Memory corruption happens when more than 1024 cursors are open + simultaneously. +*/ +static void test_bug21206() +{ + const size_t cursor_count= 1025; + + const char *create_table[]= + { + "DROP TABLE IF EXISTS t1", + "CREATE TABLE t1 (i INT)", + "INSERT INTO t1 VALUES (1), (2), (3)" + }; + const char *query= "SELECT * FROM t1"; + + Stmt_fetch *fetch_array= + (Stmt_fetch*) calloc(cursor_count, sizeof(Stmt_fetch)); + + Stmt_fetch *fetch; + + DBUG_ENTER("test_bug21206"); + myheader("test_bug21206"); + + fill_tables(create_table, sizeof(create_table) / sizeof(*create_table)); + + for (fetch= fetch_array; fetch < fetch_array + cursor_count; ++fetch) + { + /* Init will exit(1) in case of error */ + stmt_fetch_init(fetch, fetch - fetch_array, query); + } + + for (fetch= fetch_array; fetch < fetch_array + cursor_count; ++fetch) + stmt_fetch_close(fetch); + + free(fetch_array); + + DBUG_VOID_RETURN; +} + + +/* Read and parse arguments and MySQL options from my.cnf */ @@ -15291,13 +15334,16 @@ static struct my_tests_st my_tests[]= { { "test_bug14845", test_bug14845 }, { "test_bug15510", test_bug15510 }, { "test_opt_reconnect", test_opt_reconnect }, +#ifndef EMBEDDED_LIBRARY { "test_bug12744", test_bug12744 }, +#endif { "test_bug16143", test_bug16143 }, { "test_bug15613", test_bug15613 }, { "test_bug20152", test_bug20152 }, { "test_bug14169", test_bug14169 }, { "test_bug17667", test_bug17667 }, { "test_bug19671", test_bug19671}, + { "test_bug21206", test_bug21206}, { 0, 0 } }; |