~ubuntu-branches/ubuntu/saucy/drizzle/saucy-proposed

select "abc" like "a%", "abc" not like "%d%", "a%" like "a\%","abc%" like "a%\%","abcd" like "a%b_%d", "a" like "%%a","abcde" like "a%_e","abc" like "abc%";

select "a" like "%%b","a" like "%%ab","ab" like "a\%", "ab" like "_", "ab" like "ab_", "abc" like "%_d", "abc" like "abc%d";

select 'abc' like '%c','abcabc' like '%c', "ab" like "", "ab" like "a", "ab" like "ab";

select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3;

select 'b' between 'a' and 'c', 'B' between 'a' and 'c';

select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0);

select -1.49 or -1.49,0.6 or 0.6;

select 10 % 7, 10 mod 7, 10 div 3;

explain extended select 10 % 7, 10 mod 7, 10 div 3;

create table t1 (a int);

insert t1 values (1);

select * from t1 where 1 xor 1;

explain extended select * from t1 where 1 xor 1;

select - a from t1;

explain extended select - a from t1;

drop table t1;

# Wrong usage of functions

select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;

select 1 and 2 between 2 and 10, 2 between 2 and 10 and 1;

select 1 and 0 or 2, 2 or 1 and 0;

# Test for LEAST() BUG in LEFT JOIN

CREATE TABLE t1 ( faq_group_id int NOT NULL default '0', faq_id int NOT NULL default '0', title varchar(240) default NULL, keywords text, description longblob, solution longblob, status int NOT NULL default '0', access_id int default NULL, lang_id int NOT NULL default '0', created datetime NULL, updated datetime default NULL, last_access datetime default NULL, last_notify datetime default NULL, solved_count int NOT NULL default '0', static_solved int default NULL, solved_1 int default NULL, solved_2 int default NULL, solved_3 int default NULL, solved_4 int default NULL, solved_5 int default NULL, expires datetime default NULL, notes text, assigned_to int default NULL, assigned_group int default NULL, last_edited_by int default NULL, orig_ref_no varchar(15) default NULL, c$fundstate int default NULL, c$contributor int default NULL, UNIQUE KEY t1$faq_id (faq_id), KEY t1$group_id$faq_id (faq_group_id,faq_id), KEY t1$c$fundstate (c$fundstate) );

INSERT INTO t1 VALUES (82,82,'How to use the DynaVox Usage Counts Feature','usages count, number, corner, white, box, button','<as-html>\r\n<table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\">�</td>\r\n <td width=\"97%\">\r\n <h3>How \r\n To: \r\n Display or Hide the Usage Counts to find out how many times each button is being selected. </h3>\r\n </td>\r\n </tr>\r\n</table>','<as-html>\r\n <table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\">�</td>\r\n \r\n<td width=\"97%\"> \r\n \r\n1. Select \r\n the On/Setup button to access the DynaVox Setup Menu. \r\n 2. Select Button Features. \r\n 3. Below the OK button is the Usage Counts button. \r\n a. If it says \"Hidden\" then the Usage Counts will not be displayed. \r\n b. If it says \"Displayed\" then the Usage Counts will be shown. \r\n c. Select the Usage Counts Option Ring once and it will toggle \r\n to the alternative option. \r\n 4. Once the correct setting has been chosen, select OK to leave the Button \r\n Features menu. \r\n 5. Select OK out of the Setup menu and return to the communication \r\n page.\r\n For \r\n further information on Usage Counts, see the Button Features \r\n Menu Entry in the DynaVox/DynaMyte Reference Manual.\r\n</td>\r\n </tr>\r\n</table>',4,1,1,'2001-11-16 16:43:34','2002-11-25 12:09:43','2003-07-24 01:04:48',NULL,11,NULL,0,0,0,0,0,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL);

CREATE TABLE t2 ( access_id int NOT NULL default '0', name varchar(20) default NULL, rank int NOT NULL default '0', KEY t2$access_id (access_id) );

INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1);

SELECT f_acc.rank, a1.rank, a2.rank FROM t1 LEFT JOIN t1 f1 ON (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id = f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND f2.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) = f_acc.rank;

DROP TABLE t1,t2;

# Test for GREATEST() and LEAST() bug, which segfaulted 4.1.1 server

CREATE TABLE t1 (d varchar(6), k int);

INSERT INTO t1 VALUES (NULL, 2);

SELECT GREATEST(d,d) FROM t1 WHERE k=2;

DROP TABLE t1;

# Bug #6138: mod and doubles

select 1197.90 mod 50;

select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3;

# Test for mod and signed integers

select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3;

# Bug#23411: The "%" (MOD) operator is not documented; MOD-ing zero returns strange result

# Manual: "Division by zero produces a NULL result"

select (12%0) <=> null as '1';

select (12%0) is null as '1';

select 12%0 as 'NULL';

select 12%2 as '0';

select 12%NULL as 'NULL';

select 12 % null as 'NULL';

select null % 12 as 'NULL';

select null % 0 as 'NULL';

select 0 % null as 'NULL';

select null % null as 'NULL';

select (12 mod 0) <=> null as '1';

select (12 mod 0) is null as '1';

select 12 mod 0 as 'NULL';

select 12 mod 2 as '0';

select 12 mod null as 'NULL';

select null mod 12 as 'NULL';

select null mod 0 as 'NULL';

select 0 mod null as 'NULL';

select null mod null as 'NULL';

select mod(12.0, 0) as 'NULL';

select mod(12, 0.0) as 'NULL';

select mod(12, NULL) as 'NULL';

select mod(12.0, NULL) as 'NULL';

select mod(NULL, 2) as 'NULL';

select mod(NULL, 2.0) as 'NULL';

100

101

102

# Bug#6726: NOT BETWEEN parse failure

103

104

create table t1 (a int, b int);

105

insert into t1 values (1,2), (2,3), (3,4), (4,5);

106

select * from t1 where a not between 1 and 2;

107

select * from t1 where a not between 1 and 2 and b not between 3 and 4;

108

drop table t1;

109

110

111

# Test for bug #12791: one of the arguments of LEAST/GREATEST is NULL

112

113

114

SELECT GREATEST(1,NULL);

115

SELECT LEAST('xxx','aaa',NULL,'yyy');

116

SELECT LEAST(1.1,1.2,NULL,1.0);

117

SELECT GREATEST(1.5E+2,1.3E+2,NULL);

118

119

# End of 4.1 tests

Older »