search

FirebirdSQL / firebird

Firebird server, client and tools
https://firebirdsql.org
1.26k stars 217 forks source link

ISQL extract command lose COMPUTED BY field types [CORE5092] #5377

Closed firebird-automations closed 8 years ago

firebird-automations commented 8 years ago

Submitted by: @asfernandes

Attachments: c5092_ddl_plus_output_plus_result_of_metadata_extraction.zip

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

create table t1 ( n1 integer, n2 bigint computed by (1) );

But ISQL extracts only the computed expression, which may change the field type:

create table t1 ( n1 integer, n2 computed by (1) );

Commits: FirebirdSQL/firebird@fb7eca8c60484d78578f73dbef07350f7573e11c FirebirdSQL/fbt-repository@a598091c410466dc8cc623496ddd7621293ca8ea

firebird-automations commented 8 years ago
Modified by: @asfernandes assignee: Adriano dos Santos Fernandes \[ asfernandes \]
firebird-automations commented 8 years ago
Modified by: @asfernandes status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Fixed \[ 1 \] Fix Version: 3\.0 RC2 \[ 10048 \]
firebird-automations commented 8 years ago

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)

firebird-automations commented 8 years ago

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.

firebird-automations commented 8 years ago
Modified by: @pavel-zotov Attachment: c5092\_ddl\_plus\_output\_plus\_result\_of\_metadata\_extraction\.zip \[ 12892 \]
firebird-automations commented 8 years ago

Commented by: @asfernandes

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

Nothing about this ticket

firebird-automations commented 8 years ago

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.

firebird-automations commented 8 years ago

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

firebird-automations commented 8 years ago

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 ?

firebird-automations commented 8 years ago
Modified by: @pavel-zotov status: Resolved \[ 5 \] =\> Resolved \[ 5 \] QA Status: No test =\> Done successfully
firebird-automations commented 8 years ago
Modified by: @pavel-zotov status: Resolved \[ 5 \] =\> Closed \[ 6 \]
firebird-automations commented 8 years ago

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

firebird-automations commented 8 years ago

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... :-)