~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

# This is not supported by Drizzle

123

--error ER_PARSE_ERROR

124

set @first_var= cast(NULL as integer);

125

#create table t1 select @first_var;

126

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

127

#show create table t1;

128

#drop table t1;

129

130

set @first_var= NULL;

131

create table t1 select @first_var;

132

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

133

show create table t1;

134

drop table t1;

135

set @first_var= concat(NULL);

136

create table t1 select @first_var;

137

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

138

show create table t1;

139

drop table t1;

140

set @first_var=1;

141

set @first_var= cast(NULL as CHAR);

142

create table t1 select @first_var;

143

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

144

show create table t1;

145

drop table t1;

146

147

148

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

149

150

151

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

152

set @a=18446744071710965857;

153

select @a;

154

155

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

156

# then select it

157

CREATE TABLE `bigfailure` (

158

`afield` BIGINT NOT NULL

159

);

160

INSERT INTO `bigfailure` VALUES (18446744071710965857);

161

SELECT * FROM bigfailure;

162

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

163

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

164

select * from bigfailure where afield = 18446744071710965857;

165

# This is fixed in 5.0, to be uncommented there

166

select * from bigfailure where afield = '18446744071710965857';

167

select * from bigfailure where afield = 18446744071710965856+1;

168

169

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

170

SELECT @a;

171

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

172

SELECT @a;

173

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

174

SELECT @a;

175

176

drop table bigfailure;

177

178

179

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

180

# used.

181

182

create table t1(f1 int, f2 int);

183

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

184

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

185

select @var;

186

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

187

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

188

#select * from t2;

189

#select @var;

190

drop table t1;

191

#drop table t2;

192

193

194

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

195

196

--error ER_PARSE_ERROR

197

insert into city 'blah';

198

SHOW COUNT(*) WARNINGS;

199

SHOW COUNT(*) ERRORS;

200

201

202

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

203

204

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

205

insert into t1 values

206

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

207

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

208

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

209

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

210

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

211

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

212

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

213

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

214

drop table t1;

215

216

217

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

218

219

create table t1 (f1 int);

220

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

221

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

222

drop table t1;

223

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

224

225

create table t1(a int);

226

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

227

set @rownum := 0;

228

set @rank := 0;

229

set @prev_score := NULL;

230

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

231

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

232

# so we can get unexpected results.

233

--disable_result_log

234

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

235

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

236

@prev_score := a as score

237

from t1 order by score desc;

238

--enable_result_log

239

drop table t1;

240

241

--echo End of 5.1 tests

Older »