File: hash_join.result

package info (click to toggle)
mysql-8.0 8.0.43-3
links: PTS, VCS
area: main
in suites: sid
size: 1,273,924 kB
sloc: cpp: 4,684,605; ansic: 412,450; pascal: 108,398; java: 83,641; perl: 30,221; cs: 27,067; sql: 26,594; sh: 24,181; python: 21,816; yacc: 17,169; php: 11,522; xml: 7,388; javascript: 7,076; makefile: 2,194; lex: 1,075; awk: 670; asm: 520; objc: 183; ruby: 97; lisp: 86
file content (4364 lines) | stat: -rw-r--r-- 131,132 bytes
#
# Start out by testing some simple in-memory inner hash joins.
#
# Join on two integer columns.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t2 VALUES (1), (2), (5), (6);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
EXPLAIN
-> Sort: t1.col1
    -> Stream results  (cost=2.5 rows=4)
        -> Inner hash join (t2.col1 = t1.col1)  (cost=2.5 rows=4)
            -> Table scan on t2  (cost=0.0875 rows=4)
            -> Hash
                -> Table scan on t1  (cost=0.65 rows=4)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
col1	col1
1	1
5	5
DROP TABLE t1, t2;
# Join on a integer column and a string column.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 VARCHAR(255));
INSERT INTO t1 VALUES (1), (3), (5), (7);
INSERT INTO t2 VALUES (1), (2), (5), (6);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
EXPLAIN
-> Sort: t1.col1
    -> Stream results  (cost=2.5 rows=4)
        -> Inner hash join (cast(t1.col1 as double) = cast(t2.col1 as double))  (cost=2.5 rows=4)
            -> Table scan on t2  (cost=0.0875 rows=4)
            -> Hash
                -> Table scan on t1  (cost=0.65 rows=4)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
col1	col1
1	1
5	5
DROP TABLE t1, t2;
# Join on two datetime columns.
CREATE TABLE t1 (col1 DATETIME(6));
CREATE TABLE t2 (col1 DATETIME(6));
INSERT INTO t1 VALUES ('2018-01-01 00:00:00.000000'),
('2018-01-01 00:00:00.000001'),
('2018-01-02 00:00:00.000000'),
('2018-01-02 00:00:00.000001'),
(NULL), (NULL);
INSERT INTO t2 VALUES ('2018-01-01 00:00:00.000000'),
('2018-01-01 00:00:00.000002'),
('2018-01-02 00:00:00.000001'),
('2019-01-02 00:00:00.000001'),
(NULL), (NULL), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 = t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
col1	col1
2018-01-01 00:00:00.000000	2018-01-01 00:00:00.000000
2018-01-02 00:00:00.000001	2018-01-02 00:00:00.000001
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 <=> t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1 ORDER BY t1.col1;
col1	col1
2018-01-01 00:00:00.000000	2018-01-01 00:00:00.000000
2018-01-02 00:00:00.000001	2018-01-02 00:00:00.000001
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
DROP TABLE t1, t2;
# Join on a string and datetime column, where datetime comparison is
# picked.
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'), (NULL);
CREATE TABLE t2 (b VARCHAR(64));
INSERT INTO t2 VALUES ('2001#01#01'), (NULL), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE a=b;
EXPLAIN
-> Inner hash join (t1.a = cast(t2.b as datetime(6)))
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1, t2 WHERE a=b;
a	b
2001-01-01 00:00:00	2001#01#01
Warnings:
Warning	4095	Delimiter '#' in position 4 in datetime value '2001#01#01' at row 1 is deprecated. Prefer the standard '-'.
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE a<=>b;
EXPLAIN
-> Inner hash join (t1.a <=> cast(t2.b as datetime(6)))
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1, t2 WHERE a<=>b;
a	b
2001-01-01 00:00:00	2001#01#01
NULL	NULL
NULL	NULL
Warning	4095	Delimiter '#' in position 4 in datetime value '2001#01#01' at row 1 is deprecated. Prefer the standard '-'.
Warnings:
DROP TABLE t1, t2;
# Join on two double columns.
CREATE TABLE t1 (col1 DOUBLE);
CREATE TABLE t2 (col1 DOUBLE);
INSERT INTO t1 VALUES (0), (1.1), (3.3), (5.5), (7.7), (NULL);
INSERT INTO t2 VALUES (0), (1.1), (1.11), (5.5), (6.6), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t2.col1 = t1.col1)  (cost=4.7 rows=6)
    -> Table scan on t2  (cost=0.0583 rows=6)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
col1	col1
0	0
1.1	1.1
5.5	5.5
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 <=> t2.col1)  (cost=4.7 rows=6)
    -> Table scan on t2  (cost=0.0583 rows=6)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1;
col1	col1
0	0
1.1	1.1
5.5	5.5
NULL	NULL
DROP TABLE t1, t2;
# Join on two decimal columns.
CREATE TABLE t1 (col1 DECIMAL(6, 2));
CREATE TABLE t2 (col1 DECIMAL(6, 2));
INSERT INTO t1 VALUES (0), (1.1), (3.3), (5.5), (7.7), (NULL);
INSERT INTO t2 VALUES (0), (1.1), (1.10), (5.5), (6.6), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t2.col1 = t1.col1)  (cost=4.7 rows=6)
    -> Table scan on t2  (cost=0.0583 rows=6)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 = t2.col1 ORDER BY t1.col1;
col1	col1
0.00	0.00
1.10	1.10
1.10	1.10
5.50	5.50
EXPLAIN FORMAT=tree
SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 <=> t2.col1)  (cost=4.7 rows=6)
    -> Table scan on t2  (cost=0.0583 rows=6)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t1.col1 <=> t2.col1;
col1	col1
0.00	0.00
1.10	1.10
1.10	1.10
5.50	5.50
NULL	NULL
DROP TABLE t1, t2;
# See that comparison between decimal and bigint works well. The main
# challenge is that decimals with different amount of leading/trailing
# zeroes should compare equally.
CREATE TABLE t1 (col1 BIGINT);
CREATE TABLE t2 (col1 DECIMAL(64,30));
INSERT INTO t1 VALUES (0), (1), (5), (NULL);
INSERT INTO t2 VALUES (0), (0.1), (0.000000000000000000000000000001),
(5.000000000000000000000000000000), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1,t2 WHERE t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 = t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1,t2 WHERE t1.col1 = t2.col1;
col1	col1
0	0.000000000000000000000000000000
5	5.000000000000000000000000000000
EXPLAIN FORMAT=tree SELECT * FROM t1,t2 WHERE t1.col1 <=> t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 <=> t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1,t2 WHERE t1.col1 <=> t2.col1;
col1	col1
0	0.000000000000000000000000000000
5	5.000000000000000000000000000000
NULL	NULL
DROP TABLE t1, t2;
CREATE TABLE t1 (col1 DECIMAL(5));
CREATE TABLE t2 (col1 BIGINT);
INSERT INTO t1 VALUES (1), (NULL);
INSERT INTO t2 VALUES (1), (0), (NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1,t2 where t1.col1=t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 = t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1,t2 where t1.col1=t2.col1;
col1	col1
1	1
EXPLAIN FORMAT=tree SELECT * FROM t1,t2 where t1.col1<=>t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 <=> t2.col1)
    -> Table scan on t2
    -> Hash
        -> Table scan on t1

SELECT * FROM t1,t2 where t1.col1<=>t2.col1;
col1	col1
1	1
NULL	NULL
DROP TABLE t1, t2;
# Bit fields, which is a bit different depending on the storage engine.
create table t1 (id1 int, b1 bit(1)) engine = myisam;
create table t2 (id2 int, b2 bit(1)) engine = myisam;
insert into t1 values (2, 0), (3, 1), (null, null);
insert into t2 values (2, 1), (3, 0), (null, null);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE id1 = id2;
EXPLAIN
-> Inner hash join (t2.id2 = t1.id1)  (cost=2.2 rows=3)
    -> Table scan on t2  (cost=0.2 rows=3)
    -> Hash
        -> Table scan on t1  (cost=0.801 rows=3)

SELECT id1, HEX(b1), id2, HEX(b2) FROM t1, t2 WHERE id1 = id2;
id1	HEX(b1)	id2	HEX(b2)
2	0	2	1
3	1	3	0
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE id1 <=> id2;
EXPLAIN
-> Inner hash join (t1.id1 <=> t2.id2)  (cost=2.2 rows=3)
    -> Table scan on t2  (cost=0.2 rows=3)
    -> Hash
        -> Table scan on t1  (cost=0.801 rows=3)

SELECT id1, HEX(b1), id2, HEX(b2) FROM t1, t2 WHERE id1 <=> id2;
id1	HEX(b1)	id2	HEX(b2)
2	0	2	1
3	1	3	0
NULL	NULL	NULL	NULL
DROP TABLE t1, t2;
create table t1 (id1 int, b1 bit(64)) engine = innodb;
create table t2 (id2 int, b2 bit(64)) engine = innodb;
insert into t1 values (2, 0), (3, 2), (null, null);
insert into t2 values (2, 2), (3, 0), (null, null);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE id1 = id2;
EXPLAIN
-> Inner hash join (t2.id2 = t1.id1)  (cost=1.7 rows=3)
    -> Table scan on t2  (cost=0.117 rows=3)
    -> Hash
        -> Table scan on t1  (cost=0.55 rows=3)

SELECT id1, HEX(b1), id2, HEX(b2) FROM t1, t2 WHERE id1 = id2;
id1	HEX(b1)	id2	HEX(b2)
2	0	2	2
3	2	3	0
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE id1 <=> id2;
EXPLAIN
-> Inner hash join (t1.id1 <=> t2.id2)  (cost=1.7 rows=3)
    -> Table scan on t2  (cost=0.117 rows=3)
    -> Hash
        -> Table scan on t1  (cost=0.55 rows=3)

SELECT id1, HEX(b1), id2, HEX(b2) FROM t1, t2 WHERE id1 <=> id2;
id1	HEX(b1)	id2	HEX(b2)
2	0	2	2
3	2	3	0
NULL	NULL	NULL	NULL
DROP TABLE t1, t2;
# See that we handle NULL values properly.
CREATE TABLE t1 (col1 VARCHAR(255));
CREATE TABLE t2 (col1 VARCHAR(255));
INSERT INTO t1 VALUES (NULL), ('a');
INSERT INTO t2 VALUES (''), (NULL), ('x'), ('y');
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1;
EXPLAIN
-> Filter: (t2.col1 = t1.col1)  (cost=1.5 rows=2)
    -> Inner hash join (<hash>(t2.col1)=<hash>(t1.col1))  (cost=1.5 rows=2)
        -> Table scan on t2  (cost=0.176 rows=4)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)

SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1;
col1	col1
EXPLAIN FORMAT=tree SELECT * FROM t1, t2 WHERE t1.col1 <=> t2.col1;
EXPLAIN
-> Filter: (t1.col1 <=> t2.col1)  (cost=1.5 rows=2)
    -> Inner hash join (<hash>(t1.col1)=<hash>(t2.col1))  (cost=1.5 rows=2)
        -> Table scan on t2  (cost=0.176 rows=4)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)

SELECT * FROM t1, t2 WHERE t1.col1 <=> t2.col1;
col1	col1
NULL	NULL
DROP TABLE t1,t2;
#
# Now, do some queries where we end up with a GRACE hash join. That is,
# the right table of the join is bigger than the join_buffer_size.
#
CREATE TABLE t1 (col1 BIGINT);
CREATE TABLE t2 (col1 BIGINT);
INSERT INTO t1 SELECT 1;
INSERT INTO t1 SELECT col1 + 1 FROM t1;
INSERT INTO t1 SELECT col1 + 2 FROM t1;
INSERT INTO t1 SELECT col1 + 4 FROM t1;
INSERT INTO t1 SELECT col1 + 8 FROM t1;
INSERT INTO t1 SELECT col1 + 16 FROM t1;
INSERT INTO t1 SELECT col1 + 32 FROM t1;
INSERT INTO t1 SELECT col1 + 64 FROM t1;
INSERT INTO t1 SELECT col1 + 128 FROM t1;
INSERT INTO t1 SELECT col1 + 256 FROM t1;
INSERT INTO t1 SELECT col1 + 512 FROM t1;
INSERT INTO t2 SELECT col1 FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 2048;
EXPLAIN FORMAT=tree
SELECT SUM(t1.col1), SUM(t2.col1) FROM t1, t2 WHERE t1.col1 = t2.col1;
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t2.col1)  (cost=115452 rows=1)
    -> Inner hash join (t2.col1 = t1.col1)  (cost=104966 rows=104858)
        -> Table scan on t2  (cost=0.0154 rows=1024)
        -> Hash
            -> Table scan on t1  (cost=103 rows=1024)

TRUNCATE performance_schema.file_summary_by_event_name;
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
0
SELECT SUM(t1.col1), SUM(t2.col1) FROM t1, t2 WHERE t1.col1 = t2.col1;
SUM(t1.col1)	SUM(t2.col1)
524800	524800
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
SET join_buffer_size = DEFAULT;
DROP TABLE t1,t2;
# See that spill to disk (GRACE hash join) works with all kind of
# data types.
CREATE TABLE t1 (
str_col VARCHAR(255),
blob_col LONGBLOB,
text_col LONGTEXT,
bit_col BIT(64),
tinyint_col TINYINT,
smallint_col SMALLINT,
mediumint_col MEDIUMINT,
int_col INTEGER,
bigint_col BIGINT,
float_col FLOAT,
double_col DOUBLE,
decimal_col DECIMAL(65, 30),
year_col YEAR,
date_col DATE,
time_col TIME(6),
datetime_col DATETIME(6),
timestamp_col TIMESTAMP(6),
json_col JSON,
geometry_col GEOMETRY
);
SET time_zone = '+00:00';
INSERT INTO t1 VALUES (
'',
'',
'',
b'0000000000000000000000000000000000000000000000000000000000000000',
-128,
-32768,
-8388608,
-2147483648,
-9223372036854775808,
-3.402823466E+38,
-1.7976931348623157E+308,
'-99999999999999999999999999999999999.999999999999999999999999999999',
1901,
'1000-01-01',
'-838:59:59.000000',
'1000-01-01 00:00:00.000000',
'1970-01-01 00:00:01.000000',
'{}',
ST_GeomFromText('GEOMETRYCOLLECTION()')
);
INSERT INTO t1 VALUES (
'a very long and interesting string',
'a very long and interesting blob',
'a very long and interesting text',
b'1111111111111111111111111111111111111111111111111111111111111111',
127,
32767,
8388607,
2147483647,
9223372036854775807,
3.402823466E+38,
1.7976931348623157E+308,
'99999999999999999999999999999999999.999999999999999999999999999999',
2155,
'9999-12-31',
'838:59:59.000000',
'9999-12-31 23:59:59.999999',
'2038-01-19 03:14:07.999999',
'{"key": [1, 2, 3]}',
ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 2), POINT(3 4))')
);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 VALUES ();
SET join_buffer_size = 99968;
# Just do a few aggregations for sanity checking. We don't want to
# pollute the result log with thousands of lines with binary data.
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT
COUNT(*),
SUM(LENGTH(t1.text_col)),
SUM(t2.bigint_col)
FROM
t1,
t1 AS t2
WHERE
t1.int_col = t2.int_col
ORDER BY
t1.int_col;
EXPLAIN
-> Aggregate: count(0), sum(length(t1.text_col)), sum(t2.bigint_col)  (cost=5.54e+6 rows=1)
    -> Inner hash join (t2.int_col = t1.int_col)  (cost=5.54e+6 rows=1664)
        -> Table scan on t2  (cost=42963 rows=129)
        -> Hash
            -> Table scan on t1  (cost=13.9 rows=129)

SELECT
COUNT(*),
SUM(LENGTH(t1.text_col)),
SUM(t2.bigint_col)
FROM
t1,
t1 AS t2
WHERE
t1.int_col = t2.int_col
ORDER BY
t1.int_col;
COUNT(*)	SUM(LENGTH(t1.text_col))	SUM(t2.bigint_col)
8192	131072	-4096
EXPLAIN FORMAT=tree SELECT
COUNT(*),
SUM(LENGTH(t1.text_col)),
SUM(t2.bigint_col)
FROM
t1,
t1 AS t2
WHERE
t1.int_col <=> t2.int_col
ORDER BY
t1.int_col;
EXPLAIN
-> Aggregate: count(0), sum(length(t1.text_col)), sum(t2.bigint_col)  (cost=5.54e+6 rows=1)
    -> Inner hash join (t1.int_col <=> t2.int_col)  (cost=5.54e+6 rows=1664)
        -> Table scan on t2  (cost=42963 rows=129)
        -> Hash
            -> Table scan on t1  (cost=13.9 rows=129)

SELECT
COUNT(*),
SUM(LENGTH(t1.text_col)),
SUM(t2.bigint_col)
FROM
t1,
t1 AS t2
WHERE
t1.int_col <=> t2.int_col
ORDER BY
t1.int_col;
COUNT(*)	SUM(LENGTH(t1.text_col))	SUM(t2.bigint_col)
8193	131072	-4096
DROP TABLE t1;
SET join_buffer_size = DEFAULT;
SET time_zone = DEFAULT;
#
# A query where we end up with a weedout + hash join. This forces hash
# join to keep the row ID for each row, so that the duplicate removal
# works.
#
SET optimizer_switch="materialization=off,firstmatch=off";
CREATE TABLE t1 (i BIGINT);
CREATE TABLE t2 (i BIGINT);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (3);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t2 WHERE (t2.i) IN (SELECT t1.i FROM t1);
EXPLAIN
-> Remove duplicate t2 rows using temporary table (weedout)  (cost=1.3 rows=2)
    -> Inner hash join (t1.i = t2.i)  (cost=1.3 rows=2)
        -> Table scan on t1  (cost=0.175 rows=3)
        -> Hash
            -> Table scan on t2  (cost=0.45 rows=2)

