Skip to content

Commit a24950a

Browse files
author
Guilhem Bichot
committed
Fix for Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
We have a query with WHERE ... NOT IN (SELECT ...). The inner field is 1503 bytes (500 chars * 3, because it has utf8 charset). subquery_allows_materialization() considers subq-mat: per the fix for Bug 17566396: MATERIALIZATION IS NOT CHOSEN FOR LONG UTF8 VARCHAR FIELD, this field is small enough to _not_ be changed to a BLOB when stored into the tmp table (Item::is_blob_field() sees that 500 < CONVERT_IF_BIGGER_TO_BLOB), so subq-mat is chosen. Before that fix, the left operand of the inequality comparison would be the length in _bytes_ and subq-mat would not be chosen. In hash_sj_engine::setup(): create_tmp_table() uses MEMORY table with a regular unique index, without "unique constraint"; right after creation, setup() verifies that the table has no "unique constraint", otherwise subq-mat would fail - see comment in setup() and see how indexsubquery_engine::setup() does a regular index lookup of a key made with copy_ref_key(): to look up into "unique constraint" it would need to compute a checksum in copy_ref_key(). So far so good. At some point during materialization, MEMORY tmp table becomes full, we go into create_myisam_from_heap() then create_myisam_tmp_table(): if (keyinfo->key_length >= table->file->max_key_length() || keyinfo->user_defined_key_parts > table->file->max_key_parts() || share->uniques) { /* Can't create a key; Make a unique constraint instead of a key */ share->keys= 0; share->uniques= 1; using_unique_constraint=1; alas the first condition is 1503 > 1000 (engine is MyISAM) so we create "unique constraint" on the MyISAM table. Subq-mat is not ready for this, as we know, so we get a bad result: in fact, all look ups then return "not found", so "NOT IN" always returns TRUE. In 5.7, subq-mat has been made able to use "unique constraint" (see Bug 18227171 which was part of wl 6711); however this same testcase still crashes but for different reasons, a separate 5.7 patch will be made. For fixing 5.6: - we cannot simply undo the fix for Bug 17566396 as there was an associated customer - on the other hand we cannot modify the logic in create_myisam_tmp_table() to _not_ create a "unique constraint" in this case (it's 5.6, we must be conservative) - proposal: take a middle path: make subquery_allows_materialization() test the length (by extending is_blob_field()): if length is >=1000 bytes, then don't use subq-mat (because there is risk that MyISAM table is used and creates "unique constraint"). 1000 bytes is 333 utf8 characters. So we would have, for the limit of # of chars: - before the fix for Bug 17566396: 512/3 = 170 chars, - after it: 512 chars - after the proposed fix: 332 chars. The testcase in Bug 17566396 used 250 chars so still uses subq-mat, which is good.
1 parent c3e2977 commit a24950a

File tree

5 files changed

+225
-3
lines changed

5 files changed

+225
-3
lines changed

‎mysql-test/include/subquery_mat.inc‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1417,6 +1417,53 @@ eval $query;
14171417

14181418
DROP TABLE t1,t2,t3;
14191419

1420+
--echo #
1421+
--echo # Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
1422+
--echo #
1423+
1424+
CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB;
1425+
1426+
# Fill the table with 32 distinct long rows (>32kB of data)
1427+
SET @str= repeat("a",450);
1428+
SET @num=1000;
1429+
INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str));
1430+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1431+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1432+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1433+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1434+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1435+
SELECT COUNT(*) FROM t1;
1436+
1437+
ANALYZE TABLE t1;
1438+
1439+
let $query=
1440+
SELECT COUNT(*)
1441+
FROM t1
1442+
WHERE t1.a NOT IN (
1443+
SELECT t2.a FROM t1 as t2
1444+
);
1445+
1446+
# If subq-mat is used, this will force MEMORY->MYISAM tmp table conversion:
1447+
set @save_heap_size= @@max_heap_table_size;
1448+
set @@max_heap_table_size= 16384;
1449+
1450+
# Uses IN->EXISTS because field too long for subq-materialization:
1451+
eval EXPLAIN $query;
1452+
eval $query;
1453+
1454+
# Make field just below the length limit:
1455+
# Silence truncation warnings:
1456+
--disable_warnings
1457+
ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
1458+
--enable_warnings
1459+
1460+
# Now subquery materialization can be used, and result is still correct:
1461+
eval EXPLAIN $query;
1462+
eval $query;
1463+
1464+
DROP TABLE t1;
1465+
set @@max_heap_table_size= @save_heap_size;
1466+
14201467
--echo # End of 5.6 tests
14211468

