Issue Details (XML | Word | Printable)

Key: CORE-2143
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Cosmin Apreutesei
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Control reported domains of view columns

Created: 24/Oct/08 09:02 AM   Updated: 04/Nov/08 01:10 PM
Component/s: Engine
Affects Version/s: 2.1.1
Fix Version/s: None

Environment: 2.1.1 SS


 Description  « Hide
sorry if this sounds more like a support question, but I didn't quite manage to find out what exactly is the algorithm by which firebird reports domains for columns of views. It seems like only the most simple views tend to keep the domain names of the underlying table columns. Any group by or sql expression (even if I cast/convert it to a domain) makes the columns loose their types and instead get those RDB$ domains with weird varchar lengths and no charset.

I'd like to be able to control the final reported domain (or datatype + charset etc.) of view columns because my app. code inspects them for formatting etc.



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 24/Oct/08 02:03 PM
Cosmin,

You are right this does seem like a support question, but it would be appropriate to provide a simple example which would expand on the issue (i.e. I define "X" SQL but when I do "Y" my application sees "Z" results).

Cosmin Apreutesei added a comment - 25/Oct/08 07:13 AM
An example would be any view defined as a select with a group by clause, or an sql expression as selected field (joins are ok).

The result is that, for those fields, I get system-generated domain names in RDB$RELATION_FIELDS.RDB$FIELD_SOURCE, i.e. domains are lost.

Technically, whenever RDB$BASE_FIELD is lost (because your field is an sql expression, aggregate, grouped-by field, etc.), you lose track of domain too. That I understand. Yet _logically_, the domain is preserved in most cases, and when not, you can use CAST/CONVERT to enforce it.

A few examples where select column C looses its domain where it could not (i.e. there's enough information in the query to preserve it):
  - select C from T group by C
  - select CAST(C1 || ', ' || C2 as MY_DOMAIN) as C from T
  - select CAST(LIST(C) as MY_UTF8_VARCHAR_DOMAIN) from T --- this makes a system-generated domain defined as VARCHAR(n*4) with no charset, where MY_UTF8_VARCHAR_DOMAIN is defined as VARCHAR(n) UTF8.

All this is disturbing IBX, FIBPlus, etc. which then create the wrong field types and/or type lengths for datasets.


Cosmin Apreutesei added a comment - 31/Oct/08 09:48 PM
This issue also goes for COMPUTED-BY columns, as I found out.

For instance try CREATE TABLE T (C VARCHAR(200) CHARACTER SET UTF8, C2 COMPUTED BY (C)) and you'll get a RDB$xxx system domain defined as VARCHAR(800) on C2.

Adriano dos Santos Fernandes added a comment - 01/Nov/08 05:09 PM
When creating a view, the connection charset is used in the new created domains. This doesn't sound good for me.

Adriano dos Santos Fernandes added a comment - 03/Nov/08 11:24 AM
Looks like the problem I talked is fixed in V2.5.

And ability to "Control reported domains of view columns" is unlike to happen. So please test view domains in 2.5 and report cases that could be improved.

Cosmin Apreutesei added a comment - 03/Nov/08 01:40 PM
You kinda' let me hi and dry here. Unlikely because it's to hard to implement, limited application, minor importance, other reason?

Anyway, in hoping to raise some awareness on the issue of why domain inference on views is important:

    - fibplus delphi components (and I'm sure there are others) look at domains of dataset fields to automatically create the proper field objects at design time. for instance it creates a TFIBBooleanField for whatever fields of a domain named BOOLEAN, a very handy option which goes wasted with a lot of my views.

    - because character set and collation information is missing from the fields metadata, I have to redundantly set it manually in my datasets so my grids will sort right, get the max. length for edits right etc., a useless and fastidious exercise.

    - a lot of input and display information is logically tied to domains, so where as a simple domain-name-based metadata catalog could automatically set proper attributes for all of the dataset fields in the whole application, in absence of such information from the database, all we're left is to redundantly reassigning domain names to fields in the application code.

Those are only a few annoyances from my own experience, but it sure makes the database feel a lot less usable than it should. On the same line of thoughts is CORE-1556, where one could implement an error msg. table around the metadata reported from the server.

You may find all these just minor details, but their implications amplifies them a lot in large business applications.



Cosmin Apreutesei added a comment - 03/Nov/08 01:49 PM
btw, maybe a better request subject should have been "Proper inference of domains for select columns" because 1) it affects any select, not only views, and 2) I really don't care about controlling, i.e. changing the domain name, I just want it right in the first place.

Adriano dos Santos Fernandes added a comment - 03/Nov/08 02:02 PM
> btw, maybe a better request subject should have been "Proper inference of domains for select columns" because 1) it affects any select, not only views, and 2) I really don't care about controlling, i.e. changing the domain name, I just want it right in the first place.

1) Standalone selects doesn't carry a domain, so I believe you're talking about data type only.
2) Please verify if the problems exists in v2.5 - depending on the fix, it will not going to be done/backported to previously releases.

Cosmin Apreutesei added a comment - 03/Nov/08 05:42 PM
1) "Standalone selects doesn't carry a domain, so I believe you're talking about data type only."
I know, and it's one of the reasons I'm creating views instead of standalone selects, so improvement-wise, I don't see any reason why this info (the domain name) shouldn't be passed on to stand alone selects also. But that's already a different kind of improvement.

2) So the problem was "When creating a view, the connection charset is used in the new created domains."
That's not what I experience (tested with 2.1.1 with a ODS11.1 db, and with 2.5a1 with a ODS11.2 db). What I get for a UTF8 VARCHAR column is always a RDB$xxx domain with the following NULL fields in RDB$FIELDS: RDB$CHARACTER_LENGTH, RDB$COLLATION_ID, RDB$FIELD_SUBTYPE. The only difference is that now (in 2.5) I do get a correct value in RDB$CHARACTER_SET_ID. However, I don't see the relation of that bug with the general issue of this post.

Many thanks,
Cosmin.

Adriano dos Santos Fernandes added a comment - 04/Nov/08 01:10 PM
> So the problem was "When creating a view, the connection charset is used in the new created domains."
> That's not what I experience (tested with 2.1.1 with a ODS11.1 db, and with 2.5a1 with a ODS11.2 db). What I get for a UTF8 VARCHAR column is always a RDB$xxx domain with the following NULL fields in RDB$FIELDS: RDB$CHARACTER_LENGTH, RDB$COLLATION_ID, RDB$FIELD_SUBTYPE. The only difference is that now (in 2.5) I do get a correct value in RDB$CHARACTER_SET_ID. However, I don't see the relation of that bug with the general issue of this post.
>
Inspection with "set sqlda_display on" had showed that the connection charset was used on the view creation in the same manner it's used for client parameters/results description of standalone selects, in HEAD.