summaryrefslogtreecommitdiff
path: root/mysql-test/main/func_analyse.result
blob: 1e78e603bca6278c67b0441d64c215a0fc419ba7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
drop table if exists t1,t2;
create table t1 (i int, j int, empty_string char(10), bool char(1), d date);
insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6,"","Y","2002-03-04"), (7,8,"","N","2002-03-05");
select count(*) from t1 procedure analyse();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
count(*)	4	4	1	1	0	0	4.0000	0.0000	ENUM('4') NOT NULL
select * from t1 procedure analyse();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.i	1	7	1	1	0	0	4.0000	2.2361	ENUM('1','3','5','7') NOT NULL
test.t1.j	2	8	1	1	0	0	5.0000	2.2361	ENUM('2','4','6','8') NOT NULL
test.t1.empty_string			0	0	4	0	0.0000	NULL	CHAR(0) NOT NULL
test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL
select * from t1 procedure analyse(2);
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.i	1	7	1	1	0	0	4.0000	2.2361	TINYINT(1) UNSIGNED NOT NULL
test.t1.j	2	8	1	1	0	0	5.0000	2.2361	TINYINT(1) UNSIGNED NOT NULL
test.t1.empty_string			0	0	4	0	0.0000	NULL	CHAR(0) NOT NULL
test.t1.bool	N	Y	1	1	0	0	1.0000	NULL	ENUM('N','Y') NOT NULL
test.t1.d	2002-03-03	2002-03-05	10	10	0	0	10.0000	NULL	ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL
create table t2 select * from t1 procedure analyse();
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'procedure analyse()' at line 1
drop table t1;
EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
ERROR HY000: Incorrect usage of PROCEDURE and subquery
create table t1 (v varchar(128));
insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd');
select * from t1 procedure analyse();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.v	"	\\	1	19	0	0	3.7619	NULL	ENUM('"','""','"c','\'\0\\"','\'','\'\'','\'b','a\0\0\0b','a\0','a""""b','a\'\'\'\'b','abc','abc\'def\\hij"klm\0opq','a\\\\\\\\b','b\'','c"','d\\','The\ZEnd','\\','\\d','\\\\') NOT NULL
drop table t1;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
select * from t1 procedure analyse();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.df	1.1	2.2	13	13	0	0	1.65000	0.55000	ENUM('1.1','2.2') NOT NULL
drop table t1;
create table t1 (d double);
insert into t1 values (100000);
select * from t1 procedure analyse (1,1);
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.d	100000	100000	6	6	0	0	100000	0	MEDIUMINT(6) UNSIGNED NOT NULL
drop table t1;
create table t1 (product varchar(32), country_id int not null, year int,
profit int);
insert into t1  values ( 'Computer', 2,2000, 1200),
( 'TV', 1, 1999, 150),
( 'Calculator', 1, 1999,50),
( 'Computer', 1, 1999,1500),
( 'Computer', 1, 2000,1500),
( 'TV', 1, 2000, 150),
( 'TV', 2, 2000, 100),
( 'TV', 2, 2000, 100),
( 'Calculator', 1, 2000,75),
( 'Calculator', 2, 2000,75),
( 'TV', 1, 1999, 100),
( 'Computer', 1, 1999,1200),
( 'Computer', 2, 2000,1500),
( 'Calculator', 2, 2000,75),
( 'Phone', 3, 2003,10)
;
create table t2 (country_id int primary key, country char(20) not null);
insert into t2 values (1, 'USA'),(2,'India'), (3,'Finland');
select product, sum(profit),avg(profit) from t1 group by product with rollup procedure analyse();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.product	Computer	TV	2	8	0	0	4.2500	NULL	ENUM('Computer','Phone','TV') NOT NULL
sum(profit)	10	6900	11	11	0	0	1946.2500	2867.6719	ENUM('10','275','600','6900') NOT NULL
avg(profit)	10.0000	1380.0000	16	16	0	0	394.68750000	570.20033144	ENUM('10.0000','68.7500','120.0000','1380.0000') NOT NULL
drop table t1,t2;
create table t1 (f1 double(10,5), f2 char(10), f3 double(10,5));
insert into t1 values (5.999, "5.9999", 5.99999), (9.555, "9.5555", 9.55555);
select f1 from t1 procedure analyse(1, 1);
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.f1	5.99900	9.55500	7	7	0	0	7.77700	1.77800	FLOAT(4,3) NOT NULL
select f2 from t1 procedure analyse(1, 1);
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.f2	5.9999	9.5555	6	6	0	0	6.0000	NULL	FLOAT(5,4) UNSIGNED NOT NULL
select f3 from t1 procedure analyse(1, 1);
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.f3	5.99999	9.55555	7	7	0	0	7.77777	1.77778	FLOAT(6,5) NOT NULL
drop table t1;
CREATE TABLE t1(a INT,b INT,c INT,d INT,e INT,f INT,g INT,h INT,i INT,j INT,k INT);
INSERT INTO t1 VALUES ();
SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE();
ERROR HY000: Incorrect usage of PROCEDURE and subquery
DROP TABLE t1;
End of 4.1 tests
#
# Bug #48293: crash with procedure analyse, view with > 10 columns, 
#  having clause...
#
CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT,
f INT, g INT, h INT, i INT, j INT,k INT);
INSERT INTO t1 VALUES (),();
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
#should have a derived table
EXPLAIN SELECT * FROM v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
#should not crash
SELECT * FROM v1 PROCEDURE analyse();
ERROR HY000: Incorrect usage of PROCEDURE and view
#should not crash
SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse();
ERROR HY000: Incorrect usage of PROCEDURE and view
#should not crash
SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse();
ERROR HY000: Incorrect usage of PROCEDURE and subquery
#should not crash
SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse();
ERROR HY000: Incorrect usage of PROCEDURE and subquery
#should not crash
SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse();
ERROR HY000: Can't use ORDER clause with this procedure
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1),(2);
# should not crash
CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE();
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE()' at line 1
DROP TABLE t1;
End of 5.0 tests
#
# Bug#11765202: Dbug_violation_helper::~Dbug_violation_helper(): Assertion `!_entered' failed.
#
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 'test.t1'
CREATE TABLE t1 (a VARCHAR(2) CHARSET UTF8 NOT NULL);
INSERT INTO t1 VALUES ('e'),('e'),('e-');
SELECT * FROM t1 PROCEDURE ANALYSE();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.a	e	e-	1	2	0	0	1.3333	NULL	ENUM('e','e-') NOT NULL
DROP TABLE t1;
#
# Bug#11756242  48137: PROCEDURE ANALYSE() LEAKS MEMORY WHEN RETURNING NULL
#
CREATE TABLE t1(f1 INT) ENGINE=MYISAM;
CREATE TABLE t2(f2 INT) ENGINE=INNODB;
INSERT INTO t2 VALUES (1);
SELECT DISTINCTROW f1 FROM t1 NATURAL RIGHT OUTER JOIN t2 PROCEDURE ANALYSE();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t1.f1	NULL	NULL	0	0	0	1	0.0	0.0	CHAR(0)
SELECT * FROM t2 LIMIT 1 PROCEDURE ANALYSE();
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
test.t2.f2	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
DROP TABLE t1, t2;
End of 5.1 tests
#
# Start of 10.2 tests
#
(SELECT 1 FROM DUAL PROCEDURE ANALYSE());
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
1	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
((SELECT 1 FROM DUAL PROCEDURE ANALYSE()));
Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
1	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
SELECT * FROM t1 UNION SELECT * FROM t1 PROCEDURE analyse();
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE analyse()' at line 1
#
# MDEV-10030 sql_yacc.yy: Split table_expression and remove PROCEDURE from create_select, select_paren_derived, select_derived2, query_specification
#
SELECT * FROM (SELECT * FROM t1 PROCEDURE ANALYSE());
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())' at line 1
SELECT * FROM t1 NATURAL JOIN (SELECT * FROM t2 PROCEDURE ANALYSE());
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())' at line 1
SELECT (SELECT 1 FROM t1 PROCEDURE ANALYSE()) FROM t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE()) FROM t2' at line 1
SELECT ((SELECT 1 FROM t1 PROCEDURE ANALYSE())) FROM t2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROCEDURE ANALYSE())) FROM t2' at line 1