-- source include/have_ndb.inc

--disable_warnings
drop table if exists t1, test1, test2;
--enable_warnings

#
# Simple test to show use of ordered indexes 
#

CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned,
  KEY(b)	
) engine=ndbcluster;

insert t1 values(1, 2, 3), (2,3, 5), (3, 4, 6), (4, 5, 8), (5,6, 2), (6,7, 2);
select * from t1 order by b;
select * from t1 where b >= 4 order by b;
select * from t1 where b = 4 order by b;
select * from t1 where b > 4 order by b;
select * from t1 where b < 4 order by b;
select * from t1 where b <= 4 order by b;

# Test of reset_bounds
select tt1.* from t1 as tt1, t1 as tt2 use index(b) where tt1.b = tt2.b order by tt1.b;
select a, b, c from t1 where a!=2 and c=6;
select a, b, c from t1 where a!=2 order by a;

#
# Here we should add some "explain select" to verify that the ordered index is 
# used for these queries.
#

#
# Update using ordered index scan
#

update t1 set c = 3 where b = 3;
select * from t1 order by a;
update t1 set c = 10 where b >= 6;
select * from t1 order by a;
update t1 set c = 11 where b < 5;
select * from t1 order by a;
update t1 set c = 12 where b > 0;
select * from t1 order by a;
update t1 set c = 13 where b <= 3;
select * from t1 order by a;
update t1 set b = b + 1 where b > 4 and b < 7;
select * from t1 order by a;
-- Update primary key
update t1 set a = a + 10 where b > 1 and b < 7;
select * from t1 order by a;

#
# Delete using ordered index scan
#

drop table t1;

CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned,
  KEY(b)	
) engine=ndbcluster;

insert t1 values(1, 2, 13), (2,3, 13), (3, 4, 12), (4, 5, 12), (5,6, 12), (6,7, 12);

delete from t1 where b = 3;
select * from t1 order by a;
delete from t1 where b >= 6;
select * from t1 order by a;
delete from t1 where b < 4;
select * from t1 order by a;
delete from t1 where b > 5;
select * from t1 order by a;
delete from t1 where b <= 4;
select * from t1 order by a;

drop table t1;


#
#multi part key
#
CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned not null,
  c int unsigned not null
) engine = ndb;

create index a1 on t1 (b, c);

insert into t1 values (1, 2, 13);
insert into t1 values (2,3, 13);
insert into t1 values (3, 4, 12);
insert into t1 values (4, 5, 12);
insert into t1 values (5,6, 12);
insert into t1 values (6,7, 12);
insert into t1 values (7, 2, 1);
insert into t1 values (8,3, 6);
insert into t1 values (9, 4, 12);
insert into t1 values (14, 5, 4);
insert into t1 values (15,5,5);
insert into t1 values (16,5, 6);
insert into t1 values (17,4,4);
insert into t1 values (18,1, 7);



select * from t1 order by a;
select * from t1 where b<=5 order by a;
select * from t1 where b<=5 and c=0;
insert into t1 values (19,4, 0);
select * from t1 where b<=5 and c=0;
select * from t1 where b=4 and c<=5 order by a;
select * from t1 where b<=4 and c<=5 order by a;
select * from t1 where b<=5 and c=0 or b<=5 and c=2;

select count(*) from t1 where b = 0;
select count(*) from t1 where b = 1;
drop table t1;

#
# Indexing NULL values
#

CREATE TABLE t1 (
  a int unsigned NOT NULL PRIMARY KEY,
  b int unsigned,
  c int unsigned,
  KEY bc(b,c)
) engine = ndb;

insert into t1 values(1,1,1),(2,NULL,2),(3,NULL,NULL),(4,4,NULL);
select * from t1 use index (bc) where b IS NULL order by a;

select * from t1 use index (bc)order by a;
select * from t1 use index (bc) order by a;
select * from t1 use index (PRIMARY) where b IS NULL order by a;
select * from t1 use index (bc) where b IS NULL order by a;
select * from t1 use index (bc) where b IS NULL and c IS NULL order by a;
select * from t1 use index (bc) where b IS NULL and c = 2 order by a;
select * from t1 use index (bc) where b < 4 order by a;
select * from t1 use index (bc) where b IS NOT NULL order by a;
drop table t1;

#
# Order by again, including descending.
#

create table t1 (
  a int unsigned primary key,
  b int unsigned,
  c char(10),
  key bc (b, c)
) engine=ndb;

