-- source include/have_xtradb.inc

--disable_warnings
drop table if exists t1,t2,t3,t4;
--enable_warnings

set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;

--source include/mrr_tests.inc 

set storage_engine= @save_storage_engine;

# Try big rowid sizes
set @mrr_buffer_size_save= @@mrr_buffer_size;
set mrr_buffer_size=64;

# By default InnoDB will fill values only for key parts used by the query,
# which will cause DS-MRR to supply an invalid tuple on scan restoration. 
# Verify that DS-MRR's code extra(HA_EXTRA_RETRIEVE_ALL_COLS) call has effect:
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a char(8), b char(8), c char(8), filler char(100), key(a,b,c) ) engine=InnoDB;

insert into t2 select 
  concat('a-', 1000 + A.a, '-a'),
  concat('b-', 1000 + B.a, '-b'),
  concat('c-', 1000 + C.a, '-c'),
  'filler'
from t1 A, t1 B, t1 C;

explain
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a';
drop table t2;

# Try a very big rowid
create table t2 (a char(100), b char(100), c char(100), d int, 
                 filler char(10), key(d), primary key (a,b,c)) engine= innodb;
insert into t2 select A.a, B.a, B.a, A.a, 'filler' from t1 A, t1 B;
--replace_column 9 #
explain select * from t2 force index (d) where d < 10;
drop table t2;

drop table t1;
set @@mrr_buffer_size= @mrr_buffer_size_save;

#
# BUG#33033 "MySQL/InnoDB crashes with simple select range query"
#
create table t1 (f1 int not null, f2 int not null,f3 int not null, f4 char(1), primary key (f1,f2), key ix(f3))Engine=InnoDB;

--disable_query_log
let $1=55;

while ($1)
{
  eval insert into t1(f1,f2,f3,f4) values ($1,$1,$1,'A');
  dec $1;
}
--enable_query_log

# The following must not crash:
select * from t1 where (f3>=5 and f3<=10) or (f3>=1 and f3<=4);

drop table t1;

--echo
--echo BUG#37977: Wrong result returned on GROUP BY + OR + Innodb
--echo
CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(5) DEFAULT NULL,
  `varchar_nokey` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES 
(1,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
(2,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
(3,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
(4,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
(5,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
select pk from t1 WHERE `varchar_key` > 'kr' group by pk;
select pk from t1 WHERE `int_nokey` IS NULL OR  `varchar_key` > 'kr' group by pk;
drop table t1;

--echo #
--echo # BUG#39447: Error with NOT NULL condition and LIMIT 1
--echo #
CREATE TABLE t1 (                 
   id int(11) NOT NULL,                
   parent_id int(11) DEFAULT NULL,     
   name varchar(10) DEFAULT NULL,      
   PRIMARY KEY (id),                   
   KEY ind_parent_id (parent_id)     
) ENGINE=InnoDB;

insert into t1 (id, parent_id, name) values
(10,NULL,'A'),
(20,10,'B'),
(30,10,'C'),
(40,NULL,'D'),
(50,40,'E'),
(60,40,'F'),
(70,NULL,'J');

SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
--echo This must show type=index, extra=Using where
explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
drop table t1;