SELECT * FROM t2 WHERE (t2.i) IN (SELECT t1.i FROM t1);
i
2
3
# Increase the data volume, and reduce the join_buffer_size, in order to
# test that we can keep the row ID in case of GRACE hash join as well.
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t2 SELECT * FROM t2;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 2048;
EXPLAIN FORMAT=tree
SELECT COUNT(*) FROM t2 WHERE (t2.i) IN (SELECT t1.i FROM t1);
EXPLAIN
-> Aggregate: count(0)  (cost=173125 rows=1)
    -> Remove duplicate t2 rows using temporary table (weedout)  (cost=157397 rows=157286)
        -> Inner hash join (t1.i = t2.i)  (cost=157397 rows=157286)
            -> Table scan on t1  (cost=3.34 rows=1536)
            -> Hash
                -> Table scan on t2  (cost=103 rows=1024)

TRUNCATE performance_schema.file_summary_by_event_name;
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
0
SELECT COUNT(*) FROM t2 WHERE (t2.i) IN (SELECT t1.i FROM t1);
COUNT(*)
1024
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
DROP TABLE t1, t2;
SET join_buffer_size = DEFAULT;
SET optimizer_switch = DEFAULT;
# Test a query with multiple predicates; some of which are
# NULL-safe equalities and others are regular equalities.
CREATE TABLE t (a INT, b INT, c INT, d INT);
INSERT INTO t VALUES
(1, 2, 3, 4), (NULL, 2, 3, 4), (1, NULL, 3, 4), (1, 2, NULL, 4),
(1, 2, 3, NULL), (NULL, NULL, NULL, NULL), (1, NULL, 3, NULL),
(0, 0, 0, 0), (NULL, 0, NULL, 0), (0, NULL, 0, NULL);
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
EXPLAIN FORMAT=TREE
SELECT * FROM t t1, t t2
WHERE t1.a = t2.a AND t1.b <=> t2.b AND t1.c = t2.c AND t1.d <=> t2.d;
EXPLAIN
-> Inner hash join (t2.c = t1.c), (t2.a = t1.a), (t1.b <=> t2.b), (t1.d <=> t2.d)  (cost=11.5 rows=10)
    -> Table scan on t2  (cost=0.035 rows=10)
    -> Hash
        -> Table scan on t1  (cost=1.25 rows=10)

SELECT * FROM t t1, t t2
WHERE t1.a = t2.a AND t1.b <=> t2.b AND t1.c = t2.c AND t1.d <=> t2.d;
a	b	c	d	a	b	c	d
0	0	0	0	0	0	0	0
0	NULL	0	NULL	0	NULL	0	NULL
1	2	3	4	1	2	3	4
1	2	3	NULL	1	2	3	NULL
1	NULL	3	4	1	NULL	3	4
1	NULL	3	NULL	1	NULL	3	NULL
DROP TABLE t;
# Test a case where the RAND() function is pushed as late as possible in
# the join. The optimizer ends up rewriting t1.col1 = FLOOR(...) to
# t2.col1 = FLOOR(...), so this test case ensures that the executor is
# able to put the condition after the join. FLOOR and division/addition
# make this query deterministic.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT
t1.col1,
t2.col1
FROM
t1,
t2
WHERE
t1.col1 = t2.col1
AND t1.col1 = FLOOR(RAND() / 2 + 2);
EXPLAIN
-> Filter: (t1.col1 = floor(((rand() / 2) + 2)))  (cost=1 rows=2)
    -> Inner hash join (t2.col1 = t1.col1)  (cost=1 rows=2)
        -> Table scan on t2  (cost=0.35 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.35 rows=2)

SELECT
t1.col1,
t2.col1
FROM
t1,
t2
WHERE
t1.col1 = t2.col1
AND t1.col1 = FLOOR(RAND() / 2 + 2);
col1	col1
2	2
DROP TABLE t1, t2;
# Ensure that the hash join picks the correct fields and tables when both
# sides of the join condition are from the same source table.
CREATE TABLE c (
col1 varchar(1)
) ENGINE = myisam;
INSERT INTO c VALUES ('w');
INSERT INTO c VALUES ('d');
ANALYZE TABLE c;
Table	Op	Msg_type	Msg_text
test.c	analyze	status	OK
EXPLAIN format=tree SELECT *
FROM
(SELECT * FROM c) AS table1
JOIN (SELECT * FROM c) AS table2
ON table2.col1 = table1.col1;
EXPLAIN
-> Inner hash join (c.col1 = c.col1)  (cost=1.6 rows=2)
    -> Table scan on c  (cost=0.301 rows=2)
    -> Hash
        -> Table scan on c  (cost=0.702 rows=2)

SELECT *
FROM
(SELECT * FROM c) AS table1
JOIN (SELECT * FROM c) AS table2
ON table2.col1 = table1.col1;
col1	col1
w	w
d	d
DROP TABLE c;
# This query ends up with a BNL between t3 and t2. Ensure that we don't
# end up with a hash join like:
#
#  -> Constant row from <subquery2>
#    -> Materialize with deduplication
#        -> HashJoin inner join (t3.i = '2')
#            -> Table scan on t2
#            -> Table scan on t3
#
# We don't want a join condition on a constant, so it should be pushed as
# a filter.
SET optimizer_switch='firstmatch=off';
CREATE TABLE t1 (i INTEGER) ENGINE = MyISAM;
CREATE TABLE t2 (i INTEGER) ENGINE = MyISAM;
CREATE TABLE t3 (i INTEGER) ENGINE = MyISAM;
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (2);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT * FROM t1 WHERE (t1.i) IN  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
EXPLAIN
-> Constant row from <subquery2>  (cost=1.3..1.3 rows=1)
    -> Materialize with deduplication  (cost=1.1..1.1 rows=0)
        -> Filter: (t3.i is not null)  (cost=1.1 rows=0)
            -> Inner hash join (no condition)  (cost=1.1 rows=0)
                -> Filter: (t3.i = '2')  (cost=0.5 rows=0)
                    -> Table scan on t3  (cost=0.5 rows=0)
                -> Hash
                    -> Table scan on t2  (cost=0.6 rows=1)

SELECT * FROM t1 WHERE (t1.i) IN  (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
i
DROP TABLE t1,t2,t3;
SET optimizer_switch=DEFAULT;
# A bit more complicated join condition where we have multiple join
# conditions, and one of them is an expression.
CREATE TABLE t1 (a INTEGER, b INTEGER);
INSERT INTO t1 (a) VALUES (1),(2);
CREATE TABLE t3 (a INTEGER, b INTEGER);
INSERT INTO t3 VALUES (1, 10), (1, 11), (2, 10), (2, 11);
ANALYZE TABLE t1, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1, t3 WHERE t3.b = t1.a + 9 AND t3.a = t1.a;
EXPLAIN
-> Inner hash join (t3.a = t1.a), (t3.b = (t1.a + 9))  (cost=1.5 rows=2)
    -> Table scan on t3  (cost=0.175 rows=4)
    -> Hash
        -> Table scan on t1  (cost=0.45 rows=2)

SELECT * FROM t1, t3 WHERE t3.b = t1.a + 9 AND t3.a = t1.a;
a	b	a	b
1	NULL	1	10
2	NULL	2	11
DROP TABLE t1,t3;
# Ensure that outer joins doesn't degrade into a nested loop,
# but still uses join buffering.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (2);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Left hash join (t2.col1 = t1.col1)  (cost=0.625 rows=2)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Table scan on t2  (cost=0.175 rows=1)

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`col1` = `test`.`t1`.`col1`)) where true
DROP TABLE t1, t2;
# See that we can replace a BNL with hash join, even if we have extra
# join conditions that are not equi-join conditions. The result should be
# that the non-equi-join conditions should be attached as a filter after
# the join.
CREATE TABLE t1 (col1 INTEGER, col2 INTEGER);
CREATE TABLE t2 (col1 INTEGER, col2 INTEGER);
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES (1, 1), (2, 4), (3, 6);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1 AND t1.col2 < t2.col2;
EXPLAIN
-> Filter: (t1.col2 < t2.col2)  (cost=1.7 rows=3)
    -> Inner hash join (t2.col1 = t1.col1)  (cost=1.7 rows=3)
        -> Table scan on t2  (cost=0.117 rows=3)
        -> Hash
            -> Table scan on t1  (cost=0.55 rows=3)

SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1 AND t1.col2 < t2.col2;
col1	col2	col1	col2
2	2	2	4
3	3	3	6
DROP TABLE t1, t2;
CREATE TABLE t1 (col1 BIGINT);
INSERT INTO t1 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT SUM(t1.col1)
FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
EXPLAIN
-> Aggregate: sum(t1.col1)  (cost=211113 rows=1)
    -> Inner hash join (no condition)  (cost=111113 rows=1e+6)
        -> Table scan on t6  (cost=21.1e-6 rows=10)
        -> Hash
            -> Inner hash join (no condition)  (cost=11112 rows=100000)
                -> Table scan on t5  (cost=134e-6 rows=10)
                -> Hash
                    -> Inner hash join (no condition)  (cost=1112 rows=10000)
                        -> Table scan on t4  (cost=0.00126 rows=10)
                        -> Hash
                            -> Inner hash join (no condition)  (cost=112 rows=1000)
                                -> Table scan on t3  (cost=0.0125 rows=10)
                                -> Hash
                                    -> Inner hash join (no condition)  (cost=11.5 rows=100)
                                        -> Table scan on t2  (cost=0.125 rows=10)
                                        -> Hash
                                            -> Table scan on t1  (cost=1.25 rows=10)

SELECT SUM(t1.col1) FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
SUM(t1.col1)
1000000
DROP TABLE t1;
# Test that comparison between FLOAT and DOUBLE works as expected if
# given an explicit number of decimals.
CREATE TABLE t1 (col1 FLOAT(5,2), col2 DOUBLE(15,2));
Warnings:
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1.01, 1.01);
SELECT * FROM t1 a, t1 b WHERE a.col1 = b.col2;
col1	col2	col1	col2
1.01	1.01	1.01	1.01
DROP TABLE t1;
# The point of the following test is to see that if the innermost hash
# join returns zero rows, the outermost hash join should not scan the
# probe table.
CREATE TABLE t1 (col1 INT);
CREATE TABLE t2 (col1 INT);
CREATE TABLE t3 (col1 INT);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (1), (2), (3);
INSERT INTO t3 VALUES (1), (2), (3);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN FORMAT=tree SELECT STRAIGHT_JOIN * FROM t1
JOIN t2 ON t1.col1 + 10 = t2.col1
JOIN t3 ON t2.col1 = t3.col1;
EXPLAIN
-> Inner hash join (t3.col1 = t2.col1)  (cost=2.85 rows=3)
    -> Table scan on t3  (cost=0.117 rows=3)
    -> Hash
        -> Inner hash join ((t1.col1 + 10) = t2.col1)  (cost=1.7 rows=3)
            -> Table scan on t2  (cost=0.117 rows=3)
            -> Hash
                -> Table scan on t1  (cost=0.55 rows=3)

SELECT SUM(variable_value) AS Total_handler_reads
FROM performance_schema.session_status
WHERE variable_name LIKE 'Handler_read%';
Total_handler_reads
12
DROP TABLE t1, t2, t3;
#
# Bug#29898802	WL#2241: SIG6 IN HASH_JOIN_BUFFER::LOADINTOTABLEBUFFERS()
#              AT HASH_JOIN_BUFFER.CC
#
CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT,
col_varchar varchar(1),
col_varchar_key varchar(1),
PRIMARY KEY (pk),
KEY idx_CC_col_varchar_key (col_varchar_key)
);
INSERT INTO t1 VALUES (1,'n','X'),(2,'Y','8'),(3,'R','l');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT
t1.col_varchar_key AS field1
FROM
(t1, t1 as alias1)
WHERE
NOT
EXISTS(
SELECT
alias2.col_varchar_key
FROM
t1 AS alias2
WHERE
alias2.col_varchar_key >= t1.col_varchar
)
GROUP BY
field1;
EXPLAIN
-> Table scan on <temporary>  (cost=7.46..10.2 rows=27)
    -> Temporary table with deduplication  (cost=7.35..7.35 rows=27)
        -> Inner hash join (no condition)  (cost=4.65 rows=27)
            -> Index scan on alias1 using idx_CC_col_varchar_key  (cost=0.183 rows=3)
            -> Hash
                -> Nested loop antijoin  (cost=1.7 rows=9)
                    -> Table scan on t1  (cost=0.55 rows=3)
                    -> Filter: (alias2.col_varchar_key >= t1.col_varchar)  (cost=0.55 rows=3)
                        -> Index range scan on alias2 (re-planned for each iteration)  (cost=0.55 rows=3)

Warnings:
Note	1276	Field or reference 'test.t1.col_varchar' of SELECT #2 was resolved in SELECT #1
SELECT
t1.col_varchar_key AS field1
FROM
(t1, t1 as alias1)
WHERE
NOT
EXISTS(
SELECT
alias2.col_varchar_key
FROM
t1 AS alias2
WHERE
alias2.col_varchar_key >= t1.col_varchar
)
GROUP BY
field1;
field1
8
DROP TABLE t1;
# See that typed arrays are handled as blobs. That is, we do not try to
# allocate 4GB of memory during the hash join.
CREATE TABLE t1 (
col_int_key INTEGER,
col_json JSON,
KEY mv_idx ((CAST(col_json->'$[*]' AS CHAR(40) ARRAY)))
);
INSERT INTO t1 VALUES (NULL, '[1]'), (4, '[1]'), (1, '[2]');
CREATE TABLE t2(col_int INTEGER);
INSERT INTO t2 VALUES (1), (2), (3), (11), (12);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2
JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_json->'$[*]'));
EXPLAIN
-> Inner hash join (no condition)  (cost=1.96 rows=10)
    -> Table scan on t2  (cost=0.38 rows=5)
    -> Hash
        -> Filter: json'"1"' member of (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array))  (cost=0.7 rows=2)
            -> Index lookup on t1 using mv_idx (cast(json_extract(col_json,_utf8mb4'$[*]') as char(40) array)=json'"1"')  (cost=0.7 rows=2)

SELECT t1.col_int_key AS field1, t2.col_int AS field2 FROM t2
JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_json->'$[*]'));
DROP TABLE t1,t2;
#
# Bug#29906372 WL#2241: SIG6 IN HASH_JOIN_BUFFER::STOREFROMTABLEBUFFERS
#              AT HASH_JOIN_BUFFER.CC
#
CREATE TABLE a (
pk INTEGER NOT NULL AUTO_INCREMENT,
col_varchar VARCHAR(1),
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY varchar_key (col_varchar_key)
);
CREATE TABLE b (
pk INTEGER NOT NULL AUTO_INCREMENT,
col_varchar VARCHAR(1),
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY varchar_key (col_varchar_key)
);
INSERT INTO a VALUES (1, 'N', '0');
INSERT INTO b VALUES (1, '8', 'r'), (2, 'v', 'C'), (3, 'b', 'p'), (4, '7', 'W');
ANALYZE TABLE a, b;
Table	Op	Msg_type	Msg_text
test.a	analyze	status	OK
test.b	analyze	status	OK
EXPLAIN FORMAT=tree SELECT 1 FROM (b AS table1
INNER JOIN a AS table2 ON table2.pk = table1.pk OR table1.col_varchar < 'D')
WHERE (NOT EXISTS
(SELECT 1 FROM (b AS alias3 STRAIGHT_JOIN a AS alias4
ON alias4.col_varchar = alias3.col_varchar_key)
WHERE alias3.pk >= table1.pk));
EXPLAIN
-> Nested loop antijoin  (cost=2.3 rows=8)
    -> Filter: ((table1.pk = table2.pk) or (table1.col_varchar < 'D'))  (cost=1 rows=2)
        -> Inner hash join (no condition)  (cost=1 rows=2)
            -> Table scan on table1  (cost=0.45 rows=4)
            -> Hash
                -> Index scan on table2 using varchar_key  (cost=0.35 rows=1)
    -> Nested loop inner join  (cost=2.85 rows=4)
        -> Filter: (alias3.pk >= table1.pk)  (cost=0.45 rows=4)
            -> Index range scan on alias3 (re-planned for each iteration)  (cost=0.45 rows=4)
        -> Filter: (alias4.col_varchar = alias3.col_varchar_key)  (cost=1.05 rows=1)
            -> Table scan on alias4  (cost=1.05 rows=1)

Warnings:
Note	1276	Field or reference 'test.table1.pk' of SELECT #2 was resolved in SELECT #1
SELECT 1 FROM (b AS table1
INNER JOIN a AS table2 ON table2.pk = table1.pk OR table1.col_varchar < 'D')
WHERE (NOT EXISTS
(SELECT 1 FROM (b AS alias3 STRAIGHT_JOIN a AS alias4
ON alias4.col_varchar = alias3.col_varchar_key)
WHERE alias3.pk >= table1.pk));
DROP TABLE a, b;
#
# Bug#29947439 WL#2241: FLOATING POINT EXCEPTION: INITIALIZECHUNKFILES AT
#              HASH_JOIN_ITERATOR.CC
#
CREATE TABLE t1 (col1 TEXT);
INSERT INTO t1 VALUES (REPEAT('A', 50000)), (REPEAT('A', 50000));
EXPLAIN FORMAT=tree SELECT a.col1 FROM t1 AS a, t1 AS b;
EXPLAIN
-> Inner hash join (no condition)  (cost=6.37 rows=4)
    -> Table scan on b  (cost=1.86 rows=2)
    -> Hash
        -> Table scan on a  (cost=2.45 rows=2)