14221469
set @@optimizer_switch=@old_opt_switch;

‎mysql-test/r/subquery_mat.result‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1863,6 +1863,59 @@ ORDER BY innr.c4)
18631863
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
18641864
x
18651865
DROP TABLE t1,t2,t3;
1866+
#
1867+
# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
1868+
#
1869+
CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB;
1870+
SET @str= repeat("a",450);
1871+
SET @num=1000;
1872+
INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str));
1873+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1874+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1875+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1876+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1877+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1878+
SELECT COUNT(*) FROM t1;
1879+
COUNT(*)
1880+
32
1881+
ANALYZE TABLE t1;
1882+
Table Op Msg_type Msg_text
1883+
test.t1 analyze status OK
1884+
set @save_heap_size= @@max_heap_table_size;
1885+
set @@max_heap_table_size= 16384;
1886+
EXPLAIN SELECT COUNT(*)
1887+
FROM t1
1888+
WHERE t1.a NOT IN (
1889+
SELECT t2.a FROM t1 as t2
1890+
);
1891+
id select_type table type possible_keys key key_len ref rows Extra
1892+
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1893+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 32 Using where
1894+
SELECT COUNT(*)
1895+
FROM t1
1896+
WHERE t1.a NOT IN (
1897+
SELECT t2.a FROM t1 as t2
1898+
);
1899+
COUNT(*)
1900+
0
1901+
ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
1902+
EXPLAIN SELECT COUNT(*)
1903+
FROM t1
1904+
WHERE t1.a NOT IN (
1905+
SELECT t2.a FROM t1 as t2
1906+
);
1907+
id select_type table type possible_keys key key_len ref rows Extra
1908+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
1909+
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 NULL
1910+
SELECT COUNT(*)
1911+
FROM t1
1912+
WHERE t1.a NOT IN (
1913+
SELECT t2.a FROM t1 as t2
1914+
);
1915+
COUNT(*)
1916+
0
1917+
DROP TABLE t1;
1918+
set @@max_heap_table_size= @save_heap_size;
18661919
# End of 5.6 tests
18671920
set @@optimizer_switch=@old_opt_switch;
18681921
set optimizer_switch=default;

‎mysql-test/r/subquery_mat_all.result‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1876,6 +1876,59 @@ ORDER BY innr.c4)
18761876
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
18771877
x
18781878
DROP TABLE t1,t2,t3;
1879+
#
1880+
# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
1881+
#
1882+
CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB;
1883+
SET @str= repeat("a",450);
1884+
SET @num=1000;
1885+
INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str));
1886+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1887+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1888+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1889+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1890+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1891+
SELECT COUNT(*) FROM t1;
1892+
COUNT(*)
1893+
32
1894+
ANALYZE TABLE t1;
1895+
Table Op Msg_type Msg_text
1896+
test.t1 analyze status OK
1897+
set @save_heap_size= @@max_heap_table_size;
1898+
set @@max_heap_table_size= 16384;
1899+
EXPLAIN SELECT COUNT(*)
1900+
FROM t1
1901+
WHERE t1.a NOT IN (
1902+
SELECT t2.a FROM t1 as t2
1903+
);
1904+
id select_type table type possible_keys key key_len ref rows Extra
1905+
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1906+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 32 Using where
1907+
SELECT COUNT(*)
1908+
FROM t1
1909+
WHERE t1.a NOT IN (
1910+
SELECT t2.a FROM t1 as t2
1911+
);
1912+
COUNT(*)
1913+
0
1914+
ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
1915+
EXPLAIN SELECT COUNT(*)
1916+
FROM t1
1917+
WHERE t1.a NOT IN (
1918+
SELECT t2.a FROM t1 as t2
1919+
);
1920+
id select_type table type possible_keys key key_len ref rows Extra
1921+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
1922+
2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 NULL
1923+
SELECT COUNT(*)
1924+
FROM t1
1925+
WHERE t1.a NOT IN (
1926+
SELECT t2.a FROM t1 as t2
1927+
);
1928+
COUNT(*)
1929+
0
1930+
DROP TABLE t1;
1931+
set @@max_heap_table_size= @save_heap_size;
18791932
# End of 5.6 tests
18801933
set @@optimizer_switch=@old_opt_switch;
18811934
set optimizer_switch=default;

