在修复相关缺陷时发现不同的数据库有不同的语义,因此整理不同数据库的关系如下。
MySql
create table
create table table_char_test
(
charColumn char(3) null,
varCharColumn varchar(3) null
);
insert into calcite.table_char_test values ('a','a');
insert into calcite.table_char_test values ('ab','ab');
insert into calcite.table_char_test values ('ab ','ab ');
insert into calcite.table_char_test values ('abc','abc');
# Data truncation: Data too long for column 'varCharColumn' at row 1
insert into calcite.table_char_test values ('abc','abcd');
# Data truncation: Data too long for column 'charColumn' at row 1
insert into calcite.table_char_test values ('abcd','abc');
#UNSET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select * from calcite.table_char_test where charColumn in ('ab '); # return null
#SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';(PADDING CHAR TO FULL LENGHT)
select * from calcite.table_char_test where charColumn in ('ab'); # return null # in list 不做转化 用户保证
select * from calcite.table_char_test where charColumn in ('ab '); # retur 1 rows
select * from calcite.table_char_test where charColumn in ('ab '); # return null
# varChar == string
select * from calcite.table_char_test where varCharColumn in ('ab ','abc');# return 2 rows
结论
在MySql中,默认没有设置PAD_CHAR_TO_FULL_LENGTH属性,则Char只在存储时占指定字符空间。其在查询时候都按照插入时候truncate掉尾部多余的值进行比较,in列表中的字符串长度不变,转为bpchar[],如果是单个值则为bp。
如果设置PAD_CHAR_TO_FULL_LENGTH,则将列值添加空字符串直到规定长度,然后直接和列值做比较。
PostGreSQL
create table
create table table_char_test
(
charColumn character (3) null,
varCharColumn character varying(3) null
);
insert into calcite.table_char_test values ('a','a');
insert into calcite.table_char_test values ('ab','ab');
insert into calcite.table_char_test values ('ab ','ab ');
insert into calcite.table_char_test values ('abc','abc');
# ERROR: value too long for type character varying(3)
insert into calcite.table_char_test values ('abc','abcd');
# ERROR: value too long for type character(3)
insert into calcite.table_char_test values ('abcd','abc');
select * from table_char_test where charColumn in ('ab'); # return 2 rows 填充ab的长度
select * from calcite.table_char_test where charColumn in ('ab '); # retur 1 rows
select * from calcite.table_char_test where charColumn in ('ab '); # return 1 rows
select * from table_char_test where charColumn in ('ab ','abc ') # return 3 rows
select * from table_char_test where charColumn in ('ab b','abc ') # return 1 row 是去扩充charColumn的长度,而不是去cast list
# character varying == string
select * from calcite.table_char_test where varCharColumn in ('ab ','abc'); # return 2 rows
结论:
charColumn 与 list中谁少谁进行填充,都填充到最大的那个值。