SET join_buffer_size = 128;
SELECT a.col1 FROM t1 AS a, t1 AS b;
DROP TABLE t1;
# Set up a case where we have very skewed data in the probe input, and we
# degrade into an on-disk hash join. We want to trigger a code path where
# we have empty chunk files from the probe input.
CREATE TABLE t1 (col1 VARCHAR(255));
CREATE TABLE t2 (col1 VARCHAR(255));
INSERT INTO t1 VALUES (SHA2(UUID(), 512));
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t1 SELECT SHA2(UUID(), 512) FROM t1;
INSERT INTO t2 SELECT REPEAT("a", 255) FROM t1;
SET GLOBAL innodb_stats_persistent_sample_pages = 2000;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET GLOBAL innodb_stats_persistent_sample_pages = DEFAULT;
EXPLAIN FORMAT=tree SELECT STRAIGHT_JOIN COUNT(*) FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Aggregate: count(0)  (cost=41168 rows=1)
    -> Filter: (t2.col1 = t1.col1)  (cost=38546 rows=26214)
        -> Inner hash join (<hash>(t2.col1)=<hash>(t1.col1))  (cost=38546 rows=26214)
            -> Table scan on t2  (cost=24 rows=512)
            -> Hash
                -> Table scan on t1  (cost=53 rows=512)