‎mysql-test/r/subquery_mat_none.result‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1862,6 +1862,59 @@ ORDER BY innr.c4)
18621862
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
18631863
x
18641864
DROP TABLE t1,t2,t3;
1865+
#
1866+
# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
1867+
#
1868+
CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8) ENGINE=INNODB;
1869+
SET @str= repeat("a",450);
1870+
SET @num=1000;
1871+
INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str));
1872+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1873+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1874+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1875+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1876+
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
1877+
SELECT COUNT(*) FROM t1;
1878+
COUNT(*)
1879+
32
1880+
ANALYZE TABLE t1;
1881+
Table Op Msg_type Msg_text
1882+
test.t1 analyze status OK
1883+
set @save_heap_size= @@max_heap_table_size;
1884+
set @@max_heap_table_size= 16384;
1885+
EXPLAIN SELECT COUNT(*)
1886+
FROM t1
1887+
WHERE t1.a NOT IN (
1888+
SELECT t2.a FROM t1 as t2
1889+
);
1890+
id select_type table type possible_keys key key_len ref rows Extra
1891+
1 PRIMARY t1 ALL NULL NULL NULL NULL 32 Using where
1892+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 32 Using where
1893+
SELECT COUNT(*)
1894+
FROM t1
1895+
WHERE t1.a NOT IN (
1896+
SELECT t2.a FROM t1 as t2
1897+
);
1898+
COUNT(*)
1899+
0
1900+
ALTER TABLE t1 MODIFY a VARCHAR(332) CHARACTER SET UTF8;
1901+
EXPLAIN SELECT COUNT(*)
1902+
FROM t1
1903+
WHERE t1.a NOT IN (
1904+
SELECT t2.a FROM t1 as t2
1905+
);
1906+
id select_type table type possible_keys key key_len ref rows Extra
1907+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
1908+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
1909+
SELECT COUNT(*)
1910+
FROM t1
1911+
WHERE t1.a NOT IN (
1912+
SELECT t2.a FROM t1 as t2
1913+
);
1914+
COUNT(*)
1915+
0
1916+
DROP TABLE t1;
1917+
set @@max_heap_table_size= @save_heap_size;
18651918
# End of 5.6 tests
18661919
set @@optimizer_switch=@old_opt_switch;
18671920
set optimizer_switch=default;

‎sql/item.cc‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1516,9 +1516,25 @@ bool Item::is_blob_field() const
15161516
DBUG_ASSERT(fixed);
15171517

15181518
enum_field_types type= field_type();
1519-
return (type == MYSQL_TYPE_BLOB || type == MYSQL_TYPE_GEOMETRY ||
1520-
// Char length, not the byte one, should be taken into account
1521-
max_length/collation.collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB);
1519+
if (type == MYSQL_TYPE_BLOB || type == MYSQL_TYPE_GEOMETRY ||
1520+
// Char length, not the byte one, should be taken into account
1521+
(max_length/collation.collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB))
1522+
return true;
1523+
1524+
/*
1525+
This function is used only for subquery/semijoin materialization;
1526+
these are not able to handle a tmp table with share->uniques==true; it is
1527+
possible, if the tmp table has many rows, that it becomes a MyISAM table,
1528+
which will have share->uniques==true if the column's length in bytes is
1529+
bigger than MI_MAX_KEY_LENGTH.
1530+
So we add and extra restriction, only in MySQL 5.6: if longer than this,
1531+
pretend it's a blob, to disable materialization:
1532+
*/
1533+
if (max_length >=
1534+
(MI_MAX_KEY_LENGTH - HA_KEY_NULL_LENGTH - HA_KEY_BLOB_LENGTH))
1535+
return true;
1536+
1537+
return false;
15221538
}
15231539

15241540

0 commit comments

Comments
 (0)