New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Control reported domains of view columns [CORE2143] #2574
Comments
Commented by: Sean Leyne (seanleyne) 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). |
Commented by: Cosmin Apreutesei (cosmin_ap2) 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): All this is disturbing IBX, FIBPlus, etc. which then create the wrong field types and/or type lengths for datasets. |
Commented by: Cosmin Apreutesei (cosmin_ap2) 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. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @asfernandes When creating a view, the connection charset is used in the new created domains. This doesn't sound good for me. |
Commented by: @asfernandes 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. |
Commented by: Cosmin Apreutesei (cosmin_ap2) 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:
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 CORE1556, 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. |
Commented by: Cosmin Apreutesei (cosmin_ap2) 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. |
Commented by: @asfernandes > 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. |
Commented by: Cosmin Apreutesei (cosmin_ap2) 1) "Standalone selects doesn't carry a domain, so I believe you're talking about data type only." 2) So the problem was "When creating a view, the connection charset is used in the new created domains." Many thanks, |
Commented by: @asfernandes > So the problem was "When creating a view, the connection charset is used in the new created domains." |
Submitted by: Cosmin Apreutesei (cosmin_ap2)
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.
The text was updated successfully, but these errors were encountered: