diff options
Diffstat (limited to 'mysql-test/r')
43 files changed, 1255 insertions, 528 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index d622127e8f0..82a5ccc3e82 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -37,7 +37,7 @@ Note 1051 Unknown table 't1' create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap; ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key create table not_existing_database.test (a int); -Got one of the listed errors +ERROR 42000: Unknown database 'not_existing_database' create table `a/a` (a int); ERROR 42000: Incorrect table name 'a/a' create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); @@ -358,7 +358,7 @@ create table t3 like t1; create table t3 like mysqltest.t3; ERROR 42S01: Table 't3' already exists create table non_existing_database.t1 like t1; -Got one of the listed errors +ERROR 42000: Unknown database 'non_existing_database' create table t3 like non_existing_table; ERROR 42S02: Unknown table 'non_existing_table' create temporary table t3 like t1; @@ -602,3 +602,10 @@ drop database mysqltest; create table test.t1 like x; ERROR 42000: Incorrect database name 'NULL' drop table if exists test.t1; +create database mysqltest; +use mysqltest; +create view v1 as select 'foo' from dual; +create table t1 like v1; +ERROR HY000: 'mysqltest.v1' is not BASE TABLE +drop view v1; +drop database mysqltest; diff --git a/mysql-test/r/ctype_big5.result b/mysql-test/r/ctype_big5.result index 8d2c39df853..01b59b93b52 100644 --- a/mysql-test/r/ctype_big5.result +++ b/mysql-test/r/ctype_big5.result @@ -128,3 +128,9 @@ SELECT * FROM t1; a ùØ DROP TABLE t1; +CREATE TABLE t1 (a CHAR(50) CHARACTER SET big5 NOT NULL, FULLTEXT(a)); +INSERT INTO t1 VALUES(0xA741ADCCA66EB6DC20A7DAADCCABDCA66E); +SELECT HEX(a) FROM t1 WHERE MATCH(a) AGAINST (0xA741ADCCA66EB6DC IN BOOLEAN MODE); +HEX(a) +A741ADCCA66EB6DC20A7DAADCCABDCA66E +DROP TABLE t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index e9766622cf6..f8ae61b03fb 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -32,3 +32,10 @@ a b 3 d 4 e drop table t1; +create table t1 (a int not null primary key); +insert into t1 values (1); +insert delayed into t1 values (1); +select * from t1; +a +1 +drop table t1; diff --git a/mysql-test/r/flush_table.result b/mysql-test/r/flush_table.result index 2ef4ab5b52b..db54d2e53ef 100644 --- a/mysql-test/r/flush_table.result +++ b/mysql-test/r/flush_table.result @@ -101,3 +101,6 @@ table_id Record-02 handler t1 close; drop table t1; +FLUSH TABLES WITH READ LOCK ; +FLUSH TABLES WITH READ LOCK ; +UNLOCK TABLES; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 1a79f6d9736..6a41035eb7b 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -422,3 +422,11 @@ SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabrück"' IN BOOLEAN MODE); COUNT(*) 1 DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)); +INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +SET myisam_repair_threads=2; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SET myisam_repair_threads=@@global.myisam_repair_threads; +DROP TABLE t1; diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 10afb61ba1d..184bd07b066 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -445,6 +445,30 @@ group_concat(distinct b order by b) Warnings: Warning 1260 2 line(s) were cut by GROUP_CONCAT() drop table t1; +create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci, +b varchar(255) character set koi8r); +insert into t1 values ('xxx','yyy'); +select collation(a) from t1; +collation(a) +cp1250_general_ci +select collation(group_concat(a)) from t1; +collation(group_concat(a)) +cp1250_general_ci +create table t2 select group_concat(a) as a from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(400) character set cp1250 default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select collation(group_concat(a,_koi8r'test')) from t1; +collation(group_concat(a,_koi8r'test')) +cp1250_general_ci +select collation(group_concat(a,_koi8r 0xC1C2)) from t1; +ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat' +select collation(group_concat(a,b)) from t1; +ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat' +drop table t1; +drop table t2; CREATE TABLE t1 (id int); SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; gc @@ -500,3 +524,34 @@ group_concat(a) ABW ABW drop table t1; +CREATE TABLE t1 ( +aID smallint(5) unsigned NOT NULL auto_increment, +sometitle varchar(255) NOT NULL default '', +bID smallint(5) unsigned NOT NULL, +PRIMARY KEY (aID), +UNIQUE KEY sometitle (sometitle) +); +INSERT INTO t1 SET sometitle = 'title1', bID = 1; +INSERT INTO t1 SET sometitle = 'title2', bID = 1; +CREATE TABLE t2 ( +bID smallint(5) unsigned NOT NULL auto_increment, +somename varchar(255) NOT NULL default '', +PRIMARY KEY (bID), +UNIQUE KEY somename (somename) +); +INSERT INTO t2 SET somename = 'test'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +INSERT INTO t2 SET somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +DELETE FROM t2 WHERE somename = 'test2'; +SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +FROM t1 JOIN t2 ON t1.bID = t2.bID; +COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') +2 test +DROP TABLE t1,t2; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 0db2ceb0e6b..1542794798a 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -795,6 +795,32 @@ show columns from t2; Field Type Null Key Default Extra f2 datetime NO 0000-00-00 00:00:00 drop table t2, t1; +CREATE TABLE t1( +id int PRIMARY KEY, +a int, +b int, +INDEX i_b_id(a,b,id), +INDEX i_id(a,id) +); +INSERT INTO t1 VALUES +(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +MAX(id) +NULL +DROP TABLE t1; +CREATE TABLE t1( +id int PRIMARY KEY, +a int, +b int, +INDEX i_id(a,id), +INDEX i_b_id(a,b,id) +); +INSERT INTO t1 VALUES +(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); +SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; +MAX(id) +NULL +DROP TABLE t1; create table t2 (ff double); insert into t2 values (2.2); select cast(sum(distinct ff) as decimal(5,2)) from t2; @@ -860,32 +886,6 @@ select col1,sum(col1),max(col1),min(col1) from t1 group by col1; col1 sum(col1) max(col1) min(col1) 5.000000000010 10.000000000020 5.000000000010 5.000000000010 DROP TABLE t1; -CREATE TABLE t1( -id int PRIMARY KEY, -a int, -b int, -INDEX i_b_id(a,b,id), -INDEX i_id(a,id) -); -INSERT INTO t1 VALUES -(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); -SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; -MAX(id) -NULL -DROP TABLE t1; -CREATE TABLE t1( -id int PRIMARY KEY, -a int, -b int, -INDEX i_id(a,id), -INDEX i_b_id(a,b,id) -); -INSERT INTO t1 VALUES -(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1); -SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6; -MAX(id) -NULL -DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(400)); INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a "), ("B"), ("b"), ("b "), ("b "); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index b7188092b41..0149911e36b 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -152,3 +152,16 @@ ceil(0.09) select ceil(0.000000000000000009); ceil(0.000000000000000009) 1 +create table t1 select round(1, 6); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `round(1, 6)` decimal(7,6) NOT NULL default '0.000000' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +round(1, 6) +1.000000 +drop table t1; +select abs(-2) * -2; +abs(-2) * -2 +-4 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 5335f53d382..57942d1fcdf 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -879,3 +879,18 @@ drop table t1; select hex(29223372036854775809), hex(-29223372036854775809); hex(29223372036854775809) hex(-29223372036854775809) FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF +create table t1 (i int); +insert into t1 values (1000000000),(1); +select lpad(i, 7, ' ') as t from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def t 253 7 7 Y 128 31 63 +t +1000000 + 1 +select rpad(i, 7, ' ') as t from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def t 253 7 7 Y 128 31 63 +t +1000000 +1 +drop table t1; diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 93216fe2003..dfe375fd694 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -665,3 +665,8 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field insert into t1 values (pointfromtext('point(1,1)')); ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field drop table t1; +create table t1 (s1 geometry not null,s2 char(100)); +create trigger t1_bu before update on t1 for each row set new.s1 = null; +insert into t1 values (null,null); +ERROR 23000: Column 's1' cannot be null +drop table t1; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 89b3df0a83b..5688d8c2145 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -33,22 +33,22 @@ create table mysqltest.t4(a int); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; c -SCHEMATA -TABLES -COLUMNS CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY +COLUMNS +COLUMN_PRIVILEGES +KEY_COLUMN_USAGE ROUTINES -STATISTICS -VIEWS -USER_PRIVILEGES +SCHEMATA SCHEMA_PRIVILEGES -TABLE_PRIVILEGES -COLUMN_PRIVILEGES +STATISTICS +TABLES TABLE_CONSTRAINTS -KEY_COLUMN_USAGE +TABLE_PRIVILEGES TRIGGERS +VIEWS +USER_PRIVILEGES columns_priv db func @@ -76,8 +76,8 @@ inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES -TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone @@ -94,8 +94,8 @@ left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES -TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone @@ -112,8 +112,8 @@ right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES -TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_CONSTRAINTS TABLE_CONSTRAINTS +TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone @@ -545,7 +545,7 @@ proc is_deterministic enum('YES','NO') proc security_type enum('INVOKER','DEFINER') proc param_list blob proc returns char(64) -proc body blob +proc body longblob proc definer char(77) proc created timestamp proc modified timestamp @@ -577,13 +577,13 @@ select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE -SCHEMATA TEMPORARY MEMORY -TABLES TEMPORARY MEMORY +CHARACTER_SETS TEMPORARY MEMORY +COLLATIONS TEMPORARY MEMORY show tables from information_schema like "T%"; Tables_in_information_schema (T%) TABLES -TABLE_PRIVILEGES TABLE_CONSTRAINTS +TABLE_PRIVILEGES TRIGGERS create database information_schema; ERROR HY000: Can't create database 'information_schema'; database exists @@ -591,8 +591,8 @@ use information_schema; show full tables like "T%"; Tables_in_information_schema (T%) Table_type TABLES TEMPORARY -TABLE_PRIVILEGES TEMPORARY TABLE_CONSTRAINTS TEMPORARY +TABLE_PRIVILEGES TEMPORARY TRIGGERS TEMPORARY create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema @@ -603,8 +603,8 @@ use information_schema; show tables like "T%"; Tables_in_information_schema (T%) TABLES -TABLE_PRIVILEGES TABLE_CONSTRAINTS +TABLE_PRIVILEGES TRIGGERS select table_name from tables where table_name='user'; table_name @@ -689,7 +689,7 @@ show variables where variable_name like "skip_show_databas"; Variable_name Value show global status like "Threads_running"; Variable_name Value -Threads_running 1 +Threads_running # create table t1(f1 int); create table t2(f2 int); create view v1 as select * from t1, t2; @@ -710,17 +710,18 @@ table_schema table_name column_name information_schema COLUMNS COLUMN_TYPE information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE -information_schema VIEWS VIEW_DEFINITION information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT +information_schema TRIGGERS SQL_MODE +information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; table_name column_name data_type +ROUTINES CREATED datetime +ROUTINES LAST_ALTERED datetime TABLES CREATE_TIME datetime TABLES UPDATE_TIME datetime TABLES CHECK_TIME datetime -ROUTINES CREATED datetime -ROUTINES LAST_ALTERED datetime TRIGGERS CREATED datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS @@ -755,14 +756,14 @@ grant select on test.* to mysqltest_4@localhost; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; TABLE_NAME COLUMN_NAME PRIVILEGES -TABLES TABLE_NAME select COLUMNS TABLE_NAME select -STATISTICS TABLE_NAME select -VIEWS TABLE_NAME select -TABLE_PRIVILEGES TABLE_NAME select COLUMN_PRIVILEGES TABLE_NAME select -TABLE_CONSTRAINTS TABLE_NAME select KEY_COLUMN_USAGE TABLE_NAME select +STATISTICS TABLE_NAME select +TABLES TABLE_NAME select +TABLE_CONSTRAINTS TABLE_NAME select +TABLE_PRIVILEGES TABLE_NAME select +VIEWS TABLE_NAME select delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; flush privileges; @@ -790,45 +791,45 @@ set @fired:= "Yes"; end if; end| show triggers; -Trigger Event Table Statement Timing Created +Trigger Event Table Statement Timing Created sql_mode trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL +end BEFORE NULL trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL +end BEFORE NULL trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL +end AFTER NULL select * from information_schema.triggers; -TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL +end ROW BEFORE NULL NULL OLD NEW NULL NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL +end ROW AFTER NULL NULL OLD NEW NULL drop trigger trg1; drop trigger trg2; drop trigger trg3; @@ -940,3 +941,12 @@ f5 19 NULL f6 1 NULL f7 64 NULL drop table t1; +create table t1 (f1 integer); +create trigger tr1 after insert on t1 for each row set @test_var=42; +use information_schema; +select trigger_schema, trigger_name from triggers where +trigger_name='tr1'; +trigger_schema trigger_name +test tr1 +use test; +drop table t1; diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index ece30924055..d3ff310b812 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -1,27 +1,27 @@ use INFORMATION_SCHEMA; show tables; Tables_in_information_schema -SCHEMATA -TABLES -COLUMNS CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY +COLUMNS +COLUMN_PRIVILEGES +KEY_COLUMN_USAGE ROUTINES -STATISTICS -VIEWS -USER_PRIVILEGES +SCHEMATA SCHEMA_PRIVILEGES -TABLE_PRIVILEGES -COLUMN_PRIVILEGES +STATISTICS +TABLES TABLE_CONSTRAINTS -KEY_COLUMN_USAGE +TABLE_PRIVILEGES TRIGGERS +VIEWS +USER_PRIVILEGES show tables from INFORMATION_SCHEMA like 'T%'; Tables_in_information_schema (T%) TABLES -TABLE_PRIVILEGES TABLE_CONSTRAINTS +TABLE_PRIVILEGES TRIGGERS create database `inf%`; use `inf%`; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 2c73cbeeea4..2bdec5125dd 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1452,16 +1452,16 @@ Error 1146 Table 'test.t4' doesn't exist checksum table t1, t2, t3, t4; Table Checksum test.t1 2948697075 -test.t2 1157260244 -test.t3 1157260244 +test.t2 3835700799 +test.t3 3835700799 test.t4 NULL Warnings: Error 1146 Table 'test.t4' doesn't exist checksum table t1, t2, t3, t4 extended; Table Checksum test.t1 3092701434 -test.t2 1157260244 -test.t3 1157260244 +test.t2 3835700799 +test.t3 3835700799 test.t4 NULL Warnings: Error 1146 Table 'test.t4' doesn't exist diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 3ad8571aadd..bce02a1cb0f 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -354,3 +354,28 @@ t1 CREATE TABLE `t1` ( KEY `a` (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null unique); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI +b varchar(20) NO UNI +drop table t1; +create table t1 (a int not null primary key, b int not null unique); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI +b int(11) NO UNI +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10))); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI +b varchar(20) NO UNI +drop table t1; +create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10))); +desc t1; +Field Type Null Key Default Extra +a int(11) NO PRI +b varchar(20) NO MUL +c varchar(20) NO +drop table t1; diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result index 7c286ced58a..b3aa4c5061c 100644 --- a/mysql-test/r/key_cache.result +++ b/mysql-test/r/key_cache.result @@ -23,13 +23,13 @@ SELECT @@global.default.key_buffer_size; @@global.default.key_buffer_size 16777216 SELECT @@global.default.`key_buffer_size`; -@@global.default.key_buffer_size +@@global.default.`key_buffer_size` 16777216 SELECT @@global.`default`.`key_buffer_size`; -@@global.default.key_buffer_size +@@global.`default`.`key_buffer_size` 16777216 SELECT @@`default`.key_buffer_size; -@@default.key_buffer_size +@@`default`.key_buffer_size 16777216 SELECT @@small.key_buffer_size; @@small.key_buffer_size diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 8239202fc04..71cb76fe844 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -506,7 +506,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct drop table t1,t2; CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; -ERROR 42000: This version of MySQL doesn't yet support 'RTREE INDEX' +Got one of the listed errors create table t1 (a int, b varchar(200), c text not null) checksum=1; create table t2 (a int, b varchar(200), c text not null) checksum=0; insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index bf783402921..917724580cf 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -1,6 +1,5 @@ DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa; drop database if exists mysqldump_test_db; -drop database if exists db1; drop view if exists v1, v2, v3; CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1), (2); @@ -357,46 +356,6 @@ CREATE TABLE `t1` ( 2 3 drop table t1; -create table t1(a int); -create view v1 as select * from t1; - -/*!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 */; -/*!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 */; -DROP TABLE IF EXISTS `t1`; -CREATE TABLE `t1` ( - `a` int(11) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - - -/*!40000 ALTER TABLE `t1` DISABLE KEYS */; -LOCK TABLES `t1` WRITE; -UNLOCK TABLES; -/*!40000 ALTER TABLE `t1` ENABLE KEYS */; -DROP TABLE IF EXISTS `v1`; -DROP VIEW IF EXISTS `v1`; -CREATE TABLE `v1` ( - `a` int(11) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; -DROP TABLE IF EXISTS `v1`; -DROP VIEW IF EXISTS `v1`; -CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1`; - -/*!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 v1; -drop table t1; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -605,38 +564,6 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; -CREATE TABLE t1 (a char(10)); -INSERT INTO t1 VALUES ('\''); - -/*!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 */; -/*!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 */; -DROP TABLE IF EXISTS `t1`; -CREATE TABLE `t1` ( - `a` char(10) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - - -/*!40000 ALTER TABLE `t1` DISABLE KEYS */; -LOCK TABLES `t1` WRITE; -INSERT INTO `t1` VALUES ('\''); -UNLOCK TABLES; -/*!40000 ALTER TABLE `t1` ENABLE KEYS */; - -/*!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 TABLE t1; CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t1 VALUES (4),(5),(6); @@ -1428,59 +1355,6 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; DROP TABLE t1; -create database db1; -use db1; -CREATE TABLE t2 ( -a varchar(30) default NULL, -KEY a (a(5)) -); -INSERT INTO t2 VALUES ('alfred'); -INSERT INTO t2 VALUES ('angie'); -INSERT INTO t2 VALUES ('bingo'); -INSERT INTO t2 VALUES ('waffle'); -INSERT INTO t2 VALUES ('lemon'); -create view v2 as select * from t2 where a like 'a%' with check option; - -/*!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 */; -/*!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 */; -DROP TABLE IF EXISTS `t2`; -CREATE TABLE `t2` ( - `a` varchar(30) default NULL, - KEY `a` (`a`(5)) -) ENGINE=MyISAM DEFAULT CHARSET=latin1; - - -/*!40000 ALTER TABLE `t2` DISABLE KEYS */; -LOCK TABLES `t2` WRITE; -INSERT INTO `t2` VALUES ('alfred'),('angie'),('bingo'),('waffle'),('lemon'); -UNLOCK TABLES; -/*!40000 ALTER TABLE `t2` ENABLE KEYS */; -DROP TABLE IF EXISTS `v2`; -DROP VIEW IF EXISTS `v2`; -CREATE TABLE `v2` ( - `a` varchar(30) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1; -DROP TABLE IF EXISTS `v2`; -DROP VIEW IF EXISTS `v2`; -CREATE ALGORITHM=UNDEFINED VIEW `db1`.`v2` AS select `db1`.`t2`.`a` AS `a` from `db1`.`t2` where (`db1`.`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION; - -/*!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 table t2; -drop view v2; -drop database db1; CREATE DATABASE mysqldump_test_db; USE mysqldump_test_db; CREATE TABLE t1 ( a INT ); @@ -1647,6 +1521,132 @@ insert into t2 (a, b) values (NULL, NULL),(10, NULL),(NULL, "twenty"),(30, "thir </database> </mysqldump> drop table t1, t2; +create table t1(a int); +create view v1 as select * from t1; + +/*!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 */; +/*!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 */; +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +UNLOCK TABLES; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +DROP TABLE IF EXISTS `v1`; +/*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 CREATE TABLE `v1` ( + `a` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1*/; +/*!50001 DROP TABLE IF EXISTS `v1`*/; +/*!50001 DROP VIEW IF EXISTS `v1`*/; +/*!50001 CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`a` AS `a` from `test`.`t1`*/; + +/*!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 v1; +drop table t1; +create database mysqldump_test_db; +use mysqldump_test_db; +CREATE TABLE t2 ( +a varchar(30) default NULL, +KEY a (a(5)) +); +INSERT INTO t2 VALUES ('alfred'); +INSERT INTO t2 VALUES ('angie'); +INSERT INTO t2 VALUES ('bingo'); +INSERT INTO t2 VALUES ('waffle'); +INSERT INTO t2 VALUES ('lemon'); +create view v2 as select * from t2 where a like 'a%' with check option; + +/*!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 */; +/*!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 */; +DROP TABLE IF EXISTS `t2`; +CREATE TABLE `t2` ( + `a` varchar(30) default NULL, + KEY `a` (`a`(5)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; +LOCK TABLES `t2` WRITE; +INSERT INTO `t2` VALUES ('alfred'),('angie'),('bingo'),('waffle'),('lemon'); +UNLOCK TABLES; +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; +DROP TABLE IF EXISTS `v2`; +/*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 CREATE TABLE `v2` ( + `a` varchar(30) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1*/; +/*!50001 DROP TABLE IF EXISTS `v2`*/; +/*!50001 DROP VIEW IF EXISTS `v2`*/; +/*!50001 CREATE ALGORITHM=UNDEFINED VIEW `mysqldump_test_db`.`v2` AS select `mysqldump_test_db`.`t2`.`a` AS `a` from `mysqldump_test_db`.`t2` where (`mysqldump_test_db`.`t2`.`a` like _latin1'a%') WITH CASCADED CHECK OPTION*/; + +/*!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 table t2; +drop view v2; +drop database mysqldump_test_db; +use test; +CREATE TABLE t1 (a char(10)); +INSERT INTO t1 VALUES ('\''); + +/*!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 */; +/*!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 */; +DROP TABLE IF EXISTS `t1`; +CREATE TABLE `t1` ( + `a` char(10) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; + + +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +LOCK TABLES `t1` WRITE; +INSERT INTO `t1` VALUES ('\''); +UNLOCK TABLES; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; + +/*!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 TABLE t1; create table t1(a int, b int, c varchar(30)); insert into t1 values(1, 2, "one"), (2, 4, "two"), (3, 6, "three"); create view v3 as @@ -1685,6 +1685,7 @@ end| create trigger trg2 before update on t1 for each row begin if old.a % 2 = 0 then set new.b := 12; end if; end| +set sql_mode="traditional"| create trigger trg3 after update on t1 for each row begin if new.a = -1 then @@ -1697,24 +1698,25 @@ if new.a > 10 then set @fired:= "No"; end if; end| +set sql_mode=default| show triggers like "t1"; -Trigger Event Table Statement Timing Created +Trigger Event Table Statement Timing Created sql_mode trg1 INSERT t1 begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 trg2 UPDATE t1 begin if old.a % 2 = 0 then set new.b := 12; end if; -end BEFORE 0000-00-00 00:00:00 +end BEFORE 0000-00-00 00:00:00 trg3 UPDATE t1 begin if new.a = -1 then set @fired:= "Yes"; end if; -end AFTER 0000-00-00 00:00:00 +end AFTER 0000-00-00 00:00:00 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER INSERT INTO t1 (a) VALUES (1),(2),(3),(22); update t1 set a = 4 where a=3; @@ -1736,30 +1738,32 @@ CREATE TABLE `t1` ( `b` bigint(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER //; -CREATE TRIGGER `trg1` BEFORE INSERT ON `t1` -FOR EACH ROW +/*!50003 SET SESSION SQL_MODE=""*/ // +/*!50003 CREATE TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW begin if new.a > 10 then set new.a := 10; set new.a := 11; end if; -end// +end*/ // -CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1` -FOR EACH ROW begin +/*!50003 SET SESSION SQL_MODE=""*/ // +/*!50003 CREATE TRIGGER `trg2` BEFORE UPDATE ON `t1` FOR EACH ROW begin if old.a % 2 = 0 then set new.b := 12; end if; -end// +end*/ // -CREATE TRIGGER `trg3` AFTER UPDATE ON `t1` -FOR EACH ROW +/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"*/ // +/*!50003 CREATE TRIGGER `trg3` AFTER UPDATE ON `t1` FOR EACH ROW begin if new.a = -1 then set @fired:= "Yes"; end if; -end// +end*/ // DELIMITER ;// +/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/; /*!40000 ALTER TABLE `t1` DISABLE KEYS */; LOCK TABLES `t1` WRITE; @@ -1771,16 +1775,18 @@ CREATE TABLE `t2` ( `a` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER //; -CREATE TRIGGER `trg4` BEFORE INSERT ON `t2` -FOR EACH ROW +/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER"*/ // +/*!50003 CREATE TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW begin if new.a > 10 then set @fired:= "No"; end if; -end// +end*/ // DELIMITER ;// +/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/; /*!40000 ALTER TABLE `t2` DISABLE KEYS */; LOCK TABLES `t2` WRITE; @@ -1844,4 +1850,28 @@ show tables; Tables_in_test t1 t2 +show triggers; +Trigger Event Table Statement Timing Created sql_mode +trg1 INSERT t1 +begin +if new.a > 10 then +set new.a := 10; +set new.a := 11; +end if; +end BEFORE # +trg2 UPDATE t1 begin +if old.a % 2 = 0 then set new.b := 12; end if; +end BEFORE # +trg3 UPDATE t1 +begin +if new.a = -1 then +set @fired:= "Yes"; +end if; +end AFTER # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER +trg4 INSERT t2 +begin +if new.a > 10 then +set @fired:= "No"; +end if; +end BEFORE # STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER DROP TABLE t1, t2; diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index 2b171229096..256576704b5 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -164,3 +164,5 @@ root@localhost . - is longer then 80 characters and . - consists of several lines -------------------------------------------------------------------------------- +this will be executed +this will be executed diff --git a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result index 14cd4666528..1c3da1b486f 100644 --- a/mysql-test/r/ndb_condition_pushdown.result +++ b/mysql-test/r/ndb_condition_pushdown.result @@ -1514,7 +1514,7 @@ select auto from t1 where '1901-01-01 01:01:01' in(date_time) order by auto; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where with pushed condition; Using filesort +1 SIMPLE t1 ref medium_index medium_index 3 const 10 Using where with pushed condition; Using filesort select auto from t1 where "aaaa" in(string) and "aaaa" in(vstring) and diff --git a/mysql-test/r/ndb_config.result b/mysql-test/r/ndb_config.result new file mode 100644 index 00000000000..c2557f85c0b --- /dev/null +++ b/mysql-test/r/ndb_config.result @@ -0,0 +1,5 @@ +ndbd,1,localhost ndbd,2,localhost ndb_mgmd,3,localhost mysqld,4, mysqld,5, mysqld,6, mysqld,7, +1,localhost,41943040,12582912 2,localhost,41943040,12582912 +1 localhost 41943040 12582912 +2 localhost 41943040 12582912 +1 2 diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result new file mode 100644 index 00000000000..082ebe72ba4 --- /dev/null +++ b/mysql-test/r/not_embedded_server.result @@ -0,0 +1,5 @@ +prepare stmt1 from ' show full processlist '; +execute stmt1; +Id User Host db Command Time State Info +number root localhost test Execute time NULL show full processlist +deallocate prepare stmt1; diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index b9824cc4b81..7f746a3dbd8 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -369,3 +369,63 @@ select * from t1; id id2 1 1 drop table t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +CREATE TABLE t3 (b int, INDEX idx(b)); +CREATE TABLE t4 (b int, INDEX idx(b)); +INSERT INTO t1 VALUES (1), (2), (3), (4); +INSERT INTO t2 VALUES (1, 1), (3, 1); +INSERT INTO t3 VALUES +(NULL), (NULL), (NULL), (NULL), (NULL), +(NULL), (NULL), (NULL), (NULL), (NULL); +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t4 SELECT * FROM t3; +INSERT INTO t3 SELECT * FROM t4; +INSERT INTO t3 VALUES (2), (3); +ANALYZE table t1, t2, t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SELECT COUNT(*) FROM t3; +COUNT(*) +15972 +EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ref idx idx 5 test.t1.a 1 +1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index +FLUSH STATUS ; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a +LEFT JOIN t3 ON t2.b=t3.b; +a a b b +1 1 1 NULL +2 NULL NULL NULL +3 3 1 NULL +4 NULL NULL NULL +SELECT FOUND_ROWS(); +FOUND_ROWS() +4 +SHOW STATUS LIKE "handler_read%"; +Variable_name Value +Handler_read_first 0 +Handler_read_key 6 +Handler_read_next 2 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 5 +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index f2d60ac5025..7fb1e1b4df4 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -349,7 +349,7 @@ execute stmt1 ; ERROR 42S02: Unknown table 't5' prepare stmt1 from ' SELECT @@version ' ; execute stmt1 ; -@@VERSION +@@version <version> prepare stmt_do from ' do @var:= (1 in (select a from t1)) ' ; prepare stmt_set from ' set @var= (1 in (select a from t1)) ' ; diff --git a/mysql-test/r/ps_grant.result b/mysql-test/r/ps_grant.result index 651ac171e5d..fdc1f97bb4c 100644 --- a/mysql-test/r/ps_grant.result +++ b/mysql-test/r/ps_grant.result @@ -54,6 +54,7 @@ my_col 4 execute s_t9 ; ERROR 42S02: Table 'mysqltest.t9' doesn't exist +deallocate prepare s_t9; revoke all privileges on mysqltest.t1 from second_user@localhost identified by 'looser' ; show grants for second_user@localhost ; @@ -87,8 +88,3 @@ revoke all privileges on test.t1 from drop_user@localhost ; prepare stmt3 from ' drop user drop_user@localhost '; ERROR HY000: This command is not supported in the prepared statement protocol yet drop user drop_user@localhost; -prepare stmt4 from ' show full processlist '; -execute stmt4; -Id User Host db Command Time State Info -number root localhost test Execute time NULL show full processlist -deallocate prepare stmt4; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 699d81d4cef..6ff49951d27 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -152,82 +152,6 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1, t2, t3; -create table t1 (a int not null); -insert into t1 values (1),(2),(3); -create table t2 (a int not null); -insert into t2 values (1),(2),(3); -select * from t1; -a -1 -2 -3 -select * from t2; -a -1 -2 -3 -insert into t1 values (4); -show status like "Qcache_free_blocks"; -Variable_name Value -Qcache_free_blocks 2 -flush query cache; -show status like "Qcache_free_blocks"; -Variable_name Value -Qcache_free_blocks 1 -drop table t1, t2; -create table t1 (a text not null); -create table t11 (a text not null); -create table t2 (a text not null); -create table t21 (a text not null); -create table t3 (a text not null); -insert into t1 values("1111111111111111111111111111111111111111111111111111"); -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t11 select * from t1; -insert into t21 select * from t1; -insert into t1 select * from t2; -insert into t2 select * from t1; -insert into t1 select * from t2; -insert into t3 select * from t1; -insert into t3 select * from t2; -insert into t3 select * from t1; -select * from t11; -select * from t21; -show status like "Qcache_total_blocks"; -Variable_name Value -Qcache_total_blocks 7 -show status like "Qcache_free_blocks"; -Variable_name Value -Qcache_free_blocks 1 -insert into t11 values(""); -select * from t3; -show status like "Qcache_total_blocks"; -Variable_name Value -Qcache_total_blocks 8 -show status like "Qcache_free_blocks"; -Variable_name Value -Qcache_free_blocks 1 -flush query cache; -show status like "Qcache_total_blocks"; -Variable_name Value -Qcache_total_blocks 7 -show status like "Qcache_free_blocks"; -Variable_name Value -Qcache_free_blocks 1 -drop table t1, t2, t3, t11, t21; set query_cache_type=demand; create table t1 (a int not null); insert into t1 values (1),(2),(3); @@ -852,6 +776,7 @@ Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 +SET NAMES default; DROP TABLE t1; CREATE TABLE t1 (a int(1)); CREATE DATABASE mysqltest; @@ -1007,6 +932,8 @@ abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzab zyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcbazyxwvutsrqponmlkjihgfedcba flush query cache; drop table t1, t2; +set GLOBAL query_cache_size=1355776 +#; flush status; CREATE TABLE t1 ( `date` datetime NOT NULL default '0000-00-00 00:00:00', @@ -1014,24 +941,30 @@ KEY `date` (`date`) ) ENGINE=MyISAM; INSERT INTO t1 VALUES ('20050326'); INSERT INTO t1 VALUES ('20050325'); -SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0'; +SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050327 0:0:0' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 0:0:0' for column 'date' at row 1 -SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 0:0:0'; +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050328 0:0:0' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050328 0:0:0' for column 'date' at row 1 -SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0'; +Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 +Warning 1292 Incorrect datetime value: '20050328 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050328 invalid' +SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'; COUNT(*) 0 Warnings: -Warning 1292 Incorrect datetime value: '20050327 0:0:0' for column 'date' at row 1 -Warning 1292 Incorrect datetime value: '20050327 0:0:0' for column 'date' at row 1 +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' +Warning 1292 Truncated incorrect INTEGER value: '20050327 invalid' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 @@ -1123,4 +1056,80 @@ a f1() 2 2 drop procedure p1// drop table t1// +flush query cache; +reset query cache; +flush status; +create table t1 (s1 int)// +create procedure f1 () begin +select sql_cache * from t1; +select sql_cache * from t1; +end;// +call f1(); +s1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +call f1(); +s1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 3 +call f1(); +s1 +select sql_cache * from t1; +s1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 5 +insert into t1 values (1); +select sql_cache * from t1; +s1 +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 3 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 5 +call f1(); +s1 +1 +call f1(); +s1 +1 +select sql_cache * from t1; +s1 +1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 4 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 9 +drop procedure f1; +drop table t1; set GLOBAL query_cache_size=0; diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result new file mode 100644 index 00000000000..16a397f78b6 --- /dev/null +++ b/mysql-test/r/query_cache_notembedded.result @@ -0,0 +1,83 @@ +set GLOBAL query_cache_size=1355776; +flush query cache; +flush query cache; +reset query cache; +flush status; +drop table if exists t1, t2, t3, t11, t21; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +create table t2 (a int not null); +insert into t2 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +select * from t2; +a +1 +2 +3 +insert into t1 values (4); +show status like "Qcache_free_blocks"; +Variable_name Value +Qcache_free_blocks 2 +flush query cache; +show status like "Qcache_free_blocks"; +Variable_name Value +Qcache_free_blocks 1 +drop table t1, t2; +create table t1 (a text not null); +create table t11 (a text not null); +create table t2 (a text not null); +create table t21 (a text not null); +create table t3 (a text not null); +insert into t1 values("1111111111111111111111111111111111111111111111111111"); +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t11 select * from t1; +insert into t21 select * from t1; +insert into t1 select * from t2; +insert into t2 select * from t1; +insert into t1 select * from t2; +insert into t3 select * from t1; +insert into t3 select * from t2; +insert into t3 select * from t1; +select * from t11; +select * from t21; +show status like "Qcache_total_blocks"; +Variable_name Value +Qcache_total_blocks 7 +show status like "Qcache_free_blocks"; +Variable_name Value +Qcache_free_blocks 1 +insert into t11 values(""); +select * from t3; +show status like "Qcache_total_blocks"; +Variable_name Value +Qcache_total_blocks 8 +show status like "Qcache_free_blocks"; +Variable_name Value +Qcache_free_blocks 1 +flush query cache; +show status like "Qcache_total_blocks"; +Variable_name Value +Qcache_total_blocks 7 +show status like "Qcache_free_blocks"; +Variable_name Value +Qcache_free_blocks 1 +drop table t1, t2, t3, t11, t21; +set GLOBAL query_cache_size=0; diff --git a/mysql-test/r/rpl_slave_status.result b/mysql-test/r/rpl_slave_status.result new file mode 100644 index 00000000000..8badbab85ff --- /dev/null +++ b/mysql-test/r/rpl_slave_status.result @@ -0,0 +1,54 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl'; +stop slave; +change master to master_user='rpl',master_password='rpl'; +start slave; +drop table if exists t1; +create table t1 (n int); +insert into t1 values (1); +select * from t1; +n +1 +delete from mysql.user where user='rpl'; +flush privileges; +stop slave; +start slave; +show slave status; +Slave_IO_State Connecting to master +Master_Host 127.0.0.1 +Master_User rpl +Master_Port MASTER_MYPORT +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running No +Slave_SQL_Running Yes +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 0 +Last_Error +Skip_Counter 0 +Exec_Master_Log_Pos # +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master NULL diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 14c45270b04..a0f6bb7084f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2492,6 +2492,99 @@ select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid id name gid uid ident level 1 fs NULL NULL 0 READ drop table t1,t2,t3; +CREATE TABLE t1 ( +acct_id int(11) NOT NULL default '0', +profile_id smallint(6) default NULL, +UNIQUE KEY t1$acct_id (acct_id), +KEY t1$profile_id (profile_id) +); +INSERT INTO t1 VALUES (132,17),(133,18); +CREATE TABLE t2 ( +profile_id smallint(6) default NULL, +queue_id int(11) default NULL, +seq int(11) default NULL, +KEY t2$queue_id (queue_id) +); +INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); +CREATE TABLE t3 ( +id int(11) NOT NULL default '0', +qtype int(11) default NULL, +seq int(11) default NULL, +warn_lvl int(11) default NULL, +crit_lvl int(11) default NULL, +rr1 tinyint(4) NOT NULL default '0', +rr2 int(11) default NULL, +default_queue tinyint(4) NOT NULL default '0', +KEY t3$qtype (qtype), +KEY t3$id (id) +); +INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), +(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); +SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q +WHERE +(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND +(pq.queue_id = q.id) AND (q.rr1 <> 1); +COUNT(*) +4 +drop table t1,t2,t3; +create table t1 (f1 int); +insert into t1 values (1),(NULL); +create table t2 (f2 int, f3 int, f4 int); +create index idx1 on t2 (f4); +insert into t2 values (1,2,3),(2,4,6); +select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) +from t2 C where A.f4 = C.f4) or A.f3 IS NULL; +f2 +1 +NULL +drop table t1,t2; +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1; +a +0 +254 +255 +drop table t2; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +select found_rows(); +found_rows() +0 +SELECT * FROM t1; +a b c +50 3 3 +select count(*) from t1; +count(*) +1 +select found_rows(); +found_rows() +1 +select count(*) from t1 limit 2,3; +count(*) +select found_rows(); +found_rows() +0 +select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; +count(*) +select found_rows(); +found_rows() +1 +DROP TABLE t1; CREATE TABLE t1 ( city char(30) ); INSERT INTO t1 VALUES ('London'); INSERT INTO t1 VALUES ('Paris'); @@ -2579,25 +2672,6 @@ K2C4 K4N4 F2I4 WART 0100 1 WART 0200 1 WART 0300 3 -select found_rows(); -found_rows() -3 -select count(*) from t1; -count(*) -15 -select found_rows(); -found_rows() -1 -select count(*) from t1 limit 2,3; -count(*) -select found_rows(); -found_rows() -0 -select SQL_CALC_FOUND_ROWS count(*) from t1 limit 2,3; -count(*) -select found_rows(); -found_rows() -1 DROP TABLE t1; CREATE TABLE t1 ( a BLOB, INDEX (a(20)) ); CREATE TABLE t2 ( a BLOB, INDEX (a(20)) ); @@ -2612,51 +2686,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE t2 ref a a 23 test.t1.a 2 DROP TABLE t1, t2; -CREATE TABLE t1 ( city char(30) ); -INSERT INTO t1 VALUES ('London'); -INSERT INTO t1 VALUES ('Paris'); -SELECT * FROM t1 WHERE city='London'; -city -London -SELECT * FROM t1 WHERE city='london'; -city -London -EXPLAIN SELECT * FROM t1 WHERE city='London' AND city='london'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -SELECT * FROM t1 WHERE city='London' AND city='london'; -city -London -EXPLAIN SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -SELECT * FROM t1 WHERE city LIKE '%london%' AND city='London'; -city -London -DROP TABLE t1; -create table t1 (a int(11) unsigned, b int(11) unsigned); -insert into t1 values (1,0), (1,1), (1,2); -select a-b from t1 order by 1; -a-b -0 -1 -18446744073709551615 -select a-b , (a-b < 0) from t1 order by 1; -a-b (a-b < 0) -0 0 -1 0 -18446744073709551615 0 -select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0; -d (a-b >= 0) b -1 1 0 -0 1 1 -18446744073709551615 1 2 -select cast((a - b) as unsigned) from t1 order by 1; -cast((a - b) as unsigned) -0 -1 -18446744073709551615 -drop table t1; create table t1 (a int, b int); create table t2 like t1; select t1.a from (t1 inner join t2 on t1.a=t2.a) where t2.a=1; @@ -2730,77 +2759,3 @@ DROP TABLE t1,t2; select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 16 16 2 2 -CREATE TABLE t1 ( -acct_id int(11) NOT NULL default '0', -profile_id smallint(6) default NULL, -UNIQUE KEY t1$acct_id (acct_id), -KEY t1$profile_id (profile_id) -); -INSERT INTO t1 VALUES (132,17),(133,18); -CREATE TABLE t2 ( -profile_id smallint(6) default NULL, -queue_id int(11) default NULL, -seq int(11) default NULL, -KEY t2$queue_id (queue_id) -); -INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1); -CREATE TABLE t3 ( -id int(11) NOT NULL default '0', -qtype int(11) default NULL, -seq int(11) default NULL, -warn_lvl int(11) default NULL, -crit_lvl int(11) default NULL, -rr1 tinyint(4) NOT NULL default '0', -rr2 int(11) default NULL, -default_queue tinyint(4) NOT NULL default '0', -KEY t3$qtype (qtype), -KEY t3$id (id) -); -INSERT INTO t3 VALUES (30,1,29,NULL,NULL,0,NULL,0),(31,1,28,NULL,NULL,0,NULL,0), -(36,1,34,NULL,NULL,0,NULL,0),(37,1,35,NULL,NULL,0,121,0); -SELECT COUNT(*) FROM t1 a STRAIGHT_JOIN t2 pq STRAIGHT_JOIN t3 q -WHERE -(pq.profile_id = a.profile_id) AND (a.acct_id = 132) AND -(pq.queue_id = q.id) AND (q.rr1 <> 1); -COUNT(*) -4 -drop table t1,t2,t3; -create table t1 (f1 int); -insert into t1 values (1),(NULL); -create table t2 (f2 int, f3 int, f4 int); -create index idx1 on t2 (f4); -insert into t2 values (1,2,3),(2,4,6); -select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) -from t2 C where A.f4 = C.f4) or A.f3 IS NULL; -f2 -1 -NULL -drop table t1,t2; -create table t2 (a tinyint unsigned); -create index t2i on t2(a); -insert into t2 values (0), (254), (255); -explain select * from t2 where a > -1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index -select * from t2 where a > -1; -a -0 -254 -255 -drop table t2; -CREATE TABLE t1 (a int, b int, c int); -INSERT INTO t1 -SELECT 50, 3, 3 FROM DUAL -WHERE NOT EXISTS -(SELECT * FROM t1 WHERE a = 50 AND b = 3); -SELECT * FROM t1; -a b c -50 3 3 -INSERT INTO t1 -SELECT 50, 3, 3 FROM DUAL -WHERE NOT EXISTS -(SELECT * FROM t1 WHERE a = 50 AND b = 3); -SELECT * FROM t1; -a b c -50 3 3 -DROP TABLE t1; diff --git a/mysql-test/r/select_safe.result b/mysql-test/r/select_safe.result index 7a29db42dd9..5d458c40f34 100644 --- a/mysql-test/r/select_safe.result +++ b/mysql-test/r/select_safe.result @@ -60,9 +60,6 @@ a b 3 a 4 a 5 a -SELECT @@MAX_SEEKS_FOR_KEY; -@@MAX_SEEKS_FOR_KEY -4294967295 analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK diff --git a/mysql-test/r/sp-big.result b/mysql-test/r/sp-big.result new file mode 100644 index 00000000000..004ff586aab --- /dev/null +++ b/mysql-test/r/sp-big.result @@ -0,0 +1,15 @@ +drop procedure if exists test.longprocedure; +drop table if exists t1; +create table t1 (a int); +insert into t1 values (1),(2),(3); +length +107520 +select length(routine_definition) from information_schema.routines where routine_schema = 'test' and routine_name = 'longprocedure'; +length(routine_definition) +107530 +call test.longprocedure(@value); +select @value; +@value +3 +drop procedure test.longprocedure; +drop table t1; diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result new file mode 100644 index 00000000000..32d6d4e6264 --- /dev/null +++ b/mysql-test/r/sp-prelocking.result @@ -0,0 +1,215 @@ +drop database if exists mysqltest; +drop table if exists t1, t2, t3, t4; +drop procedure if exists sp1; +drop procedure if exists sp2; +drop procedure if exists sp3; +drop procedure if exists sp4; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +create database mysqltest; +use mysqltest// +create procedure sp1 () +begin +drop table if exists t1; +select 1 as "my-col"; +end; +// +select database(); +database() +mysqltest +call sp1(); +my-col +1 +Warnings: +Note 1051 Unknown table 't1' +select database(); +database() +mysqltest +use test; +select database(); +database() +test +call mysqltest.sp1(); +my-col +1 +Warnings: +Note 1051 Unknown table 't1' +select database(); +database() +test +drop procedure mysqltest.sp1; +drop database mysqltest; +create procedure sp1() +begin +create table t1 (a int); +insert into t1 values (10); +end// +create procedure sp2() +begin +create table t2(a int); +insert into t2 values(1); +call sp1(); +end// +create function f1() returns int +begin +return (select max(a) from t1); +end// +create procedure sp3() +begin +call sp1(); +select 'func', f1(); +end// +call sp1(); +select 't1',a from t1; +t1 a +t1 10 +drop table t1; +call sp2(); +select 't1',a from t1; +t1 a +t1 10 +select 't2',a from t2; +t2 a +t2 1 +drop table t1, t2; +call sp3(); +func f1() +func 10 +select 't1',a from t1; +t1 a +t1 10 +drop table t1; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop function f1; +create procedure sp1() +begin +create temporary table t2(a int); +insert into t2 select * from t1; +end// +create procedure sp2() +begin +create temporary table t1 (a int); +insert into t1 values(1); +call sp1(); +select 't1', a from t1; +select 't2', a from t2; +drop table t1; +drop table t2; +end// +call sp2(); +t1 a +t1 1 +t2 a +t2 1 +drop procedure sp1; +drop procedure sp2; +create table t1 (a int); +insert into t1 values(1),(2); +create table t2 as select * from t1; +create table t3 as select * from t1; +create table t4 as select * from t1; +create procedure sp1(a int) +begin +select a; +end // +create function f1() returns int +begin +return (select max(a) from t1); +end // +CALL sp1(f1()); +a +2 +create procedure sp2(a int) +begin +select * from t3; +select a; +end // +create procedure sp3() +begin +select * from t1; +call sp2(5); +end // +create procedure sp4() +begin +select * from t2; +call sp3(); +end // +call sp4(); +a +1 +2 +a +1 +2 +a +1 +2 +a +5 +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop function f1; +drop view if exists v1; +create function f1(ab int) returns int +begin +declare i int; +set i= (select max(a) from t1 where a < ab) ; +return i; +end // +create function f2(ab int) returns int +begin +declare i int; +set i= (select max(a) from t2 where a < ab) ; +return i; +end // +create view v1 as +select t3.a as x, t4.a as y, f2(3) as z +from t3, t4 where t3.a = t4.a // +create procedure sp1() +begin +declare a int; +set a= (select f1(4) + count(*) A from t1, v1); +end // +create function f3() returns int +begin +call sp1(); +return 1; +end // +call sp1() // +select f3() // +f3() +1 +select f3() // +f3() +1 +call sp1() // +drop procedure sp1// +drop function f3// +create procedure sp1() +begin +declare x int; +declare c cursor for select f1(3) + count(*) from v1; +open c; +fetch c into x; +end;// +create function f3() returns int +begin +call sp1(); +return 1; +end // +call sp1() // +call sp1() // +select f3() // +f3() +1 +call sp1() // +drop table t1,t2,t3; +drop function f1; +drop function f2; +drop function f3; +drop procedure sp1; diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result index e6b8128c336..2f7e8021aa7 100644 --- a/mysql-test/r/sp-threads.result +++ b/mysql-test/r/sp-threads.result @@ -35,7 +35,7 @@ lock tables t2 write; show processlist; Id User Host db Command Time State Info # root localhost test Sleep # NULL -# root localhost test Query # Locked call bug9486() +# root localhost test Query # Locked update t1, t2 set val= 1 where id1=id2 # root localhost test Query # NULL show processlist unlock tables; drop procedure bug9486; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index fd63204e32f..e04f8fce5c4 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -1,10 +1,9 @@ use test; -drop table if exists t1; +drop table if exists t1,t2,t3,t4; create table t1 ( id char(16) not null default '', data int not null ); -drop table if exists t2; create table t2 ( s char(16), i int, @@ -85,7 +84,6 @@ foo 1 kaka 3 delete from t1| drop procedure setcontext| -drop table if exists t3| create table t3 ( d date, i int, f double, s varchar(32) )| drop procedure if exists nullset| create procedure nullset() @@ -521,7 +519,6 @@ select data into x from test.t1 limit 1; insert into test.t3 values ("into4", x); end| delete from t1| -drop table if exists t3| create table t3 ( s char(16), d int)| call into_test4()| Warnings: @@ -565,13 +562,12 @@ 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| -drop table if exists t3| call create_select("cs", 90)| select * from t1, t3| id data id data cs 90 cs 90 cs 90 cs2 92 -drop table if exists t3| +drop table t3| delete from t1| drop procedure create_select| drop function if exists e| @@ -702,7 +698,6 @@ id data hndlr3 13 delete from t1| drop procedure hndlr3| -drop table if exists t3| create table t3 ( id char(16), data int )| drop procedure if exists hndlr4| create procedure hndlr4() @@ -745,7 +740,6 @@ foo 40 bar 15 zap 663 drop procedure cur1| -drop table if exists t3| create table t3 ( s char(16), i int )| drop procedure if exists cur2| create procedure cur2() @@ -1309,7 +1303,6 @@ select t1max()| t1max() 5 drop function t1max| -drop table if exists t3| create table t3 ( v char(16) not null primary key, c int unsigned not null @@ -1430,7 +1423,6 @@ select @1, @2| 2 NULL drop table t70| drop procedure bug1656| -drop table if exists t3| create table t3(a int)| drop procedure if exists bug1862| create procedure bug1862() @@ -1555,7 +1547,6 @@ select @x| 42 drop procedure bug2776_1| drop procedure bug2776_2| -drop table if exists t3| create table t3 (s1 smallint)| insert into t3 values (123456789012)| Warnings: @@ -1616,7 +1607,6 @@ f1 rc t3 drop procedure bug1863| drop temporary table temp_t1; drop table t3, t4| -drop table if exists t3, t4| create table t3 ( OrderID int not null, MarketID int, @@ -1694,7 +1684,6 @@ 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| -drop table if exists t3, t4| create table t3 ( a int primary key, ach char(1) @@ -1724,7 +1713,6 @@ a ach b bch 1 a 1 b drop procedure bug3448| drop table t3, t4| -drop table if exists t3| create table t3 ( id int unsigned auto_increment not null primary key, title VARCHAR(200), @@ -1873,7 +1861,6 @@ select 1+2| 1+2 3 drop procedure bug3843| -drop table if exists t3| create table t3 ( s1 char(10) )| insert into t3 values ('a'), ('b')| drop procedure if exists bug3368| @@ -1889,7 +1876,6 @@ group_concat(v) yz,yz drop procedure bug3368| drop table t3| -drop table if exists t3| create table t3 (f1 int, f2 int)| insert into t3 values (1,1)| drop procedure if exists bug4579_1| @@ -1914,7 +1900,6 @@ Warning 1329 No data to FETCH drop procedure bug4579_1| drop procedure bug4579_2| drop table t3| -drop table if exists t3| drop procedure if exists bug2773| create function bug2773() returns int return null| create table t3 as select bug2773()| @@ -1936,7 +1921,6 @@ select bug3788()| bug3788() 5 drop function bug3788| -drop table if exists t3| create table t3 (f1 int, f2 int, f3 int)| insert into t3 values (1,1,1)| drop procedure if exists bug4726| @@ -2097,7 +2081,6 @@ call bug4902_2()| Id User Host db Command Time State Info # root localhost test Query # NULL show processlist drop procedure bug4902_2| -drop table if exists t3| drop procedure if exists bug4904| create procedure bug4904() begin @@ -2286,7 +2269,6 @@ flush status| flush query cache| delete from t1| drop procedure bug3583| -drop table if exists t3| drop procedure if exists bug4905| create table t3 (s1 int,primary key (s1))| drop procedure if exists bug4905| @@ -2344,7 +2326,6 @@ call bug8540()| y z 1 1 drop procedure bug8540| -drop table if exists t3| create table t3 (s1 int)| drop procedure if exists bug6642| create procedure bug6642() @@ -2427,7 +2408,6 @@ call bug7992_2()| drop procedure bug7992_1| drop procedure bug7992_2| drop table t3| -drop table if exists t3| create table t3 ( userid bigint(20) not null default 0 )| drop procedure if exists bug8116| create procedure bug8116(in _userid int) @@ -2588,7 +2568,6 @@ delete from t1| drop procedure if exists bug6900| drop procedure if exists bug9074| drop procedure if exists bug6900_9074| -drop table if exists t3| create table t3 (w char unique, x char)| insert into t3 values ('a', 'b')| create procedure bug6900() @@ -2658,20 +2637,20 @@ call avg ()| drop procedure avg| drop procedure if exists bug6129| set @old_mode= @@sql_mode; -set @@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 -NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO +ERROR_FOR_DIVISION_BY_ZERO set @@sql_mode= "NO_ZERO_IN_DATE"| call bug6129()| @@sql_mode -NO_ZERO_IN_DATE +ERROR_FOR_DIVISION_BY_ZERO set @@sql_mode=@old_mode; drop procedure bug6129| drop procedure if exists bug9856| @@ -3042,32 +3021,6 @@ drop procedure bug11529| drop procedure if exists bug6063| drop procedure if exists bug7088_1| drop procedure if exists bug7088_2| -create procedure bug6063() -lâbel: begin end| -call bug6063()| -show create procedure bug6063| -Procedure sql_mode Create Procedure -bug6063 CREATE PROCEDURE `test`.`bug6063`() -l?bel: begin end -set character set utf8| -create procedure bug7088_1() -label1: begin end label1| -create procedure bug7088_2() -läbel1: begin end| -call bug7088_1()| -call bug7088_2()| -set character set default| -show create procedure bug7088_1| -Procedure sql_mode Create Procedure -bug7088_1 CREATE PROCEDURE `test`.`bug7088_1`() -label1: begin end label1 -show create procedure bug7088_2| -Procedure sql_mode Create Procedure -bug7088_2 CREATE PROCEDURE `test`.`bug7088_2`() -läbel1: begin end -drop procedure bug6063| -drop procedure bug7088_1| -drop procedure bug7088_2| drop procedure if exists bug9565_sub| drop procedure if exists bug9565| create procedure bug9565_sub() @@ -3099,4 +3052,28 @@ select @@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 table t1,t2; diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 49f118b1d96..6299e8dcc88 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1258,3 +1258,31 @@ select * from t1; d 2000-10-01 drop table t1; +set @@sql_mode='traditional'; +create table t1(a int, b timestamp); +alter table t1 add primary key(a); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + `b` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1(a int, b timestamp default 20050102030405); +alter table t1 add primary key(a); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL default '0', + `b` timestamp NOT NULL default '2005-01-02 03:04:05', + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +set @@sql_mode='traditional'; +create table t1(a bit(2)); +insert into t1 values(b'101'); +ERROR 22001: Data too long for column 'a' at row 1 +select * from t1; +a +drop table t1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 693146c869e..bbca9c905df 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(20) NOT NULL default '0', - `(SELECT 1)` bigint(20) NOT NULL default '0' + `a` bigint(1) NOT NULL default '0', + `(SELECT 1)` bigint(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(20) NOT NULL default '0', - `(SELECT a)` bigint(20) NOT NULL default '0' + `a` bigint(1) NOT NULL default '0', + `(SELECT a)` bigint(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(20) NOT NULL default '0', - `(SELECT a+0)` bigint(20) NOT NULL default '0' + `a` bigint(1) NOT NULL default '0', + `(SELECT a+0)` bigint(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; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 277115733d4..d2872878cb9 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -172,7 +172,7 @@ proc CREATE TABLE `proc` ( `security_type` enum('INVOKER','DEFINER') NOT NULL default 'DEFINER', `param_list` blob NOT NULL, `returns` char(64) NOT NULL default '', - `body` blob NOT NULL, + `body` longblob NOT NULL, `definer` char(77) character set utf8 collate utf8_bin NOT NULL default '', `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `modified` timestamp NOT NULL default '0000-00-00 00:00:00', diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 7e3a6fa65d4..52bf307a686 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -595,3 +595,43 @@ update t1 set col2 = 4; ERROR 42000: FUNCTION test.bug5893 does not exist drop trigger t1_bu; drop table t1; +set sql_mode='ansi'; +create table t1 ("t1 column" int); +create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5; +set sql_mode=""; +insert into t1 values (0); +create trigger t1_af after insert on t1 for each row set @a=10; +insert into t1 values (0); +select * from t1; +t1 column +5 +5 +select @a; +@a +10 +show triggers; +Trigger Event Table Statement Timing Created sql_mode +t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI +t1_af INSERT t1 set @a=10 AFTER # +drop table t1; +set sql_mode="traditional"; +create table t1 (a date); +insert into t1 values ('2004-01-00'); +ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1 +set sql_mode=""; +create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00'; +set sql_mode="traditional"; +insert into t1 values ('2004-01-01'); +select * from t1; +a +2004-01-00 +set sql_mode=default; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` date default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show triggers; +Trigger Event Table Statement Timing Created sql_mode +t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # +drop table t1; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 33c7e837997..788478de77e 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -153,3 +153,13 @@ dt 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table t1; +create table t1 (dt datetime); +insert into t1 values ("20010101T010101"); +insert into t1 values ("2001-01-01T01:01:01"); +insert into t1 values ("2001-1-1T1:01:01"); +select * from t1; +dt +2001-01-01 01:01:01 +2001-01-01 01:01:01 +2001-01-01 01:01:01 +drop table t1; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index c1039189a43..d39d2e3401d 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -946,6 +946,13 @@ t1 CREATE TABLE `t1` ( `sl` decimal(5,5) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +create table t1 (sl decimal(65, 30)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `sl` decimal(65,30) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; create table t1 ( f1 decimal unsigned not null default 17.49, f2 decimal unsigned not null default 17.68, @@ -969,3 +976,13 @@ select * from t1; f1 f2 f3 f4 f5 f6 f7 f8 1 18 99 100 104 200 1000 10000 drop table t1; +create table t1 ( +f0 decimal (30,30) zerofill not null DEFAULT 0, +f1 decimal (0,0) zerofill not null default 0); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f0` decimal(30,30) unsigned zerofill NOT NULL default '0.000000000000000000000000000000', + `f1` decimal(10,0) unsigned zerofill NOT NULL default '0000000000' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index df71c97033f..acdc793fb69 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -183,6 +183,30 @@ set session @honk=99; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@honk=99' at line 1 set one_shot @honk=99; ERROR HY000: The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server +select @@local.max_allowed_packet; +@@local.max_allowed_packet +# +select @@session.max_allowed_packet; +@@session.max_allowed_packet +# +select @@global.max_allowed_packet; +@@global.max_allowed_packet +# +select @@max_allowed_packet; +@@max_allowed_packet +# +select @@Max_Allowed_Packet; +@@Max_Allowed_Packet +# +select @@version; +@@version +# +select @@global.version; +@@global.version +# +select @@session.VERSION; +@@session.VERSION +# set @first_var= NULL; create table t1 select @first_var; show create table t1; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 5468508165c..a7c96d89f0d 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -92,7 +92,7 @@ Variable_name Value max_join_size HA_POS_ERROR set @@max_join_size=1000, @@global.max_join_size=2000; select @@local.max_join_size, @@global.max_join_size; -@@session.max_join_size @@global.max_join_size +@@local.max_join_size @@global.max_join_size 1000 2000 select @@identity, length(@@version)>0; @@identity length(@@version)>0 @@ -428,23 +428,23 @@ Variable_name Value myisam_max_sort_file_size MAX_FILE_SIZE set global myisam_max_sort_file_size=default; select @@global.max_user_connections,@@local.max_join_size; -@@global.max_user_connections @@session.max_join_size +@@global.max_user_connections @@local.max_join_size 100 200 set @svc=@@global.max_user_connections, @svj=@@local.max_join_size; select @@global.max_user_connections,@@local.max_join_size; -@@global.max_user_connections @@session.max_join_size +@@global.max_user_connections @@local.max_join_size 100 200 set @@global.max_user_connections=111,@@local.max_join_size=222; select @@global.max_user_connections,@@local.max_join_size; -@@global.max_user_connections @@session.max_join_size +@@global.max_user_connections @@local.max_join_size 111 222 set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections; select @@global.max_user_connections,@@local.max_join_size; -@@global.max_user_connections @@session.max_join_size +@@global.max_user_connections @@local.max_join_size 222 111 set @@global.max_user_connections=@svc, @@local.max_join_size=@svj; select @@global.max_user_connections,@@local.max_join_size; -@@global.max_user_connections @@session.max_join_size +@@global.max_user_connections @@local.max_join_size 100 200 set @a=1, @b=2; set @a=@b, @b=@a; @@ -525,3 +525,15 @@ set @@warning_count=1; ERROR HY000: Variable 'warning_count' is a read only variable set @@global.error_count=1; ERROR HY000: Variable 'error_count' is a read only variable +set @@max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +@@max_heap_table_size > 0 +1 +set global max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +@@max_heap_table_size > 0 +1 +set @@max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +@@max_heap_table_size > 0 +1 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index c8078a0604e..f6b5018cf3a 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -566,7 +566,7 @@ select * from v1; col1 describe v1; Field Type Null Key Default Extra -col1 varchar(2) YES NULL +col1 char(2) YES NULL drop view v1; drop table `t1a``b`; create table t1 (col1 char(5),col2 char(5)); @@ -581,6 +581,11 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function drop view v1; create view v1 (a,a) as select 'a','a'; ERROR 42S21: Duplicate column name 'a' +drop procedure if exists p1; +create procedure p1 () begin declare v int; create view v1 as select v; end;// +call p1(); +ERROR HY000: View's SELECT contains a variable or parameter +drop procedure p1; create table t1 (col1 int,col2 char(22)); insert into t1 values(5,'Hello, world of views'); create view v1 as select * from t1; @@ -2016,6 +2021,17 @@ CALL p1(); DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +Field Type Null Key Default Extra +f1 tinyint(1) YES NULL +f2 char(1) YES NULL +f3 varchar(1) YES NULL +f4 geometry YES NULL +f5 datetime YES NULL +drop view v1; +drop table t1; create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); create view v1 as select f1, subtime(f1, '1:1:1') as sb from t1; @@ -2024,3 +2040,26 @@ f1 sb 2005-01-01 12:00:00 2005-01-01 10:58:59 drop view v1; drop table t1; +CREATE TABLE t1 ( +aid int PRIMARY KEY, +fn varchar(20) NOT NULL, +ln varchar(20) NOT NULL +); +CREATE TABLE t2 ( +aid int NOT NULL, +pid int NOT NULL +); +INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); +INSERT INTO t2 values (1,1), (2,1), (2,2); +CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; +SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 +WHERE t1.aid = t2.aid GROUP BY pid; +pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) +1 a b,c d +2 c d +SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; +pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) +1 a b,c d +2 c d +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index b77ee59b3ff..71f0f28e59f 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -72,12 +72,12 @@ select c from mysqltest.v4; c show columns from mysqltest.v1; Field Type Null Key Default Extra -c bigint(20) YES NULL -d bigint(20) YES NULL +c bigint(12) YES NULL +d bigint(12) YES NULL show columns from mysqltest.v2; Field Type Null Key Default Extra -c bigint(20) YES NULL -d bigint(20) YES NULL +c bigint(12) YES NULL +d bigint(12) YES NULL explain select c from mysqltest.v1; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table show create view mysqltest.v1; |