CREATE TABLE tc()

in mysql-test/include/hash_join.inc [787:1681]

• 750 lines of code
• 2 McCabe index (conditional complexity)

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 up a query where we have hash join of a streamed materialization, which
# verifies that hash join does not try and ask the StreamingIterator for
# row IDs; the StreamingIterator will provide the row IDs automatically.
#
# The output of the query does not matter; it should just not assert.
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;

--echo # Do a join between DECIMAL and INTEGER to verify that we get a match
--echo # 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);

ANALYZE TABLE t1, t2;

--replace_regex $elide_costs
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
DROP TABLE t1, t2;

--echo # See that we get the correct results with a PAD SPACE collation and
--echo # PAD_CHAR_TO_FULL_LENGTH. Note that the latter is deprecated, so this
--echo # 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");

ANALYZE TABLE t1, t2;

SET sql_mode="PAD_CHAR_TO_FULL_LENGTH";
--replace_regex $elide_costs
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
SELECT * FROM t1 JOIN t2 ON t1.col1 = t2.col1;
SET sql_mode=DEFAULT;
DROP TABLE t1, t2;

--echo # Set up a case where the join planner will set up a BNL with linked
--echo # join buffers, and where the row ID should be kept due to duplicate
--echo # removal. rowid_status will be set on several QEP_TABs to indicate that
--echo # a row ID is needed, even though we should not request the row ID on all
--echo # 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
  );
DROP TABLE b1, c1, cc1;

--echo # 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;

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';

--sorted_result
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
  )
;
set optimizer_switch=DEFAULT;
DROP TABLE t1;


--echo # A case where we have a hash join iterator both above and below a
--echo # WeedoutIterator.
CREATE TABLE t1(f1 INT(11) NOT NULL);
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)
);
INSERT INTO t2 VALUES (1, 7), (2, 1), (4, 7), (5, 6), (8, 9), (10, 11);

CREATE TABLE t4(f1 INT DEFAULT NULL);
INSERT INTO t4 VALUES (2);

ANALYZE TABLE t1, t2, t4;

let $query=
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);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
eval $query;

DROP TABLE t1, t2, t4;

--echo #
--echo # Bug#30035890 SIG 11 IN HASHJOINITERATOR::READJOINEDROW AT
--echo #              SQL/HASH_JOIN_ITERATOR.CC
--echo #
--echo # Note that this test case needs ASAN to reproduce.
--echo # Note: Different plan with new hypergraph cost model.

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');

--echo # 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;

--echo #
--echo # Bug#30060691 ASSERTION `M_INDEX_CURSOR.IS_POSITIONED()' IN
--echo #              TEMPTABLE::HANDLER::POSITION()
--echo #

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;

let $query = 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
    );

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
--echo # We only want to see that the query does not hit an assertion, so ignore
--echo # the results.
--disable_result_log
eval $query;
--enable_result_log

DROP TABLE bb, c, cc;

--echo #
--echo # Bug#30049217 ASSERTION FAILURE AT
--echo #              TEMPTABLE::HANDLER::POSITION|SRC/HANDLER.CC
--echo #
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;

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;

DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#30153695 ASSERTION SIG6 TEMPTABLE::HANDLER::POSITION
--echo #              SRC/HANDLER.CC:715
--echo #

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');

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;
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');
ALTER TABLE cc ENABLE KEYS;

ANALYZE TABLE c, cc;

let $query = 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;

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;

DROP TABLE c, cc;


--echo #
--echo # Bug#30119783 SIG11 IN
--echo #   HASH_JOIN_BUFFER::STOREFROMTABLEBUFFERS|SQL/HASH_JOIN_BUFFER.CC
--echo #
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');
ANALYZE TABLE b, cc;

# A complicated test case, but what we want to achieve is a weedout over a
# hash join, where the hash join build input is a combination of left join over
# a temporary table. Very simplified, it will look something like this:
# -> Weedout
#   -> Inner hash join
#     -> Table scan
#     -> Hash
#       -> Nested loop left join
#         -> Table scan
#         -> Materialize
#           -> Table scan
#
# The behavior we want to trigger is that hash join should ask for the row ID
# from a temporary table, where the temporary table row is NULL-complemented.

let $query = 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
          );

--echo # Note: Different plan with new hypergraph cost model.
--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;

DROP TABLE b, cc;

--echo #
--echo # Bug#30049083 [REGRESSION]REPLACE/INSERT WITH LIMIT TAKING MORE TIME AND
--echo #              SPACE
--echo #
--echo # If the query has a LIMIT, the hash join should not spill to disk. Note
--echo # that if the query contains either grouping or sorting, we allow spill
--echo # 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;

SET join_buffer_size = 2048;  # 2 kB.

let $hash_join_file_operations =
  SELECT COUNT_STAR > 0 FROM performance_schema.file_summary_by_event_name
    WHERE event_name LIKE '%hash_join%';

--echo # This should spill to disk since we do not have any LIMIT.
--disable_result_log
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1;
--enable_result_log
eval $hash_join_file_operations;

--echo # This should NOT spill to disk since we have a LIMIT.
--disable_result_log
TRUNCATE performance_schema.file_summary_by_event_name;
SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1 LIMIT 1;
--enable_result_log
eval $hash_join_file_operations;

--echo # 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;
eval $hash_join_file_operations;

--echo # 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;
eval $hash_join_file_operations;

SET join_buffer_size = DEFAULT;
DROP TABLE t1,t2;

