Commented by: @pavel-zotov

> It's valid to specify a datatype for a computed field

If one may to specify built-in data type then why it's not so for domains ?

SQL> create domain dm_int int;
SQL> create table t1(n0 int, n1 int computed by(123));
SQL> create table t2(n0 int, n1 dm_int computed by(123));
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 35
-computed

(it's not feature request, I'm asking just out of curiosity)

Commented by: @pavel-zotov

One more question.
Consider script (it's also in attached .zip; encoding = UTF8):

shell del C:\MIX\firebird\QA\fbt-repo\tmp\c5092.fdb 2>nul;
set names utf8;
create database '/:C:\MIX\firebird\QA\fbt-repo\tmp\c5092.fdb' default character set utf8;
show version;

recreate table t1 (
n0 int

 ,si smallint computed by\(32767\)
,bi bigint computed by \(2147483647\)
,s2 smallint computed by \( mod\(bi, nullif\(si,0\)\) \)

,dx double precision computed by \(pi\(\)\)
,fx float computed by \(dx\*dx\)
,nf numeric\(3,1\) computed by \(fx\)

,dt date computed by \('now'\)
,tm time computed by \('now'\)
\-\- eval not supported: ,dx  computed by \( dateadd\(1 day to dt\) \)

,c1 char character set win1251 computed by \('ы'\)
,c2 char character set win1252 computed by \('å'\)
,cu char character set utf8 computed by \('∑'\)

,c1x char computed by\(c1\)
,c2x char computed by\(c2\)
,cux char computed by\(cu\)

,b1 blob character set win1251 computed by \('ы'\)
,b2 blob character set win1252 computed by \('ä'\)
,bu blob character set utf8 computed by \('∑'\)
,bb blob computed by \('∞'\)

,b1x blob computed by \(b1\)
,b2x blob computed by \(b2\)
,bux blob computed by \(bu\)
,bbx blob computed by \(bb\)

);

insert into t1 values(null);
commit;

set sqlda_display on;
set blob all;
set list on;
select * from t1;
commit;
set echo on;
show table t1;

Note at these fields:
,c1x char computed by(c1)
,c2x char computed by(c2)
,cux char computed by(cu)
. . .
,b1x blob computed by (b1)
,b2x blob computed by (b2)
,bux blob computed by (bu)
,bbx blob computed by (bb)

-- they all are computed-by and are based, in turn, also on computed-by fields, like this:
c1x char computed by(c1) ==> c1 char character set win1251 computed by ('ы'), etc.

Running this script issues lot of rows but I'm interesting now about columns that are mentioned above.

First, note on part of sqlda_display for fields C1X, C2X and CUX:

===
13: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 4 charset: 4 UTF8
: name: C1X alias: C1X
14: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 4 charset: 4 UTF8
: name: C2X alias: C2X
: table: T1 owner: SYSDBA
15: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 4 charset: 4 UTF8
: name: CUX alias: CUX
: table: T1 owner: SYSDBA

Why they all are of charset = UTF8 ? Field 'c1x' is based on 'c1' but its charset = WIN1251.
Simiarly for field 'c2x' ==> 'c2' and its charset = WIN1252.

Second, look at SQLDA output for BLOB fields B1X, B2X and BUX (they are based on computed-by fields B1, B2 & BU):

20: sqltype: 520 BLOB Nullable scale: 0 subtype: 0 len: 8
: name: B1X alias: B1X
21: sqltype: 520 BLOB Nullable scale: 0 subtype: 0 len: 8
: name: B2X alias: B2X
22: sqltype: 520 BLOB Nullable scale: 0 subtype: 0 len: 8
: name: BUX alias: BUX

Why all of them have subtype = 0 ?

Fields B1, B2 & BU are 'base' for B1X, B2X and BUX, and they have proper subtype and charset in SQLDA:

16: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 4 UTF8
: name: B1 alias: B1
17: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 4 UTF8
: name: B2 alias: B2
18: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 4 UTF8
: name: BU alias: BU

- so, why this info does no 'transported' to B1X, B2X and BUX ?

Third, let's look at the output of SHOW TABLE:

show table t1;
N0 INTEGER Nullable
SI Computed by: (32767)
BI Computed by: (2147483647)
S2 Computed by: ( mod(bi, nullif(si,0)) )
DX Computed by: (pi())
FX Computed by: (dx*dx)
NF Computed by: (fx)
DT Computed by: ('now')
TM Computed by: ('now')
C1 Computed by: ('ы')
C2 Computed by: ('å')
CU Computed by: ('∑')
C1X Computed by: (c1)
C2X Computed by: (c2)
CUX Computed by: (cu)
B1 Computed by: ('ы')
B2 Computed by: ('ä')
BU Computed by: ('∑')
BB Computed by: ('∞')
B1X Computed by: (b1)
B2X Computed by: (b2)
BUX Computed by: (bu)
BBX Computed by: (bb)

Here we can not see actual types of computed-by fields (may be this is subject for new ticket ?).

Full output of script (plus result of ISQL -X) see in attached file.

Checked on WI-V3.0.0.32306.

Commented by: @asfernandes

Of course you're talking about different problems:
- computed by transportation
- isql show

Nothing about this ticket

Commented by: @pavel-zotov

This:
> Why they all are of charset = UTF8 ? Field 'c1x' is based on 'c1' but its charset = WIN1251.
> Simiarly for field 'c2x' ==> 'c2' and its charset = WIN1252.
-- is expected because of connection charset (it was = UTF8).

The rest ( "computed by transportation") relates only to BLOB. I'll create two separate tickets - for this and for SHOW command.

Commented by: @pavel-zotov

> The rest ( "computed by transportation") relates only to BLOB. I'll create two separate tickets - for this and for SHOW command.
CORE5095
CORE5096

Commented by: @pavel-zotov

Adriano,

I have one more question.

SQL> recreate table test(
CON> t0 timestamp default 'now'
CON> ,t1 timestamp computed by( 'now' ) ---------------- ::: NB ::: 't1' has EXPLICITLY defined type: TIMESTAMP
CON> ,t2 computed by( extract(day from t1) ) ------------ Should TIMESTAMP type of 't1' be seen from here ?
CON> );
SQL> insert into test default values;
SQL> set list on;
SQL> select t0, t1 from test;

T0 2016-02-01 16:21:00.2210
T1 2016-02-01 16:21:00.2210

SQL> select t0, t1, t2 from test;

Statement failed, SQLSTATE = 42000
expression evaluation not supported
-Invalid argument for EXTRACT() not being of DATE/TIME/TIMESTAMP type
SQL>

I understand that last statement failed because EXTRACT() tried to evaluate day from STRING "now" rather than actual value of field 'T1' (which type is TIMESTAMP and is defined explicitly).

But is it expected ?

Commented by: @asfernandes

Pavel,

> I have one more question.
...

A new ticket, please. Add this test to it too, please:

create table t1 (n1 integer, c1 integer computed by (1.2));
insert into t1 values (0);
commit;

select * from t1;

      N1           C1 

============ ============
0 1

select c1 || '' from t1;

CONCATENATION

1.2

Commented by: @pavel-zotov

> A new ticket, please. Add this test to it too, please:

CORE5097 // sorry, but I could not provide adequate subject for it... :-)