GreatSQL社区»论坛问答 › GreatSQL › GreatSQL专区

[已解决] 隐式转换导致结果输出异常

721 1 2023-12-13 17:26

本帖最后由 ShawnYan 于 2023-12-13 17:54 编辑

(Wed Dec 13 17:24:51 2023)[root@GreatSQL][s1]>select version();
+-----------+
| version() |
+-----------+
| 8.0.32-24 |
+-----------+
1 row in set (0.00 sec)

复制代码

test case:

create table t1 (
id int not null,
name varchar(255) default null,
primary key(id)
);
insert into t1 values (1,'123456789012345611');
insert into t1 values (2,'123456789012345612');
insert into t1 values (3,'123123');
create table t2 (
id int not null,
name bigint default null,
primary key(id)
);
insert into t2 values (1,'123456789012345611');
insert into t2 values (2,'123456789012345611');
insert into t2 values (3,'123123');

复制代码

results:

(Wed Dec 13 17:24:04 2023)[root@GreatSQL][s1]>select t1.id id1, t1.name name1, t2.id id2, t2.name name2
-> from t1,t2
-> where t1.name = t2.name;
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 2 | 123456789012345612 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 2 | 123456789012345612 | 2 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
5 rows in set (0.01 sec)
(Wed Dec 13 17:53:57 2023)[root@GreatSQL][s1]>select t1.id id1, t1.name name1, t2.id id2, t2.name name2 from t1,t2 where cast(t1.name as decimal(20)) = t2.name;
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.00 sec)
(Wed Dec 13 17:24:42 2023)[root@GreatSQL][s1]>select t1.id id1, t1.name name1, t2.id id2, t2.name name2
-> from t1,t2
-> where t1.name = cast(t2.name as binary);
+-----+--------------------+-----+--------------------+
| id1 | name1 | id2 | name2 |
+-----+--------------------+-----+--------------------+
| 1 | 123456789012345611 | 1 | 123456789012345611 |
| 1 | 123456789012345611 | 2 | 123456789012345611 |
| 3 | 123123 | 3 | 123123 |
+-----+--------------------+-----+--------------------+
3 rows in set (0.00 sec)