--echo #
--echo # Bug#30214767 SIG11 AT QUICK_INDEX_MERGE_SELECT::GET_NEXT |
--echo #              SQL/OPT_RANGE.CC
--echo #
--echo # Set up a query with hash join, where the build input uses an index
--echo # range scan with index merge sort-union. Also, a LIMIT greater than
--echo # the number of rows satisfying the join condition is needed to
--echo # reproduce the bug. What we want to achieve is to get the hash join
--echo # to call Read() on the build input after it has returned EOF. This can
--echo # be triggered by using LIMIT, as this causes the hash join to go back
--echo # and read from the build input after the probe iterator has returned
--echo # EOF (see comment on HashJoinIterator regarding spill to disk and LIMIT
--echo # 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;

let $query = 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;

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
DROP TABLE t1, t2;


--echo #
--echo # Bug#30224582 ASSERTION `M_INDEX_CURSOR.IS_POSITIONED()' FAILED
--echo #
--echo # Set up a query where the hash join build input consists of a
--echo # materialized table, where we do an index lookup on the materialized
--echo # table. The LIMIT is also needed in order to trigger a second build
--echo # 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);
ANALYZE TABLE t1, t2;

let $query = SELECT /*+ JOIN_ORDER(table1, t2) */
  *
FROM
  (
    SELECT
      DISTINCT t1.*
    FROM
      t1
  ) AS table1 JOIN t2
  WHERE table1.col1 = 1
LIMIT 50;

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
DROP TABLE t1, t2;

--echo #
--echo # Bug#30306279 ASAN: MEMCPY-PARAM-OVERLAP NEAR
--echo #              EXTRACT_VALUE_FOR_HASH_JOIN
--echo #
--echo # Set up a test where geometry data is written to chunk files on disk.
--echo # 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;
--disable_result_log
SELECT * FROM t1 a JOIN t1 b ON a.col1 = b.col1;
--enable_result_log
SET join_buffer_size = DEFAULT;
DROP TABLE t1;

--echo #
--echo # Bug#30535541 HASH JOIN DOES NOT HANDLE PAD SPACE COLLATIONS CORRECTLY
--echo #
--echo # Test that hash join compares strings that only differs in end spaces
--echo # 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 ');
ANALYZE TABLE t1, t2;

let $query = SELECT * FROM t1 JOIN t2 ON t1.c = t2.c;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
DROP TABLE t1, t2;

--echo #
--echo # Bug#30579922 INCORRECT RESULT IF HASH-JOIN ALGORITHM EXHAUST ITS
--echo #              JOIN_BUFFER
--echo #
--echo # Set up a query where the build input is a nested loop, where the inner
--echo # side of the nested loop also has a filter. We want to see that if the
--echo # hash join is not allowed to spill to disk, it will restore the last row
--echo # 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;

let $query =
  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;

SET join_buffer_size = 128;
--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;

--sorted_result
eval $query;

--sorted_result
eval $query LIMIT 1000;

SET join_buffer_size = DEFAULT;
DROP TABLE t1, t2, t3;

--echo #
--echo #  Bug#30282693 SIG6 IN TEMPTABLE::HANDLER::POSITION() AT SRC/HANDLER.CC
--echo #
--echo # Create a query where we get a confluent weedout as the right side of an
--echo # antijoin. Since a confluent weedout returns at most one row, the
--echo # iterator executor will replace it with a LIMIT 1. Note that the test
--echo # case is not easy to understand, but it is the smallest test case I
--echo # 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;

let $query = 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;

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;

DROP TABLE t1, t2;

--echo #
--echo # Bug#30626264 ASSERTS IN BASIC_ROW_ITERATORS.H:345:
--echo #              FAKESINGLEROWITERATOR::SETNULLROWFLAG
--echo #

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;

# Set up a hash join where we get the const tables on the right-hand side,
# and that is run multiple times (so that the hash table is built twice).
# We don't really need to join in t3 twice, except the optimizer doesn't
# select a hash join otherwise.
let $query =
  SELECT (
    SELECT t1.pk
    FROM t2
      LEFT JOIN t3 USING (pk)
      JOIN t3 AS t4 USING (pk)
      WHERE t2.pk = 9
  ) FROM t1;
--replace_regex $elide_costs
eval explain format=tree $query;
eval $query;

DROP TABLE t1, t2, t3;
--echo # 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;
--replace_regex $elide_costs
EXPLAIN FORMAT=tree SELECT * FROM t1 JOIN t2 ON t1.col1 < t2.col1;
DROP TABLE t1, t2;

--echo # 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;
--replace_regex $elide_costs
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
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), (2, 2), (2, 2), (2, 2), (2, 2);
INSERT INTO t2 VALUES (1, 1), (1, 2), (3, 3), (3, 3), (3, 3), (3, 3);
ANALYZE TABLE t1, t2;
--replace_regex $elide_costs
EXPLAIN FORMAT=tree
  SELECT * FROM t1 WHERE (t1.col1) IN
    (SELECT t2.col1 FROM t2 WHERE t2.col2 < 3);
DROP TABLE t1, t2;

--echo # Test a query where the optimizer will create a semijoin condition that
--echo # is not an equi-join condition. These kind of conditions must be
--echo # attached as "extra" conditions in the hash join iterator in order to
--echo # 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;

let $query = SELECT * FROM t1 WHERE 1 IN (SELECT t1.col1 = t2.col1 FROM t2);
--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
DROP TABLE t1, t2;

--echo # 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;

SET optimizer_switch="materialization=off,duplicateweedout=off";
let $query = SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
  WHERE (t1.a,t2.a) IN (SELECT a, a FROM t3);

--replace_regex $elide_costs
eval EXPLAIN FORMAT=tree $query;
eval $query;
SET optimizer_switch=DEFAULT;
DROP TABLE t1, t2, t3;

--echo # 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);
let $count=0;
while ($count < 10)
{
  eval INSERT INTO t1
         SELECT col1 + POW(2, $count), col2 + POW(2, $count) FROM t1;
  inc $count;
}