oceanbase

1
# owner: yuchen.wyc
2
#tags: pl, optimizer
3
# owner group: SQL1
4
# description: foobar
5
# tags: dml
6
# case_transfer: 
7
# （1） update ignore语法不支持；
8
# （2）update 多表操作不支持；
9
# （3）create table t1 (a int, b char(255), key(a, b(20))); 不支持key为列的部分长度
10
# （4）alter table t1 disable keys; 不支持；
11
# （5）flush status；不支持
12
# （6）系统变量%Handler_read% 不支持；
13
# （7）set tmp_table_size=1024; 不支持
14
# （8）系统变量不支持；SET SESSION sql_safe_updates = DEFAULT;
15
# （9）HANDLER_UPDATE 系统变量不支持
16
#
17
# test of updating of keys
18
#
19
--source mysql_test/include/backup_spm_var.inc
20
--disable_query_log
21
set @@session.explicit_defaults_for_timestamp=off;
22
connect (conn_admin, $OBMYSQL_MS0,admin,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
23
connection conn_admin;
24
alter system set_tp tp_no = 509, error_code = 4016, frequency = 1;
25
connection default;
26
--enable_query_log
27

28
--disable_warnings
29
drop table if exists t1,t2;
30
--enable_warnings
31
--explain_protocol 1
32
--result_format 4
33

34
alter system set ob_enable_batched_multi_statement=true;
35
--real_sleep 1
36
create table t1 (a int auto_increment , primary key (a));
37
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); 
38
update t1 set a=a+10 where a > 34;
39
update t1 set a=a+100 where a > 0;
40

41
# Some strange updates to test some otherwise unused code
42
update t1 set a=a+100 where a=1 and a=2;
43
--error 1054
44
update t1 set a=b+100 where a=1 and a=2; 
45
--error 1054
46
update t1 set a=b+100 where c=1 and a=2; 
47
--error 1054
48
update t1 set d=a+100 where a=1;
49
select * from t1;
50
drop table t1;
51

52
create table t1(a int primary key, b int) partition by hash(a) partitions 3;
53
insert into t1 values(1, 1), (2, 2);
54
update t1 set b=b+1 where a>0;
55
select * from t1;
56
drop table t1;
57

58
create table t1(a int primary key, b int) partition by hash(a) partitions 3;
59
insert into t1 values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
60
update t1 set b=b+1 where a>0;
61
select * from t1;
62
drop table t1;
63

64
create table t1(a int primary key, b int) partition by hash(a) partitions 3;
65
create index gkey on t1(b) global;
66
insert into t1 values(1, 1), (2, 2); 
67
update t1 set b=b+1 where a>0;
68
select * from t1; 
69
drop table t1; 
70

71
create table t1(a int primary key, b int) partition by hash(a) partitions 3;
72
create index gkey on t1(b) global;
73
insert into t1 values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
74
update t1 set b=b+1 where a>0;
75
select * from t1;
76
drop table t1;
77

78
create table t1(a int primary key, b int);
79
create index gkey on t1(b) partition by hash(b) partitions 2;
80
insert into t1 values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
81
update /*+index(t1 gkey)*/ t1 set b=b+1 where b>0 and b<5;
82
select /*+index(t1 primary)*/ * from t1 order by a;
83
drop table t1;
84

