From 940b88b686bcf037a36f6e81be4017a07fcf782a Mon Sep 17 00:00:00 2001 From: Ajo Robert Date: Tue, 10 Apr 2018 00:30:59 +0530 Subject: Bug#27197235 USER VARIABLE + UINON + DECIMAL COLUMN RETURNS WRONG VALUES User variables will have the default session collation associated with it. And a select which uses it as part of a union may infer the collation while type merging. This leads to problems when the result is of DECIMAL type. Setting the appropriate collation of DECIMAL result type is missing in 5.7 code base. Added code to set appropriate collation when the result is of DECIMAL type during Item_type_holder::join_types(). --- mysql-test/r/union.result | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 97c1a8b4d49..c0b364d0fde 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1860,3 +1860,19 @@ DROP TABLE t17059925, t2, t3; SET @@long_query_time= @old_long_query_time; SET @@global.log_output= @old_log_output; SET @@global.slow_query_log= @old_slow_query_log; +# +# Bug#27197235 USER VARIABLE + UINON + DECIMAL COLUMN RETURNS +# WRONG VALUES +# +SET NAMES utf8; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; +a +1000003.0 +1.0 +SET NAMES latin1; +SET @advertAcctId = 1000003; +select @advertAcctId as a from dual union all select 1.0 from dual; +a +1000003.0 +1.0 -- cgit v1.2.1 From a08508abf83d953f11e4823798398d9229ba0237 Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Tue, 24 Apr 2018 10:02:04 +0530 Subject: Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE Description:- Incorrect granting of EXECUTE and ALTER ROUTINE privileges when the 'automatic_sp_privileges' variable is set. Fix:- EXECUTE and ALTER ROUTINE privileges are correctly granted to the creator of the procedure when the 'automatic_sp_privileges' is SET. --- mysql-test/r/grant.result | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 7603e4b1b1c..e524bb5cac2 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1780,8 +1780,6 @@ BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END ;|| -Warnings: -Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost GRANT USAGE ON *.* TO 'user1'@'localhost' @@ -1791,6 +1789,7 @@ SHOW GRANTS FOR 'user2'; Grants for user2@% GRANT USAGE ON *.* TO 'user2'@'%' GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO 'user2'@'%' DROP PROCEDURE db1.proc1; DROP PROCEDURE db1.proc2; REVOKE ALL ON db1.* FROM 'user1'@'localhost'; -- cgit v1.2.1 From 6d570d729682039edd6c490187a0434e7d75d486 Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Thu, 10 May 2018 10:14:30 +0530 Subject: Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS Description :- Server crashes in show_routine_grants(). Analysis :- When "grant_reload_procs_priv" encounters an error, the grant structures (structures with column, function and procedure privileges) are freed. Server crashes when trying to access these structures later. Fix :- Grant structures are retained even when "grant_reload_procs_priv()" encounters an error while reloading column, function and procedure privileges. --- mysql-test/r/grant.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index e524bb5cac2..d151b58e9ce 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1692,6 +1692,7 @@ revoke create, insert on mysqltest.t6 from mysqltest@localhost; drop user mysqltest@localhost; drop database mysqltest; use test; +call mtr.add_suppression("Can't open and lock privilege tables"); FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; -- cgit v1.2.1 From 937c19318819f63d72a66dac5a60c638386512d6 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 27 Jun 2018 13:17:18 +0300 Subject: Fixed MDEV-16512, crashing on re-execution of failing SP MDEV-16512 Server crashes in find_field_in_table_ref on 2nd execution of SP referring to non-existing field Problem was in the natural join code that it changed TABLE_LIST and Item_fields but didn't restore changed things if things goes wrong and was not able to re-execute after failure. Some of the problems could have been avoided if we would have run fix_fields before doing natural join transformations. Fixed by marking functions complete AFTER they had executed, instead at start. I had also to change some tests that checked if Item_fields are usable. This doesn't fix all known problems, but at least avoids some crashes. What should be done in the near future is to mark the statement in the SP as 'not re-executable' and force a reparse of it on next execution. Reviewer: Sergei Petrunia --- mysql-test/r/join.result | 40 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 40 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 1e51d55f012..7b0e7807e39 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1497,3 +1497,43 @@ Note 1003 select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` DROP VIEW v2; DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; +# +# MDEV-16512 +# Server crashes in find_field_in_table_ref on 2nd execution of SP referring to +# non-existing field +# +CREATE TABLE t (i INT); +CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +FLUSH TABLES; +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP TABLE t; +CREATE TABLE t (f INT); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP TABLE t; +CREATE TABLE t (i INT); +CALL p; +ERROR 42S22: Unknown column 'f' in 'from clause' +DROP PROCEDURE p; +DROP TABLE t; +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, c INT); +CREATE TABLE t4 (a INT, c INT); +CREATE TABLE t5 (a INT, c INT); +CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) +LEFT JOIN t5 USING (a)) USING (a); +CALL p1; +ERROR 23000: Column 'c' in field list is ambiguous +CALL p1; +ERROR 23000: Column 'c' in field list is ambiguous +DROP PROCEDURE p1; +DROP TABLE t1,t2,t3,t4,t5; +# +# End of MariaDB 5.5 tests +# -- cgit v1.2.1 From 90cb7212742e9ae3a63bd183e171c95bd12d559f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 29 Jun 2018 22:46:38 -0700 Subject: MDEV-16603 Crash with set join_cache_level=4 When the definition of the index used for hash join was created in create_hj_key_for_table() it could cause memory overwrite due to a bug that led to an underestimation of the number of the index component. --- mysql-test/r/join_cache.result | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 386f7119bc8..f1e6fb577c8 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5871,4 +5871,37 @@ SET join_buffer_size = default; SET join_buffer_space_limit= default; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; +# +# MDEV-16603: BNLH for query with materialized semi-join +# +set join_cache_level=4; +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), +(228,'x'),(3,'y'),(1,'z'),(9,'z'); +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +f1 f2 +7 x +7 x +7 x +7 x +7 x +7 x +7 x +EXPLAIN EXTENDED SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 1 100.00 +1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where +2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using join buffer (flat, BNLH join) +Warnings: +Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`temp`.`f1` = `test`.`t1`.`i1`) and (`test`.`t2`.`v1` = `test`.`t1`.`v1`) and (`test`.`temp`.`f2` = `test`.`t1`.`v1`)) +DROP TABLE t1,t2,temp; +SET join_cache_level = default; set @@optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1 From 24a0a74f5d92a861989fcea17a0c62c1ee7b1299 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Tue, 10 Jul 2018 13:54:04 +0530 Subject: MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views In this issue we are using derived_with_keys optimization and we are using these keys to do a hash join which is incorrect. We cannot create keys for dervied tables whose keyparts have types are of BLOB or TEXT type. TEXT or BLOB columns can only be indexed over a specified length. --- mysql-test/r/derived.result | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 54c78dc9f6f..5debfaed5e3 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1023,6 +1023,7 @@ INSERT INTO t2 VALUES (NULL),(NULL); CREATE TABLE t3 (c VARCHAR(1024) CHARACTER SET utf8, d INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v3 AS SELECT * FROM t3; INSERT INTO t3 VALUES ('abc',NULL),('def',4); +set @save_join_cache_level= @@join_cache_level; SET join_cache_level= 8; explain SELECT * FROM v1, t2, v3 WHERE a = c AND b = d; @@ -1052,4 +1053,36 @@ i drop procedure pr; drop view v1; drop table t1; +set @@join_cache_level= @save_join_cache_level; +# +# MDEV-16307: Incorrect results when using BNLH join instead of BNL join with views +# +CREATE TABLE t1 (c1 text, c2 int); +INSERT INTO t1 VALUES ('a',1), ('c',3), ('g',7), ('d',4), ('c',3); +CREATE TABLE t2 (c1 text, c2 int); +INSERT INTO t2 VALUES ('b',2), ('c',3); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +1 PRIMARY ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +set @save_join_cache_level= @@join_cache_level; +set @@join_cache_level=4; +explain SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY hash_ALL NULL #hash#$hj 3 test.t2.c1 5 Using where; Using join buffer (flat, BNLH join) +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +SELECT v1.c1, v1.c2, t2.c1, t2.c2 FROM v1, t2 WHERE v1.c1=t2.c1; +c1 c2 c1 c2 +c 3 c 3 +c 3 c 3 +drop table t1,t2; +drop view v1; +set @@join_cache_level= @save_join_cache_level; # end of 5.5 -- cgit v1.2.1 From 2fbf2277ffec86d69f793534da7043b6dd540780 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 11 Jul 2018 10:43:38 +0300 Subject: MDEV-15982: Incorrect results when subquery is materialized fix_semijoin_strategies_for_picked_join_order() should set join->sjm_lookup_tables to be a bitmap of tables inside SJ-Materialization-Lookup nests. --- mysql-test/r/subselect_sj2_mat.result | 51 +++++++++++++++++++++++++++++++++++ 1 file changed, 51 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 19aa3bd3f02..7f97e1aabee 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1658,3 +1658,54 @@ id 12 13 drop table t1; +# +# MDEV-15982: Incorrect results when subquery is materialized +# +CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); +INSERT INTO `t1` VALUES +(45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), +(63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), +(81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), +(99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), +(114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), +(129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), +(147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), +(162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), +(174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), +(7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); +CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); +INSERT INTO `t2` VALUES +(2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), +(1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), +(2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); +CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); +INSERT INTO `t3` VALUES +(1,'incident',31),(2,'faux pas',32), +(5,'oopsies',33),(3,'deniable',34), +(11,'wasntme',35),(10,'wasntme',36), +(17,'faux pas',37),(13,'unlikely',38), +(13,'improbable',39),(14,'incident',40), +(26,'problem',41),(14,'problem',42), +(26,'incident',43),(27,'incident',44); +explain +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 +2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id +10 +11 +set optimizer_switch='materialization=off'; +SELECT t2.id FROM t2,t1 +WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; +id +11 +10 +set optimizer_switch='materialization=on'; +DROP TABLE t1,t2,t3; -- cgit v1.2.1 From ad9d1e8c3f5a8e1b3e222921e825247aa47c4d23 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Wed, 11 Jul 2018 02:28:42 +0530 Subject: MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed Backport the fix f214d365121 to 10.0 Author: Sergei Golubchik Date: Tue Apr 17 00:44:34 2018 +0200 ASAN error in is_stat_table() don't memcmp beyond the first argument's end Also: use my_strcasecmp(table_alias_charset), like elsewhere, not memcmp --- mysql-test/r/stat_tables.result | 7 +++++++ mysql-test/r/stat_tables_innodb.result | 7 +++++++ 2 files changed, 14 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced761283..4a608089d7d 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -516,4 +516,11 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e866755532..87049b228e5 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -543,6 +543,13 @@ use test; drop database db1; drop database db2; drop table t1; +# +# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed +# +SET use_stat_tables = PREFERABLY; +SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); +CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) +NULL set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From c89bb15c31f98d2d368414c7366ce61955b70b44 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Jul 2018 17:48:30 -0700 Subject: MDEV-16757 Memory leak after adding manually min/max statistical data for blob column ANALYZE TABLE does not collect statistical data on min/max values for BLOB columns of
. However these values can be added into mysql.column_stats manually by executing proper statements. Unfortunately this led to a memory leak because the memory allocated for these values was never freed. This patch provides the server with a function to free memory allocated for min/max statistical values of BLOB types. --- mysql-test/r/stat_tables.result | 28 ++++++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 28 ++++++++++++++++++++++++++++ 2 files changed, 56 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 4a608089d7d..e6b675060a5 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -524,3 +524,31 @@ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) NULL set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 87049b228e5..04d73868cfb 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -551,5 +551,33 @@ SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ); CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' ) NULL set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t NULL NULL 0.0000 3.0000 NULL NULL NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From 1fd84f9129f2ed98706f6e225b06b16a13d0ebd0 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Jul 2018 23:03:57 -0700 Subject: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables If the command CREATE OR REPLACE TABLE really replaces a table then it should remove all data on this table from all statistical tables. --- mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index e6b675060a5..c1457d5e91a 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -552,3 +552,28 @@ pk 2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 04d73868cfb..2ac868e9341 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -579,5 +579,30 @@ pk 2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From 095dc81158902380b8618338efabb5ce480dbd79 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 15 Jul 2018 16:24:24 -0700 Subject: MDEV-16757 Memory leak after adding manually min/max statistical data for blob column ANALYZE TABLE
does not collect statistical data on min/max values for BLOB columns of
. However these values can be added into mysql.column_stats manually by executing proper statements. Unfortunately this led to a memory leak because the memory allocated for these values was never freed. This patch provides the server with a function to free memory allocated for min/max statistical values of BLOB types. Temporarily changed the test case until MDEV-16711 is fixed as without this fix the test case for MDEV-16757 did not fail only for 10.0. --- mysql-test/r/stat_tables.result | 24 ++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 24 ++++++++++++++++++++++++ 2 files changed, 48 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index fcced761283..f299603f8f1 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -517,3 +517,27 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 0e866755532..d2d92966d2b 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -544,5 +544,29 @@ drop database db1; drop database db2; drop table t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16757: manual addition of min/max statistics for BLOB +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 't' +test.t1 analyze status OK +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +DELETE FROM mysql.column_stats +WHERE db_name='test' AND table_name='t1' AND column_name='t'; +INSERT INTO mysql.column_stats VALUES +('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From ae0eb507bda858a13475faef4476dd496c4dbc01 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 13 Jul 2018 23:03:57 -0700 Subject: MDEV-16760 CREATE OR REPLACE TABLE never updates statistical tables If the command CREATE OR REPLACE TABLE really replaces a table then it should remove all data on this table from all statistical tables. --- mysql-test/r/stat_tables.result | 25 +++++++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 25 +++++++++++++++++++++++++ 2 files changed, 50 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index f299603f8f1..1f1cad10c23 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -541,3 +541,28 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index d2d92966d2b..fc1ecdf66c4 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -568,5 +568,30 @@ test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT * FROM t1; +pk c +1 foo +2 bar +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL +test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL +CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); +SELECT * FROM t1; +pk a +SELECT * FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From b75d819604bbb6c461e6363e331bd6bd1ba7a787 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sun, 15 Jul 2018 18:40:25 -0700 Subject: MDEV-16711 Crash in Field_blob::store() while reading statistics for the small InnoDB table This bug was introduced by the patch 6c414fcf89510215d6d3466eb9992d444eadae89. The patch has not taken into account that some objects of the Field_* types are created only for TABLE_SHARE and the field 'table' is set to NULL for them. In particular such are objects created to store statistical min/max values for columns. --- mysql-test/r/stat_tables.result | 20 ++++++++++++++++++++ mysql-test/r/stat_tables_innodb.result | 20 ++++++++++++++++++++ 2 files changed, 40 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/stat_tables.result b/mysql-test/r/stat_tables.result index 1f1cad10c23..279c09ff44f 100644 --- a/mysql-test/r/stat_tables.result +++ b/mysql-test/r/stat_tables.result @@ -539,6 +539,10 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # @@ -566,3 +570,19 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index fc1ecdf66c4..1604c413743 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -566,6 +566,10 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL +SELECT pk FROM t1; +pk +1 +2 DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # @@ -593,5 +597,21 @@ SELECT * FROM mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column +# +SET use_stat_tables= PREFERABLY; +CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60)); +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); +SELECT MAX(pk) FROM t1; +MAX(pk) +NULL +DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1 From ab58493db22d870cb6a470d6b0551cfc70dd09f3 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 19 Jul 2018 09:55:19 +0400 Subject: MDEV-13118 Wrong results with LOWER and UPPER and subquery This problem is similar to MDEV-10306. 1. Fixing Item_str_conv::val_str(String *str) to return the result in "str", and to use tmp_value only as a temporary buffer for args[0]->val_str(). The new code version now guarantees that the result is always returned in "str". The trick with copy_if_not_alloced() is not used any more. 2. The change #1 revealed the same problem in SUBSTRING_INDEX(), so some tests with combinations of UPPER()/LOWER() and SUBSTRING_INDEX() started to fail. Fixing Item_func_substr_index::val_str() the same way, to return the result in "str" and use tmp_value as a temporary buffer for args[0]->val_str(). --- mysql-test/r/ctype_binary.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_eucjpms.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_euckr.result | 29 +++++++++++++++++++++++++++++ mysql-test/r/ctype_gbk.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_latin1.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_ucs.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_ujis.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_utf16.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_utf16le.result | 29 +++++++++++++++++++++++++++++ mysql-test/r/ctype_utf32.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_utf8.result | 23 +++++++++++++++++++++++ mysql-test/r/ctype_utf8mb4.result | 23 +++++++++++++++++++++++ 12 files changed, 288 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 48b481883ca..b28d9040547 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -3022,6 +3022,29 @@ DROP TABLE t1; SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; _binary 0x7E _binary X'7E' _binary B'01111110' ~ ~ ~ +SET NAMES utf8, character_set_connection=binary; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varbinary(10) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +abcdefghi-abcdefghi +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_eucjpms.result b/mysql-test/r/ctype_eucjpms.result index a1232c115e9..1e2312638c9 100644 --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@ -33636,6 +33636,29 @@ HEX(a) CHAR_LENGTH(a) DROP TABLE t1; SELECT _eucjpms 0x8EA0; ERROR HY000: Invalid eucjpms character string: '8EA0' +SET NAMES eucjpms; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET eucjpms NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result index dcb68cfe60b..cea93b0c808 100644 --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@ -25274,3 +25274,32 @@ A1A1A1A1A1A120202020202020202020202020202020202020 # # End of 5.6 tests # +# +# Start of 10.0 tests +# +SET NAMES utf8, character_set_connection=euckr; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET euckr NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# End of 10.0 tests +# diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result index 2d31a15bb62..15f9bc6bfea 100644 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@ -4943,6 +4943,29 @@ E05C5B E05B DROP TABLE t1; # Start of ctype_E05C.inc +SET NAMES utf8, character_set_connection=gbk; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET gbk NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant # diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index cf48aaab09b..8913c8082c8 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -7939,6 +7939,29 @@ a 0 DROP VIEW v1; DROP TABLE t1; +SET NAMES latin1; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 59d88414cab..58fd308c7e2 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -5629,6 +5629,29 @@ c2 YWJjZGVmZ2hp-YWJjZGVmZ2hp DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +SET NAMES utf8, character_set_connection=ucs2; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result index 413ab4efe31..d4589f62ae5 100644 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@ -25942,6 +25942,29 @@ HEX(a) CHAR_LENGTH(a) DROP TABLE t1; SELECT _ujis 0x8EA0; ERROR HY000: Invalid ujis character string: '8EA0' +SET NAMES ujis; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET ujis NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result index 958ade902df..e182432faec 100644 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@ -2134,6 +2134,29 @@ EXECUTE stmt USING @arg00; CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; +SET NAMES utf8, character_set_connection=utf16; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result index 8098b0d1666..f6a4d351ad4 100644 --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@ -2319,3 +2319,32 @@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF # # End of 5.6 tests # +# +# Start of 10.0 tests +# +SET NAMES utf8, character_set_connection=utf16le; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf16le NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# Start of 10.0 tests +# diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result index 1f3e519a525..91277cd5108 100644 --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@ -2231,6 +2231,29 @@ EXECUTE stmt USING @arg00; CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; +SET NAMEs utf8, character_set_connection=utf32; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf32 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 7a3ff3732c3..6a4001597c4 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -10127,6 +10127,29 @@ SELECT * FROM v1; c ß DROP VIEW v1; +SET NAMES utf8; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index f31b7a6781e..4d91c42cf51 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -3427,6 +3427,29 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET NAMES default; +SET NAMES utf8mb4; +# +# MDEV-13118 Wrong results with LOWER and UPPER and subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch=_latin1'derived_merge=on'; +CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; +c2 +abcdefghi-abcdefghi +abcdefghi-abcdefghi +SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; +c2 +ABCDEFGHI-ABCDEFGHI +ABCDEFGHI-ABCDEFGHI +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; # End of 10.0 tests # # End of tests -- cgit v1.2.1 From bd5cf02bbe7bce029b0275be1b15d2108806d5e9 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 13 Jul 2018 16:54:47 +0200 Subject: MDEV-11741 handler::ha_reset(): Assertion `bitmap_is_set_all(&table->s->all_set)' failed or server crash in mi_reset or buffer overrun or unexpected ER_CANT_REMOVE_ALL_FIELDS MEMORY table could be renamed into a non-extistent database. rename() is documented to return ENOENT when the source file does not exist OR when the target directory not exist. Nonexistent source .frm file is ok (table can still exist in the engine), nonexistent target directory is not. Make my_rename to use ENOTDIR for the latter case. Make RENAME TABLE issue an appropriate error ("unknown database" instead of "unknown table") --- mysql-test/r/rename.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result index c90aaf24e9b..f019527c951 100644 --- a/mysql-test/r/rename.result +++ b/mysql-test/r/rename.result @@ -133,3 +133,7 @@ select * from t2; a 1 drop table tmp,t2; +create table t1 (a int) engine=memory; +rename table t1 to non_existent.t2; +ERROR 42000: Unknown database 'non_existent' +drop table t1; -- cgit v1.2.1 From 9cea4ccf12cb6e8746b9b440d9c62408a9ef04af Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 19 Jul 2018 15:31:30 -0700 Subject: MDEV-16726 Assertion `tab->type == JT_REF || tab->type == JT_EQ_REF' failed Due to a legacy bug in the code of make_join_statistics() detecting so-called constant tables could miss some of them in rare queries that used RIGHT JOIN. As a result these queries had execution plans different from the execution plans of the equivalent queries with LEFT JOIN. Besides starting from 10.2 this could trigger an assertion failure. --- mysql-test/r/join_outer.result | 50 +++++++++++++++++++++++++++++ mysql-test/r/join_outer_jcl6.result | 50 +++++++++++++++++++++++++++++ mysql-test/r/subselect_mat_cost_bugs.result | 2 +- 3 files changed, 101 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 67b22ca86b2..d55f11cc2f2 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2368,5 +2368,55 @@ id sid id 1 NULL NULL 2 NULL NULL drop table t1, t2; +# +# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +# converted to INNER JOIN with first constant inner table +# +CREATE TABLE t1 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES +(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), +(14,226,'m','m'),(15,133,'p','p'); +CREATE TABLE t2 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t2,t1) +LEFT JOIN +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +v2 +DROP TABLE t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index c019da6197b..8a9b395edff 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2379,6 +2379,56 @@ id sid id 1 NULL NULL 2 NULL NULL drop table t1, t2; +# +# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN +# converted to INNER JOIN with first constant inner table +# +CREATE TABLE t1 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) +) engine=MyISAM; +INSERT INTO t1 VALUES +(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), +(14,226,'m','m'),(15,133,'p','p'); +CREATE TABLE t2 ( +pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) +) engine=MyISAM; +INSERT INTO t2 VALUES (10,6,'p','p'); +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL +EXPLAIN EXTENDED +SELECT STRAIGHT_JOIN t2.v2 +FROM +(t2,t1) +LEFT JOIN +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL +SELECT STRAIGHT_JOIN DISTINCT t2.v2 +FROM +(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) +RIGHT JOIN +(t2,t1) +ON t1.pk = t2.pk AND t2.v2 = tb1.v1 +WHERE tb1.pk = 40 +ORDER BY tb1.i1; +v2 +DROP TABLE t1,t2; # end of 5.5 tests SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index d33f1488e4d..03f4b3e1903 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -442,7 +442,7 @@ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 -2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index +2 DEPENDENT SUBQUERY t2 index i2 i2 11 NULL 2 Using where; Using index DROP TABLE t1,t2,t3; # # MDEV-7599: in-to-exists chosen after min/max optimization -- cgit v1.2.1 From d567f1611e27a17427380e9aae67939792f68ad1 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 24 Jul 2018 20:00:28 -0700 Subject: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ 5 files changed, 80 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index e2f2b6521c8..cdedc02f825 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7171,4 +7171,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 25ef4a76962..a7291297e7c 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7168,6 +7168,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 074874fbd5b..c41fa1be47b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7166,5 +7166,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index de49585b562..1c181357050 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7177,6 +7177,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 46a46c91ddc..89c671252ff 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7166,6 +7166,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; -- cgit v1.2.1 From c631060713a2af2890284feb7aea96c0cf4ba49f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 24 Jul 2018 23:45:55 -0700 Subject: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ 6 files changed, 96 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 79b2810d529..b074fb371a5 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7211,4 +7211,20 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index 7b2054f4a67..d5aa16a2ce9 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7211,6 +7211,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 51c5263ad8a..aff68bd6729 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7204,6 +7204,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 574af7b4d1f..f1181785a5c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7202,5 +7202,21 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 03285cf2968..6cefce21c20 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7217,6 +7217,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 6669c27ede7..884374a74b1 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7202,6 +7202,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; -- cgit v1.2.1 From 1fde449f1d4e8ebe12e989754bfdc84b123cf0b2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 24 Jul 2018 23:45:55 -0700 Subject: MDEV-16820 Lost 'Impossible where' from query with inexpensive subquery This patch fixes another problem introduced by the patch for mdev-4817. The latter changed Item_cond::fix_fields() in such a way that it could call the virtual method is_expensive(). With the first its call the method saves the result in Item::is_expensive_cache. For all next calls the method returns the result from this cache. So if the item once was determined as expensive the method always returns true. For subqueries it's not good, because non-optimized subqueries always is considered as expensive. It means that the cache should be invalidated after the call of optimize_constant_subqueries(). --- mysql-test/r/subselect.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_mat.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_opts.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_scache.result | 16 ++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 16 ++++++++++++++++ 6 files changed, 96 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 40f936fb3b4..69db0959373 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index a23820820ef..ef3bf7ef692 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7235,6 +7235,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index baa74307f89..703572e0ef8 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7228,6 +7228,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 039f2fe1a9e..39a098b754b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 0ce77bbb376..b9a59bf3952 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7241,6 +7241,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 574e78122f1..f1df85fed6e 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7226,6 +7226,22 @@ a 5 SET @@optimizer_switch= @optimiser_switch_save; DROP TABLE t1, t2, t3; +# +# MDEV-16820: impossible where with inexpensive subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (1), (7); +create table t2 (b int, index idx(b)); +insert into t2 values (2), (5), (3), (2); +explain select * from t1 where (select max(b) from t2) = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +explain select * from t1 where (select max(b) from t2) = 10 and t1.a > 3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; End of 5.5 tests # End of 10.0 tests # -- cgit v1.2.1 From f9b43c2565284feefe94e4feaa4c02bf25a6f921 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Wed, 25 Jul 2018 14:20:16 +0530 Subject: MDEV-16751: Server crashes in st_join_table::cleanup or TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 During muliple equality propagation for a query in which we have an IN subquery, the items in the select list of the subquery may not be part of the multiple equality because there might be another occurence of the same field in the where clause of the subquery. So we keyuse_is_valid_for_access_in_chosen_plan function which expects the items in the select list of the subquery to be same to the ones in the multiple equality (through these multiple equalities we create keyuse array). The solution would be that we expect the same field not the same Item because when we have SEMI JOIN MATERIALIZATION SCAN, we use copy back technique to copies back the materialised table fields to the original fields of the base tables. --- mysql-test/r/subselect_mat.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 35 +++++++++++++++++++++++++++++++++++ 2 files changed, 70 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 00448ac4f91..a56d076d528 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2360,6 +2360,41 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index cb5012a91c9..fa62259e3d6 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2400,4 +2400,39 @@ ec70316637232000158bbfc8bcbe5d60 ebb4620037332000158bbfc8bcbe5d89 DROP TABLE t1,t2,t3; set optimizer_switch=@save_optimizer_switch; +# +# MDEV-16751: Server crashes in st_join_table::cleanup or +# TABLE_LIST::is_with_table_recursive_reference with join_cache_level>2 +# +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=4; +CREATE TABLE t1 ( id int NOT NULL); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL) ; +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +set @@join_cache_level= @save_join_cache_level; +alter table t1 add key(id); +explain +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); +1 +1 +1 +1 +drop table t1,t2; # End of 5.5 tests -- cgit v1.2.1 From 37dee22d27c55c14f0be7005c3e5bdbb09e1fc92 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Wed, 25 Jul 2018 21:17:50 +0530 Subject: MDEV-15454: Nested SELECT IN returns wrong results In this case we are setting the field Item_func_eq::in_eqaulity_no for the semi-join equalities. This helps us to remove these equalites as the inner tables are not available during parent select execution while the outer tables are not available during materialization phase. We only have it set for the equalites for the fields involved with the IN subquery and reset it for the equalities which do not belong to the IN subquery. For example in case of nested IN subqueries: SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 where t2.b IN (select t3.b from t3 where t3.c=27 )) there are two equalites involving the fields of the IN subquery 1) t2.b = t3.b : the field Item_func_eq::in_eqaulity_no is set when we merge the grandchild select into the child select 2) t1.a = t2.a : the field Item_func_eq::in_eqaulity_no is set when we merge the child select into the parent select But when we perform case 2) we should ensure that we reset the equalities in the child's WHERE clause. --- mysql-test/r/subselect_mat.result | 41 ++++++++++++++++++++++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 41 ++++++++++++++++++++++++++++++++++++ 2 files changed, 82 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index a56d076d528..7f39f1fb788 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2395,6 +2395,47 @@ SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 1 drop table t1,t2; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); +a +7878 +3465 +1403 +4189 +8732 +5 +set @save_optimizer_switch= @@optimizer_switch; +SET optimizer_switch='materialization=off'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2,t3; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fa62259e3d6..44006eadfc6 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2435,4 +2435,45 @@ SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 1 drop table t1,t2; +# +# MDEV-15454: Nested SELECT IN returns wrong results +# +CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY); +CREATE TABLE t2 ( a int, b int ); +INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); +CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b)); +INSERT INTO t3 (c, b) VALUES (27, 96); +CREATE PROCEDURE prepare_data() +BEGIN +DECLARE i INT DEFAULT 1; +WHILE i < 1000 DO +INSERT INTO t1 (a) VALUES (i); +INSERT INTO t2 (a,b) VALUES (i,56); +INSERT INTO t3 (c,b) VALUES (i,i); +SET i = i + 1; +END WHILE; +END$$ +CALL prepare_data(); +SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27); +a +7878 +3465 +1403 +4189 +8732 +5 +set @save_optimizer_switch= @@optimizer_switch; +SET optimizer_switch='materialization=off'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +SET optimizer_switch='materialization=on'; +SELECT t1.a FROM t1 +WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5; +a +5 +drop procedure prepare_data; +set @@optimizer_switch= @save_optimizer_switch; +drop table t1,t2,t3; # End of 5.5 tests -- cgit v1.2.1 From bd0b368119b48ffbb1e5ab3cd2887270c5c6840e Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Fri, 27 Jul 2018 11:34:34 +0530 Subject: Fix added along with a test for a case missed in the patch for MDEV-16751 --- mysql-test/r/subselect_mat.result | 17 +++++++++++++++++ mysql-test/r/subselect_sj_mat.result | 17 +++++++++++++++++ 2 files changed, 34 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 7f39f1fb788..eca3b760b65 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2436,6 +2436,23 @@ a drop procedure prepare_data; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 44006eadfc6..180c182a51a 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2476,4 +2476,21 @@ a drop procedure prepare_data; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests -- cgit v1.2.1