insert into t1 values(1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d'),(5,5,'e');
insert into t1 select a*7,10*b,'f' from t1;
insert into t1 select a*13,10*b,'g' from t1;
insert into t1 select a*17,10*b,'h' from t1;
insert into t1 select a*19,10*b,'i' from t1;
insert into t1 select a*23,10*b,'j' from t1;
insert into t1 select a*29,10*b,'k' from t1;
#
select b, c from t1 where b <= 10 and c <'f' order by b, c;
select b, c from t1 where b <= 10 and c <'f' order by b desc, c desc;
#
select b, c from t1 where b=4000 and c<'k' order by b, c;
select b, c from t1 where b=4000 and c<'k' order by b desc, c desc;
select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b, c;
select b, c from t1 where 1000<=b and b<=100000 and c<'j' order by b desc, c desc;
#
select min(b), max(b) from t1;
#
drop table t1;

#
# Bug #6435
CREATE TABLE test1 (
SubscrID int(11) NOT NULL auto_increment,
UsrID int(11) NOT NULL default '0',
PRIMARY KEY  (SubscrID),
KEY idx_usrid (UsrID)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO test1 VALUES (2,224),(3,224),(1,224);

CREATE TABLE test2 (
SbclID int(11) NOT NULL auto_increment,
SbcrID int(11) NOT NULL default '0',
PRIMARY KEY  (SbclID),
KEY idx_sbcrid (SbcrID)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO test2 VALUES (3,2),(1,1),(2,1),(4,2);
select * from test1 order by 1;
select * from test2 order by 1;
SELECT s.SubscrID,l.SbclID FROM test1 s left JOIN test2 l ON
l.SbcrID=s.SubscrID WHERE s.UsrID=224 order by 1, 2;
drop table test1;
drop table test2;

# bug#7424 + bug#7725

create table t1 (
  pk int primary key,
  dt datetime not null,
  da date not null,
  ye year not null,
  ti time not null,
  ts timestamp not null,
  index(dt),
  index(da),
  index(ye),
  index(ti),
  index(ts)
) engine=ndb;

insert into t1 (pk,dt,da,ye,ti) values
  (1, '1901-05-05 23:00:59', '1901-05-05', '1901', '23:00:59'),
  (2, '1912-09-05 13:00:59', '1912-09-05', '1912', '13:00:59'),
  (3, '1945-12-31 00:00:00', '1945-12-31', '1945', '00:00:00'),
  (4, '1955-12-31 00:00:00', '1955-12-31', '1955', '00:00:00'),
  (5, '1963-06-06 06:06:06', '1963-06-06', '1963', '06:06:06'),
  (6, '1993-06-06 06:06:06', '1993-06-06', '1993', '06:06:06'),
  (7, '2001-01-01 10:11:10', '2001-01-01', '2001', '10:11:10'),
  (8, '2001-01-01 10:11:11', '2001-01-01', '2001', '10:11:11'),
  (9, '2005-01-31 23:59:59', '2005-01-31', '2005', '23:59:59');

# datetime
select count(*)-9 from t1 use index (dt) where dt >  '1900-01-01 00:00:00';
select count(*)-6 from t1 use index (dt) where dt >= '1955-12-31 00:00:00';
select count(*)-5 from t1 use index (dt) where dt >  '1955-12-31 00:00:00';
select count(*)-5 from t1 use index (dt) where dt <  '1970-03-03 22:22:22';
select count(*)-7 from t1 use index (dt) where dt <  '2001-01-01 10:11:11';
select count(*)-8 from t1 use index (dt) where dt <= '2001-01-01 10:11:11';
select count(*)-9 from t1 use index (dt) where dt <= '2055-01-01 00:00:00';

# date
select count(*)-9 from t1 use index (da) where da >  '1900-01-01';
select count(*)-6 from t1 use index (da) where da >= '1955-12-31';
select count(*)-5 from t1 use index (da) where da >  '1955-12-31';
select count(*)-5 from t1 use index (da) where da <  '1970-03-03';
select count(*)-6 from t1 use index (da) where da <  '2001-01-01';
select count(*)-8 from t1 use index (da) where da <= '2001-01-02';
select count(*)-9 from t1 use index (da) where da <= '2055-01-01';

# year
select count(*)-9 from t1 use index (ye) where ye >  '1900';
select count(*)-6 from t1 use index (ye) where ye >= '1955';
select count(*)-5 from t1 use index (ye) where ye >  '1955';
select count(*)-5 from t1 use index (ye) where ye <  '1970';
select count(*)-6 from t1 use index (ye) where ye <  '2001';
select count(*)-8 from t1 use index (ye) where ye <= '2001';
select count(*)-9 from t1 use index (ye) where ye <= '2055';

# time
select count(*)-9 from t1 use index (ti) where ti >= '00:00:00';
select count(*)-7 from t1 use index (ti) where ti >  '00:00:00';
select count(*)-7 from t1 use index (ti) where ti >  '05:05:05';
select count(*)-5 from t1 use index (ti) where ti >  '06:06:06';
select count(*)-5 from t1 use index (ti) where ti <  '10:11:11';
select count(*)-6 from t1 use index (ti) where ti <= '10:11:11';
select count(*)-8 from t1 use index (ti) where ti <  '23:59:59';
select count(*)-9 from t1 use index (ti) where ti <= '23:59:59';