Skip to content
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

Too long column name for select alias should be rejected [CORE2350] #2773

Closed
firebird-automations opened this issue Mar 4, 2009 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Bill Oliver (verbguy)

Relate to CORE5001

Votes: 1

A co-worker reported this test case. The last query silently truncates the aliased column name. He thinks that an error should be raised in this case. That seems reasonable to me.

The truncated column can still be referenced by the long name. This query returns the "correct" column. This is likely because we always chop to 31 chars.

Dmitry suggested to add a tracker, with this additional information.

I'm not sure it's really truncated, because a reference via a one-char-shorter fails:

select i2345678901234567890123456789012345 from (select i as
i23456789012345678901234567890123456 from t1);

or

select i23456789012345678901234567890123456 from (select i as
i2345678901234567890123456789012345 from t1);

Column alias names are not part of ODS, they don't even exist at the BLR level. It's a purely parsing time thing, so I believe the parser has no need to truncate the name and always deals with longer alias names. So, technically speaking, this is not a bug.

From another side, column alias names can be used to implicitly name the view columns and this fails:

create view v as
select rdb$relation_id as i23456789012345678901234567890123456
from rdb$relations

which looks somewhat inconsistent with the above.

Besides, I suspect that the SQL spec also requires alias names to share the implementation-defined length limits with column names, table names,
etc. If so, maybe it's worth entering a request into the tracker.

Dmitry

create database 'test.fdb';

-- fails, table name too long
create table t23456789012345678901234567890123456 (i integer);

-- fails, column name too long
create table t1 (i23456789012345678901234567890123456 integer);

create table t1 (i integer);
-- passes, and column name is truncated to 31 characters?
select i as i23456789012345678901234567890123456 from t1;

-- this works, too. We can still reference truncated column by long name,
-- which is better than nothing, I guess.
select i23456789012345678901234567890123456 from (select i as
i23456789012345678901234567890123456 from t1);

drop database;

@firebird-automations
Copy link
Collaborator Author

Commented by: Balázs Csaba (symboltech)

Field alias truncation occured very often when our framework used:

SELECT TBL27."Id" "http://stockoutsettling.id",
TBL27."StockOut" "stockoutsettling.stockout",
TBL28."VoucherNumber" "stockout.vouchernumber",
TBL27."SettleDate" "stockoutsettling.settledate",
TBL27."CheckoutVoucherDetail" "stockoutsettling.checkoutvoucherdetail",
TBL27."NegStockOut" "stockoutsettling.negstockout",
TBL27."GrossValue" "stockoutsettling.grossvalue"
FROM "StockOutSettling" TBL27

Often the truncated string occurs fieldname equality, so the resultset has fewer column than the select command.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I cannot reproduce it on v3 anymore, suposedly it was fixed in the meantime.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5001 [ CORE5001 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Workdev Tecnologia da Informação LTDA (workdev)

The nickname should not have limited or at least 50 when it is used for reports where the user can edit the same columns are unrecognizable . Limitations should come in the number of database corruption and not on the user's flexibility.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Max. length of procedure and parameter names, as all other DB objects, is restricted in 4.0 to 64 characters.
But one more good news is that max length of local aliases and variable names now equals 255 (i've checked only ascii charset).

Is it expected ?

For example, following code will be compiled without error:

set term ^;
create or alter procedure p2 returns(o2 double precision) as
declare
v2345678901234567890123456789012345678901234567890123456789012345b2345678901234567890123456789012345678901234567890123456789012345a2345678901234567890123456789012345678901234567890123456789012345b23456789012345678901234567890123456789012345678901234567890
int;
begin
select rand()*1000 as
a2345678901234567890123456789012345678901234567890123456789012345b2345678901234567890123456789012345678901234567890123456789012345a2345678901234567890123456789012345678901234567890123456789012345b23456789012345678901234567890123456789012345678901234567890
from rdb$database as
t2345678901234567890123456789012345678901234567890123456789012345b2345678901234567890123456789012345678901234567890123456789012345a2345678901234567890123456789012345678901234567890123456789012345b23456789012345678901234567890123456789012345678901234567890
into
v2345678901234567890123456789012345678901234567890123456789012345b2345678901234567890123456789012345678901234567890123456789012345a2345678901234567890123456789012345678901234567890123456789012345b23456789012345678901234567890123456789012345678901234567890;
o2 = sqrt(v2345678901234567890123456789012345678901234567890123456789012345b2345678901234567890123456789012345678901234567890123456789012345a2345678901234567890123456789012345678901234567890123456789012345b23456789012345678901234567890123456789012345678901234567890);
suspend;
end
^
set term ;^
commit;

show procedure p2;

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Pavel, I saw it yesterday while testing CORE5277. No need to reopen this if it is a recent regression. I'll fix it.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Pavel, must now be fixed.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> must now be fixed.

Checked on WI-T4.0.0.254 --- all fine.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment