~drizzle-trunk/drizzle/development

select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3;

explain select * from t1 where i=@vv1;

select @vv1,i,v from t1 where i=@vv1;

explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;

explain select @vv1:=i from t1 where i=@vv1;

explain select * from t1 where i=@vv1;

drop table t1,t2;

# Check types of variables

set @a=0,@b=0;

select @a:=10, @b:=1, @a > @b, @a < @b;

# Note that here a and b will be avaluated as number

select @a:="10", @b:="1", @a > @b, @a < @b;

# Note that here a and b will be avaluated as strings

select @a:=10, @b:=2, @a > @b, @a < @b;

select @a:="10", @b:="2", @a > @b, @a < @b;

# Fixed bug #1194

select @a:=1;

select @a, @a:=1;

create table t1 (id int, d double, c char(10));

insert into t1 values (1,2.0, "test");

select @c:=0;

update t1 SET id=(@c:=@c+1);

select @c;

select @c:=0;

update t1 set id=(@c:=@c+1);

select @c;

select @c:=0;

select @c:=@c+1;

select @d,(@d:=id),@d from t1;

select @e,(@e:=d),@e from t1;

select @f,(@f:=c),@f from t1;

set @g=1;

select @g,(@g:=c),@g from t1;

select @c, @d, @e, @f;

select @d:=id, @e:=id, @f:=id, @g:=@id from t1;

select @c, @d, @e, @f, @g;

drop table t1;

# just for fun :)

select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b;

# bug#1739

# Item_func_set_user_var sets update_query_id, Item_func_get_user_var checks it

create table t1 (i int not null);

insert t1 values (1),(2),(2),(3),(3),(3);

select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i;

select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;

set @a=0;

select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;

drop table t1;

# Bug #6321 strange error:

# string function FIELD(<uservariable content NULL>, ...)

set @var= NULL ;

select FIELD( @var,'1it','Hit') as my_column;

# Bug #9286 SESSION/GLOBAL should be disallowed for user variables

--error ER_PARSE_ERROR

set session @honk=99;

# Bug #10724 @@local not preserved in column name of select

# The value doesn't actually matter, we just care about the column name

--replace_column 1 #

select @@local.max_allowed_packet;

--replace_column 1 #

select @@session.max_allowed_packet;

100

--replace_column 1 #

101

select @@global.max_allowed_packet;

102

--replace_column 1 #

103

select @@max_allowed_packet;

104

--replace_column 1 #

105

select @@Max_Allowed_Packet;

106

--replace_column 1 #

107

select @@version;

108

--replace_column 1 #

109

select @@global.version;

110

111

--echo End of 4.1 tests

112

113

# Bug #6598: problem with cast(NULL as signed integer);

114

115

116

set @first_var= NULL;

117

create table t1 select @first_var;

118

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

119

show create table t1;

120

drop table t1;

121

122

set @first_var= cast(NULL as integer);

123

124

create table t1 select @first_var;

125

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

126

show create table t1;

127

drop table t1;

128

129

set @first_var= NULL;

130

create table t1 select @first_var;

131

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

132

show create table t1;

133

drop table t1;

134

set @first_var= concat(NULL);

135

create table t1 select @first_var;

136

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

137

show create table t1;

138

drop table t1;

139

set @first_var=1;

140

set @first_var= cast(NULL as CHAR);

141

create table t1 select @first_var;

142

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

143

show create table t1;

144

drop table t1;

145

146

147

# Bug #7498 User variable SET saves SIGNED BIGINT as BIGINT

148

149

150

# First part, set user var to large number and select it

151

set @a=18446744071710965857;

152

select @a;

153

154

# Second part, set user var from large number in table

155

# then select it

156

CREATE TABLE `bigfailure` (

157

`afield` BIGINT UNSIGNED NOT NULL

158

);

159

INSERT INTO `bigfailure` VALUES (18446744071710965857);

160

SELECT * FROM bigfailure;

161

select * from (SELECT afield FROM bigfailure) as b;

162

select * from bigfailure where afield = (SELECT afield FROM bigfailure);

163

select * from bigfailure where afield = 18446744071710965857;

164

# This is fixed in 5.0, to be uncommented there

165

select * from bigfailure where afield = '18446744071710965857';

166

select * from bigfailure where afield = 18446744071710965856+1;

167

168

SET @a := (SELECT afield FROM bigfailure);

169

SELECT @a;

170

SET @a := (select afield from (SELECT afield FROM bigfailure) as b);

171

SELECT @a;

172

SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure));

173

SELECT @a;

174

175

drop table bigfailure;

176

177

178

# Bug#16861: User defined variable can have a wrong value if a tmp table was

179

# used.

180

181

create table t1(f1 int, f2 int);

182

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

183

select @var:=f2 from t1 group by f1 order by f2 desc limit 1;

184

select @var;

185

# Bug 310977, uncomment this test after the bug is fixed

186

#create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;

187

#select * from t2;

188

#select @var;

189

drop table t1;

190

#drop table t2;

191

192

193

# Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors

194

195

--error ER_PARSE_ERROR

196

insert into city 'blah';

197

SHOW COUNT(*) WARNINGS;

198

SHOW COUNT(*) ERRORS;

199

200

201

# Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.

202

203

create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));

204

insert into t1 values

205

(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),

206

(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),

207

(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);

208

select @a:=f1, count(f1) from t1 group by 1 desc;

209

select @a:=f1, count(f1) from t1 group by 1 asc;

210

select @a:=f2, count(f2) from t1 group by 1 desc;

211

select @a:=f3, count(f3) from t1 group by 1 desc;

212

select @a:=f4, count(f4) from t1 group by 1 desc;

213

drop table t1;

214

215

216

# Bug#32482: Crash for a query with ORDER BY a user variable.

217

218

create table t1 (f1 int);

219

insert into t1 values (2), (1);

220

select @i := f1 as j from t1 order by 1;

221

drop table t1;

222

# Bug #32260: User variables in query cause server crash

223

224

create table t1(a int);

225

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

226

set @rownum := 0;

227

set @rank := 0;

228

set @prev_score := NULL;

229

# Disable the result log as we assign a value to a user variable in one part

230

# of a statement and use the same variable in other part of the same statement,

231

# so we can get unexpected results.

232

--disable_result_log

233

select @rownum := @rownum + 1 as row,

234

@rank := IF(@prev_score!=a, @rownum, @rank) as rank,

235

@prev_score := a as score

236

from t1 order by score desc;

237

--enable_result_log

238

drop table t1;

239

240

--echo End of 5.1 tests

Older »