order_by.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

SELECT * FROM t1 ORDER BY b;
SELECT * FROM t1 ORDER BY b DESC;
SELECT * FROM t1 ORDER BY (a + b);
SELECT * FROM t1 ORDER BY (a + b) DESC;
DROP TABLE t1;

#
# Test of FORCE INDEX ... ORDER BY
#

create table t1(id int not null auto_increment primary key, t char(12));
disable_query_log;
let $1 = 1000;
while ($1)
 {
  eval insert into t1(t) values ('$1'); 
  dec $1;
 }
enable_query_log;
explain select id,t from t1 order by id;
explain select id,t from t1 force index (primary) order by id;
drop table t1;

#
# Test of test_if_subkey() function
#
CREATE TABLE t1 (
  FieldKey varchar(36) NOT NULL default '',
  LongVal bigint(20) default NULL,
  StringVal mediumtext,
  KEY FieldKey (FieldKey),
  KEY LongField (FieldKey,LongVal),
  KEY StringField (FieldKey,StringVal(32))
);
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
DROP TABLE t1;
#
# Bug #1945 - Crashing bug with bad User Variables in UPDATE ... ORDER BY ...
#
CREATE TABLE t1 (a INT, b INT);
SET @id=0;
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
DROP TABLE t1;

#
# Bug when doing an order by on a 1 byte string (Bug #2147)
#

CREATE TABLE t1 (  id smallint(6) unsigned NOT NULL default '0',  menu tinyint(4) NOT NULL default '0',  KEY id (id),  KEY menu (menu)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
drop table t1;

#
# REF_OR_NULL optimization + filesort (bug #2419)
#

create table t1(a int, b int, index(b));
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
select * from t1 where b=1 or b is null order by a;
explain select * from t1 where b=2 or b is null order by a;
select * from t1 where b=2 or b is null order by a;
drop table t1;

#
# Bug #3155 - Strange results with index (x, y) ... WHERE ... ORDER BY pk
#

create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
key(a,b,d), key(c,b,a));
create table t2 like t1;
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
optimize table t1;
set @row=10;
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
select * from t1 where a=1 and b in (1) order by c, b, a;
select * from t1 where a=1 and b in (1);
drop table t1, t2;

#
# Bug #4302
# Ambiguos order by when renamed column is identical to another in result.
# Should not fail and prefer column from t1 for sorting.
#
create table t1 (col1 int, col int);
create table t2 (col2 int, col int);
insert into t1 values (1,1),(2,2),(3,3);
insert into t2 values (1,3),(2,2),(3,1);
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
  order by col;

#
# Let us also test various ambiguos and potentially ambiguos cases 
# related to aliases
#
--error 1052
select col1 as col, col from t1 order by col;
--error 1052
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
  order by col;
--error 1052
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
  order by col;
--error 1052
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
--error 1052
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
  order by col;

select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
  order by col;
select col2 as c, col as c from t2 order by col;
select col2 as col, col as col2 from t2 order by col; 
select t2.col2, t2.col, t2.col from t2 order by col;

select t2.col2 as col from t2 order by t2.col;
select t2.col2 as col, t2.col from t2 order by t2.col;
select t2.col2, t2.col, t2.col from t2 order by t2.col;

drop table t1, t2;

#
# Bug #5428: a problem with small max_sort_length value
#

create table t1 (a char(25));
insert into t1 set a = repeat('x', 20);
insert into t1 set a = concat(repeat('x', 19), 'z');
insert into t1 set a = concat(repeat('x', 19), 'ab');
insert into t1 set a = concat(repeat('x', 19), 'aa');
set max_sort_length=20;
select a from t1 order by a;
drop table t1;

#
# Bug #7331
#

create table t1 (
  `sid` decimal(8,0) default null,
  `wnid` varchar(11) not null default '',
  key `wnid14` (`wnid`(4)),
  key `wnid` (`wnid`)
) engine=myisam default charset=latin1;

insert into t1 (`sid`, `wnid`) values
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');

explain select * from t1 where wnid like '0101%' order by wnid;

select * from t1 where wnid like '0101%' order by wnid;

drop table t1;

#
# Bug #7672 - a wrong result for a select query in braces followed by order by
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2), (1), (1), (2), (1);
SELECT a FROM t1 ORDER BY a;
(SELECT a FROM t1) ORDER BY a;
DROP TABLE t1;

#
# Bug #18767: global ORDER BY applied to a SELECT with ORDER BY either was
#             ignored or 'concatened' to the latter. 

CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (1,30), (2,20), (1,10), (2,30), (1,20), (2,10);

(SELECT b,a FROM t1 ORDER BY a,b) ORDER BY b,a;
(SELECT b FROM t1 ORDER BY b DESC) ORDER BY b ASC;
(SELECT b,a FROM t1 ORDER BY b,a) ORDER BY a,b;
(SELECT b,a FROM t1 ORDER by b,a LIMIT 3) ORDER by a,b;

DROP TABLE t1;

#
# Bug #22457: Column alias in ORDER BY works, but not if in an expression
#

CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
SELECT a + 1 AS num FROM t1 HAVING 30 - num;
--error 1054
SELECT a + 1 AS num, num + 1 FROM t1;
SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
--error 1054
SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
DROP TABLE t1;

#
# Bug#25126: Reference to non-existant column in UPDATE...ORDER BY... 
#       crashes server
#
CREATE TABLE bug25126 (
  val int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
);
--error 1054
UPDATE bug25126 SET MissingCol = MissingCol;
--error 1054
UPDATE bug25126 SET val = val ORDER BY MissingCol;
UPDATE bug25126 SET val = val ORDER BY val;
UPDATE bug25126 SET val = 1 ORDER BY val;
--error 1054
UPDATE bug25126 SET val = 1 ORDER BY MissingCol;
--error 1054
UPDATE bug25126 SET val = 1 ORDER BY val, MissingCol;
--error 1054
UPDATE bug25126 SET val = MissingCol ORDER BY MissingCol;
--error 1054
UPDATE bug25126 SET MissingCol = 1 ORDER BY val, MissingCol;
--error 1054
UPDATE bug25126 SET MissingCol = 1 ORDER BY MissingCol;
--error 1054
UPDATE bug25126 SET MissingCol = val ORDER BY MissingCol;
--error 1054
UPDATE bug25126 SET MissingCol = MissingCol ORDER BY MissingCol;
DROP TABLE bug25126;

#
# Bug #25427: crash when order by expression contains a name
#             that cannot be resolved unambiguously               
#

CREATE TABLE t1 (a int);

SELECT p.a AS val, q.a AS val1 FROM t1 p, t1 q ORDER BY val > 1;
--error 1052
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val;
--error 1052
SELECT p.a AS val, q.a AS val FROM t1 p, t1 q ORDER BY val > 1;

DROP TABLE t1;

#
# Bug #27532: ORDER/GROUP BY expressions with IN/BETWEEN and NOT IN/BETWEEN
#                          

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (3), (2), (4), (1);

SELECT a, IF(a IN (2,3), a, a+10) FROM t1
  ORDER BY IF(a IN (2,3), a, a+10);
SELECT a, IF(a NOT IN (2,3), a, a+10) FROM t1 
  ORDER BY IF(a NOT IN (2,3), a, a+10);
SELECT a, IF(a IN (2,3), a, a+10) FROM t1 
  ORDER BY IF(a NOT IN (2,3), a, a+10);

SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1
  ORDER BY IF(a BETWEEN 2 AND 3, a, a+10);
SELECT a, IF(a NOT BETWEEN 2 AND 3, a, a+10) FROM t1 
  ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);
SELECT a, IF(a BETWEEN 2 AND 3, a, a+10) FROM t1 
  ORDER BY IF(a NOT BETWEEN 2 AND 3, a, a+10);

SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
  FROM t1 GROUP BY x1, x2;
SELECT IF(a IN (1,2), a, '') as x1, IF(a NOT IN (1,2), a, '') as x2
  FROM t1 GROUP BY x1, IF(a NOT IN (1,2), a, '');

# The remaining queries are for better coverage
SELECT a, a IN (1,2) FROM t1 ORDER BY a IN (1,2);
SELECT a FROM t1 ORDER BY a IN (1,2);
SELECT a+10 FROM t1 ORDER BY a IN (1,2);
SELECT a, IF(a IN (1,2), a, a+10) FROM t1
  ORDER BY IF(a IN (3,4), a, a+10);   
DROP TABLE t1;

# End of 4.1
create table t1 (a int not null, b  int not null, c int not null);
insert t1 values (1,1,1),(1,1,2),(1,2,1);
select a, b from t1 group by a, b order by sum(c);
drop table t1;

#
# Bug#21302: Result not properly sorted when using an ORDER BY on a second 
#             table in a join
#
CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a));
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);

explain SELECT t1.b as a, t2.b as c FROM 
 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
ORDER BY c;
SELECT t2.b as c FROM 
 t1 LEFT JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2) 
ORDER BY c;

# check that it still removes sort of const table
explain SELECT t1.b as a, t2.b as c FROM 
 t1 JOIN t1 t2 ON (t1.a = t2.a AND t2.a = 2)  
ORDER BY c;

CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * from t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t3 SELECT * from t1;
CREATE TABLE t4 LIKE t1;
INSERT INTO t4 SELECT * from t1;
INSERT INTO t1 values (0,0),(4,4);

SELECT t2.b FROM t1 LEFT JOIN (t2, t3 LEFT JOIN t4 ON t3.a=t4.a)
ON (t1.a=t2.a AND t1.b=t3.b) order by t2.b;

DROP TABLE t1,t2,t3,t4;

#
# Bug#25376: Incomplete setup of ORDER BY clause results in a wrong result.
#
create table t1 (a int, b int, c int);
insert into t1 values (1,2,3), (9,8,3), (19,4,3), (1,4,9);
select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
drop table t1;

#
# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
#
CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
UPDATE t1 SET b = SEC_TO_TIME(a);

# Correct ORDER
SELECT a, b FROM t1 ORDER BY b DESC;

# must be ordered as the above
SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;

DROP TABLE t1;