diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/gis.result | 5 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 50 | ||||
| -rw-r--r-- | mysql-test/r/information_schema_db.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/join_nested.result | 55 | ||||
| -rw-r--r-- | mysql-test/r/query_cache.result | 24 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 13 | ||||
| -rw-r--r-- | mysql-test/r/view_grant.result | 8 | ||||
| -rw-r--r-- | mysql-test/t/gis.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/join_nested.test | 48 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 9 |
11 files changed, 199 insertions, 55 deletions
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 e6a929d7e3e..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 @@ -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 @@ -710,18 +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 @@ -756,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; 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/join_nested.result b/mysql-test/r/join_nested.result index 27edac1b30b..1fd7e6f4390 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1343,3 +1343,58 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 0 1 SIMPLE t3 ALL NULL NULL NULL NULL 0 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); +INSERT INTO t1 VALUES (23, 2340), (26, 9900); +CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2)); +INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr'); +create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL); +INSERT INTO t3 VALUES (3,23), (6,26); +CREATE TABLE t4 (groupid int(12)); +INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6); +SELECT * FROM +(SELECT DISTINCT gl.groupid, gp.price +FROM t4 gl +LEFT JOIN +(t3 g INNER JOIN t2 p ON g.goodsid = p.goods +INNER JOIN t1 gp ON p.goods = gp.goods) +ON gl.groupid = g.groupid and p.shop = 'fr') t; +groupid price +1 NULL +2 NULL +3 2340 +4 NULL +5 NULL +6 9900 +CREATE VIEW v1 AS +SELECT g.groupid groupid, p.goods goods, +p.name name, p.shop shop, +gp.price price +FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods +INNER JOIN t1 gp on p.goods = gp.goods; +CREATE VIEW v2 AS +SELECT DISTINCT g.groupid, fr.price +FROM t4 g +LEFT JOIN +v1 fr on g.groupid = fr.groupid and fr.shop = 'fr'; +SELECT * FROM v2; +groupid price +1 NULL +2 NULL +3 2340 +4 NULL +5 NULL +6 9900 +SELECT * FROM +(SELECT DISTINCT g.groupid, fr.price +FROM t4 g +LEFT JOIN +v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t; +groupid price +1 NULL +2 NULL +3 2340 +4 NULL +5 NULL +6 9900 +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 637435cb080..6ff49951d27 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -941,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 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/view.result b/mysql-test/r/view.result index 062c1ac6a8a..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)); @@ -2021,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; 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; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 661616c0a6c..202042d0257 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -373,3 +373,13 @@ insert into t1 values (pointfromtext('point(1,1)')); drop table t1; # End of 4.1 tests + +# +# Bug #12281 (Geometry: crash in trigger) +# + +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; +--error 1048 +insert into t1 values (null,null); +drop table t1; diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 992217d0391..10b2dac5c8b 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -770,3 +770,51 @@ SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; DROP TABLE t1,t2,t3; + +# +# Bug #12154: creation of temp table for a query with nested outer join +# + +CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); +INSERT INTO t1 VALUES (23, 2340), (26, 9900); + +CREATE TABLE t2 (goods int(12), name varchar(50), shop char(2)); +INSERT INTO t2 VALUES (23, 'as300', 'fr'), (26, 'as600', 'fr'); + +create table t3 (groupid int(12) NOT NULL, goodsid int(12) NOT NULL); +INSERT INTO t3 VALUES (3,23), (6,26); + +CREATE TABLE t4 (groupid int(12)); +INSERT INTO t4 VALUES (1), (2), (3), (4), (5), (6); + +SELECT * FROM +(SELECT DISTINCT gl.groupid, gp.price + FROM t4 gl + LEFT JOIN + (t3 g INNER JOIN t2 p ON g.goodsid = p.goods + INNER JOIN t1 gp ON p.goods = gp.goods) + ON gl.groupid = g.groupid and p.shop = 'fr') t; + +CREATE VIEW v1 AS +SELECT g.groupid groupid, p.goods goods, + p.name name, p.shop shop, + gp.price price + FROM t3 g INNER JOIN t2 p ON g.goodsid = p.goods + INNER JOIN t1 gp on p.goods = gp.goods; + +CREATE VIEW v2 AS +SELECT DISTINCT g.groupid, fr.price + FROM t4 g + LEFT JOIN + v1 fr on g.groupid = fr.groupid and fr.shop = 'fr'; + +SELECT * FROM v2; + +SELECT * FROM +(SELECT DISTINCT g.groupid, fr.price + FROM t4 g + LEFT JOIN + v1 fr on g.groupid = fr.groupid and fr.shop = 'fr') t; + +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index cb6939df49c..9885566442f 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -1856,6 +1856,15 @@ DROP VIEW v1; DROP TABLE t1; # +# Bug #11335 View redefines column types +# +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; +drop view v1; +drop table t1; + +# # Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned # subtime() in view create table t1(f1 datetime); |