SET join_buffer_size = 1024;
SELECT STRAIGHT_JOIN COUNT(*) FROM t1 JOIN t2 ON t1.col1 = t2.col1;
DROP TABLE t1, t2;
SET join_buffer_size = DEFAULT;
#
# Bug#29964536 WL#2241: ASSERTION FAILURE IN
#              TEMPTABLE::HANDLER::POSITION() AT SRC/HANDLER.CC
#
CREATE TABLE tc (
col_int INTEGER,
col_varchar VARCHAR(1)
);
INSERT INTO tc VALUES (0,'x');
CREATE TABLE tcc (
col_varchar VARCHAR(1)
);
INSERT INTO tcc VALUES ('r'), ('f'), ('y'), ('u'), ('m'), (NULL);
CREATE TABLE t1 (field1 INTEGER);
INSERT INTO t1 VALUES (0);
SET optimizer_switch="firstmatch=off";
UPDATE t1 SET field1 = 9999 WHERE field1 NOT IN ( SELECT alias1.col_int AS
field1 FROM ( tcc, ( SELECT * FROM tc WHERE col_int < 1 ) AS alias1 ) WHERE (
alias1.col_varchar IN ( SELECT col_varchar FROM tcc ) ) GROUP BY field1
HAVING field1 <> 1 );
SET optimizer_switch="firstmatch=on";
DROP TABLE tc,tcc,t1;
# Do a join between DECIMAL and INTEGER to verify that we get a match
# between these two types.
CREATE TABLE t1 (col1 DECIMAL(4, 2));
INSERT INTO t1 VALUES (0);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t2 VALUES (0);
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 = t2.col1)  (cost=0.7 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
col1	col1
0.00	0
DROP TABLE t1, t2;
# See that we get the correct results with a PAD SPACE collation and
# PAD_CHAR_TO_FULL_LENGTH. Note that the latter is deprecated, so this
# test should go away once the SQL mode is removed.
CREATE TABLE t1 (
col1 CHAR(4)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
INSERT INTO t1 VALUES ("foo");
CREATE TABLE t2 (
col1 CHAR(40)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
INSERT INTO t2 VALUES ("foo");
SET sql_mode="PAD_CHAR_TO_FULL_LENGTH";
Warnings:
Warning	3090	Changing sql mode 'PAD_CHAR_TO_FULL_LENGTH' is deprecated. It will be removed in a future release.
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Inner hash join (t1.col1 = t2.col1)  (cost=0.7 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
col1	col1
foo 	foo                                     
SET sql_mode=DEFAULT;
DROP TABLE t1, t2;
# Set up a case where the join planner will set up a BNL with linked
# join buffers, and where the row ID should be kept due to duplicate
# removal. rowid_status will be set on several QEP_TABs to indicate that
# a row ID is needed, even though we should not request the row ID on all
# of them.
CREATE TABLE b1 (col_int INTEGER);
INSERT INTO b1 VALUES (1);
CREATE TABLE c1 (
col_int INTEGER,
col_timestamp TIMESTAMP NULL,
col_decimal DECIMAL(10, 4)
);
INSERT INTO c1 VALUES
(1741569678,'2004-01-07 20:47:51',-4.7563),
(-1533615975,'2037-10-27 16:40:24',7.7785);
CREATE TABLE cc1 (
col_int INTEGER,
col_decimal DECIMAL(10, 4),
col_timestamp TIMESTAMP NULL
);
INSERT INTO cc1 VALUES
(-190646953,6.4052,'2007-11-21 09:45:29'),
(-423321712,6.9636,'1988-01-04 13:34:47');
SELECT
1
FROM
b1
LEFT JOIN (
c1
RIGHT JOIN (SELECT DISTINCT * FROM cc1) AS alias3 ON
alias3.col_timestamp = c1.col_timestamp
) ON b1.col_int = c1.col_int AND 1
WHERE
EXISTS(
SELECT
1
FROM
cc1 JOIN c1 ON c1.col_decimal = cc1.col_decimal AND 1
WHERE
cc1.col_int <= b1.col_int OR cc1.col_int = c1.col_int
);
1
DROP TABLE b1, c1, cc1;
# Yet another problematic case involing duplicate weedout.
CREATE TABLE t1 (
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar_nokey varchar(1) DEFAULT NULL,
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) charset utf8mb4;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (4,'v','v');
INSERT INTO t1 VALUES (62,'v','v');
INSERT INTO t1 VALUES (7,'c','c');
INSERT INTO t1 VALUES (1,NULL,NULL);
set optimizer_switch='firstmatch=off';
set optimizer_switch='materialization=off';
SELECT
alias1.col_varchar_nokey AS a1_nokey,
alias1.col_varchar_key AS a1_key,
alias2.col_varchar_nokey AS a2_nokey
FROM
t1 AS alias1, t1 AS alias2
WHERE
(alias1.col_varchar_nokey,alias2.col_varchar_nokey)
IN
(
SELECT
sq2_alias2.col_varchar_nokey, sq2_alias1.col_varchar_key
FROM
t1 AS sq2_alias1, t1 AS sq2_alias2
)
;
a1_nokey	a1_key	a2_nokey
c	c	c
c	c	v
c	c	v
v	v	c
v	v	c
v	v	v
v	v	v
v	v	v
v	v	v
set optimizer_switch=DEFAULT;
DROP TABLE t1;
# A case where we have a hash join iterator both above and below a
# WeedoutIterator.
CREATE TABLE t1(f1 INT(11) NOT NULL);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (10);
CREATE TABLE t2
(
f1 INT(11) NOT NULL AUTO_INCREMENT,
f2 INT(11) DEFAULT NULL,
PRIMARY KEY (f1),
KEY (f2)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (1, 7), (2, 1), (4, 7);
CREATE TABLE t4(f1 INT DEFAULT NULL);
INSERT INTO t4 VALUES (2);
ANALYZE TABLE t1, t2, t4;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t4	analyze	status	OK
EXPLAIN FORMAT=tree SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
EXPLAIN
-> Aggregate: count(0)  (cost=4.15 rows=1)
    -> Inner hash join (no condition)  (cost=3.7 rows=4.5)
        -> Table scan on t1  (cost=0.0778 rows=1)
        -> Hash
            -> Remove duplicate (ta3, ta4) rows using temporary table (weedout)  (cost=3 rows=4.5)
                -> Nested loop inner join  (cost=3 rows=4.5)
                    -> Nested loop inner join  (cost=2.3 rows=4.5)
                        -> Inner hash join (no condition)  (cost=1.1 rows=3)
                            -> Filter: (t4.f1 is not null)  (cost=0.117 rows=1)
                                -> Table scan on t4  (cost=0.117 rows=1)
                            -> Hash
                                -> Filter: (t2.f2 is not null)  (cost=0.55 rows=3)
                                    -> Index scan on t2 using f2  (cost=0.55 rows=3)
                        -> Covering index lookup on ta3 using f2 (f2=t2.f2)  (cost=0.3 rows=1.5)
                    -> Single-row covering index lookup on ta4 using PRIMARY (f1=t4.f1)  (cost=0.0778 rows=1)

SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
COUNT(*)
3
SELECT /*+ JOIN_PREFIX(t2@qb2, t4@qb1, ta3, ta4) */
COUNT(*) FROM t1 JOIN t2 AS ta3 JOIN t2 AS ta4
WHERE ta4.f1 IN (SELECT /*+ QB_NAME(qb1) */ f1 FROM t4) AND
ta3.f2 IN (SELECT /*+ QB_NAME(qb2) */ f2 FROM t2);
COUNT(*)
3
DROP TABLE t1, t2, t4;
#
# Bug#30035890 SIG 11 IN HASHJOINITERATOR::READJOINEDROW AT
#              SQL/HASH_JOIN_ITERATOR.CC
#
# Note that this test case needs ASAN to reproduce.
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (7), (7);
CREATE TABLE t2 (b INT, c DATETIME);
INSERT IGNORE INTO t2 VALUES (7, NULL), (7, '2006'), (7, '2002');
Warnings:
Warning	1265	Data truncated for column 'c' at row 2
Warning	1265	Data truncated for column 'c' at row 3
# Set up a case where the hash join row buffer will be re-inited.
UPDATE t1
SET a = 42
WHERE a NOT IN (
SELECT alias2.b FROM t2 AS alias2 JOIN t2 AS alias1 ON (alias2.c = alias1.c)
);
DROP TABLE t1, t2;
#
# Bug#30060691 ASSERTION `M_INDEX_CURSOR.IS_POSITIONED()' IN
#              TEMPTABLE::HANDLER::POSITION()
#
CREATE TABLE c (
col_int INTEGER,
col_varchar VARCHAR(1) ,
col_varchar_key VARCHAR(1));
CREATE TABLE bb (
pk INTEGER auto_increment,
col_int_key INTEGER,
col_varchar VARCHAR(1),
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk));
CREATE TABLE cc (
col_varchar_key VARCHAR(1),
INDEX idx (col_varchar_key));
INSERT INTO bb VALUES (DEFAULT, 41509313, 'S', 'W');
INSERT INTO c VALUES
(-792274908, 'P', 'r'),
(281391051, 'w', 'x'),
(-1381986093, 'l', '2'),
(-78303180, 'f', 'Q'),
(1027797776, 'w', 'G'),
(-1361294690, 'm', 'L'),
(65604698, '7', 'Y'),
(-964881813, 'j', 'F'),
(1831120981, 'q', 'q'),
(-573388832, 'F', 'M'),
(571640392, '1', 'R'),
(857813414, 'y', 'l'),
(555892383, 'x', 'P'),
(601556555, 'z', 'k'),
(-578249624, 'N', 'e'),
(-843749952, '4', 'J'),
(2058477272, '4', 'R'),
(-1732353317, 'C', 'Z'),
(-1639317818, '9', 'f'),
(19700948, 'K', 'V');
INSERT INTO cc VALUES
('b'), ('E'), ('v'), ('4'), ('L'), ('g'), ('i'), ('D'), ('S'), ('s'), ('4'),
('5'), ('4'), ('y'), ('v'), ('Z'), ('O'), ('2'), ('v'), ('5');
ANALYZE TABLE c, bb, cc;
Table	Op	Msg_type	Msg_text
test.c	analyze	status	OK
test.bb	analyze	status	OK
test.cc	analyze	status	OK
EXPLAIN FORMAT=tree SELECT *
FROM
cc AS alias1
LEFT JOIN (
(
bb AS alias2
INNER JOIN (SELECT DISTINCT sq1_alias1.* FROM bb AS sq1_alias1)
AS alias3 ON alias3.col_int_key = alias2.col_int_key
)
) ON alias3.col_varchar_key = alias2.col_varchar_key
WHERE
alias1.col_varchar_key
IN (
SELECT
sq2_alias1.col_varchar AS sq2_field1
FROM
c AS sq2_alias1
WHERE
sq2_alias1.col_varchar_key != alias2.col_varchar
AND sq2_alias1.col_int > alias2.pk
);
EXPLAIN
-> Remove duplicate (alias2, alias3, alias1) rows using temporary table (weedout)  (cost=5.06 rows=2.57)
    -> Nested loop inner join  (cost=5.06 rows=2.57)
        -> Filter: ((sq2_alias1.col_varchar_key <> alias2.col_varchar) and (sq2_alias1.col_int > alias2.pk))  (cost=1.8 rows=1.8)
            -> Inner hash join (no condition)  (cost=1.8 rows=1.8)
                -> Filter: (sq2_alias1.col_varchar is not null)  (cost=0.425 rows=6)
                    -> Table scan on sq2_alias1  (cost=0.425 rows=20)
                -> Hash
                    -> Nested loop inner join  (cost=0.95 rows=1)
                        -> Table scan on alias2  (cost=0.35 rows=1)
                        -> Index lookup on alias3 using <auto_key0> (col_int_key=alias2.col_int_key, col_varchar_key=alias2.col_varchar_key)  (cost=0.8..1.15 rows=2)
                            -> Materialize  (cost=0.45..0.45 rows=1)
                                -> Table scan on sq1_alias1  (cost=0.35 rows=1)
        -> Covering index lookup on alias1 using idx (col_varchar_key=sq2_alias1.col_varchar)  (cost=0.262 rows=1.43)

Warnings:
Note	1276	Field or reference 'test.alias2.col_varchar' of SELECT #3 was resolved in SELECT #1
Note	1276	Field or reference 'test.alias2.pk' of SELECT #3 was resolved in SELECT #1
# We only want to see that the query does not hit an assertion, so ignore
# the results.
SELECT *
FROM
cc AS alias1
LEFT JOIN (
(
bb AS alias2
INNER JOIN (SELECT DISTINCT sq1_alias1.* FROM bb AS sq1_alias1)
AS alias3 ON alias3.col_int_key = alias2.col_int_key
)
) ON alias3.col_varchar_key = alias2.col_varchar_key
WHERE
alias1.col_varchar_key
IN (
SELECT
sq2_alias1.col_varchar AS sq2_field1
FROM
c AS sq2_alias1
WHERE
sq2_alias1.col_varchar_key != alias2.col_varchar
AND sq2_alias1.col_int > alias2.pk
);
DROP TABLE bb, c, cc;
#
# Bug#30049217 ASSERTION FAILURE AT
#              TEMPTABLE::HANDLER::POSITION|SRC/HANDLER.CC
#
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES
(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
CREATE TABLE t2 (c2 INT, c3 INT, KEY (c3));
INSERT INTO t2 VALUES
(1,-823867270),
(19,1130654803),
(20,1299270309);
CREATE TABLE t3 (c4 INT);
INSERT INTO t3 VALUES (1);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
SELECT *
FROM ((SELECT DISTINCT * FROM t2) AS alias2 JOIN t3 ON (t3.c4 = alias2.c2))
WHERE (EXISTS (SELECT * FROM (t1 LEFT JOIN (t3 JOIN t2 ON (t2.c3 = t3.c4)) ON
(1))))
AND alias2.c3 < 19;
c2	c3	c4
1	-823867270	1
DROP TABLE t1, t2, t3;
#
# Bug#30153695 ASSERTION SIG6 TEMPTABLE::HANDLER::POSITION
#              SRC/HANDLER.CC:715
#
CREATE TABLE c (
col_date date,
col_datetime_key datetime,
col_varchar_key varchar (1),
col_varchar varchar (1),
col_date_key date,
col_int_key int,
col_time time,
col_time_key time,
col_int int,
pk integer auto_increment,
col_datetime datetime,
key (col_datetime_key ),
key (col_varchar_key ),
key (col_date_key ),
key (col_int_key ),
key (col_time_key ),
primary key (pk)) ENGINE=innodb;
INSERT IGNORE INTO c VALUES
('2001-07-23', '2004-12-11', 'k', 's', NULL, 7, '2004-11-12', '2000-03-18', 3,
NULL, NULL),
(NULL, NULL, 's', 'j', NULL, 6, NULL, '2005', 1, NULL, NULL),
('2006-07-02', NULL, 'w', 'y', NULL, 2, '04:35:59.017853', '2002', 7, NULL,
'2004-09-04 21:23:05.023144'),
(NULL, '2009-02-16 21:37:23.010045', 'w', 'o', '2005-05-25', NULL, NULL,
'04:32:06.000870', 9, NULL, '2004'),
(NULL, NULL, 'y', 'k', '2002-12-15', 81, NULL, '2009-03-14', 3, NULL, NULL),
(NULL, '2005', 'x', 's', '2004-07-12', 9, NULL, NULL, 7, NULL, '2009'),
('2003', '2000-11-08', 'd', 'h', '2002-09-25', 8, NULL, '2002', NULL, NULL,
'2004'),
('2000', '2008-01-08 20:49:13.011386', 't', 'w', '2000-12-11', 6,
'18:31:35.007025', '19:28:20.040544', 4, NULL, '2005-03-13'),
('2006-10-04', '2000-12-16', 'i', 'f', NULL, 3, '2008', NULL, 5, NULL,
'2003-12-03 13:55:06.040156'),
('2009-07-26', '2009-11-22 07:59:12.037926', 'o', 'n', '2004-07-23', 4,
'2005', '12:00:51.020344', 5, NULL, '2006'),
('2009-02-25', NULL, 'm', NULL, '2003', NULL, '2000', '2002-07-28', 1, NULL,
'2004-06-26'),
('2008-01-11', '2001-05-27', 'c', 'w', '2001-11-21', 4, '2004-07-23',
'2005-07-19', 3, NULL, '2001'),
('2009', NULL, 'x', NULL, NULL, 6, '2006-10-03', NULL, 1, NULL, '2009-12-03'),
('2008-09-22', '2008-08-09 11:16:52.037869', 'r', 'c', '2008-01-23', 3, NULL,
NULL, 6, NULL, '2008'),
('2007-01-21', NULL, 'u', 'u', '2008', 5, '2003-07-15', '07:04:43.054922',
NULL, NULL, NULL),
('2009-06-15', '2004-01-25', 'x', NULL, NULL, 189, '2008', '2000-06-14', 1,
NULL, NULL),
('2005', '2008-03-22', NULL, 'g', '2008', 1, '20:53:08.022885', '2006', 3,
NULL, '2009-04-06 15:24:52.051014'),
('2002', '2003-07-10 12:29:23.023649', 'g', 'u', '2000-10-16', 9, '2003',
'2006', 9, NULL, NULL),
('2005-10-23', NULL, 's', 'x', '2005', 9, '2008-07-09', '2001-08-12', 8, NULL,
NULL),
('2005', NULL, 'g', 'm', '2000-01-03', 9, '2008', NULL, 1, NULL,
'2001-01-21');
Warnings:
Warning	1265	Data truncated for column 'col_time' at row 1
Warning	1265	Data truncated for column 'col_time_key' at row 1
Warning	1265	Data truncated for column 'col_datetime' at row 4
Warning	1265	Data truncated for column 'col_time_key' at row 5
Warning	1265	Data truncated for column 'col_datetime_key' at row 6
Warning	1265	Data truncated for column 'col_datetime' at row 6
Warning	1265	Data truncated for column 'col_date' at row 7
Warning	1265	Data truncated for column 'col_datetime' at row 7
Warning	1265	Data truncated for column 'col_date' at row 8
Warning	1265	Data truncated for column 'col_datetime' at row 10
Warning	1265	Data truncated for column 'col_date_key' at row 11
Warning	1265	Data truncated for column 'col_time_key' at row 11
Warning	1265	Data truncated for column 'col_time' at row 12
Warning	1265	Data truncated for column 'col_time_key' at row 12
Warning	1265	Data truncated for column 'col_datetime' at row 12
Warning	1265	Data truncated for column 'col_date' at row 13
Warning	1265	Data truncated for column 'col_time' at row 13
Warning	1265	Data truncated for column 'col_datetime' at row 14
Warning	1265	Data truncated for column 'col_date_key' at row 15
Warning	1265	Data truncated for column 'col_time' at row 15
Warning	1265	Data truncated for column 'col_time_key' at row 16
Warning	1265	Data truncated for column 'col_date' at row 17
Warning	1265	Data truncated for column 'col_date_key' at row 17
Warning	1265	Data truncated for column 'col_date' at row 18
Warning	1265	Data truncated for column 'col_date_key' at row 19
Warning	1265	Data truncated for column 'col_time' at row 19
Warning	1265	Data truncated for column 'col_time_key' at row 19
Warning	1265	Data truncated for column 'col_date' at row 20
CREATE TABLE cc (
col_date date,
col_int int,
col_int_key int,
col_varchar_key varchar (1),
col_datetime_key datetime,
col_datetime datetime,
pk integer auto_increment,
col_varchar varchar (1),
col_time_key time,
col_time time,
col_date_key date,
key (col_int_key ),
key (col_varchar_key ),
key (col_datetime_key ),
primary key (pk),
key (col_time_key ),
key (col_date_key )) ENGINE=innodb;
ALTER TABLE cc DISABLE KEYS;
Warnings:
Note	1031	Table storage engine for 'cc' doesn't have this option
INSERT IGNORE  INTO cc VALUES
('2006-06-04', 3, 0, 'y', '2006-04-12 00:44:48.055959', NULL, NULL, 'l',
'2005-01-10', '2004', '2004-07-14'),
('2008', 6, 8, NULL, '2006-10-23', NULL, NULL, 'a', NULL, NULL, '2000-04-26'),
('2009-06-11', NULL, 9, 'w', '2008', '2005', NULL, 'q', '04:42:05.061538',
'2004-08-18', NULL),
('2007-03-01', 4, 7, 'f', NULL, '2000-10-06 15:26:40.040137', NULL, 'd',
'2008', '2006-11-17', '2006'),
('2001-02-08', 4, 210, 'j', '2003-11-14 04:26:34.047333', NULL, NULL, 'h',
'06:13:13.012974', '02:20:21.050151', '2006-08-20'),
('2000', 9, 5, 'b', '2006-12-16', NULL, NULL, 'z', '2000-09-09', '2007-06-15',
'2008'),
(NULL, 1, 6, 'z', '2007-12-10 00:57:04.007939', NULL, NULL, 'i', '2002-02-11',
'2004', '2006-08-08'),
('2007', NULL, 1, 'w', '2007-09-03 21:11:14.028959', '2009', NULL, 'n',
'2009-05-03', '2005-06-23', NULL),
(NULL, 4, NULL, 'f', '2007-04-12', NULL, NULL, 'f', '2007-12-01', '2006',
'2000-05-11'),
('2008', 7, 1, 's', NULL, NULL, NULL, 'o', '2002', '2003', '2009-12-03'),
(NULL, 5, 62, 'i', '2009-10-06 12:22:10.055548', '2003', NULL, 'p', NULL,
NULL, '2006-02-03'),
('2006-02-10', 4, 9, 'g', NULL, '2000-07-26 23:20:24.031805', NULL, 'c',
'2007-12-12', '2002', '2003'),
('2000', 5, 0, 'j', '2000-02-23', '2000', NULL, 'a', '2005', '2000-04-15',
'2000-09-19'),
(NULL, 2, 9, 'q', '2003-12-24', NULL, NULL, NULL, NULL, '2000', '2008-05-23'),
(NULL, 9, NULL, 'i', '2003-10-22 02:03:47.003490', '2006-01-03', NULL, 'b',
NULL, '2003', '2008-01-21'),
('2008-06-09', 9, 0, 'a', '2000', NULL, NULL, 'c', '21:15:46.049912', '2001',
NULL),
('2000', 2, 8, NULL, '2009-11-27', NULL, NULL, NULL, '2004-05-08',
'12:30:30.041709', '2005-12-01'),
('2009-03-27', 3, 0, 'l', '2009', '2009', NULL, 'a', NULL, '04:16:53.049190',
NULL),
('2008-08-26', 114, 3, 'o', '2008-03-06', NULL, NULL, 'k', '07:26:47.018798',
'2002-08-17', '2004-09-07'),
(NULL, 8, 7, 'm', '2007-12-28 23:49:04.022501', '2005-04-08', NULL, 't',
'2000-11-12', '22:19:29.060590', '2005-09-20');
Warnings:
Warning	1265	Data truncated for column 'col_time_key' at row 1
Warning	1265	Data truncated for column 'col_date' at row 2
Warning	1265	Data truncated for column 'col_datetime_key' at row 3
Warning	1265	Data truncated for column 'col_datetime' at row 3
Warning	1265	Data truncated for column 'col_time' at row 3
Warning	1265	Data truncated for column 'col_time' at row 4
Warning	1265	Data truncated for column 'col_date_key' at row 4
Warning	1265	Data truncated for column 'col_date' at row 6
Warning	1265	Data truncated for column 'col_time_key' at row 6
Warning	1265	Data truncated for column 'col_time' at row 6
Warning	1265	Data truncated for column 'col_date_key' at row 6
Warning	1265	Data truncated for column 'col_time_key' at row 7
Warning	1265	Data truncated for column 'col_date' at row 8
Warning	1265	Data truncated for column 'col_datetime' at row 8
Warning	1265	Data truncated for column 'col_time_key' at row 8
Warning	1265	Data truncated for column 'col_time' at row 8
Warning	1265	Data truncated for column 'col_time_key' at row 9
Warning	1265	Data truncated for column 'col_date' at row 10
Warning	1265	Data truncated for column 'col_datetime' at row 11
Warning	1265	Data truncated for column 'col_time_key' at row 12
Warning	1265	Data truncated for column 'col_date_key' at row 12
Warning	1265	Data truncated for column 'col_date' at row 13
Warning	1265	Data truncated for column 'col_datetime' at row 13
Warning	1265	Data truncated for column 'col_time' at row 13
Warning	1265	Data truncated for column 'col_datetime_key' at row 16
Warning	1265	Data truncated for column 'col_date' at row 17
Warning	1265	Data truncated for column 'col_time_key' at row 17
Warning	1265	Data truncated for column 'col_datetime_key' at row 18
Warning	1265	Data truncated for column 'col_datetime' at row 18
Warning	1265	Data truncated for column 'col_time' at row 19
Warning	1265	Data truncated for column 'col_time_key' at row 20
ALTER TABLE cc ENABLE KEYS;
Warnings:
Note	1031	Table storage engine for 'cc' doesn't have this option
ANALYZE TABLE c, cc;
Table	Op	Msg_type	Msg_text
test.c	analyze	status	OK
test.cc	analyze	status	OK
EXPLAIN FORMAT=tree SELECT
alias1.pk AS field1
FROM
(
SELECT
sq1_alias2.*
FROM
cc AS sq1_alias1
RIGHT JOIN cc AS sq1_alias2 ON
sq1_alias2.col_varchar_key = sq1_alias1.col_varchar_key
LIMIT
100
)
AS alias1
WHERE
alias1.col_varchar_key
IN (
SELECT
sq2_alias1.col_varchar_key AS sq2_field1
FROM
(cc AS sq2_alias1, c AS sq2_alias2)
WHERE
sq2_alias1.col_varchar_key != alias1.col_varchar
)
GROUP BY
field1
HAVING
field1 != 'pg'
ORDER BY
alias1.col_int_key DESC, field1
LIMIT 2 OFFSET 2;
EXPLAIN
-> Limit/Offset: 2/2 row(s)
    -> Sort: alias1.col_int_key DESC, alias1.pk
        -> Filter: (field1 <> 0)
            -> Table scan on <temporary>  (cost=208..222 rows=936)
                -> Temporary table with deduplication  (cost=208..208 rows=936)
                    -> Remove duplicate alias1 rows using temporary table (weedout)  (cost=114 rows=936)
                        -> Inner hash join (no condition)  (cost=114 rows=936)
                            -> Index scan on sq2_alias2 using col_date_key  (cost=1.93 rows=20)
                            -> Hash
                                -> Nested loop inner join  (cost=20.4 rows=46.8)
                                    -> Filter: (sq2_alias1.col_varchar_key is not null)  (cost=2.25 rows=20)
                                        -> Index scan on sq2_alias1 using col_varchar_key  (cost=2.25 rows=20)
                                    -> Filter: (sq2_alias1.col_varchar_key <> alias1.col_varchar)  (cost=7.49..0.662 rows=2.34)
                                        -> Index lookup on alias1 using <auto_key0> (col_varchar_key=sq2_alias1.col_varchar_key)  (cost=12.8..13.2 rows=2.6)
                                            -> Materialize  (cost=12.6..12.6 rows=26.7)
                                                -> Limit: 100 row(s)  (cost=9.92 rows=26.7)
                                                    -> Nested loop left join  (cost=9.92 rows=26.7)
                                                        -> Table scan on sq1_alias2  (cost=2.25 rows=20)
                                                        -> Covering index lookup on sq1_alias1 using col_varchar_key (col_varchar_key=sq1_alias2.col_varchar_key)  (cost=0.257 rows=1.33)

Warnings:
Note	1276	Field or reference 'alias1.col_varchar' of SELECT #3 was resolved in SELECT #1
Warning	1292	Truncated incorrect DOUBLE value: 'pg'
SELECT
alias1.pk AS field1
FROM
(
SELECT
sq1_alias2.*
FROM
cc AS sq1_alias1
RIGHT JOIN cc AS sq1_alias2 ON
sq1_alias2.col_varchar_key = sq1_alias1.col_varchar_key
LIMIT
100
)
AS alias1
WHERE
alias1.col_varchar_key
IN (
SELECT
sq2_alias1.col_varchar_key AS sq2_field1
FROM
(cc AS sq2_alias1, c AS sq2_alias2)
WHERE
sq2_alias1.col_varchar_key != alias1.col_varchar
)
GROUP BY
field1
HAVING
field1 != 'pg'
ORDER BY
alias1.col_int_key DESC, field1
LIMIT 2 OFFSET 2;
field1
3
12
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'pg'
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime_key' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime_key' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date_key' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date_key' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date_key' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date_key' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime_key' at row 1
Warning	1292	Incorrect date value: '0000-00-00' for column 'col_date' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime_key' at row 1
Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00' for column 'col_datetime' at row 1
DROP TABLE c, cc;
#
# Bug#30119783 SIG11 IN
#   HASH_JOIN_BUFFER::STOREFROMTABLEBUFFERS|SQL/HASH_JOIN_BUFFER.CC
#
CREATE TABLE b(pk INT PRIMARY KEY, col_varchar VARCHAR(1));
CREATE TABLE cc(pk INT PRIMARY KEY, col_varchar VARCHAR(1));
INSERT INTO b VALUES (1, '4');
INSERT INTO cc VALUES (1, 'c'), (2, 'c');
EXPLAIN FORMAT=tree SELECT
table1.col_varchar
FROM
(
SELECT
subquery1_t1.*
FROM
b AS subquery1_t1
INNER JOIN cc AS subquery1_t2 ON
subquery1_t1.col_varchar = subquery1_t2.col_varchar
)
AS table1
LEFT JOIN (
SELECT
col_varchar
FROM
cc AS subquery2_t1
GROUP BY
subquery2_t1.col_varchar
)
AS table2 ON
table2.col_varchar = table1.col_varchar
AND table1.col_varchar
IN (
SELECT
lower(subquery3_t1.pk) AS subquery3_field1
FROM
b AS subquery3_t1
);
EXPLAIN
-> Nested loop left join  (cost=1.5 rows=2)
    -> Inner hash join (subquery1_t2.col_varchar = subquery1_t1.col_varchar)  (cost=0.8 rows=1)
        -> Table scan on subquery1_t2  (cost=0.35 rows=2)
        -> Hash
            -> Table scan on subquery1_t1  (cost=0.35 rows=1)
    -> Nested loop semijoin  (cost=4.53 rows=2)
        -> Covering index lookup on table2 using <auto_key0> (col_varchar=subquery1_t1.col_varchar)  (cost=3.73..4.08 rows=2)
            -> Materialize  (cost=3.38..3.38 rows=2)
                -> Table scan on <temporary>  (cost=1.91..3.18 rows=2)
                    -> Temporary table with deduplication  (cost=0.65..0.65 rows=2)
                        -> Table scan on subquery2_t1  (cost=0.45 rows=2)
        -> Filter: (subquery1_t1.col_varchar = lower(subquery3_t1.pk))  (cost=0.175 rows=1)
            -> Index scan on subquery3_t1 using PRIMARY  (cost=0.175 rows=1)

SELECT
table1.col_varchar
FROM
(
SELECT
subquery1_t1.*
FROM
b AS subquery1_t1
INNER JOIN cc AS subquery1_t2 ON
subquery1_t1.col_varchar = subquery1_t2.col_varchar
)
AS table1
LEFT JOIN (
SELECT
col_varchar
FROM
cc AS subquery2_t1
GROUP BY
subquery2_t1.col_varchar
)
AS table2 ON
table2.col_varchar = table1.col_varchar
AND table1.col_varchar
IN (
SELECT
lower(subquery3_t1.pk) AS subquery3_field1
FROM
b AS subquery3_t1
);
col_varchar
DROP TABLE b, cc;
#
# Bug#30049083 [REGRESSION]REPLACE/INSERT WITH LIMIT TAKING MORE TIME AND
#              SPACE
#
# If the query has a LIMIT, the hash join should not spill to disk. Note
# that if the query contains either grouping or sorting, we allow spill
# to disk even if the query contains a LIMIT.
CREATE TABLE t1 (col1 BIGINT);
INSERT INTO t1 SELECT 1;
INSERT INTO t1 SELECT col1 + 1 FROM t1;
INSERT INTO t1 SELECT col1 + 2 FROM t1;
INSERT INTO t1 SELECT col1 + 4 FROM t1;
INSERT INTO t1 SELECT col1 + 8 FROM t1;
INSERT INTO t1 SELECT col1 + 16 FROM t1;
INSERT INTO t1 SELECT col1 + 32 FROM t1;
INSERT INTO t1 SELECT col1 + 64 FROM t1;
INSERT INTO t1 SELECT col1 + 128 FROM t1;
INSERT INTO t1 SELECT col1 + 256 FROM t1;
INSERT INTO t1 SELECT col1 + 512 FROM t1;
CREATE TABLE t2 SELECT col1 FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 2048;
# This should spill to disk since we do not have any LIMIT.
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1;
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
# This should NOT spill to disk since we have a LIMIT.
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1 LIMIT 1;
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
0
# This should spill to disk since we have sorting.
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1 ORDER BY t1.col1 LIMIT 1;
col1	col1
1	1
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
# This should spill to disk since we have (implicit) grouping.
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT SUM(t1.col1) FROM t1, t2 WHERE t1.col1 = t2.col1 LIMIT 10;
SUM(t1.col1)
524800
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
SET join_buffer_size = DEFAULT;
DROP TABLE t1,t2;
#
# Bug#30214767 SIG11 AT QUICK_INDEX_MERGE_SELECT::GET_NEXT |
#              SQL/OPT_RANGE.CC
#
# Set up a query with hash join, where the build input uses an index
# range scan with index merge sort-union. Also, a LIMIT greater than
# the number of rows satisfying the join condition is needed to
# reproduce the bug. What we want to achieve is to get the hash join
# to call Read() on the build input after it has returned EOF. This can
# be triggered by using LIMIT, as this causes the hash join to go back
# and read from the build input after the probe iterator has returned
# EOF (see comment on HashJoinIterator regarding spill to disk and LIMIT
# for more details around this).
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
INDEX idx_a (col2),
INDEX idx_b (col3));
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1, 1, 1);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT /*+ JOIN_ORDER(t2, t1) INDEX_MERGE(t2) */ t1.col1
FROM t1
JOIN t2 ON t1.col1 = t2.col1
WHERE t2.col2 > 0 OR t2.col3 > 0 LIMIT 10;
EXPLAIN
-> Limit: 10 row(s)  (cost=1.86 rows=1)
    -> Inner hash join (t1.col1 = t2.col1)  (cost=1.86 rows=1)
        -> Table scan on t1  (cost=0.35 rows=1)
        -> Hash
            -> Filter: ((t2.col2 > 0) or (t2.col3 > 0))  (cost=1.51 rows=1)
                -> Sort-deduplicate by row ID  (cost=1.51 rows=1)
                    -> Index range scan on t2 using idx_a over (0 < col2)  (cost=0.36 rows=1)
                    -> Index range scan on t2 using idx_b over (0 < col3)  (cost=0.36 rows=1)

SELECT /*+ JOIN_ORDER(t2, t1) INDEX_MERGE(t2) */ t1.col1
FROM t1
JOIN t2 ON t1.col1 = t2.col1
WHERE t2.col2 > 0 OR t2.col3 > 0 LIMIT 10;
col1
1
DROP TABLE t1, t2;
#
# Bug#30224582 ASSERTION `M_INDEX_CURSOR.IS_POSITIONED()' FAILED
#
# Set up a query where the hash join build input consists of a
# materialized table, where we do an index lookup on the materialized
# table. The LIMIT is also needed in order to trigger a second build
# phase in the hash join.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
EXPLAIN FORMAT=tree SELECT /*+ JOIN_ORDER(table1, t2) */
*
FROM
(
SELECT
DISTINCT t1.*
FROM
t1
) AS table1 JOIN t2
WHERE table1.col1 = 1
LIMIT 50;
EXPLAIN
-> Limit: 50 row(s)  (cost=0.35 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)

SELECT /*+ JOIN_ORDER(table1, t2) */
*
FROM
(
SELECT
DISTINCT t1.*
FROM
t1
) AS table1 JOIN t2
WHERE table1.col1 = 1
LIMIT 50;
col1	col1
1	1
DROP TABLE t1, t2;
#
# Bug#30306279 ASAN: MEMCPY-PARAM-OVERLAP NEAR
#              EXTRACT_VALUE_FOR_HASH_JOIN
#
# Set up a test where geometry data is written to chunk files on disk.
# Note that the test only fails with ASAN.
CREATE TABLE t1 (col1 GEOMETRY);
INSERT INTO t1 SELECT ST_GeomFromText(CONCAT('GEOMETRYCOLLECTION(POINT(1 1)',
REPEAT(',POINT(1 1)', 100), ')'));
INSERT INTO t1 SELECT ST_GeomFromText(CONCAT('GEOMETRYCOLLECTION(POINT(1 1)',
REPEAT(',POINT(1 1)', 100), ')')) FROM t1;
INSERT INTO t1 SELECT ST_GeomFromText(CONCAT('GEOMETRYCOLLECTION(POINT(1 1)',
REPEAT(',POINT(1 1)', 100), ')')) FROM t1;
SET join_buffer_size = 128;
SELECT * FROM t1 a JOIN t1 b ON a.col1 = b.col1;
SET join_buffer_size = DEFAULT;
DROP TABLE t1;
#
# Bug#30535541 HASH JOIN DOES NOT HANDLE PAD SPACE COLLATIONS CORRECTLY
#
# Test that hash join compares strings that only differs in end spaces
# equally in PAD SPACE collations.
CREATE TABLE t1 (c VARCHAR(255) COLLATE utf8mb4_bin);
CREATE TABLE t2 (c VARCHAR(255) COLLATE utf8mb4_bin);
INSERT INTO t1 VALUES ('foo');
INSERT INTO t2 VALUES ('foo ');
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.c = t2.c;
EXPLAIN
-> Inner hash join (t2.c = t1.c)  (cost=0.701 rows=1)
    -> Table scan on t2  (cost=0.351 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

SELECT * FROM t1 JOIN t2 ON t1.c = t2.c;
c	c
foo	foo 
DROP TABLE t1, t2;
#
# Bug#30579922 INCORRECT RESULT IF HASH-JOIN ALGORITHM EXHAUST ITS
#              JOIN_BUFFER
#
# Set up a query where the build input is a nested loop, where the inner
# side of the nested loop also has a filter. We want to see that if the
# hash join is not allowed to spill to disk, it will restore the last row
# read from the build iterator before doing a re-fill of the hash table.
CREATE TABLE t1 (i BIGINT);
INSERT INTO t1 SELECT 1;
INSERT INTO t1 SELECT i + POW(2, 0) FROM t1;
INSERT INTO t1 SELECT i + POW(2, 1) FROM t1;
INSERT INTO t1 SELECT i + POW(2, 2) FROM t1;
INSERT INTO t1 SELECT i + POW(2, 3) FROM t1;
INSERT INTO t1 SELECT i + POW(2, 4) FROM t1;
INSERT INTO t1 SELECT i + POW(2, 5) FROM t1;
CREATE TABLE t2 AS SELECT i FROM t1;
CREATE TABLE t3 AS SELECT i + 60 AS i FROM t1;
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
SET join_buffer_size = 128;
EXPLAIN FORMAT=tree SELECT /*+ NO_BNL(t2) JOIN_ORDER(t1, t2, t3) */ * FROM t1
JOIN t2 ON t1.i > t2.i
JOIN t3 ON t2.i = t3.i;
EXPLAIN
-> Inner hash join (t3.i = t2.i)  (cost=9218 rows=8737)
    -> Table scan on t3  (cost=0.0358 rows=64)
    -> Hash
        -> Nested loop inner join  (cost=432 rows=1365)
            -> Table scan on t1  (cost=6.65 rows=64)
            -> Filter: (t1.i > t2.i)  (cost=0.283 rows=21.3)
                -> Table scan on t2  (cost=0.283 rows=64)

SELECT /*+ NO_BNL(t2) JOIN_ORDER(t1, t2, t3) */ * FROM t1
JOIN t2 ON t1.i > t2.i
JOIN t3 ON t2.i = t3.i;
i	i	i
62	61	61
63	61	61
63	62	62
64	61	61
64	62	62
64	63	63
SELECT /*+ NO_BNL(t2) JOIN_ORDER(t1, t2, t3) */ * FROM t1
JOIN t2 ON t1.i > t2.i
JOIN t3 ON t2.i = t3.i LIMIT 1000;
i	i	i
62	61	61
63	61	61
63	62	62
64	61	61
64	62	62
64	63	63
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2, t3;
#
#  Bug#30282693 SIG6 IN TEMPTABLE::HANDLER::POSITION() AT SRC/HANDLER.CC
#
# Create a query where we get a confluent weedout as the right side of an
# antijoin. Since a confluent weedout returns at most one row, the
# iterator executor will replace it with a LIMIT 1. Note that the test
# case is not easy to understand, but it is the smallest test case I
# could make that reproduces the bug.
CREATE TABLE t1 (
col1 INTEGER,
KEY idx (col1)
);
CREATE TABLE t2 (
col1 INTEGER,
KEY idx (col1)
);
INSERT INTO t2 VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT /*+ JOIN_ORDER(const_table, t2, t1, t1_2) */ 1 FROM t2
RIGHT OUTER JOIN (VALUES ROW(4), ROW(5)) AS const_table ON TRUE
WHERE NOT EXISTS (
SELECT t1.col1 FROM t1
INNER JOIN t1 AS t1_2 ON t1_2.col1 = t1.col1
WHERE t1.col1 <> t2.col1
) AND t2.col1 < 49;
EXPLAIN
-> Nested loop antijoin  (cost=10 rows=34)
    -> Inner hash join (no condition)  (cost=6.38 rows=34)
        -> Filter: (t2.col1 < 49)  (cost=0.975 rows=17)
            -> Index scan on t2 using idx  (cost=0.975 rows=17)
        -> Hash
            -> Table scan on const_table  (cost=1.46..2.73 rows=2)
                -> Materialize  (cost=0.2..0.2 rows=2)
                    -> Rows fetched before execution  (cost=0..0 rows=2)
    -> Limit: 1 row(s)  (cost=8.61 rows=1)
        -> Nested loop inner join  (cost=8.61 rows=1)
            -> Filter: (t1.col1 <> t2.col1)  (cost=0.0103 rows=1)
                -> Index scan on t1 using idx  (cost=0.0103 rows=1)
            -> Covering index lookup on t1_2 using idx (col1=t1.col1)  (cost=0.253 rows=1)

Warnings:
Note	1276	Field or reference 'test.t2.col1' of SELECT #3 was resolved in SELECT #1
SELECT /*+ JOIN_ORDER(const_table, t2, t1, t1_2) */ 1 FROM t2
RIGHT OUTER JOIN (VALUES ROW(4), ROW(5)) AS const_table ON TRUE
WHERE NOT EXISTS (
SELECT t1.col1 FROM t1
INNER JOIN t1 AS t1_2 ON t1_2.col1 = t1.col1
WHERE t1.col1 <> t2.col1
) AND t2.col1 < 49;
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
DROP TABLE t1, t2;
#
# Bug#30626264 ASSERTS IN BASIC_ROW_ITERATORS.H:345:
#              FAKESINGLEROWITERATOR::SETNULLROWFLAG
#
CREATE TABLE t1 (
pk INTEGER
);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (
pk INTEGER NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (9);
CREATE TABLE t3 (
pk INTEGER
);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
explain format=tree SELECT (
SELECT t1.pk
FROM t2
LEFT JOIN t3 USING (pk)
JOIN t3 AS t4 USING (pk)
WHERE t2.pk = 9
) FROM t1;
EXPLAIN
-> Table scan on t1  (cost=0.45 rows=2)
-> Select #2 (subquery in projection; dependent)
    -> Inner hash join (no condition)  (cost=0.7 rows=1)
        -> Filter: (t4.pk = 9)  (cost=0.35 rows=1)
            -> Table scan on t4  (cost=0.35 rows=1)
        -> Hash
            -> Nested loop left join  (cost=0.35 rows=1)
                -> Rows fetched before execution  (cost=0..0 rows=1)
                -> Filter: (t3.pk = 9)  (cost=0.35 rows=1)
                    -> Table scan on t3  (cost=0.35 rows=1)

Warnings:
Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
SELECT (
SELECT t1.pk
FROM t2
LEFT JOIN t3 USING (pk)
JOIN t3 AS t4 USING (pk)
WHERE t2.pk = 9
) FROM t1;
(
SELECT t1.pk
FROM t2
LEFT JOIN t3 USING (pk)
JOIN t3 AS t4 USING (pk)
WHERE t2.pk = 9
)
NULL
NULL
DROP TABLE t1, t2, t3;
# See that non equi-join BNL are executed using hash join.
CREATE TABLE t1 (col1 INT);
CREATE TABLE t2 (col1 INT);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 < t2.col1;
EXPLAIN
-> Filter: (t1.col1 < t2.col1)  (cost=0.7 rows=1)
    -> Inner hash join (no condition)  (cost=0.7 rows=1)
        -> Table scan on t2  (cost=0.35 rows=1)
        -> Hash
            -> Table scan on t1  (cost=0.35 rows=1)

DROP TABLE t1, t2;
# Semijoin.
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (1);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
EXPLAIN
-> Hash semijoin (t2.i = t1.i)  (cost=0.825 rows=2)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Table scan on t2  (cost=0.175 rows=2)

SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
i
1
DROP TABLE t1, t2;
CREATE TABLE t1 (col1 INTEGER, col2 INTEGER);
CREATE TABLE t2 (col1 INTEGER, col2 INTEGER);
INSERT INTO t1 VALUES (1, 1), (2, 2);
INSERT INTO t2 VALUES (1, 1), (1, 2);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree
SELECT * FROM t1 WHERE (t1.col1) IN
(SELECT t2.col1 FROM t2 WHERE t2.col2 < 3);
EXPLAIN
-> Hash semijoin (t2.col1 = t1.col1)  (cost=0.775 rows=1)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Filter: (t2.col2 < 3)  (cost=0.225 rows=1)
            -> Table scan on t2  (cost=0.225 rows=2)

DROP TABLE t1, t2;
# Test a query where the optimizer will create a semijoin condition that
# is not an equi-join condition. These kind of conditions must be
# attached as "extra" conditions in the hash join iterator in order to
# produce correct results.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (3), (4), (5);
INSERT INTO t2 VALUES (2), (3), (4), (6);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE 1 IN (SELECT t1.col1 = t2.col1 FROM t2);
EXPLAIN
-> Hash semijoin (no condition), extra conditions: (1 = (t1.col1 = t2.col1))  (cost=2.5 rows=16)
    -> Table scan on t1  (cost=0.65 rows=4)
    -> Hash
        -> Table scan on t2  (cost=0.65 rows=4)

Warnings:
Note	1276	Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1 WHERE 1 IN (SELECT t1.col1 = t2.col1 FROM t2);
col1
3
4
DROP TABLE t1, t2;
# Exercise a test where we get multiple tables in the probe input.
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
CREATE TABLE t3(a INTEGER);
INSERT INTO t1 VALUES (0);
INSERT INTO t2 VALUES (0);
INSERT INTO t3 VALUES (0);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
SET optimizer_switch="materialization=off,duplicateweedout=off";
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE (t1.a,t2.a) IN (SELECT a, a FROM t3);
EXPLAIN
-> Hash semijoin (t3.a = t1.a)  (cost=0.7 rows=1)
    -> Inner hash join (t2.a = t1.a)  (cost=0.7 rows=1)
        -> Table scan on t2  (cost=0.35 rows=1)
        -> Hash
            -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t3  (cost=0.35 rows=1)

SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE (t1.a,t2.a) IN (SELECT a, a FROM t3);
a	a
0	0
SET optimizer_switch=DEFAULT;
DROP TABLE t1, t2, t3;
# See that semijoin with spill to disk works as expected.
CREATE TABLE t1 (col1 BIGINT, col2 BIGINT);
INSERT INTO t1 SELECT POW(2, 0), POW(2, 0);
INSERT INTO t1
SELECT col1 + POW(2, 0), col2 + POW(2, 0) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 1), col2 + POW(2, 1) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 2), col2 + POW(2, 2) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 3), col2 + POW(2, 3) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 4), col2 + POW(2, 4) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 5), col2 + POW(2, 5) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 6), col2 + POW(2, 6) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 7), col2 + POW(2, 7) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 8), col2 + POW(2, 8) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 9), col2 + POW(2, 9) FROM t1;
CREATE TABLE t2 AS SELECT * FROM t1;
UPDATE t1 SET col2 = col1 + 1 WHERE MOD(col1, 2) = 0;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 1024;
SET optimizer_switch = "materialization=off";
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 IN
(SELECT t2.col1 FROM t2);
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=115369 rows=1)
    -> Hash semijoin (t2.col1 = t1.col1)  (cost=104883 rows=104858)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=3.27 rows=1024)

SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 IN
(SELECT t2.col1 FROM t2);
SUM(t1.col1)	SUM(t1.col2)
524800	525312
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 IN
(SELECT t2.col1 FROM t2 WHERE t1.col2 <> t2.col2);
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=114320 rows=1)
    -> Hash semijoin (t2.col1 = t1.col1), extra conditions: (t1.col2 <> t2.col2)  (cost=104883 rows=94372)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=2.85 rows=1024)

Warnings:
Note	1276	Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1
SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 IN
(SELECT t2.col1 FROM t2 WHERE t1.col2 <> t2.col2);
SUM(t1.col1)	SUM(t1.col2)
262656	263168
SET optimizer_switch = DEFAULT;
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2;
# Set up a test case where hash join spills to disk, and the chunk files
# for the build input is too big to fit into memory. This causes the
# probe chunk file to be read multiple times. What we want to see is that
# if a row from the probe chunk file finds a match in the first part of
# the build chunk file, the row must not be sent to the client the second
# time the row is read from the probe chunk. Note that it is not trivial
# to set up a test case that is guaranteed to hit this case.
CREATE TABLE build (col1 BIGINT);
CREATE TABLE probe (col1 BIGINT);
INSERT INTO probe SELECT 1;
INSERT INTO probe SELECT col1 + POW(2, 0) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 1) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 2) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 3) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 4) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 5) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 6) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 7) FROM probe;
INSERT INTO build SELECT col1 FROM probe;
INSERT INTO build SELECT col1 * 2 FROM probe;
INSERT INTO build SELECT col1 * 3 FROM probe;
SELECT COUNT(*) AS expected_num_rows FROM probe;
expected_num_rows
256
SET join_buffer_size = 128;
SET optimizer_switch="materialization=off";
EXPLAIN FORMAT=tree SELECT COUNT(*) FROM probe WHERE col1 IN (SELECT col1 FROM build);
EXPLAIN
-> Aggregate: count(0)  (cost=21635 rows=1)
    -> Hash semijoin (build.col1 = probe.col1)  (cost=19669 rows=19661)
        -> Table scan on probe  (cost=25.9 rows=256)
        -> Hash
            -> Table scan on build  (cost=3.73 rows=768)

