--source include/default_optimizer_switch.inc

#
# MDEV-6065 MySQL Bug#13623473 "MISSING ROWS ON SELECT AND JOIN WITH TIME/DATETIME COMPARE"
#

# Systematic testing of ref access and range scan

SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');

CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key));
INSERT INTO t1 VALUES ('00:00:00'),('-24:00:00'),('-48:00:00'),('24:00:00'),('48:00:00');
CREATE TABLE t2 (col_datetime_key DATETIME, KEY(col_datetime_key));
INSERT INTO t2 SELECT * FROM t1;

let $cnt_0=5;
let $operator= =;
# For operator in =, >=, >, <=, <
while ($cnt_0)
{  
  let $cnt_1=2;
  let $first_table=t1;
  # for table in t1,t2
  while ($cnt_1)
  {
    if ($first_table==t1)
    {
      let $first_index=col_time_key;
      let $second_table=t2;
      let $second_index=col_datetime_key;
    }
    if ($first_table==t2)
    {
      let $first_index=col_datetime_key;
      let $second_table=t1;
      let $second_index=col_time_key;
    }
    let $cnt_2=2;
    let $first_index_hint=ignore;
    # for first_index_hint in ignore,force
    while ($cnt_2)
    {
      let $cnt_3=2;
      let $second_index_hint=ignore;
      # for second_index_hint in ignore, force
      while ($cnt_3)
      {
        let $cnt_4=2;
        let $first_operand=col_time_key;
        # for first_operand in col_time_key, col_datetime_key
        while ($cnt_4)
        {
          if ($first_operand==col_time_key)
          {
            let $second_operand=col_datetime_key;
          }
          if ($first_operand==col_datetime_key)
          {
            let $second_operand=col_time_key;
          }
  
          eval EXPLAIN EXTENDED SELECT * FROM
               $first_table $first_index_hint INDEX ($first_index)
               STRAIGHT_JOIN
               $second_table $second_index_hint INDEX ($second_index)
               WHERE $first_operand $operator $second_operand;
          --sorted_result
          eval SELECT * FROM
               $first_table $first_index_hint INDEX ($first_index)
               STRAIGHT_JOIN
               $second_table $second_index_hint INDEX ($second_index)
               WHERE $first_operand $operator $second_operand;
  
          let $first_operand=col_datetime_key;
          dec $cnt_4;
        }
        let $second_index_hint=force;
        dec $cnt_3;
      }
      let $first_index_hint=force;
      dec $cnt_2;
    }
    let $first_table=t2;
    dec $cnt_1;
  }
  if ($cnt_0==5)
  {
    let $operator= >=;
  }
  if ($cnt_0==4)
  {
    let $operator= >;
  }
  if ($cnt_0==3)
  {
    let $operator= <=;
  }
  if ($cnt_0==2)
  {
    let $operator= <;
  }
  dec $cnt_0;
}

DROP TABLE t1,t2;

#
# Original test of the bug report
#

CREATE TABLE t1 (
  pk INT NOT NULL AUTO_INCREMENT,
  col_int_nokey INT,
  col_int_key INT NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
);

INSERT INTO t1 VALUES (10,1,7), (11,7,0), (12,4,9), (13,7,3),
(14,0,4), (15,2,2), (16,9,5), (17,4,3), (18,0,1), (19,9,3), (20,1,6),
(21,3,7), (22,8,5), (23,8,1), (24,18,204), (25,84,224), (26,6,9),
(27,3,5), (28,6,0), (29,6,3);

CREATE TABLE t2 (
  col_int_nokey INT NOT NULL,
  col_datetime_key DATETIME NOT NULL,
  col_varchar_key VARCHAR(1) NOT NULL,
  KEY col_datetime_key (col_datetime_key),
  KEY col_varchar_key (col_varchar_key)
);

INSERT INTO t2 VALUES (1,'2001-11-04 19:07:55','k');

CREATE TABLE t3 (
  col_time_key TIME,
  KEY col_time_key (col_time_key)
);

INSERT INTO t3 VALUES ('21:22:34'), ('10:50:38'), ('00:21:38'),
('04:08:02'), ('16:25:11'), ('10:14:58'), ('19:47:59'), ('11:14:24'),
('00:00:00'), ('00:00:00'), ('15:57:25'), ('07:05:51'), ('19:22:21'),
('03:53:16'), ('09:16:38'), ('15:37:26'), ('00:00:00'), ('05:03:03'),
('02:59:24'), ('00:01:58');

let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 FORCE INDEX (col_time_key)
           ON t3.col_time_key > t2.col_datetime_key;
eval EXPLAIN EXTENDED $query;
--sorted_result
eval $query;

let $query=SELECT * FROM t2 STRAIGHT_JOIN t3 IGNORE INDEX (col_time_key)
           ON t3.col_time_key > t2.col_datetime_key;
eval EXPLAIN EXTENDED $query;
--sorted_result
eval $query;

let $query=SELECT outr.col_int_nokey
FROM t2 as outr
  STRAIGHT_JOIN t3 AS outr2
  ON outr2.col_time_key > outr.col_datetime_key
WHERE outr.col_int_nokey IN (
  SELECT col_int_key
  FROM t1 AS innr
  WHERE innr.pk >= innr.col_int_nokey
) AND (
  outr.col_int_nokey <= 6
  OR
  outr.col_varchar_key IS NULL
);
eval EXPLAIN EXTENDED $query;
--sorted_result
eval $query;

DROP TABLE t1,t2,t3;
SET TIMESTAMP=0; # back to current time


--echo #
--echo # MDEV-15262 Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column
--echo #

SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-31 10:14:35');
CREATE TABLE t1 (col_time_key TIME, KEY(col_time_key));
CREATE TABLE t2 (col_datetime_key DATETIME);
INSERT INTO t1 VALUES ('-760:00:00'),('760:00:00');
INSERT INTO t1 VALUES ('-770:00:00'),('770:00:00');
INSERT INTO t2 SELECT * FROM t1;
SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key;
SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key;
INSERT INTO t1 VALUES ('-838:59:59'),('838:59:59');
INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-838:59:59' HOUR_SECOND));
INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '838:59:59' HOUR_SECOND));
INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '-839:00:00' HOUR_SECOND));
INSERT INTO t2 VALUES (DATE_ADD(CURRENT_DATE, INTERVAL '839:00:00' HOUR_SECOND));
SELECT * FROM t2 STRAIGHT_JOIN t1 IGNORE INDEX(col_time_key) WHERE col_time_key = col_datetime_key;
SELECT * FROM t2 STRAIGHT_JOIN t1 FORCE INDEX (col_time_key) WHERE col_time_key = col_datetime_key;
DROP TABLE t1, t2;
SET TIMESTAMP=DEFAULT;

#
# End of 10.0 tests
#