merge into的字段限制

有网友反馈说，merge into报229/102错误，并提供相应的表结构。测试并复现了该问题，并最终确认如下结论：

merge into使用的表的特殊字段不能超过112个。

测试过程如下：
创建表mergetab

1
2
3
4
5
6
7
8
9
10
create table mergetab
(
 col1 varchar(10),
 col2 varchar(10),
 col3 varchar(10),
 col4 varchar(10),
 -- 中间还有 col5 至 col111字段，均为varchar(10)
 col112 varchar(10),
 col113 varchar(10)
);

执行merge into 操作，由于mergetab有113个特殊字段，执行报错

1
2
3
4
5
6
7
8
9
10
11
> MERGE INTO mergetab t1
using mergetab t2
 on t1.col1=t2.col1
WHEN MATCHED THEN update SET
t1.col2 = t2.col2;
 
  229: Could not open or create a temporary file.
 
  102: ISAM error:  illegal argument to ISAM function.
Error in line 5
Near character position 15

oncheck -pt testdb:mergetab的输出

1
2
3
4
5
6
7
8
9
10
11
12
13
TBLspace Report for testdb:gbasedbt.mergetab
 
    Physical Address               6:576
    Creation date                  04/17/2019 15:23:28
    TBLspace Flags                 902        Row Locking
                                              TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
    Maximum row size               1243
    Number of special columns      113        # 特殊字段数为113
    Number of keys                 0
    Number of extents              0
    Current serial value           1
    Current SERIAL8 value          1

当特殊字段数减少到112时，即将其中一个varchar改成char，或者去掉一个字段后，执行是成功的。

1
2
3
4
5
6
7
8
9
10
create table mergetab
(
 col1 varchar(10),
 col2 varchar(10),
 col3 varchar(10),
 col4 varchar(10),
 -- 中间还有 col5 至 col111字段，均为varchar(10)
 col112 varchar(10),
 col113 char(10)
);

oncheck -pt 的输出

1
2
3
4
5
6
7
8
9
10
Physical Address               6:576
Creation date                  04/17/2019 16:01:42
TBLspace Flags                 902        Row Locking
                                          TBLspace contains VARCHARS
                                          TBLspace use 4 bit bit-maps
Maximum row size               1242
Number of special columns      112        # 特殊字段数为112
Number of keys                 0
Number of extents              0
Current serial value           1

此时再执行merge into操作。

1
2
3
4
5
6
7
> MERGE INTO mergetab t1
using mergetab t2
 on t1.col1=t2.col1
WHEN MATCHED THEN update SET
t1.col2 = t2.col2;
 
0 row(s) merged.

标签: gbase8s, merge into, merge, 特殊字段

相关文章推荐

添加新评论