summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/delete.result9
-rw-r--r--mysql-test/r/func_gconcat.result10
-rw-r--r--mysql-test/r/gis.result17
-rw-r--r--mysql-test/r/insert_update.result78
-rw-r--r--mysql-test/r/range.result141
-rw-r--r--mysql-test/r/select.result6
-rw-r--r--mysql-test/r/subselect.result42
-rw-r--r--mysql-test/r/subselect3.result6
8 files changed, 303 insertions, 6 deletions
diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result
index ba4e9386312..4bdf1c770d3 100644
--- a/mysql-test/r/delete.result
+++ b/mysql-test/r/delete.result
@@ -214,3 +214,12 @@ select count(*) from t1;
count(*)
0
drop table t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+DELETE FROM t1 ORDER BY x;
+ERROR 42S22: Unknown column 'x' in 'order clause'
+DELETE FROM t1 ORDER BY t2.x;
+ERROR 42S22: Unknown column 't2.x' in 'order clause'
+DELETE FROM t1 ORDER BY (SELECT x);
+ERROR 42S22: Unknown column 'x' in 'field list'
+DROP TABLE t1;
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 6989b89833b..71419b5b2c3 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -728,3 +728,13 @@ f2 group_concat(f1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
drop table t1;
+CREATE TABLE t1(a TEXT, b CHAR(20));
+INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3");
+SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
+GROUP_CONCAT(DISTINCT UCASE(a))
+ONE.1,TWO.2,ONE.3
+SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
+GROUP_CONCAT(DISTINCT UCASE(b))
+ONE.1,TWO.2,ONE.3
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index de034d93dc5..749c84a1a6f 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -730,6 +730,12 @@ point(b, b) IS NULL linestring(b) IS NULL polygon(b) IS NULL multipoint(b) IS NU
1 1 1 1 1 1 1
0 1 1 1 1 1 1
drop table t1;
+CREATE TABLE t1(a POINT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+a
+NULL
+DROP TABLE t1;
End of 4.1 tests
create table t1 (s1 geometry not null,s2 char(100));
create trigger t1_bu before update on t1 for each row set new.s1 = null;
@@ -763,3 +769,14 @@ create table t1 (g geometry not null);
insert into t1 values(default);
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
drop table t1;
+CREATE TABLE t1 (a GEOMETRY);
+CREATE VIEW v1 AS SELECT GeomFromwkb(ASBINARY(a)) FROM t1;
+CREATE VIEW v2 AS SELECT a FROM t1;
+DESCRIBE v1;
+Field Type Null Key Default Extra
+GeomFromwkb(ASBINARY(a)) geometry YES NULL
+DESCRIBE v2;
+Field Type Null Key Default Extra
+a geometry YES NULL
+DROP VIEW v1,v2;
+DROP TABLE t1;
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index ef0d8ec239e..fd70fcb9084 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -258,3 +258,81 @@ SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1
DROP TABLE t1;
+SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
+CREATE TABLE `t1` (
+`id` int(11) PRIMARY KEY auto_increment,
+`f1` varchar(10) NOT NULL UNIQUE
+);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+SELECT * FROM t1;
+id f1
+1 test1
+INSERT IGNORE INTO t1 (f1) VALUES ("test2")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT * FROM t1;
+id f1
+1 test1
+2 test2
+INSERT IGNORE INTO t1 (f1) VALUES ("test2")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+2
+SELECT * FROM t1;
+id f1
+1 test1
+2 test2
+INSERT IGNORE INTO t1 (f1) VALUES ("test3")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+SELECT * FROM t1;
+id f1
+1 test1
+2 test2
+3 test3
+DROP TABLE t1;
+CREATE TABLE `t1` (
+`id` int(11) PRIMARY KEY auto_increment,
+`f1` varchar(10) NOT NULL UNIQUE
+);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+SELECT * FROM t1;
+id f1
+1 test1
+INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+SELECT * FROM t1;
+id f1
+1 test1
+2 test4
+DROP TABLE t1;
+CREATE TABLE `t1` (
+`id` int(11) PRIMARY KEY auto_increment,
+`f1` varchar(10) NOT NULL UNIQUE,
+tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
+);
+INSERT INTO t1 (f1) VALUES ("test1");
+SELECT id, f1 FROM t1;
+id f1
+1 test1
+REPLACE INTO t1 VALUES (0,"test1",null);
+SELECT id, f1 FROM t1;
+id f1
+0 test1
+DROP TABLE t1;
+SET SQL_MODE='';
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index e342e8d1d7f..58f8d077ac5 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -717,6 +717,147 @@ d8c4177d225791924.30714720
d8c4177d2380fc201.39666693
d8c4177d24ccef970.14957924
DROP TABLE t1;
+create table t1 (
+c1 char(10), c2 char(10), c3 char(10), c4 char(10),
+c5 char(10), c6 char(10), c7 char(10), c8 char(10),
+c9 char(10), c10 char(10), c11 char(10), c12 char(10),
+c13 char(10), c14 char(10), c15 char(10), c16 char(10),
+index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16)
+);
+insert into t1 (c1) values ('1'),('1'),('1'),('1');
+select * from t1 where
+c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
+and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
+"abcdefg1", "123456781", "qwertyui1", "asddfg1",
+"abcdefg2", "123456782", "qwertyui2", "asddfg2",
+"abcdefg3", "123456783", "qwertyui3", "asddfg3",
+"abcdefg4", "123456784", "qwertyui4", "asddfg4",
+"abcdefg5", "123456785", "qwertyui5", "asddfg5",
+"abcdefg6", "123456786", "qwertyui6", "asddfg6",
+"abcdefg7", "123456787", "qwertyui7", "asddfg7",
+"abcdefg8", "123456788", "qwertyui8", "asddfg8",
+"abcdefg9", "123456789", "qwertyui9", "asddfg9",
+"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
+"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
+"abcdefgC", "12345678C", "qwertyuiC", "asddfgC");
+c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16
+drop table t1;
End of 4.1 tests
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 31d15981d93..b501d547e0a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3636,6 +3636,12 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT 1 FROM t1 IGNORE INDEX FOR JOIN (a) WHERE a = 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+EXPLAIN SELECT 1 FROM t1 USE INDEX FOR JOIN (a) WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
+EXPLAIN SELECT 1 FROM t1 FORCE INDEX FOR JOIN (a) WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 5 const 1 Using where; Using index
DROP TABLE t1;
CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);
CREATE TABLE t2 ( f11 int PRIMARY KEY );
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 72bde001e87..d5a1c0b2451 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3905,3 +3905,45 @@ COUNT(*) a
2 2
3 3
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+SELECT COUNT(*) c, a,
+(SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
+FROM t1 GROUP BY a;
+c a (SELECT GROUP_CONCAT(COUNT(a)) FROM t2 WHERE m = a)
+2 2 2
+3 3 3
+1 4 1,1
+SELECT COUNT(*) c, a,
+(SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
+FROM t1 GROUP BY a;
+c a (SELECT GROUP_CONCAT(COUNT(a)+1) FROM t2 WHERE m = a)
+2 2 3
+3 3 4
+1 4 2,2
+DROP table t1,t2;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+a
+1
+2
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+a
+SELECT a FROM t1 t0
+WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+a
+1
+2
+SET @@sql_mode='ansi';
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 0 GROUP BY a;
+ERROR HY000: Invalid use of group function
+SELECT a FROM t1 WHERE (SELECT COUNT(b) FROM DUAL) > 1 GROUP BY a;
+ERROR HY000: Invalid use of group function
+SELECT a FROM t1 t0
+WHERE (SELECT COUNT(t0.b) FROM t1 t WHERE t.b>20) GROUP BY a;
+ERROR HY000: Invalid use of group function
+SET @@sql_mode=default;
+DROP TABLE t1;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 6a7a601ccf9..96a3ee00a59 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -661,12 +661,6 @@ SELECT * FROM t1 GROUP by t1.a
HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
HAVING MAX(t2.b+t1.a) < 10));
a b c
-SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b))
-AS test FROM t1 GROUP BY a;
-a AVG(b) test
-1 4.0000 NULL
-2 2.0000 k
-3 2.5000 NULL
SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
a b c
1 3 c
View Lorry Controller Status