85
create table t1(a int primary key, b int) partition by hash(a) partitions 3;
86
create table t2(a int primary key, b int) partition by hash(a) partitions 3;
87
insert into t1 values(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
88
update /*+use_nl(t1, t2), leading(t1)*/ t1 set b=b+1 where a in (select a from t2);
89
select * from t1;
90
drop table t1, t2;
91

92
CREATE TABLE t1
93
 (
94
 place_id int (10) unsigned NOT NULL,
95
 shows int(10) unsigned DEFAULT '0' NOT NULL,
96
 ishows int(10) unsigned DEFAULT '0' NOT NULL,
97
 ushows int(10) unsigned DEFAULT '0' NOT NULL,
98
 clicks int(10) unsigned DEFAULT '0' NOT NULL,
99
 iclicks int(10) unsigned DEFAULT '0' NOT NULL,
100
 uclicks int(10) unsigned DEFAULT '0' NOT NULL,
101
 ts timestamp,
102
 PRIMARY KEY (place_id,ts)
103
 );
104

105
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
106
VALUES (1,0,0,0,0,0,0,20000928174434);
107
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
108
select place_id,shows from t1;
109
drop table t1;
110

111
#
112
# Test bug with update reported by Jan Legenhausen
113
#
114

115
CREATE TABLE t1 (
116
  lfdnr int(10) unsigned NOT NULL default '0',
117
  ticket int(10) unsigned NOT NULL default '0',
118
  client varchar(255) NOT NULL default '',
119
  replyto varchar(255) NOT NULL default '',
120
  subject varchar(100) NOT NULL default '',
121
  timestamp int(10) unsigned NOT NULL default '0',
122
  tstamp timestamp NOT NULL,
123
  status int(3) NOT NULL default '0',
124
  type varchar(15) NOT NULL default '',
125
  assignment int(10) unsigned NOT NULL default '0',
126
  fupcount int(4) unsigned NOT NULL default '0',
127
  parent int(10) unsigned NOT NULL default '0',
128
  activity int(10) unsigned NOT NULL default '0',
129
  priority tinyint(1) unsigned NOT NULL default '1',
130
  cc varchar(255) NOT NULL default '',
131
  bcc varchar(255) NOT NULL default '',
132
  body varchar(255) NOT NULL,
133
  comment varchar(255),
134
  header varchar(255),
135
  PRIMARY KEY  (lfdnr),
136
  KEY k1 (timestamp),
137
  KEY k2 (type),
138
  KEY k3 (parent),
139
  KEY k4 (assignment),
140
  KEY ticket (ticket)
141
) ;
142

143
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
144

145
alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
146
update t1 set status=1 where type='Open';
147
select status from t1;
148
drop table t1;
149

150
#
151
# Test of ORDER BY
152
#
153

154
create table t1 (a int not null, b int not null, key (a));
155
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
156
SET @tmp=0;
157
update t1 set b=(@tmp:=@tmp+1) order by a;
158
update /*+index(t1 a)*/ t1 set b=99 where a=1 order by b asc limit 1;
159
select * from t1 order by a,b;
160
update t1 set b=100 where a=1 order by b desc limit 2;
161
update t1 set a=a+10+b where a=1 order by b;
162
select * from t1 order by a,b;
163
create table t2 (a int not null, b int not null);
164
insert into t2 values (1,1),(1,2),(1,3);
165
update t1 set b=(select distinct 1 from (select * from t2) a);
166
drop table t1,t2;
167

168
#
169
# Test with limit (Bug #393)
170
#
171

172
CREATE TABLE t1 (
173
   `id_param` smallint(3) unsigned NOT NULL default '0',
174
   `nom_option` char(40) NOT NULL default '',
175
   `valid` tinyint(1) NOT NULL default '0',
176
   KEY `id_param` (`id_param`,`nom_option`)
177
 );
178

179
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
180

181
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
182
select * from t1;
183
drop table t1;
184

185
#
186
# Multi table update test from bugs
187
#
188

189
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
190

191
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
192
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
193
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
194
('2','2','0',1,7);
195
delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
196
select * from t1;
197
drop table t1;
198

199

200
# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys 
201

202

203
CREATE TABLE t1 ( 
204
   `colA` int(10) unsigned NOT NULL auto_increment,
205
   `colB` int(11) NOT NULL default '0',
206
   PRIMARY KEY  (`colA`)
207
);
208
INSERT INTO t1 VALUES (4433,5424);
209
CREATE TABLE t2 (
210
  `colC` int(10) unsigned NOT NULL default '0',
211
  `colA` int(10) unsigned NOT NULL default '0',
212
  `colD` int(10) unsigned NOT NULL default '0',
213
  `colE` int(10) unsigned NOT NULL default '0',
214
  `colF` int(10) unsigned NOT NULL default '0',
215
  PRIMARY KEY  (`colC`,`colA`,`colD`,`colE`)
216
);
217
INSERT INTO t2 VALUES (3,4433,10005,495,500);
218
INSERT INTO t2 VALUES (3,4433,10005,496,500);
219
INSERT INTO t2 VALUES (3,4433,10009,494,500);
220
INSERT INTO t2 VALUES (3,4433,10011,494,500);
221
INSERT INTO t2 VALUES (3,4433,10005,497,500);
222
INSERT INTO t2 VALUES (3,4433,10013,489,500);
223
INSERT INTO t2 VALUES (3,4433,10005,494,500);
224
INSERT INTO t2 VALUES (3,4433,10005,493,500);
225
INSERT INTO t2 VALUES (3,4433,10005,492,500);
226
#UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
227
--error 1062
228
UPDATE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
229

230
SELECT * FROM t2;
231
DROP TABLE t1;
232
DROP TABLE t2;
233

234
#
235
# Bug #6054 
236
#
237
create table t1 (c1 int, c2 char(6), c3 int);
238
create table t2 (c1 int, c2 char(6));
239
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
240
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
241
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
242
drop table t1, t2;
243

244
#
245
# Bug #8057
246
#
247
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
248
insert into t1 (id_str) values ("test");
249
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
250
select * from t1;
251
drop table t1;
252

253
#
254
# Bug #8942: a problem with update and partial key part
255
#
256

257
create table t1 (a int, b char(255), key(a, b(20)));
258
insert into t1 values (0, '1');
259
update /*+index(t1 a)*/ t1 set b = b + 1 where a = 0;
260
select * from t1;
261
drop table t1;
262

263
# BUG#9103 "Erroneous data truncation warnings on multi-table updates"
264
create table t1 (a int, b varchar(10), key b(b(5)));
265
create table t2 (a int, b varchar(10));
266

267
insert into t1 values ( 1, 'abcd1e');
268
insert into t1 values ( 2, 'abcd2e');
269
insert into t2 values ( 1, 'abcd1e');
270
insert into t2 values ( 2, 'abcd2e');
271
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
272
show warnings;
273
select * from t1;
274
select * from t2;
275
drop table t1, t2;
276

277
#
278
# Bug #11868 Update with subquery with ref built with a key from the updated
279
#            table crashes server
280
#
281
create table t1(f1 int, f2 int);
282
create table t2(f3 int, f4 int);
283
create index idx on t2(f3);
284
insert into t1 values(1,0),(2,0);
285
insert into t2 values(1,1),(2,2);
286
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
287
select * from t1;
288
drop table t1,t2;
289

290
#
291
# Bug #13180 sometimes server accepts sum func in update/delete where condition
292
#
293
create table t1(f1 int);
294
select DATABASE();
295
--error 1111
296
update t1 set f1=1 where count(*)=1;
297
select DATABASE();
298
--error 1111
299
delete from t1 where count(*)=1;
300
drop table t1;
301

302
# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
303
create table t1 ( a int, b int default 0, index (a) );
304
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
305

306
#flush status;
307
select a from t1 order by a limit 1;
308
#show status like 'handler_read%';
309

310
#flush status;
311
update t1 set a=9999 order by a limit 1;
312
update t1 set b=9999 order by a limit 1;
313
#show status like 'handler_read%';
314

315
#flush status;
316
delete from t1 order by a limit 1;
317
#show status like 'handler_read%';
318

319
#flush status;
320
delete from t1 order by a desc limit 1;
321
#show status like 'handler_read%';
322

323
#alter table t1 disable keys;
324

325
#flush status;
326
delete from t1 order by a limit 1;
327
#show status like 'handler_read%';
328

329
select * from t1;
330
update t1 set a=a+10,b=1 order by a limit 3;
331
update t1 set a=a+11,b=2 order by a limit 3;
332
update t1 set a=a+12,b=3 order by a limit 3;
333
select * from t1 order by a;
334

335
drop table t1;
336

337
#
338
# Bug#14186 select datefield is null not updated
339
#
340
create table t1 (f1 date not null);
341
insert into t1 values('2000-01-01'),('0000-00-00');
342
update t1 set f1='2002-02-02' where f1 is null;
343
select * from t1;
344
drop table t1;
345

346
#
347
# Bug#15028 Multitable update returns different numbers of matched rows
348
#           depending on table order
349
create table t1 (f1 int);
350
create table t2 (f2 int);
351
insert into t1 values(1),(2);
352
insert into t2 values(1),(1);
353
#--enable_info
354
#update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
355
#--disable_info
356
update t2 set f2=1;
357
update t1 set f1=1 where f1=3;
358
#--enable_info
359
#update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
360
#--disable_info
361
drop table t1,t2;
362

363

364
# BUG#15935
365
#create table t1 (a int);
366
#insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
367
#create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
368
#insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
369
#flush status;
370
#update t2 set a=3 where a=2;
371
#show status like 'handler_read%';
372
#drop table t1, t2;
373

374
#
375
# Bug #16510 Updating field named like '*name' caused server crash
376
#
377
create table t1(f1 int, `*f2` int);
378
insert into t1 values (1,1);
379
update t1 set `*f2`=1;
380
drop table t1;
381

382
#
383
# Bug#25126: Wrongly resolved field leads to a crash
384
#
385
create table t1(f1 int);
386
--error 1054
387
update t1 set f2=1 order by f2;
388
drop table t1;
389
# End of 4.1 tests
390

391
#
392
# Bug #24035: performance degradation with condition int_field=big_decimal
393
#
394

395
CREATE TABLE t1 (
396
  request_id int unsigned NOT NULL auto_increment,
397
  user_id varchar(12) default NULL,
398
  time_stamp datetime NOT NULL default '0000-00-00 00:00:00',
399
  ip_address varchar(15) default NULL,
400
  PRIMARY KEY (request_id),
401
  KEY user_id_2 (user_id,time_stamp)
402
);
403

404
INSERT INTO t1 (user_id) VALUES ('user1');
405
INSERT INTO t1 (user_id) VALUES ('user1');
406
INSERT INTO t1 (user_id) VALUES ('user1');
407
INSERT INTO t1 (user_id) VALUES ('user1');
408
INSERT INTO t1 (user_id) VALUES ('user1');
409
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
410
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
411
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
412
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
413
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
414
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
415
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
416
#INSERT INTO t1(user_id) SELECT user_id FROM t1;
417
#
418
#flush status;
419
SELECT user_id FROM t1 WHERE request_id=9999999999999; 
420
#show status like '%Handler_read%';
421
SELECT user_id FROM t1 WHERE request_id=999999999999999999999999999999; 
422
#show status like '%Handler_read%';
423
UPDATE t1 SET user_id=null WHERE request_id=9999999999999;
424
#show status like '%Handler_read%';
425
UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999;
426
#show status like '%Handler_read%';
427

428
DROP TABLE t1;
429

430
#
431
# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it 
432
# doesn't select
433
#
434
CREATE TABLE t1 (
435

436
  a INT(11),
437
  quux decimal( 31, 30 ),
438

439
  UNIQUE KEY bar (a),
440
  KEY quux (quux)
441
);
442

443
INSERT INTO
444
 t1 ( a, quux )
445
VALUES
446
    ( 1,    1 ),
447
    ( 2,  0.1 );
448

449
#INSERT INTO t1( a )
450
#  SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
451

452
SELECT * FROM t1;
453

454
DROP TABLE t1;
455

456
#
457
# Bug #22364: Inconsistent "matched rows" when executing UPDATE
458
#
459

460
#connect (con1,localhost,root,,test);
461
#connection con1;
462
#
463
#set tmp_table_size=1024;
464
#
465
## Create the test tables
466
#create table t1 (id int, a int, key idx(a));
467
#create table t2 (id int unsigned not null auto_increment primary key, a int);
468
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
469
##insert into t2(a) select a from t2; 
470
##insert into t2(a) select a from t2;
471
##insert into t2(a) select a from t2; 
472
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
473
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
474
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
475
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
476
#insert into t2(a) values(1),(2),(3),(4),(5),(6),(7),(8);
477
#update t2 set a=id;
478
#insert into t1 select * from t2;
479
#
480
## Check that the number of matched rows is correct when the temporary
481
## table is small enough to not be converted to MyISAM
482
#select count(*) from t1 join t2 on (t1.a=t2.a);
483
##--enable_info
484
##update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
485
##--disable_info
486
#
487
## Increase table sizes
488
##insert into t2(a) select a from t2; 
489
#update t2 set a=id; 
490
#truncate t1; 
491
##insert into t1 select * from t2; 
492
#
493
## Check that the number of matched rows is correct when the temporary
494
## table has to be converted to MyISAM
495
#select count(*) from t1 join t2 on (t1.a=t2.a);
496
#--enable_info
497
#update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
498
#--disable_info
499
#
500
## Check that the number of matched rows is correct when there are duplicate
501
## key errors
502
#update t1 set a=1;
503
#update t2 set a=1;
504
#select count(*) from t1 join t2 on (t1.a=t2.a);
505
#--enable_info
506
#update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
507
#--disable_info
508
#
509
#drop table t1,t2;
510
#
511
#connection default;
512
#disconnect con1;
513

514
#
515
# Bug #40745: Error during WHERE clause calculation in UPDATE
516
#             leads to an assertion failure
517
#
518
#--disable_warnings
519
#DROP TABLE IF EXISTS t1;
520
#DROP FUNCTION IF EXISTS f1;
521
#--enable_warnings
522
#
523
#CREATE FUNCTION f1() RETURNS INT RETURN f1();
524
#CREATE TABLE t1 (i INT);
525
#INSERT INTO t1 VALUES (1);
526
#
527
#--error ER_SP_NO_RECURSION
528
#UPDATE t1 SET i = 3 WHERE f1();
529
#--error ER_SP_NO_RECURSION
530
#UPDATE t1 SET i = f1();
531
#
532
#DROP TABLE t1;
533
#DROP FUNCTION f1;
534
#
535
#--echo End of 5.0 tests
536
#
537
#--echo #
538
#--echo # Bug #47919 assert in open_table during ALTER temporary table
539
#--echo #
540
#
541
CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
542
#CREATE TEMPORARY TABLE t2 LIKE t1;
543
CREATE TABLE t2(f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
544
INSERT INTO t1 VALUES (1);
545
INSERT INTO t2 VALUES (1);
546

547
ALTER TABLE t2 COMMENT = 'ABC';
548
#UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9;
549
ALTER TABLE t2 COMMENT = 'DEF';
550

551
DROP TABLE t1, t2;
552

553
--echo #
554
--echo # Bug#50545: Single table UPDATE IGNORE crashes on join view in
555
--echo # sql_safe_updates mode.
556
--echo #
557
CREATE TABLE t1 ( a INT, KEY( a ) );
558
INSERT INTO t1 VALUES (0), (1);
559
CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
560
#SET SESSION sql_safe_updates = 1;
561

562
#--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
563
#UPDATE IGNORE v1 SET a = 1;
564

565
#SET SESSION sql_safe_updates = DEFAULT;
566
DROP TABLE t1;
567
DROP VIEW v1;
568

569
--echo #
570
--echo # Bug#54734 assert in Diagnostics_area::set_ok_status
571
--echo #
572

573
#--disable_warnings
574
#DROP TABLE IF EXISTS t1, not_exists;
575
#DROP FUNCTION IF EXISTS f1;
576
#DROP VIEW IF EXISTS v1;
577
#--enable_warnings
578
#
579
#CREATE TABLE t1 (PRIMARY KEY(pk)) AS SELECT 1 AS pk;
580
#CREATE FUNCTION f1() RETURNS INTEGER RETURN (SELECT 1 FROM not_exists);
581
#CREATE VIEW v1 AS SELECT pk FROM t1 WHERE f1() = 13;
582
#--error ER_VIEW_INVALID
583
#UPDATE v1 SET pk = 7 WHERE pk > 0;
584
#
585
#DROP VIEW v1;
586
#DROP FUNCTION f1;
587
#DROP TABLE t1;
588

589
--echo #
590
--echo # Verify that UPDATE does the same number of handler_update
591
--echo # operations, no matter if there is ORDER BY or not.
592
--echo #
593

594
CREATE TABLE t1 (i INT);
595
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
596
                      (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
597
                      (30),(31),(32),(33),(34),(35);
598
#CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2),
599
#                 INDEX idx (a,b(1),c));
600
CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2),
601
                 INDEX idx (a,b,c));
602

603
INSERT INTO t2 VALUES (10,10,10,10),(11,11,11,11),(12,12,12,12),(13,13,13,13),(14,14,14,14),(15,15,15,15),(16,16,16,16),(17,17,17,17),(18,18,18,18),(19,19,19,19);
604

605
#INSERT INTO t2 SELECT i, i, i, i FROM t1;
606
#FLUSH STATUS; # FLUSH is autocommit, so we put it outside of transaction
607
START TRANSACTION;
608
UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5;
609
#SHOW STATUS LIKE 'HANDLER_UPDATE';
610
ROLLBACK;
611
#FLUSH STATUS;
612
START TRANSACTION;
613
UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
614
#SHOW STATUS LIKE 'HANDLER_UPDATE';
615
ROLLBACK;
616

617
--echo Same test with a different UPDATE.
618

619
ALTER TABLE t2 DROP INDEX idx;
620
alter table t2 ADD INDEX idx2 (a, b);
621
#FLUSH STATUS;
622
START TRANSACTION;
623
UPDATE t2 SET c = 10 LIMIT 5;
624
#SHOW STATUS LIKE 'HANDLER_UPDATE';
625
ROLLBACK;
626
#FLUSH STATUS;
627
START TRANSACTION;
628
UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5;
629
#SHOW STATUS LIKE 'HANDLER_UPDATE';
630
ROLLBACK;
631
DROP TABLE t1, t2;
632

633
--disable_warnings
634
drop table if exists table10_bigint;
635
--enable_warnings
636

637
CREATE TABLE table10_bigint (col_char_34_key char(34),col_char_24 char(24),col_char_48_unique_index char(48),col_char_26_unique_index char(26),col_char_36 char(36),col_char_26 char(26),col_char_20 char(20),col_char_37 char(37),col_char_29_key char(29),pk bigint,col_char_20_unique_index char(20),col_char_39 char(39),col_char_23 char(23),col_char_28_key char(28),col_char_45 char(45),col_char_40_unique_index char(40),col_char_43_unique_index char(43),col_char_21_key char(21),col_char_29 char(29),col_char_25_key char(25),col_timestamp_6 timestamp(6) NULL DEFAULT NULL,col_char_32_key char(32),col_char_22 char(22),col_char_45_key char(45),col_char_20_key char(20),col_char_35_key char(35),col_char_22_unique_index char(22),col_char_46_unique_index char(46),col_char_28_unique_index char(28),col_char_25 char(25),col_char_42_unique_index char(42),col_char_30_key char(30),col_char_33_unique_index char(33),col_char_42 char(42),col_char_23_key char(23),col_char_36_key char(36),col_timestamp_6_unique_index timestamp(6) NULL DEFAULT NULL,col_char_37_unique_index char(37),col_char_46_key char(46),col_char_32_unique_index char(32),col_char_34_unique_index char(34),col_char_28 char(28),col_char_25_unique_index char(25),col_char_39_unique_index char(39),col_char_30 char(30),col_char_36_unique_index char(36),col_char_47_key char(47),col_char_41_unique_index char(41),col_char_42_key char(42),col_char_48 char(48),col_char_46 char(46),col_char_48_key char(48),col_char_22_key char(22),col_bigint_unique_index bigint,col_char_38 char(38),col_char_21 char(21),col_char_43 char(43),col_char_27 char(27),col_char_40_key char(40),col_char_40 char(40),col_char_47_unique_index char(47),col_char_21_unique_index char(21),col_char_33 char(33),col_char_27_unique_index char(27),col_char_44_key char(44),col_char_35_unique_index char(35),col_char_26_key char(26),col_bigint bigint,col_char_30_unique_index char(30),col_char_45_unique_index char(45),col_char_32 char(32),col_char_41 char(41),col_char_24_unique_index char(24),col_char_31_unique_index char(31),col_char_33_key char(33),col_char_39_key char(39),col_char_35 char(35),col_timestamp_6_key timestamp(6) NULL DEFAULT NULL,col_char_44 char(44),col_char_31 char(31),col_char_43_key char(43),col_char_29_unique_index char(29),col_char_37_key char(37),col_char_23_unique_index char(23),col_char_41_key char(41),col_char_38_unique_index char(38),col_char_44_unique_index char(44),col_bigint_key bigint,col_char_24_key char(24),col_char_27_key char(27),col_char_38_key char(38),col_char_47 char(47),col_char_31_key char(31),col_char_34 char(34),key idx46(col_char_34_key ),key idx31(col_char_29_key ),primary key (pk)) ;
638
insert into table10_bigint values('a', 'b','c','d','e', 'f', 'h','i','j',1,'a', 'b','c','d','e', 'f', 'h','i','j','k','2001-03-04 10:10:10', 'a', 'b','c','d','e', 'f', 'h','i','j','k','j','i','o','p','u','2001-03-04 10:10:10','a', 'b','c','d','e', 'f',     'h','i','j','k','j','i','o','p','u','y',2,'a', 'b','c','d','e', 'f','h','i','j','k','j','i','o',3,'a', 'b','c','d','e', 'f', 'h','i','j','2001-03-04 10:10:10','a', 'b','c','d','e', 'f', 'h','i','j',4,'a', 'b','c','d','e', 'f');
639
UPDATE table10_bigint SET col_char_20 = 'a' ;
640
delete from table10_bigint;
641
alter system set ob_enable_batched_multi_statement=false;
642
DROP TABLE /*! IF EXISTS*/ table10_bigint;
643

644
--disable_warnings
645
drop table if exists t1,t2;
646
drop view if exists update_view, update_view1;
647
--enable_warnings
648
create table t1 (c1 int, c2 int, c3 int);
649
create table t2 (c1 int, c2 int, c3 int);
650
create view update_view (c1,c2) as select t1.c2 as c1, t2.c2 as c2 from t1, t2 where t1.c1 = t2.c1;
651
create view update_view1 (c1,c2) as select t1.c2 as c1, t2.c2 as c2 from t1 join t2 on t1.c1 = t2.c1;
652
--error 5868
653
update t1, t2 set t1.c2 = t2.c2 where t1.c1 = t2.c1 order by t2.c2;
654
--error 5868
655
update t1 join t2 on t1.c1 = t2.c1 set t1.c2 = t2.c2 order by t2.c2;
656
--error 5868
657
update t1 left join t2 on t1.c1 = t2.c1 set t1.c2 = t2.c2 order by t2.c2;
658
--error 5868
659
update update_view set c1 = c2 order by c2;
660
--error 5868
661
update update_view1 set c1 = c2 order by c2;
662
--error 5869
663
update t1, t2 set t1.c2 = t2.c2 where t1.c1 = t2.c1 limit 10;
664
--error 5869
665
update t1 join t2 on t1.c1 = t2.c1 set t1.c2 = t2.c2 limit 10;
666
--error 5869
667
update t1 left join t2 on t1.c1 = t2.c1 set t1.c2 = t2.c2 limit 10;
668
--error 5869
669
update update_view set c1 = c2 limit 10;
670
--error 5869
671
update update_view1 set c1 = c2 limit 10;
672
drop view update_view;
673
drop view update_view1;
674
drop table t1;
675
drop table t2;
676

677
--disable_warnings
678
drop table if exists gf_ar_mthly_bill;
679
--enable_warnings
680

681
set binlog_row_image='MINIMAL';
682
CREATE TABLE `gf_ar_mthly_bill` (
683
   `tnt_inst_id` varchar(8) COLLATE utf8mb4_bin NOT NULL COMMENT '租户机构id',
684
   `bill_no` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '账单号',
685
   `ip_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '结算对象ipid',
686
   `ip_role_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '结算对象pid',
687
   `inst_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '分支机构',
688
   `pd_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '产品码',
689
   `ar_no` varchar(34) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '合约号',
690
   `bill_tp` varchar(2) COLLATE utf8mb4_bin NOT NULL COMMENT '账单类型',
691
   `setl_tp` varchar(2) COLLATE utf8mb4_bin NOT NULL COMMENT '结算方式',
692
   `bill_st` varchar(2) COLLATE utf8mb4_bin NOT NULL COMMENT '核销状态',
693
   `bill_strt_dt` varchar(8) COLLATE utf8mb4_bin NOT NULL COMMENT '账单开始日期',
694
   `bill_end_dt` varchar(8) COLLATE utf8mb4_bin NOT NULL COMMENT '账单结束日期',
695
   `bill_mth` varchar(6) COLLATE utf8mb4_bin NOT NULL COMMENT '账单月份',
696
   `acrd_dt` varchar(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应收日期',
697
   `gmt_pay` timestamp(6) NULL DEFAULT NULL COMMENT '付款时间',
698
   `payee_ac` varchar(34) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '收款方账号',
699
   `payer_ac` varchar(34) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '付款方账号',
700
   `pay_way` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '支付方式',
701
   `pay_orig` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '支付来源',
702
   `clcn_bsc_amt` decimal(25,4) DEFAULT NULL COMMENT '计算交易量',
703
   `clcn_bsc_tp` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '计算依据',
704
   `clcn_mthd` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '计算方法',
705
   `chrg_itm_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '收费项编码',
706
   `ccy` varchar(3) COLLATE utf8mb4_bin NOT NULL COMMENT '币种',
707
   `bill_amt` bigint(20) NOT NULL COMMENT '账单金额',
708
   `adj_amt` bigint(20) NOT NULL COMMENT '调整金额',
709
   `rcvd_amt` bigint(20) NOT NULL COMMENT '已收金额',
710
   `tax_tp` varchar(16) COLLATE utf8mb4_bin NOT NULL COMMENT '税种 01:增值税 02:营业税',
711
   `tax_rate` decimal(10,8) NOT NULL COMMENT '税率',
712
   `tax_amt` bigint(20) NOT NULL COMMENT '税额',
713
   `exn_inf` varchar(4000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '扩展信息',
714
   `anl_dmsn1` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '分析维度1',
715
   `anl_dmsn2` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '分析维度2',
716
   `anl_dmsn3` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '分析维度3',
717
   `anl_dmsn4` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '分析维度4',
718
   `wrtof_dtl_cnt` bigint(20) DEFAULT NULL COMMENT '已核销明细数量',
719
   `to_wrtof_dtl_cnt` bigint(20) DEFAULT NULL COMMENT '待核销明细数量',
720
   `rcrd_id` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '数据唯一ID',
721
   `last_moder` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '最近修改人',
722
   `gmt_create` timestamp(6) NOT NULL COMMENT '创建时间',
723
   `gmt_modified` timestamp(6) NOT NULL COMMENT '修改时间',
724
   `charge_tp` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '计收费环境类型 01 主站云计收费 02 主站离线计收费 03 主站老计收费',
725
   `stl_ip_role_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '结算对象',
726
   `svc_amt` bigint(20) DEFAULT NULL COMMENT '交易金额',
727
   `inved_amt` bigint(20) DEFAULT NULL COMMENT '已开票金额',
728
   `chk_st` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '出账状态 01 初始化 02 待确认出账 03 已出账 04 客户已对账 05 对账差异',
729
   `acnt_day` varchar(14) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '出账日',
730
   `inv_amt` bigint(20) DEFAULT NULL COMMENT '可开票金额',
731
   `rel_inv_amt` bigint(20) DEFAULT NULL COMMENT '已关联发票金额',
732
   `tp` varchar(2) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应收1 返点2',
733
   `payer_ip_role_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '扣款对象pid',
734
   `sgn_ip_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '签约对象ipid',
735
   `sgn_ip_role_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '签约对象pid',
736
   `chking_amt` bigint(20) DEFAULT '0' COMMENT '出账中金额',
737
   `frz_amt` bigint(20) DEFAULT '0' COMMENT '冻结金额（用于控制流程上互斥的业务）',
738
   `chked_amt` bigint(20) DEFAULT '0' COMMENT '已出账金额',
739
   `writingoff_amt` bigint(20) DEFAULT '0' COMMENT '核销中金额',
740
   `svc_bill_amt` bigint(20) DEFAULT NULL COMMENT '账单计费金额，计收费计费时生成的中间金额',
741
   `svc_ccy` varchar(3) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账单计费币种',
742
   `env_source` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '环境标识',
743
   `metadata_source` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '元数据来源标识，包括(用户、商户、产品、合约、计费)来源',
744
   `setl_time_zone` varchar(12) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账单结算时区',
745
   `actg_time_zone` varchar(12) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账单核算时区',
746
   `inter_trade_flag` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Y: 是关联交易 N: 不是关联交易',
747
   `actg_bill_mth` varchar(6) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '核算时区账单月份',
748
   `auto_writeoff_group_no` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '自动核销关联组号',
749
   `partition_id` varchar(4) COLLATE utf8mb4_bin GENERATED ALWAYS AS (substr(`bill_no`,23,2)) VIRTUAL,
750
   PRIMARY KEY (`tnt_inst_id`, `bill_no`),
751
   UNIQUE KEY `uk_tnt_inst_id_rcrd_id` (`tnt_inst_id`, `rcrd_id`) GLOBAL  BLOCK_SIZE 16384,
752
   KEY `idx_tnt_inst_id_acnt_day_chk_st_gmt_create` (`tnt_inst_id`, `acnt_day`, `chk_st`, `gmt_create`) GLOBAL  BLOCK_SIZE 16384,
753
   KEY `idx_tnt_inst_id_acnt_day_chk_st` (`tnt_inst_id`, `acnt_day`, `chk_st`) GLOBAL  BLOCK_SIZE 16384,
754
   KEY `idx_tnt_inst_id_ip_role_id` (`tnt_inst_id`, `ip_role_id`) LOCAL  BLOCK_SIZE 16384,
755
   KEY `idx_tnt_inst_id_setl_tp_bill_st` (`tnt_inst_id`, `setl_tp`, `bill_st`) GLOBAL  BLOCK_SIZE 16384,
756
   KEY `idx_tnt_inst_id_ar_no` (`tnt_inst_id`, `ar_no`) GLOBAL  BLOCK_SIZE 16384,
757
   KEY `idx_tnt_inst_id_bill_mth` (`tnt_inst_id`, `bill_mth`) GLOBAL  BLOCK_SIZE 16384,
758
   KEY `idx_ip_role_id_bill_mth` (`tnt_inst_id`, `ip_role_id`, `bill_mth`) LOCAL  BLOCK_SIZE 16384,
759
   KEY `idx_tnt_inst_id_ip_id` (`tnt_inst_id`, `ip_id`, `bill_mth`, `setl_tp`) GLOBAL  BLOCK_SIZE 16384,
760
   KEY `idx_acrd_dt_setl_tp_bill_st` (`tnt_inst_id`, `acrd_dt`, `setl_tp`, `bill_st`) GLOBAL  BLOCK_SIZE 16384,
761
   KEY `idx_tnt_inst_id_ip_role_id_bill_mth_setl_tp` (`tnt_inst_id`, `ip_role_id`, `bill_mth`, `setl_tp`) LOCAL  BLOCK_SIZE 16384,
762
   KEY `idx_bill_mth` (`bill_mth`, `tnt_inst_id`) GLOBAL  BLOCK_SIZE 16384,
763
   KEY `idx_payer_ip_role_id_bill_mth` (`tnt_inst_id`, `payer_ip_role_id`, `bill_mth`) GLOBAL  BLOCK_SIZE 16384,
764
   KEY `idx_ip_id_bill_mth` (`tnt_inst_id`, `ip_id`, `bill_mth`) GLOBAL  BLOCK_SIZE 16384,
765
   KEY `idx_policy_id_bill_mth_inst_id_type` (`tnt_inst_id`, `inst_id`, `bill_mth`, `tp`, `anl_dmsn2`) GLOBAL,
766
   KEY `idx_tnt_inst_id_inst_id_tp_anl_dmsn_1_bill_mth` (`tnt_inst_id`, `inst_id`, `tp`, `anl_dmsn1`, `bill_mth`) GLOBAL,
767
   KEY `idx_tnt_inst_id_inst_id_tp_anl_dmsn_3_bill_mth` (`tnt_inst_id`, `inst_id`, `tp`, `anl_dmsn3`, `bill_mth`) GLOBAL,
768
   KEY `idx_tnt_inst_id_inst_id_tp_anl_dmsn_4_bill_mth` (`tnt_inst_id`, `inst_id`, `tp`, `anl_dmsn4`, `bill_mth`) GLOBAL,
769
   KEY `idx_tnt_inst_id_inst_id_tp_anl_dmsn_2_bill_mth` (`tnt_inst_id`, `inst_id`, `tp`, `anl_dmsn2`, `bill_mth`) GLOBAL
770
 ) partition by list columns(partition_id)
771
 (partition p0 values in ('00'),
772
 partition p80 values in ('80'),
773
 partition p81 values in ('81'),
774
 partition p82 values in ('82'),
775
 partition p95 values in ('95'),
776
 partition p96 values in ('96'),
777
 partition p97 values in ('97'),
778
 partition p98 values in ('98'),
779
 partition p99 values in ('99'));
780

781
insert into gf_ar_mthly_bill(tnt_inst_id, bill_no, ip_id, ip_role_id, inst_id,
782
bill_tp, setl_tp, bill_st, bill_strt_dt, bill_end_dt, bill_mth, ccy, bill_amt,
783
adj_amt, rcvd_amt, tax_tp, tax_rate, tax_amt, rcrd_id, last_moder, gmt_create, gmt_modified)
784
values('ylctest', '2018090310122000040400820033834415',
785
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, '2021-05-17 12:09:34', '2021-05-17 12:09:34');
786
begin;
787
update gf_ar_mthly_bill
788
    set bill_st='01',
789
    gmt_pay='2021-05-17 12:09:35',
790
    clcn_bsc_amt='123.12',
791
    bill_amt=1,
792
    rcvd_amt=2,
793
    tax_amt=3,
794
    exn_inf='123456',
795
    anl_dmsn1='123456',
796
    anl_dmsn2='234567',
797
    anl_dmsn3='345678',
798
    anl_dmsn4='456789',
799
    last_moder='ylc',
800
    adj_amt=4,
801
    svc_amt = 5,
802
    chk_st = '06',
803
    inv_amt = 6,
804
    writingoff_amt = 7,
805
    wrtof_dtl_cnt = 9 ,
806
    to_wrtof_dtl_cnt = 8,
807
    chking_amt=10,
808
    chked_amt=11,
809
    frz_amt=12,
810
    svc_bill_amt=13,
811
    gmt_modified='2021-05-17 12:09:34'
812
 where
813
    tnt_inst_id='ylctest' and bill_no='2018090310122000040400820033834415';
814

815
select * from gf_ar_mthly_bill;
816
rollback;
817
connect (obsys,$OBMYSQL_MS0,admin,$OBMYSQL_PWD,test,$OBMYSQL_PORT);
818
connection obsys;
819
--sleep 3
820
connection default;
821
set binlog_row_image='MINIMAL';
822
update gf_ar_mthly_bill
823
    set bill_st='01',
824
    gmt_pay='2021-05-17 12:09:35',
825
    clcn_bsc_amt='123.12',
826
    bill_amt=1,
827
    rcvd_amt=2,
828
    tax_amt=3,
829
    exn_inf='123456',
830
    anl_dmsn1='123456',
831
    anl_dmsn2='234567',
832
    anl_dmsn3='345678',
833
    anl_dmsn4='456789',
834
    last_moder='ylc',
835
    adj_amt=4,
836
    svc_amt = 5,
837
    chk_st = '06',
838
    inv_amt = 6,
839
    writingoff_amt = 7,
840
    wrtof_dtl_cnt = 9 , 
841
    to_wrtof_dtl_cnt = 8,
842
    chking_amt=10,
843
    chked_amt=11,
844
    frz_amt=12,
845
    svc_bill_amt=13,
846
    gmt_modified='2021-05-17 12:09:34'
847
 where
848
    tnt_inst_id='ylctest' and bill_no='2018090310122000040400820033834415';
849

850
select * from gf_ar_mthly_bill;
851
drop table gf_ar_mthly_bill;
852
set binlog_row_image='FULL';
853
connection obsys;
854
connection default;
855

856
#bug fix:
857
drop table if exists TBFUNDSALESTAT;
858
CREATE TABLE TBFUNDSALESTAT (
859
  PRD_CODE VARCHAR(32) DEFAULT ' ',
860
  SELLER_CODE VARCHAR(9) DEFAULT ' ',
861
  HOLD_AMT NUMBER(18,2) DEFAULT 0 
862
);
863

864
connection obsys;
865
connection default;
866

867
UPDATE tbfundsalestat a
868
SET    a.hold_amt = (SELECT /*+merge*/Sum(c.hold_amt)
869
                      FROM tbfundsalestat c
870
                      WHERE c.prd_code = a.prd_code)
871
WHERE  a.seller_code = (SELECT /*+unnest*/Max(b.seller_code)
872
                            FROM   tbfundsalestat b
873
                            WHERE  a.prd_code = b.prd_code);
874

875
connection obsys;
876
connection default;
877

878
UPDATE tbfundsalestat a
879
SET    a.hold_amt = (SELECT /*+merge*/Sum(c.hold_amt)
880
                      FROM tbfundsalestat c
881
                      WHERE c.prd_code = a.prd_code)
882
WHERE  a.seller_code = (SELECT /*+unnest*/Max(b.seller_code)
883
                            FROM   tbfundsalestat b
884
                            WHERE  a.prd_code = b.prd_code);
885

886
drop table if exists TBFUNDSALESTAT;
887
--source mysql_test/include/recover_spm_var.inc
888