SELECT COUNT(*) FROM probe WHERE col1 IN (SELECT col1 FROM build);
COUNT(*)
256
SET join_buffer_size = DEFAULT;
SET optimizer_switch = DEFAULT;
DROP TABLE build, probe;
# Using LIMIT in the query rejects hash join from spilling to disk,
# causing the probe input to be read multiple times if the build input
# does not fit in memory. This test case is somewhat similar to the
# above; see that we do not output rows from the probe input multiple
# times even if the probe input is read multiple times.
CREATE TABLE build (col1 BIGINT);
CREATE TABLE probe (col1 BIGINT);
INSERT INTO probe SELECT 1;
INSERT INTO probe SELECT col1 + POW(2, 0) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 1) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 2) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 3) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 4) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 5) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 6) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 7) FROM probe;
INSERT INTO build SELECT col1 FROM probe;
INSERT INTO build SELECT col1 FROM probe ORDER BY col1 DESC;
SET optimizer_switch = "materialization=off";
SET join_buffer_size = 1024;
EXPLAIN FORMAT=tree SELECT * FROM probe WHERE col1 IN (SELECT * FROM build);
EXPLAIN
-> Hash semijoin (build.col1 = probe.col1)  (cost=13109 rows=13107)
    -> Table scan on probe  (cost=25.9 rows=256)
    -> Hash
        -> Table scan on build  (cost=1.19 rows=512)

SELECT * FROM probe WHERE col1 IN (SELECT * FROM build);
col1
1
10
100
101
102
103
104
105
106
107
108
109
11
110
111
112
113
114
115
116
117
118
119
12
120
121
122
123
124
125
126
127
128
129
13
130
131
132
133
134
135
136
137
138
139
14
140
141
142
143
144
145
146
147
148
149
15
150
151
152
153
154
155
156
157
158
159
16
160
161
162
163
164
165
166
167
168
169
17
170
171
172
173
174
175
176
177
178
179
18
180
181
182
183
184
185
186
187
188
189
19
190
191
192
193
194
195
196
197
198
199
2
20
200
201
202
203
204
205
206
207
208
209
21
210
211
212
213
214
215
216
217
218
219
22
220
221
222
223
224
225
226
227
228
229
23
230
231
232
233
234
235
236
237
238
239
24
240
241
242
243
244
245
246
247
248
249
25
250
251
252
253
254
255
256
26
27
28
29
3
30
31
32
33
34
35
36
37
38
39
4
40
41
42
43
44
45
46
47
48
49
5
50
51
52
53
54
55
56
57
58
59
6
60
61
62
63
64
65
66
67
68
69
7
70
71
72
73
74
75
76
77
78
79
8
80
81
82
83
84
85
86
87
88
89
9
90
91
92
93
94
95
96
97
98
99
SELECT * FROM probe WHERE col1 IN (SELECT * FROM build) LIMIT 1000;
col1
1
10
100
101
102
103
104
105
106
107
108
109
11
110
111
112
113
114
115
116
117
118
119
12
120
121
122
123
124
125
126
127
128
129
13
130
131
132
133
134
135
136
137
138
139
14
140
141
142
143
144
145
146
147
148
149
15
150
151
152
153
154
155
156
157
158
159
16
160
161
162
163
164
165
166
167
168
169
17
170
171
172
173
174
175
176
177
178
179
18
180
181
182
183
184
185
186
187
188
189
19
190
191
192
193
194
195
196
197
198
199
2
20
200
201
202
203
204
205
206
207
208
209
21
210
211
212
213
214
215
216
217
218
219
22
220
221
222
223
224
225
226
227
228
229
23
230
231
232
233
234
235
236
237
238
239
24
240
241
242
243
244
245
246
247
248
249
25
250
251
252
253
254
255
256
26
27
28
29
3
30
31
32
33
34
35
36
37
38
39
4
40
41
42
43
44
45
46
47
48
49
5
50
51
52
53
54
55
56
57
58
59
6
60
61
62
63
64
65
66
67
68
69
7
70
71
72
73
74
75
76
77
78
79
8
80
81
82
83
84
85
86
87
88
89
9
90
91
92
93
94
95
96
97
98
99
SET optimizer_switch = DEFAULT;
SET join_buffer_size = DEFAULT;
DROP TABLE build, probe;
#
# Bug#30512118 WL#13476: ASSERTION FAILURE AT
#              HASHJOINITERATOR::INITPROBEITERATOR()
#
# Execute a query that causes the hash join to do everything in memory
# with multiple refills of the hash table. This can be triggered when the
# build input does not fit in memory, and the iterator is not allowed to
# spill to disk due to LIMIT in the query.
CREATE TABLE t1 (col1 VARCHAR(255));
INSERT INTO t1 SELECT UUID();
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
CREATE TABLE t2 AS SELECT UUID() AS col1 FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 128;
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1 LIMIT 1000;
EXPLAIN
-> Limit: 1000 row(s)  (cost=1907 rows=1000)
    -> Filter: (t1.col1 = t2.col1)  (cost=1907 rows=1638)
        -> Inner hash join (<hash>(t1.col1)=<hash>(t2.col1))  (cost=1907 rows=1638)
            -> Table scan on t2  (cost=2.01 rows=128)
            -> Hash
                -> Table scan on t1  (cost=13.1 rows=128)

SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1 LIMIT 1000;
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2;
# Antijoin.
CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TABLE t2 (f1 INTEGER NOT NULL);
INSERT INTO t2 VALUES (1), (2), (3), (4);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 USING (f1) WHERE t2.f1 IS NULL;
EXPLAIN
-> Filter: (t2.f1 is null)  (cost=1.97 rows=3)
    -> Hash antijoin (t2.f1 = t1.f1)  (cost=1.97 rows=3)
        -> Index scan on t1 using PRIMARY  (cost=0.55 rows=3)
        -> Hash
            -> Table scan on t2  (cost=0.217 rows=4)

SELECT * FROM t1 LEFT JOIN t2 USING (f1) WHERE t2.f1 IS NULL;
f1
DROP TABLE t1,t2;
# Set up a case where we get multiple antijoin conditions AND'ed together
# inside a is_not_null_compl. We want to see that the server is able to
# split these conditions and attach them as antijoin conditions to the
# hash join.
CREATE TABLE t1 (
aid INTEGER NOT NULL,
bid INTEGER NOT NULL);
CREATE TABLE t2 LIKE t1;
INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
INSERT INTO t2 VALUES (1,2),(2,2);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1
WHERE t1.aid NOT IN (SELECT aid FROM t2 WHERE bid = t1.bid);
EXPLAIN
-> Hash antijoin (t2.bid = t1.bid), (t2.aid = t1.aid)  (cost=1.28 rows=8)
    -> Table scan on t1  (cost=0.65 rows=4)
    -> Hash
        -> Table scan on t2  (cost=0.225 rows=2)

Warnings:
Note	1276	Field or reference 'test.t1.bid' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1
WHERE t1.aid NOT IN (SELECT aid FROM t2 WHERE bid = t1.bid);
aid	bid
1	1
2	1
DROP TABLE t1, t2;
# Check that hash antijoin can handle NULL values correctly.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (NULL);
EXPLAIN FORMAT=tree SELECT * FROM t1
WHERE 1 IN (SELECT 1 FROM t2 WHERE t1.col1 = t2.col1) IS FALSE;
EXPLAIN
-> Hash antijoin (t2.col1 = t1.col1)  (cost=0.7 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

Warnings:
Note	1276	Field or reference 'test.t1.col1' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1
WHERE 1 IN (SELECT 1 FROM t2 WHERE t1.col1 = t2.col1) IS FALSE;
col1
NULL
DROP TABLE t1, t2;
# See that antijoin with spill to disk works.
CREATE TABLE t1 (col1 BIGINT NOT NULL, col2 BIGINT NOT NULL);
INSERT INTO t1 SELECT POW(2, 0), POW(2, 0);
INSERT INTO t1
SELECT col1 + POW(2, 0), col2 + POW(2, 0) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 1), col2 + POW(2, 1) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 2), col2 + POW(2, 2) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 3), col2 + POW(2, 3) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 4), col2 + POW(2, 4) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 5), col2 + POW(2, 5) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 6), col2 + POW(2, 6) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 7), col2 + POW(2, 7) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 8), col2 + POW(2, 8) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 9), col2 + POW(2, 9) FROM t1;
CREATE TABLE t2 AS SELECT * FROM t1;
UPDATE t1 SET col2 = col1 + 1 WHERE MOD(col1, 2) = 0;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 1024;
SET optimizer_switch = "materialization=off";
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 NOT IN
(SELECT t2.col1 FROM t2);
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=209860 rows=1)
    -> Hash antijoin (t2.col1 = t1.col1)  (cost=105002 rows=1.05e+6)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=124 rows=1024)

SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 NOT IN
(SELECT t2.col1 FROM t2);
SUM(t1.col1)	SUM(t1.col2)
NULL	NULL
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 NOT IN
(SELECT t2.col1 FROM t2 WHERE t1.col2 < t2.col2);
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=209860 rows=1)
    -> Hash antijoin (t2.col1 = t1.col1), extra conditions: (t1.col2 < t2.col2)  (cost=105002 rows=1.05e+6)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=124 rows=1024)

Warnings:
Note	1276	Field or reference 'test.t1.col2' of SELECT #2 was resolved in SELECT #1
SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 WHERE t1.col1 NOT IN
(SELECT t2.col1 FROM t2 WHERE t1.col2 < t2.col2);
SUM(t1.col1)	SUM(t1.col2)
524800	525312
SET optimizer_switch = DEFAULT;
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2;
# Set up an antijoin that spills to disk, and the probe chunk files are
# too big to fit in memory. This causes the hash join to read the probe
# chunk files in several smaller chunks, which in turn enables probe row
# saving. So this test tries to verify that on-disk antijoin with probe
# row saving works correctly. This is not the easiest scenario to set up
# correctly, and it is difficult to show that on-disk antijoin with probe
# row saving actually is triggered...
CREATE TABLE build (col1 BIGINT NOT NULL);
CREATE TABLE probe (col1 BIGINT NOT NULL);
INSERT INTO probe SELECT 1;
INSERT INTO probe SELECT col1 + POW(2, 0) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 1) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 2) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 3) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 4) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 5) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 6) FROM probe;
INSERT INTO probe SELECT col1 + POW(2, 7) FROM probe;
INSERT INTO build SELECT col1 FROM probe;
INSERT INTO build SELECT col1 * 2 FROM probe;
INSERT INTO build SELECT col1 * 3 FROM probe;
DELETE FROM build WHERE col1 = 254;
SET join_buffer_size = 128;
SET optimizer_switch="materialization=off";
EXPLAIN FORMAT=tree SELECT * FROM probe WHERE col1 NOT IN (SELECT col1 FROM build);
EXPLAIN
-> Hash antijoin (build.col1 = probe.col1)  (cost=19856 rows=196096)
    -> Table scan on probe  (cost=25.9 rows=256)
    -> Hash
        -> Table scan on build  (cost=242 rows=766)

SELECT * FROM probe WHERE col1 NOT IN (SELECT col1 FROM build);
col1
254
SET join_buffer_size = DEFAULT;
SET optimizer_switch = DEFAULT;
DROP TABLE build, probe;
# Outer join.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Left hash join (t2.col1 = t1.col1)  (cost=0.625 rows=2)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Table scan on t2  (cost=0.175 rows=1)

SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
col1	col1
1	1
2	NULL
DROP TABLE t1,t2;
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
CREATE TABLE t3 (col1 INTEGER);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN FORMAT=tree SELECT t1.col1, t2.col1, t3.col1 FROM t1
LEFT JOIN (
t2 LEFT JOIN t3 ON t2.col1 = t3.col1)
ON t2.col1 = t1.col1
ORDER BY t1.col1, t2.col1, t3.col1;
EXPLAIN
-> Sort: t1.col1, t2.col1, t3.col1
    -> Stream results  (cost=1.36 rows=6)
        -> Left hash join (t2.col1 = t1.col1)  (cost=1.36 rows=6)
            -> Table scan on t1  (cost=0.55 rows=3)
            -> Hash
                -> Left hash join (t3.col1 = t2.col1)  (cost=0.508 rows=2)
                    -> Table scan on t2  (cost=0.15 rows=2)
                    -> Hash
                        -> Table scan on t3  (cost=0.0583 rows=1)

SELECT t1.col1, t2.col1, t3.col1 FROM t1
LEFT JOIN (
t2 LEFT JOIN t3 ON t2.col1 = t3.col1)
ON t2.col1 = t1.col1
ORDER BY t1.col1, t2.col1, t3.col1;
col1	col1	col1
1	1	1
2	2	NULL
3	NULL	NULL
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (i INTEGER NOT NULL);
INSERT INTO t1 VALUES (0), (2), (3),(4);
CREATE TABLE t2 (i INTEGER NOT NULL);
INSERT INTO t2 VALUES (0), (1), (3),(4);
CREATE TABLE t3 (i INTEGER NOT NULL);
INSERT INTO t3 VALUES (0), (1), (2), (4);
CREATE TABLE t4 (i INTEGER NOT NULL);
INSERT INTO t4 VALUES (0), (1), (2), (3);
ANALYZE TABLE t1, t2, t3, t4;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
test.t4	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM
t1 LEFT JOIN
( t2 LEFT JOIN
( t3 LEFT JOIN
t4
ON t4.i = t3.i
)
ON t3.i = t2.i
)
ON t2.i = t1.i;
EXPLAIN
-> Left hash join (t2.i = t1.i)  (cost=34.4 rows=256)
    -> Table scan on t1  (cost=0.65 rows=4)
    -> Hash
        -> Left hash join (t2.i = t3.i), (t3.i = t2.i)  (cost=8.51 rows=64)
            -> Table scan on t2  (cost=0.163 rows=4)
            -> Hash
                -> Left hash join (t4.i = t3.i)  (cost=1.86 rows=16)
                    -> Table scan on t3  (cost=0.0406 rows=4)
                    -> Hash
                        -> Table scan on t4  (cost=0.0102 rows=4)

SELECT * FROM
t1 LEFT JOIN
( t2 LEFT JOIN
( t3 LEFT JOIN
t4
ON t4.i = t3.i
)
ON t3.i = t2.i
)
ON t2.i = t1.i;
i	i	i	i
0	0	0	0
2	NULL	NULL	NULL
3	3	NULL	NULL
4	4	4	NULL
DROP TABLE t1, t2, t3, t4;
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
CREATE TABLE t3 (col1 INTEGER);
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN (t2 INNER JOIN t3 ON t2.col1 = t3.col1)
ON t1.col1 = t2.col1;
EXPLAIN
-> Left hash join (t2.col1 = t1.col1), (t3.col1 = t1.col1)  (cost=1.05 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Inner hash join (no condition)  (cost=0.7 rows=1)
            -> Table scan on t3  (cost=0.35 rows=1)
            -> Hash
                -> Table scan on t2  (cost=0.35 rows=1)

DROP TABLE t1, t2, t3;
# Set up a case where the join condition in a left join does not refer
# to any of the tables (degenerate join predicate). It will be attached
# as an "extra" condition on the hash join iterator.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON RAND() < -1;
EXPLAIN
-> Left hash join (no condition), extra conditions: (rand() < <cache>(-(1)))  (cost=0.7 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

SELECT * FROM t1 LEFT JOIN t2 ON RAND() < -1;
col1	col1
1	NULL
DROP TABLE t1, t2;
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER, col2 INTEGER);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5);
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 1;
col1	col1	col2
1	1	1
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 2;
col1	col1	col2
1	1	2
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 3;
col1	col1	col2
1	1	3
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 4;
col1	col1	col2
1	1	4
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 5;
col1	col1	col2
1	1	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND t2.col2 = 6;
col1	col1	col2
1	NULL	NULL
DROP TABLE t1, t2;
# Run a query where we get a cached outer join condition (i.e. comparison
# between two constant values. The condition is evaluated once and then
# cached.). This condition must be placed as a condition below the
# hash join iterator, and not as a filter after it.
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
INSERT INTO t1 VALUES (1,1), (2,2);
EXPLAIN FORMAT=tree SELECT
tt.t
FROM
(SELECT 'crash1' AS t, f2 FROM t1) AS tt
LEFT JOIN t1 ON tt.t = 'crash2' AND tt.f2 = t1.f2
WHERE
tt.t = 'crash1';
EXPLAIN
-> Left hash join (no condition)  (cost=0.25 rows=0)
    -> Table scan on t1  (cost=0.45 rows=2)
    -> Hash
        -> Zero rows (Impossible filter)  (cost=0..0 rows=0)

SELECT
tt.t
FROM
(SELECT 'crash1' AS t, f2 FROM t1) AS tt
LEFT JOIN t1 ON tt.t = 'crash2' AND tt.f2 = t1.f2
WHERE
tt.t = 'crash1';
t
crash1
crash1
DROP TABLE t1;
# Set up a query where the optimizer will attach an AND condition to ot3,
# where the AND condition is inside a trigger condition guard.
# Additionally, one of the conditions inside the AND condition is a join
# condition while the other is not. What we should see in EXPLAIN is that
# the hash join iterator uses one of these conditions as a join
# condition, while the other condition should be attached as an "extra"
# condition.
CREATE TABLE t1 (col1 INTEGER);
CREATE TABLE t2 (col1 INTEGER);
EXPLAIN FORMAT=tree
SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 AND RAND() < RAND();
EXPLAIN
-> Left hash join (t2.col1 = t1.col1), extra conditions: (rand() < rand())  (cost=0.7 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

DROP TABLE t1, t2;
# See that outer join with spill to disk works.
CREATE TABLE t1 (col1 BIGINT NOT NULL, col2 BIGINT NOT NULL);
INSERT INTO t1 SELECT POW(2, 0), POW(2, 0);
INSERT INTO t1
SELECT col1 + POW(2, 0), col2 + POW(2, 0) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 1), col2 + POW(2, 1) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 2), col2 + POW(2, 2) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 3), col2 + POW(2, 3) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 4), col2 + POW(2, 4) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 5), col2 + POW(2, 5) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 6), col2 + POW(2, 6) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 7), col2 + POW(2, 7) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 8), col2 + POW(2, 8) FROM t1;
INSERT INTO t1
SELECT col1 + POW(2, 9), col2 + POW(2, 9) FROM t1;
CREATE TABLE t2 AS SELECT * FROM t1;
UPDATE t1 SET col2 = col1 + 1 WHERE MOD(col1, 2) = 0;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 1024;
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=209737 rows=1)
    -> Left hash join (t2.col1 = t1.col1)  (cost=104879 rows=1.05e+6)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=0.121 rows=1024)

