Articles

How to put 29 characters into VARCHAR(10)

Firebird Friday Joke #6 (from https://t.me/firebirdsql)
Old, but still a good joke: with multi-byte character sets, it is possible to insert more symbols than declared. Learn Unicode! :)
Enjoy!

C:\HQbird\Firebird25\bin>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> create database "c:\databases\test1.fdb" user "SYSDBA" password "masterkey";
SQL> create table t2(v1 varchar(10) character set UNICODE_FSS);
SQL> insert into t2(v1) values('12345678901');
SQL> insert into t2(v1) values('123456789012');
SQL> insert into t2(v1) values('1234567890123');
SQL> insert into t2(v1) values('12345678901234');
SQL> insert into t2(v1) values('123456789012345');
SQL> insert into t2(v1) values('1234567890123456');
SQL> insert into t2(v1) values('12345678901234567');
SQL> insert into t2(v1) values('1234567890123456789101234');
SQL> insert into t2(v1) values('12345678901234567891012345');
SQL> insert into t2(v1) values('123456789012345678910123456');
SQL> insert into t2(v1) values('1234567890123456789101234567');
SQL> insert into t2(v1) values('12345678901234567891012345678');
SQL> insert into t2(v1) values('123456789012345678910123456789');  <--- 29 characters!
SQL> insert into t2(v1) values('1234567890123456789101234567890');    
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL> create table t3(v1 varchar(10) character set UTF8);
SQL> insert into t2(v1) values('1234567890');
SQL> insert into t2(v1) values('12345678901');
SQL> insert into t2(v1) values('123456789012');
SQL> insert into t2(v1) values('123456789012345678');
SQL> insert into t2(v1) values('1234567890123456789');
SQL> insert into t2(v1) values('12345678901234567890');
SQL> insert into t2(v1) values('123456789012345678901');
SQL> insert into t2(v1) values('12345678901234567890123');
SQL> insert into t2(v1) values('1234567890123456789012345678');
SQL> insert into t2(v1) values('123456789012345678901234567890');
SQL> insert into t2(v1) values('1234567890123456789012345678901');
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
SQL>
It works in 2.5, in 3.0 it behaves differently with utf8
SQL> create table t2(v1 varchar(10) character set UNICODE_FSS);
SQL> insert into t2(v1) values('12345678901');
SQL> insert into t2(v1) values('12345678901234567890');
SQL> insert into t2(v1) values('12345678901234567890123456789');
SQL> insert into t2(v1) values('123456789012345678901234567890');
SQL> insert into t2(v1) values('1234567890123456789012345678901');
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 10, actual 30
SQL> create table t3(v1 varchar(10) character set utf8);
SQL> insert into t3(v1) values('1234567890');
SQL> insert into t3(v1) values('12345678901');
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 10, actual 11
SQL>