summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/group_min_max.result94
-rw-r--r--mysql-test/r/multi_update.result51
-rw-r--r--mysql-test/r/subselect_sj2_mat.result102
3 files changed, 247 insertions, 0 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 34ec9433e85..c8dbfd570c6 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -3186,3 +3186,97 @@ a b
109 19
drop table t1;
End of 5.1 tests
+#
+# MDEV-765: LP:825075 - Wrong result with GROUP BY + multipart key + MIN/MAX loose scan
+#
+CREATE TABLE t1 (a varchar(1), b varchar(1), KEY (b,a));
+INSERT INTO t1 VALUES
+('0',NULL),('9',NULL),('8','c'),('4','d'),('7','d'),(NULL,'f'),
+('7','f'),('8','g'),(NULL,'j');
+explain
+SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index
+SELECT max(a) , b FROM t1 WHERE a IS NULL OR b = 'z' GROUP BY b;
+max(a) b
+NULL f
+NULL j
+explain
+SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index
+SELECT b, min(a) FROM t1 WHERE a = '7' OR b = 'z' GROUP BY b;
+b min(a)
+d 7
+f 7
+explain
+SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index
+SELECT b, min(a) FROM t1 WHERE (a = b OR b = 'd' OR b is NULL) GROUP BY b;
+b min(a)
+NULL 0
+d 4
+explain
+SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref b b 4 const 1 Using where; Using index
+SELECT b, min(a) FROM t1 WHERE a > ('0' = b) AND b = 'z' GROUP BY b;
+b min(a)
+explain
+SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 8 NULL 9 Using where; Using index
+SELECT b, min(a) FROM t1 WHERE a > '0' AND (b < (a = '7')) GROUP BY b;
+b min(a)
+d 7
+f 7
+explain
+SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index b b 8 NULL 9 Using where; Using index
+SELECT b, min(a) FROM t1 WHERE (a > '0' AND (a > '1' OR b = 'd')) GROUP BY b;
+b min(a)
+NULL 9
+c 8
+d 4
+f 7
+g 8
+drop table t1;
+#
+# MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
+#
+CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+CREATE TABLE t2 (c int) ;
+INSERT INTO t2 VALUES (0),(1);
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range NULL b 10 NULL 10 Using where; Using index for group-by
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+MIN(a) b
+1 0
+9 99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 10 NULL 9 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+MIN(a) b
+1 0
+9 99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL b 10 NULL 9 Using where; Using index
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1a index NULL b 10 NULL 9 Using index; Using join buffer (flat, BNL join)
+2 SUBQUERY t1b index NULL b 10 NULL 9 Using index; Using join buffer (incremental, BNL join)
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+MIN(a) b
+1 0
+9 99
+drop table t1, t2;
+End of 5.3 tests
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index f5ae87c69ce..410ce34ee65 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -675,4 +675,55 @@ Warning 1292 Incorrect datetime value: '1'
DROP VIEW v1;
DROP FUNCTION f1;
DROP TABLE t1;
+#
+# MDEV-4123: Incorrect results after multi-table update or
+# assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))' failure
+#
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note 1051 Unknown table 't1'
+CREATE TABLE t1 (
+id int(10) unsigned NOT NULL,
+level tinyint(3) unsigned NOT NULL,
+PRIMARY KEY (id)
+);
+INSERT INTO t1 VALUES (2519583,1);
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t2 (
+club_id int(11) NOT NULL DEFAULT '0',
+profile_id int(11) NOT NULL DEFAULT '0',
+member_level_id int(11) NOT NULL DEFAULT '0',
+PRIMARY KEY (profile_id,club_id)
+);
+INSERT INTO t2 VALUES (2,2519583,12);
+DROP TABLE IF EXISTS t3;
+Warnings:
+Note 1051 Unknown table 't3'
+CREATE TABLE t3 (
+member_level_id int(11) unsigned NOT NULL DEFAULT '0',
+map_level int(11) unsigned NOT NULL DEFAULT '0',
+map_status int(11) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (member_level_id)
+);
+INSERT INTO t3 VALUES (12,12,1);
+CREATE
+VIEW v1 AS
+select club_id,profile_id,
+map_level AS member_level_id,map_status AS member_status
+from (t2 tc join t3 map
+on(((tc.member_level_id = map.member_level_id) and
+(club_id = 2))));
+select level, count(*) as cnt from t1 group by level;
+level cnt
+1 1
+UPDATE t1 c LEFT JOIN v1 t ON (c.id = t.profile_id AND t.club_id = 2)
+SET c.level = IF (t.member_status IS NULL, 1, IF (t.member_status = 1, 2,3));
+select level, count(*) as cnt from t1 group by level;
+level cnt
+2 1
+drop view v1;
+drop table t1,t2,t3;
end of tests
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 3e735a48636..bfae3e57338 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1127,6 +1127,108 @@ AND ( 6 ) IN
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
DROP TABLE t1, t2;
set max_join_size= @tmp_906385;
+#
+# mdev-3995: Wrong result for semijoin with materialization
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (
+cat_id int(10) unsigned NOT NULL,
+PRIMARY KEY (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(709411),(709412),(709413),(709414),(709416),(709417),(709418),(709419),(709421),(709422),
+(709424),(709425),(709427),(709428),(709429),(709431),(709432),(709433),(709434),(709435),
+(709438),(709439),(709441),(709442),(709443),(709444),(709445),(709446),(709447),(709450),
+(709451),(709454),(709455),(709456),(709457),(709459),(709460),(709461),(709462),(709463),
+(709464),(709465),(709467),(709469),(709470),(709471),(709472),(709473),(709474),(709475),
+(709476),(709477),(709478),(709479),(709480),(709481),(709483),(709484),(709485),(709487),
+(709490),(709491),(709492),(709493),(709494),(709495),(709496),(709497),(709498),(709499),
+(709500),(709501),(709502),(709503),(709504),(709505),(709506),(709507),(709509),(709510),
+(709511),(709512),(709513),(709514),(709515),(709516),(709517),(709518),(709519),(709520),
+(709521),(709522),(709523),(709524),(709525),(709526),(709527),(709528),(709529),(709530),
+(709531),(709532),(709533),(709534),(709535),(709536),(709537),(709538),(709539),(709540),
+(709541),(709542),(709543),(709544),(709545),(709546),(709548),(709549),(709551),(709552),
+(709553),(709555),(709556),(709557),(709558),(709559),(709560),(709561),(709562),(709563),
+(709564),(709565),(709566),(709567),(709568),(709569),(709570),(709571),(709572),(709573),
+(709574),(709575),(709576),(709577),(709578),(709579),(709580),(709581),(709582),(709583),
+(709584),(709585),(709586),(709587),(709588),(709590),(709591),(709592),(709593),(709594),
+(709595),(709596),(709597),(709598),(709600),(709601),(709602),(709603),(709604),(709605),
+(709606),(709608),(709609),(709610),(709611),(709612),(709613),(709614),(709615),(709616),
+(709617),(709618),(709619),(709620),(709621),(709622),(709623),(709624),(709625),(709626),
+(709627),(709628),(709629),(709630),(709631),(709632),(709633),(709634),(709635),(709637),
+(709638),(709639),(709640),(709641),(709642),(709643),(709644),(709645),(709646),(709649),
+(709650),(709651),(709652),(709653),(709654),(709655),(709656),(709657),(709658),(709659);
+CREATE TABLE t2 (
+cat_id int(10) NOT NULL,
+KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(708742),(708755),(708759),(708761),(708766),(708769),(708796),(708798),(708824),(708825),
+(708838),(708844),(708861),(708882),(708887),(708889),(708890),(709586),(709626);
+CREATE TABLE t3 (
+sack_id int(10) unsigned NOT NULL,
+kit_id tinyint(3) unsigned NOT NULL DEFAULT '0',
+cat_id int(10) unsigned NOT NULL,
+PRIMARY KEY (sack_id,kit_id,cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(33479,6,708523),(33479,6,708632),(33479,6,709085),(33479,6,709586),(33479,6,709626);
+CREATE TABLE t4 (
+cat_id int(10) unsigned NOT NULL,
+KEY cat_id (cat_id)
+) ENGINE=MyISAM;
+INSERT INTO t4 (cat_id) SELECT cat_id from t2;
+set optimizer_switch='materialization=off';
+EXPLAIN
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t2) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t3)
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.cat_id 1 Using where; Using index
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t2) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+set optimizer_switch='materialization=on';
+EXPLAIN
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t4) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index
+2 MATERIALIZED t4 index cat_id cat_id 4 NULL 19 Using index
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t4) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+EXPLAIN
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t2) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 4 Using index
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index
+2 MATERIALIZED t2 index cat_id cat_id 4 NULL 19 Using index
+SELECT count(*) FROM t1, t3
+WHERE t1.cat_id = t3.cat_id AND
+t3.cat_id IN (SELECT cat_id FROM t2) AND
+t3.sack_id = 33479 AND t3.kit_id = 6;
+count(*)
+2
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch=@save_optimizer_switch;
#
# mdev-3913: LEFT JOIN with materialized multi-table IN subquery in WHERE
#
View Lorry Controller Status