SELECT SUM(t1.col1), SUM(t1.col2) FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1;
SUM(t1.col1)	SUM(t1.col2)
524800	525312
EXPLAIN FORMAT=tree SELECT SUM(t1.col1), SUM(t1.col2) FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1 AND t1.col2 < t2.col2;
EXPLAIN
-> Aggregate: sum(t1.col1), sum(t1.col2)  (cost=209737 rows=1)
    -> Left hash join (t2.col1 = t1.col1), extra conditions: (t1.col2 < t2.col2)  (cost=104879 rows=1.05e+6)
        -> Table scan on t1  (cost=104 rows=1024)
        -> Hash
            -> Table scan on t2  (cost=0.121 rows=1024)

SELECT SUM(t1.col1), SUM(t1.col2) FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1 AND t1.col2 < t2.col2;
SUM(t1.col1)	SUM(t1.col2)
524800	525312
SET optimizer_switch = DEFAULT;
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2;
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
CREATE TABLE t3 (i INTEGER);
EXPLAIN FORMAT=tree
SELECT * FROM t1 LEFT JOIN
(t2 LEFT JOIN t3 ON ABS(t2.i) = t3.i)
ON t2.i = t1.i;
EXPLAIN
-> Left hash join (t2.i = t1.i)  (cost=1.05 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Left hash join (abs(t2.i) = t3.i)  (cost=0.7 rows=1)
            -> Table scan on t2  (cost=0.35 rows=1)
            -> Hash
                -> Table scan on t3  (cost=0.35 rows=1)

DROP TABLE t1, t2, t3;
# See that we do not attach conditions that are always 'true' to the hash
# join iterator.
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON 1=1;
EXPLAIN
-> Left hash join (no condition)  (cost=0.7 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

DROP TABLE t1, t2;
# Set up a query where the join condition is an equi-join condition,
# but it refers to only one side of the hash join (degenerate join
# predicate). This condition must be attached as an 'extra' condition
# (proper join conditions must refer to both sides of the join).
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
CREATE TABLE t3 (i INTEGER);
EXPLAIN FORMAT=tree
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON TRUE) ON t3.i = t2.i;
EXPLAIN
-> Left hash join (no condition)  (cost=1.05 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t3.i = t2.i)  (cost=0.7 rows=1)
            -> Inner hash join (no condition)  (cost=0.7 rows=1)
                -> Table scan on t3  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t2  (cost=0.35 rows=1)

DROP TABLE t1, t2, t3;
# Execute a query that causes the hash join to do everything in memory
# with multiple refills of the hash table. This can be triggered when the
# build input does not fit in memory, and the iterator is not allowed to
# spill to disk due to LIMIT in the query.
CREATE TABLE t1 (col1 VARCHAR(255));
INSERT INTO t1 SELECT UUID();
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
INSERT INTO t1 SELECT UUID() FROM t1;
CREATE TABLE t2 AS SELECT UUID() AS col1 FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET join_buffer_size = 128;
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LIMIT 1000;
EXPLAIN
-> Limit: 1000 row(s)  (cost=1896 rows=1000)
    -> Left hash join (<hash>(t1.col1)=<hash>(t2.col1)), extra conditions: (t1.col1 = t2.col1)  (cost=1896 rows=16384)
        -> Table scan on t1  (cost=13.1 rows=128)
        -> Hash
            -> Table scan on t2  (cost=2.1 rows=128)

SELECT * FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LIMIT 1000;
SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2;
#
# Bug #30631730: WL#13377: RESULT DIFF WITH LEFT HASH JOIN
#
CREATE TABLE t1 (pk INTEGER PRIMARY KEY);
INSERT INTO t1 VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON NOT EXISTS (
SELECT * FROM t1 WHERE alias1.pk NOT IN ( 20 ) );
EXPLAIN
-> Nested loop left join  (cost=42.5 rows=400)
    -> Index scan on alias1 using PRIMARY  (cost=2.25 rows=20)
    -> Hash antijoin (no condition)  (cost=2.37 rows=20)
        -> Index scan on alias2 using PRIMARY  (cost=0.113 rows=20)
        -> Hash
            -> Limit: 1 row(s)  (cost=0.113 rows=1)
                -> Filter: (alias1.pk <> 20)  (cost=0.113 rows=20)
                    -> Index scan on t1 using PRIMARY  (cost=0.113 rows=20)

Warnings:
Note	1276	Field or reference 'test.alias1.pk' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON NOT EXISTS (
SELECT * FROM t1 WHERE alias1.pk NOT IN ( 20 ) );
pk	pk
1	NULL
10	NULL
11	NULL
12	NULL
13	NULL
14	NULL
15	NULL
16	NULL
17	NULL
18	NULL
19	NULL
2	NULL
20	1
20	10
20	11
20	12
20	13
20	14
20	15
20	16
20	17
20	18
20	19
20	2
20	20
20	3
20	4
20	5
20	6
20	7
20	8
20	9
3	NULL
4	NULL
5	NULL
6	NULL
7	NULL
8	NULL
9	NULL
DROP TABLE t1;
#
# Bug #30642968: WL#13377: RESULTSET DIFFERENCE WITH HASH ANTIJOIN
#
CREATE TABLE t1 (f1 INTEGER);
INSERT INTO t1 VALUES ( 100 ), ( 200 );
EXPLAIN FORMAT=tree SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON alias2.f1 = alias1.f1
AND NOT EXISTS ( SELECT * FROM t1 AS alias3 WHERE alias1.f1 <= 1 );
EXPLAIN
-> Nested loop left join  (cost=1.1 rows=4)
    -> Table scan on alias1  (cost=0.45 rows=2)
    -> Filter: (alias2.f1 = alias1.f1)  (cost=0.675 rows=2)
        -> Hash antijoin (no condition)  (cost=0.675 rows=2)
            -> Table scan on alias2  (cost=0.225 rows=2)
            -> Hash
                -> Limit: 1 row(s)  (cost=0.225 rows=1)
                    -> Filter: (alias1.f1 <= 1)  (cost=0.225 rows=2)
                        -> Table scan on alias3  (cost=0.225 rows=2)

Warnings:
Note	1276	Field or reference 'test.alias1.f1' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON alias2.f1 = alias1.f1
AND NOT EXISTS ( SELECT * FROM t1 AS alias3 WHERE alias1.f1 <= 1 );
f1	f1
100	100
200	200
DROP TABLE t1;
#
# Bug #30650725: WL13476: RESULTSET DIFFERENCE WITH BKA
#
CREATE TABLE t1 (
pk int NOT NULL,
col_datetime datetime
);
INSERT INTO t1 VALUES (9,'1988-02-06 01:32:15');
CREATE TABLE t2 (
col_int_key int,
col_datetime datetime,
KEY idx_N_col_int_key (col_int_key)
);
INSERT INTO t2 VALUES (9,'2025-01-07 03:12:24');
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
EXPLAIN FORMAT=tree SELECT * FROM
t1 AS table3 LEFT JOIN (t1 AS table1, t2 AS table2) ON table2.col_int_key=table3.pk;
EXPLAIN
-> Nested loop left join
    -> Table scan on table3  (cost=0.35 rows=1)
    -> Batched key access inner join
        -> Batch input rows
            -> Table scan on table1  (cost=0.35 rows=1)
        -> Multi-range index lookup on table2 using idx_N_col_int_key (col_int_key=table3.pk)  (cost=0.35 rows=1)

SELECT * FROM
t1 AS table3 LEFT JOIN (t1 AS table1, t2 AS table2) ON table2.col_int_key=table3.pk;
pk	col_datetime	pk	col_datetime	col_int_key	col_datetime
9	1988-02-06 01:32:15	9	1988-02-06 01:32:15	9	2025-01-07 03:12:24
SET optimizer_switch=DEFAULT;
DROP TABLE t1, t2;
#
# Bug #30665042: WL#13476: RESULTSET DIFFERENT NUMBER OF ROWS
#
CREATE TABLE t1 (
pk INTEGER,
f1 varchar(1),
KEY i1 (pk)
);
INSERT INTO t1 VALUES (2,'x');
INSERT INTO t1 VALUES (3,'x');
INSERT INTO t1 VALUES (4,'x');
INSERT INTO t1 VALUES (5,'x');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
EXPLAIN FORMAT=tree SELECT *
FROM t1 AS table1
LEFT JOIN (
SELECT inner_t3.* FROM
t1 AS inner_t1
JOIN t1 AS inner_t2 ON inner_t1.pk = inner_t2.pk
JOIN t1 AS inner_t3 ON inner_t1.f1 = inner_t2.f1
) AS table2 ON NOT EXISTS (
SELECT * FROM t1 WHERE table2.pk >= 1 OR table1.f1 = 'G'
    );
EXPLAIN
-> Nested loop left join
    -> Table scan on table1  (cost=0.65 rows=4)
    -> Batched key access inner join
        -> Batch input rows
            -> Inner hash join (no condition)  (cost=8.66 rows=64)
                -> Table scan on inner_t1  (cost=0.0406 rows=4)
                -> Hash
                    -> Hash antijoin (no condition), extra conditions: ((inner_t3.pk >= 1) or (table1.f1 = 'G'))  (cost=2.01 rows=16)
                        -> Table scan on inner_t3  (cost=0.163 rows=4)
                        -> Hash
                            -> Index scan on t1 using i1  (cost=0.163 rows=4)
        -> Filter: (inner_t2.f1 = inner_t1.f1)  (cost=0.252 rows=1)
            -> Multi-range index lookup on inner_t2 using i1 (pk=inner_t1.pk)  (cost=0.252 rows=1)

Warnings:
Note	1276	Field or reference 'table2.pk' of SELECT #3 was resolved in SELECT #1
Note	1276	Field or reference 'test.table1.f1' of SELECT #3 was resolved in SELECT #1
SELECT *
FROM t1 AS table1
LEFT JOIN (
SELECT inner_t3.* FROM
t1 AS inner_t1
JOIN t1 AS inner_t2 ON inner_t1.pk = inner_t2.pk
JOIN t1 AS inner_t3 ON inner_t1.f1 = inner_t2.f1
) AS table2 ON NOT EXISTS (
SELECT * FROM t1 WHERE table2.pk >= 1 OR table1.f1 = 'G'
    );
pk	f1	pk	f1
2	x	NULL	NULL
3	x	NULL	NULL
4	x	NULL	NULL
5	x	NULL	NULL
SET optimizer_switch=DEFAULT;
DROP TABLE t1;
#
# Bug#30520749 - REGRESSION: LEFT JOIN WITH IMPOSSIBLE ON CONDITION PERFORMS SLOWLY
#
CREATE TABLE t1 ( f1 INTEGER );
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
CREATE TABLE t2 AS SELECT * FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON 1=2;
EXPLAIN
-> Table scan on t1  (cost=0.75 rows=5)

EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 > 3;
EXPLAIN
-> Left hash join (no condition)  (cost=2.9 rows=25)
    -> Table scan on t1  (cost=0.75 rows=5)
    -> Hash
        -> Filter: (t2.f1 > 3)  (cost=0.15 rows=5)
            -> Table scan on t2  (cost=0.15 rows=5)

EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 > 3;
EXPLAIN
-> Left hash join (no condition), extra conditions: (t1.f1 > 3)  (cost=2.9 rows=25)
    -> Table scan on t1  (cost=0.75 rows=5)
    -> Hash
        -> Table scan on t2  (cost=0.15 rows=5)

DROP TABLE t1, t2;
#
# Bug #30664671: HASH JOINS : CRASH WITH COMPARING DIFFERENT CHARSET
# Bug #30664831: SEVERE PERFORMANCE REGRESSION JOINING ON MEDIUMTEXT COLUMNS!
#
CREATE TABLE t1 (
a LONGTEXT COLLATE utf32_sinhala_ci
);
INSERT INTO t1 (a) VALUES ('abc');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT 1 FROM t1 JOIN t1 AS t2 ON t1.a=t2.a;
EXPLAIN
-> Filter: (t2.a = t1.a)  (cost=0.716 rows=1)
    -> Inner hash join (<hash>(t2.a)=<hash>(t1.a))  (cost=0.716 rows=1)
        -> Table scan on t2  (cost=0.366 rows=1)
        -> Hash
            -> Table scan on t1  (cost=0.35 rows=1)

SELECT 1 FROM t1 JOIN t1 AS t2 ON t1.a=t2.a;
1
1
EXPLAIN FORMAT=tree SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON t1.a=t2.a;
EXPLAIN
-> Left hash join (<hash>(t2.a)=<hash>(t1.a)), extra conditions: (t2.a = t1.a)  (cost=0.731 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.366 rows=1)

SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON t1.a=t2.a;
1
1
DROP TABLE t1;
#
# Bug#30716607 SIG11 IN WELL_FORMED_COPY_NCHARS() AT
#              SQL-COMMON/SQL_STRING.CC
# Bug#30648809 HASH_JOIN_BUFFER::LOADINTOTABLEBUFFERS() HITS AN ASSERT
#              AND CRASH
CREATE TABLE t1 (
str_col VARCHAR(1)
);
CREATE TABLE t2 (
i INTEGER,
str_col VARCHAR(1),
PRIMARY KEY (i),
KEY idx (str_col)
);
CREATE TABLE t3 (i INTEGER);
INSERT INTO t1 VALUES ('r'), ('s');
INSERT INTO t2 VALUES (9,'s'), (11,'o');
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
SET sql_mode = '';
EXPLAIN FORMAT=tree SELECT 1 FROM t1 RIGHT JOIN t2 ON t2.str_col = t1.str_col
WHERE (t1.str_col, t1.str_col) NOT IN (
SELECT c1_1.str_col, MIN(c1_2.str_col)
FROM t2 AS c1_1 INNER JOIN t2 AS c1_2 STRAIGHT_JOIN t3 ON t3.i = c1_2.i
);
EXPLAIN
-> Filter: <in_optimizer>((t1.str_col,t1.str_col),<exists>(select #2) is false)  (cost=0.875 rows=4)
    -> Left hash join (t1.str_col = t2.str_col)  (cost=0.875 rows=4)
        -> Index scan on t2 using idx  (cost=0.45 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.225 rows=2)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s)  (cost=2.15 rows=1)
            -> Filter: (<if>(outer_field_is_not_null, ((<cache>(t1.str_col) = c1_1.str_col) or (c1_1.str_col is null)), true) and <if>(outer_field_is_not_null, ((<cache>(t1.str_col) = min(c1_2.str_col)) or (min(c1_2.str_col) is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(c1_1.str_col), true) and <if>(outer_field_is_not_null, <is_not_null_test>(min(c1_2.str_col)), true))  (cost=2.15 rows=1)
                -> Aggregate: min(c1_2.str_col)  (cost=2.15 rows=1)
                    -> Inner hash join (t3.i = c1_2.i)  (cost=1.75 rows=4)
                        -> Table scan on t3  (cost=0.0875 rows=1)
                        -> Hash
                            -> Inner hash join (no condition)  (cost=1.1 rows=4)
                                -> Index scan on c1_2 using idx  (cost=0.225 rows=2)
                                -> Hash
                                    -> Index scan on c1_1 using idx  (cost=0.45 rows=2)

SELECT 1 FROM t1 RIGHT JOIN t2 ON t2.str_col = t1.str_col
WHERE (t1.str_col, t1.str_col) NOT IN (
SELECT c1_1.str_col, MIN(c1_2.str_col)
FROM t2 AS c1_1 INNER JOIN t2 AS c1_2 STRAIGHT_JOIN t3 ON t3.i = c1_2.i
);
SET sql_mode = DEFAULT;
DROP TABLE t1, t2, t3;
#
# Bug#30738687 CRASH IN HASHJOINCONDITION::HASHJOINCONDITION
#
CREATE TABLE t (
c TEXT
);
EXPLAIN FORMAT=tree SELECT c FROM
(
SELECT 1 AS c FROM t AS a
RIGHT JOIN t AS b ON 1
RIGHT JOIN t AS c ON 1
RIGHT JOIN t AS d ON 1
) AS z
NATURAL RIGHT JOIN t;
EXPLAIN
-> Left hash join (no condition), extra conditions: (t.c = 1)  (cost=1.83 rows=1)
    -> Table scan on t  (cost=0.35 rows=1)
    -> Hash
        -> Left hash join (no condition)  (cost=1.46 rows=1)
            -> Table scan on d  (cost=0.366 rows=1)
            -> Hash
                -> Left hash join (no condition)  (cost=1.1 rows=1)
                    -> Table scan on c  (cost=0.366 rows=1)
                    -> Hash
                        -> Left hash join (no condition)  (cost=0.731 rows=1)
                            -> Table scan on b  (cost=0.366 rows=1)
                            -> Hash
                                -> Table scan on a  (cost=0.366 rows=1)

DROP TABLE t;
#
# Bug#30741679 ASSERTION `FALSE' IN EXTRACT_VALUE_FOR_HASH_JOIN
# Bug#30769883 HIT ASSERT IN
#              ITEM_FUNC_EQ::APPEND_JOIN_KEY_FOR_HASH_JOIN()
#
CREATE TABLE t1 (
a INTEGER,
b INTEGER
);
INSERT INTO t1 VALUES (1, 1);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 as table1
WHERE (SELECT a, b FROM t1 WHERE table1.a <> 6) IN (SELECT a, b FROM t1);
EXPLAIN
-> Hash semijoin (no condition), extra conditions: ((select #2) = (t1.a,t1.b))  (cost=0.7 rows=1)
    -> Table scan on table1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

Warnings:
Note	1276	Field or reference 'test.table1.a' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1 as table1
WHERE (SELECT a, b FROM t1 WHERE table1.a <> 6) IN (SELECT a, b FROM t1);
a	b
1	1
EXPLAIN FORMAT=tree SELECT * FROM t1 as table1
WHERE (SELECT a FROM t1 WHERE table1.a <> 6) IN (SELECT a FROM t1);
EXPLAIN
-> Hash semijoin ((select #2) = (t1.a))  (cost=0.7 rows=1)
    -> Table scan on table1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

Warnings:
Note	1276	Field or reference 'test.table1.a' of SELECT #2 was resolved in SELECT #1
SELECT * FROM t1 as table1
WHERE (SELECT a FROM t1 WHERE table1.a <> 6) IN (SELECT a FROM t1);
a	b
1	1
DROP TABLE t1;
#
# Bug#30792727 ERRORS WHEN BUILDING HASH TABLE FOR HASH JOIN NOT
#              PROPAGATED
#
CREATE TABLE t (i INTEGER);
INSERT INTO t VALUES (1), (2);
EXPLAIN FORMAT=tree SELECT * FROM t t1, t t2 WHERE JSON_EXTRACT(t1.i, t1.i) = t2.i;
EXPLAIN
-> Filter: (json_extract(t1.i,t1.i) = t2.i)  (cost=1.1 rows=2)
    -> Inner hash join (<hash>(json_extract(t1.i,t1.i))=<hash>(t2.i))  (cost=1.1 rows=2)
        -> Table scan on t2  (cost=0.175 rows=2)
        -> Hash
            -> Table scan on t1  (cost=0.45 rows=2)

SELECT * FROM t t1, t t2 WHERE JSON_EXTRACT(t1.i, t1.i) = t2.i;
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.
SHOW ERRORS;
Level	Code	Message
Error	3146	Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.
DROP TABLE t;
#
# Bug #31102789 A PREDICATE THAT COMPARES 0 WITH -0
#               YIELDS AN INCORRECT RESULT
#
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 DOUBLE);
CREATE TABLE t2(c0 DOUBLE);
INSERT INTO t0 VALUES(0);
INSERT INTO t1 VALUES('-0');
INSERT INTO t2 VALUES('+0');
SELECT * FROM t0, t1 WHERE t0.c0 = t1.c0;
c0	c0
0	-0
SELECT * FROM t1, t2 WHERE t1.c0 = t2.c0;
c0	c0
-0	0
SELECT * FROM t0, t2 WHERE t0.c0 = t2.c0;
c0	c0
0	0
DROP TABLE t0, t1, t2;
#
# Bug #31376809: PERFORMANCE REGRESSION FROM 5.7->8.0, DUE TO ANTIJOIN OF NOT EXISTS SUBQUERY
#
CREATE TABLE t1 (a INTEGER NOT NULL, b INTEGER);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN ANALYZE SELECT t1.a FROM t1 LEFT JOIN t1 AS t2 ON TRUE WHERE t2.a IS NULL;
EXPLAIN
-> Filter: (t2.a is null)  (cost=1.3 rows=1) (actual rows=0 loops=1)
    -> Hash antijoin (no condition)  (cost=1.3 rows=1) (actual rows=0 loops=1)
        -> Table scan on t1  (cost=0.75 rows=5) (never executed)
        -> Hash
            -> Limit: 1 row(s)  (cost=0.15 rows=1) (actual rows=1 loops=1)
                -> Table scan on t2  (cost=0.15 rows=5) (actual rows=1 loops=1)

DROP TABLE t1;
#
# Bug #31563414: REGRESSION: ITEM_FUNC_EQ::APPEND_JOIN_KEY_FOR_HASH_JOIN CONST: ASSERTION `FALSE' FAILED.
#
CREATE TABLE t1 (a INTEGER NOT NULL);
INSERT INTO t1 VALUES (1),(2),(3),(4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=tree SELECT *
FROM t1 AS a
LEFT JOIN t1 AS b ON FALSE
LEFT JOIN t1 AS c ON b.a=c.a;
EXPLAIN
-> Left hash join (no condition)  (cost=2.01 rows=16)
    -> Table scan on a  (cost=0.65 rows=4)
    -> Hash
        -> Filter: (c.a = b.a)  (cost=0.163 rows=4)
            -> Table scan on c  (cost=0.163 rows=4)

SELECT *
FROM t1 AS a
LEFT JOIN t1 AS b ON FALSE
LEFT JOIN t1 AS c ON b.a=c.a;
a	a	a
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
DROP TABLE t1;
#
# Bug #31940549: NEW MEMORY LEAK ON TRUNK RELATING TO HASHJOINITERATOR::BUILDHASHTABLE , OPEN_CACHED_FILE
#
SET join_buffer_size=32768;
CREATE TABLE t(a DOUBLE NOT NULL, b VARCHAR(15873) NOT NULL);
INSERT INTO t VALUES(1, REPEAT('abs',1000));
INSERT INTO t VALUES(2, REPEAT('ajs',1000));
INSERT INTO t VALUES(3, REPEAT('ajj',1000));
INSERT INTO t VALUES(4, REPEAT('jjs',1000));
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
SELECT
(
SELECT 1 FROM (SELECT t_outer.a) Z LEFT JOIN t
ON t.a > t.b LIMIT 1
) FROM t AS t_outer;
(
SELECT 1 FROM (SELECT t_outer.a) Z LEFT JOIN t
ON t.a > t.b LIMIT 1
)
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
DROP TABLE t;
SET join_buffer_size=DEFAULT;
#
# Bug #31832001 HASH JOIN MISMATCHES SIGNED AND UNSIGNED
#
CREATE TABLE t2 (c1 BIGINT NOT NULL);
CREATE TABLE t13 (c1 BIGINT UNSIGNED NOT NULL);
INSERT INTO t2 VALUES (-1),(1);
INSERT INTO t13 VALUES (18446744073709551615),(1);
SELECT * FROM t2 JOIN t13 WHERE t2.c1=t13.c1;
c1	c1
1	1
SELECT /*+ NO_BNL(t2,t13) */ * FROM t2 JOIN t13 WHERE t2.c1=t13.c1;
c1	c1
1	1
DROP TABLE t2, t13;
#
# Bug #32244974: SIG 6 IN HASHJOINITERATOR::READROWFROMPROBEITERATOR() AT HASH_JOIN_ITERATOR.CC
#
CREATE TABLE t1 (
pk INTEGER,
col_int INTEGER,
PRIMARY KEY (pk)
);
CREATE TABLE t2 (
pk INTEGER,
col_int INTEGER
);
INSERT INTO t1 VALUES (2,6);
INSERT INTO t1 VALUES (3,NULL);
INSERT INTO t2 VALUES (1,6);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SELECT 1
FROM
t1 AS alias1
JOIN t2 AS alias2 ON alias1.col_int = alias2.col_int
JOIN t1 AS alias3 ON 1
WHERE
( SELECT 1 UNION SELECT MAX(alias1.pk) ) = alias3.pk;
ERROR 21000: Subquery returns more than 1 row
DROP TABLE t1, t2;
# Bug#32579184: Hash join: my_lengthsp_utf32: assertion (length % 4)
CREATE TABLE t (
a TEXT CHARACTER SET ucs2 COLLATE ucs2_sinhala_ci,
b TEXT CHARACTER SET utf32 COLLATE utf32_german2_ci
);
Warnings:
Warning	1287	'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning	4079	'ucs2_sinhala_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
INSERT INTO t(a, b) VALUES('h','k'), ('w','e');
explain SELECT 1 FROM t AS a INNER JOIN t AS b ON a.a=b.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	b	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t` `a` join `test`.`t` `b` where (`test`.`a`.`a` = `test`.`b`.`b`)
SELECT 1 FROM t AS a INNER JOIN t AS b ON a.a=b.b;
1
DROP TABLE t;
# Bug#33204161: Strings with ASCII charset and UCS2 don't match in join
CREATE TABLE t1(wstr_name VARCHAR(64) COLLATE ucs2_general_ci);
Warnings:
Warning	4079	'ucs2_general_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
CREATE TABLE t2(str_name VARCHAR(64) COLLATE ascii_general_ci);
INSERT INTO t1 VALUES('9337a53f-cfd6-4a68-a1fc-e2d84eba8079');
INSERT INTO t2 VALUES('9337a53f-cfd6-4a68-a1fc-e2d84eba8079');
explain SELECT wstr_name, str_name
FROM t1 INNER JOIN t2 ON t1.wstr_name = t2.str_name;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`wstr_name` AS `wstr_name`,`test`.`t2`.`str_name` AS `str_name` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`wstr_name` = `test`.`t2`.`str_name`)
SELECT wstr_name, str_name
FROM t1 INNER JOIN t2 ON t1.wstr_name = t2.str_name;
wstr_name	str_name
9337a53f-cfd6-4a68-a1fc-e2d84eba8079	9337a53f-cfd6-4a68-a1fc-e2d84eba8079
DROP TABLE t1, t2;
# Bug#33787914: int-decimal. using left join, the returned result is incorrect
CREATE TABLE t(a DECIMAL(5, 3));
INSERT INTO t VALUES (0.1), (0.2), (0.3), (0.01), (0.91), (0.001), (0.991);
SELECT * FROM t AS t1, t AS t2 WHERE 1 - t1.a = 1 - t2.a;
a	a
0.001	0.001
0.010	0.010
0.100	0.100
0.200	0.200
0.300	0.300
0.910	0.910
0.991	0.991
SELECT * FROM t AS t1, t AS t2 WHERE t1.a = t2.a;
a	a
0.001	0.001
0.010	0.010
0.100	0.100
0.200	0.200
0.300	0.300
0.910	0.910
0.991	0.991
DROP TABLE t;
# Bug#33794977: Wrong results from hash join with compound hash keys
CREATE TABLE t(a VARCHAR(10), b VARCHAR(10));
INSERT INTO t VALUES ('x', 'xx'), ('xx', 'x');
SELECT * FROM t AS t1, t AS t2 WHERE t1.a = t2.a AND t1.b = t2.b;
a	b	a	b
x	xx	x	xx
xx	x	xx	x
DROP TABLE t;
CREATE TABLE t(a DECIMAL(3,0), b DECIMAL(3,0));
INSERT INTO t
VALUES (0, 128), (128, 0), (382, 1), (1, 382), (-1, 385), (385, -1);
SELECT * FROM t AS t1, t AS t2 WHERE t1.a = t2.a AND t1.b = t2.b;
a	b	a	b
-1	385	-1	385
0	128	0	128
1	382	1	382
128	0	128	0
382	1	382	1
385	-1	385	-1
DROP TABLE t;
# Bug#33638843: Hypergraph Offload issue : Invalid Join condition
CREATE TABLE t1 (f1 INTEGER);
CREATE TABLE t2 (f1 INTEGER);
CREATE VIEW vmerge AS SELECT 1 AS f1, f1 AS b_f1 FROM t2;
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2);
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN vmerge AS v ON t1.f1 = v.f1;
EXPLAIN
-> Left hash join (no condition), extra conditions: (t1.f1 = 1)  (...)
    -> Table scan on t1  (...)
    -> Hash
        -> Table scan on t2  (...)

DROP TABLE t1,t2;
DROP VIEW vmerge;
# Bug#34352156: Hypergraph: Assert PassesConflictRules() failed in
#               join_optimizer.cc
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
SELECT 1
FROM t1 JOIN ((t1 AS t2 LEFT JOIN t1 AS t3 ON t2.f2 = t3.f2)
JOIN (t1 AS t4 JOIN t1 AS t5 ON t4.f2 = t5.f1) ON t2.f2 = t5.f1)
ON t1.f2 = t5.f2;
1
DROP TABLE t1;
#
# Bug#34837464: Incorrect Left Outer Join Execution
#
CREATE TABLE t1(varchar_col VARCHAR(10), decimal_col DECIMAL(10, 0));
INSERT INTO t1 VALUES ('', 0), ('', 0), ('', 0), ('', 0);
CREATE TABLE t2(id INT, varchar_col VARCHAR(10), decimal_col DECIMAL(10, 0));
INSERT INTO t2 VALUES (1, NULL, NULL), (2, NULL, NULL), (3, NULL, NULL);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=TREE SELECT id FROM t1 LEFT JOIN t2 USING (varchar_col);
EXPLAIN
-> Left hash join (t1.varchar_col = t2.varchar_col)
    -> Table scan on t1
    -> Hash
        -> Table scan on t2

SELECT id FROM t1 LEFT JOIN t2 USING (varchar_col);
id
NULL
NULL
NULL
NULL
EXPLAIN FORMAT=TREE SELECT id FROM t1 LEFT JOIN t2 USING (decimal_col);
EXPLAIN
-> Left hash join (t1.decimal_col = t2.decimal_col)
    -> Table scan on t1
    -> Hash
        -> Table scan on t2

SELECT id FROM t1 LEFT JOIN t2 USING (decimal_col);
id
NULL
NULL
NULL
NULL
DROP TABLE t1, t2;
#
# Bug#31969607: THE RESULT IS INCONSISTENT BETWEEN JOIN AND GROUP BY
#
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, j JSON);
INSERT INTO t1(j) VALUES
(NULL), ('-1'), ('1'), ('1.0'), ('"1"'), ('[1, 2]'), ('[1.0, 2.0]'),
('9223372036854775806'), ('9223372036854775807'), ('"abc"'), ('"Abc"');
CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT, vc VARCHAR(100));
INSERT INTO t2(vc) VALUES
(NULL), ('-1'), ('1'), ('1.0'), ('"1"'), ('[1, 2]'), ('[1.0, 2.0]'),
('9223372036854775806'), ('9223372036854775807'), ('abc'), ('Abc');
CREATE TABLE t3 (i BIGINT);
INSERT INTO t3 VALUES (NULL), (-1), (0), (1), (2), (3),
(9223372036854775806), (9223372036854775807);
SELECT * FROM t1 AS a, t1 AS b WHERE a.j = b.j;
id	j	id	j
10	"abc"	10	"abc"
11	"Abc"	11	"Abc"
2	-1	2	-1
3	1	3	1
3	1	4	1.0
4	1.0	3	1
4	1.0	4	1.0
5	"1"	5	"1"
6	[1, 2]	6	[1, 2]
6	[1, 2]	7	[1.0, 2.0]
7	[1.0, 2.0]	6	[1, 2]
7	[1.0, 2.0]	7	[1.0, 2.0]
8	9223372036854775806	8	9223372036854775806
9	9223372036854775807	9	9223372036854775807
SELECT * FROM t1 AS a, t1 AS b WHERE a.j <=> b.j;
id	j	id	j
1	NULL	1	NULL
10	"abc"	10	"abc"
11	"Abc"	11	"Abc"
2	-1	2	-1
3	1	3	1
3	1	4	1.0
4	1.0	3	1
4	1.0	4	1.0
5	"1"	5	"1"
6	[1, 2]	6	[1, 2]
6	[1, 2]	7	[1.0, 2.0]
7	[1.0, 2.0]	6	[1, 2]
7	[1.0, 2.0]	7	[1.0, 2.0]
8	9223372036854775806	8	9223372036854775806
9	9223372036854775807	9	9223372036854775807
SELECT * FROM t1, t2 WHERE t1.j = t2.vc;
id	j	id	vc
10	"abc"	10	abc
11	"Abc"	11	Abc
5	"1"	3	1
SELECT * FROM t1, t2 WHERE t1.j <=> t2.vc;
id	j	id	vc
1	NULL	1	NULL
10	"abc"	10	abc
11	"Abc"	11	Abc
5	"1"	3	1
SELECT * FROM t1, t3 WHERE t1.j = t3.i;
id	j	i
2	-1	-1
3	1	1
4	1.0	1
8	9223372036854775806	9223372036854775806
9	9223372036854775807	9223372036854775807
SELECT * FROM t1, t3 WHERE t1.j <=> t3.i;
id	j	i
1	NULL	NULL
2	-1	-1
3	1	1
4	1.0	1
8	9223372036854775806	9223372036854775806
9	9223372036854775807	9223372036854775807
DROP TABLE t1, t2, t3;
#
# Bug#37161583: Incorrect result with LEFT JOIN and small
#               join_buffer_size
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (NULL);
CREATE TABLE t2(b INT);
INSERT INTO t2
WITH RECURSIVE
s(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 1000)
SELECT n FROM s;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE a = b);
EXPLAIN
-> Hash antijoin (t2.b = t1.a)  (rows=1000)
    -> Table scan on t1  (rows=1)
    -> Hash
        -> Table scan on t2  (rows=1000)

Warnings:
Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
TRUNCATE performance_schema.file_summary_by_event_name;
SET join_buffer_size = DEFAULT;
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE a = b);
a
NULL
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
0
TRUNCATE performance_schema.file_summary_by_event_name;
SET join_buffer_size = 128;
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE a = b);
a
NULL
SET join_buffer_size = DEFAULT;
SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
WHERE event_name LIKE '%hash_join%';
COUNT_STAR > 0
1
DROP TABLE t1, t2;