Issue Details (XML | Word | Printable)

Key: CORE-2932
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: Gabor Boros
Votes: 0
Watchers: 1
Operations

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

Wrong field position after ALTER POSITION

Created: 19/Mar/10 07:22 PM   Updated: 22/Sep/15 09:32 AM
Component/s: Engine
Affects Version/s: 2.1.3, 3.0 Initial
Fix Version/s: 3.0 Beta 2

Environment: Windows XP SP3 32 bit, Firebird 2.1.2 SS 32bit

QA Status: Done successfully


 Description  « Hide
I got ugly(random?) field position after an ALTER TABLE ... ALTER ... POSITION statement.
Described on firebird-devel in "Strange ALTER POSITION result" thread.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 07/Apr/15 03:29 PM
It seems that new position can be evaluated wrong in case when field is "moved" to the end of field list after some manipulations.
Sample:
=======
recreate table test(
   f01 int
  ,f02 int
  ,f03 int
  ,f04 int
  ,f05 int
  ,f06 int
  ,f07 int
  ,f08 int
  ,f09 int
  ,f10 int
);
commit;

set echo on;
show table test; -- 1
set echo off;

alter table test drop f01, drop f02, drop f03, drop f04;
commit;

-- move field f10 to the "start" of DDL:
alter table test alter f10 position 3;
-- after this DDL must be: f05 f06 f10 f07 f08 f09
commit;

set echo on;
show table test; -- 2
set echo off;

alter table test add n01 int, add n02 int, add n03 int, add n04 int, add n05 int, add n06 int;
commit;

alter table test drop n01, drop n02, drop n03, drop n04;
-- after this DDL must be: f05 f06 f10 f07 f08 f09 n05 n06
commit;

set echo on;
show table test; -- 3
set echo off;

-- move field f10 to the "end" of DDL: exchange positions of n05 and f10
alter table test alter f10 position 7, alter n06 position 3;
-- after this DDL result must be: f05 f06 n05 f07 f08 f09 f10 n06
commit;

set echo on;
show table test; -- 4


Result for "show table test; -- 4":
========
F05 INTEGER Nullable -- 1
F06 INTEGER Nullable -- 2
N06 INTEGER Nullable -- 3
F07 INTEGER Nullable -- 4
F08 INTEGER Nullable -- 5
F09 INTEGER Nullable -- 6
N05 INTEGER Nullable -- 7
F10 INTEGER Nullable -- 8 <<<<<<<<<<<<<<<<<< ??? <<<<<<<<<<<<<<

So, field `F10` is appeared at the position #8 rather than #7 as itwas required.


But if change
-- move field f10 to the "end" of DDL: exchange positions of n05 and f10
alter table test alter f10 position 7, alter n06 position 3;

To:
-- move field f10 to the "end" of DDL: exchange positions of n05 and f10
alter table test alter n06 position 3, alter f10 position 7;

(i.e. first move `n06` to the beginning of DDL, and after this - move `f10` to the end)

-- then all works fine:

show table test; -- 4
F05 INTEGER Nullable -- 1
F06 INTEGER Nullable -- 2
N06 INTEGER Nullable -- 3
F07 INTEGER Nullable -- 4
F08 INTEGER Nullable -- 5
F09 INTEGER Nullable -- 6
F10 INTEGER Nullable -- 7
N05 INTEGER Nullable -- 8

Pavel Zotov added a comment - 07/Apr/15 03:33 PM
PS. Tested on WI-T3.0.0.31773

Pavel Zotov added a comment - 07/Apr/15 10:04 PM
One more sample, shorter than previous:

SQL> recreate table t(f01 int, f02 int, f03 int);
SQL> commit;
SQL> alter table t drop f01, drop f02;
SQL> commit;
SQL> alter table t add new_a int, add new_b int;
SQL> commit;
SQL> show table t;
F03 INTEGER Nullable
NEW_A INTEGER Nullable
NEW_B INTEGER Nullable

SQL> alter table t alter f03 position 3;
SQL> commit;

SQL> show table t;
F03 INTEGER Nullable ---------------- position was not changed!
NEW_A INTEGER Nullable
NEW_B INTEGER Nullable

SQL> alter table t alter f03 position 2;
SQL> commit;

SQL> show table t;
F03 INTEGER Nullable ---------------- position was not changed!
NEW_A INTEGER Nullable
NEW_B INTEGER Nullable

Adriano dos Santos Fernandes added a comment - 08/Apr/15 04:46 AM
Pavel, I think this is wrong:

-- move field f10 to the "end" of DDL: exchange positions of n05 and f10
alter table test alter f10 position 7, alter n06 position 3;
-- after this DDL result must be: f05 f06 n05 f07 f08 f09 f10 n06

It must be:

F05 INTEGER Nullable
F06 INTEGER Nullable
N06 INTEGER Nullable
F07 INTEGER Nullable
F08 INTEGER Nullable
F09 INTEGER Nullable
N05 INTEGER Nullable
F10 INTEGER Nullable

I'm changing for this. Please retest.

Pavel Zotov added a comment - 08/Apr/15 07:14 AM
> I think this is wrong

You're right, it was my mistake.
I've tested on WI-T3.0.0.31780 both cases -- now all works fine.