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

Bug in create/alter Database trigger (with Russian comment) [CORE1694] #2119

Closed
firebird-automations opened this issue Jan 15, 2008 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sviridov Sergey (sergsw)

Attachments:
BUG_TEST.FDB
ibtestunicode.zip

Bug in create/alter Database trigger

If execute statment

alter TRIGGER DBTRIGGER_COPY ACTIVE ON CONNECT POSITION 0
AS
/* дескрипшн переменной триггера БД
дескрипшн переменной триггера БД строка 2
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR SMALLINT DEFAULT 4;
/* дескрипшн переменной триггера БД
дескрипшн переменной триггера БД строка 2
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR1 VARCHAR(30) CHARACTER SET UTF8 COLLATE UTF8 DEFAULT 'Дефолт Default';
DECLARE VARIABLE EFWEF TYPE OF VARCHAR_DOMAIN2;
/* дескрипшн переменной триггера БД
дескрипшн переменной триггера БД строка 2
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR2 TYPE OF VARCHAR_DOMAIN DEFAULT 'Дефолт Default';
/* дескрипшн переменной триггера БД
дескрипшн переменной триггера БД
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR3 TYPE OF SMALLINT_DOMAIN DEFAULT 3;
BEGIN
/* Database Trigger body */
END

It will cause a mistake
"This operation is not defined for system tables.Unsuccessful metadata update.
Create metadata BLOB failed.
Cannot transliterate character between character sets."

But if change domain type variable EFWEF to VARCHAR_DOMAIN, or change
...
DECLARE VARIABLE NEW_VAR2 TYPE OF VARCHAR_DOMAIN DEFAULT 'Дефолт Default';
/* дескрипшн переменной триггера БД
дескрипшн переменной триггера БД
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR3 TYPE OF SMALLINT_DOMAIN DEFAULT 3;
...

to

...
DECLARE VARIABLE NEW_VAR2 TYPE OF VARCHAR_DOMAIN DEFAULT 'Дефолт Default';
/* дескрипшн переменной триггера БД
дескрипшн
database trigger description
database trigger description string 2 */
DECLARE VARIABLE NEW_VAR3 TYPE OF SMALLINT_DOMAIN DEFAULT 3;
...

that the inquiry will be executed successfully

Domain statements(Identical):
CREATE DOMAIN VARCHAR_DOMAIN AS
VARCHAR(30) CHARACTER SET UTF8
COLLATE UTF8;

CREATE DOMAIN VARCHAR_DOMAIN2 AS
VARCHAR(30) CHARACTER SET UTF8
COLLATE UTF8;

Commits: b4a3990 b11f60b

====== Test Details ======

See also tests that manipulates with NULL fields/domains and check results:
CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent;
CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work);
CORE4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs);
CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR);

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This is WIN1251 text, correct?

I can't reproduce the problem...

What tool you used? Can the error be reproducible with SQL?

Can you also test with a more recent snapshot?

@firebird-automations
Copy link
Collaborator Author

Commented by: Sviridov Sergey (sergsw)

This is database on which the bug reproduce

@firebird-automations
Copy link
Collaborator Author

Modified by: Sviridov Sergey (sergsw)

Attachment: BUG_TEST.FDB [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sviridov Sergey (sergsw)

On server FB 2.1 beta 2 too reproduce

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I tested with ISQL in Win32 Beta1 (15999) and Linux x86 RC1 and there is no problem.

There is only a minor problem, when you try to alter a non existent trigger:
Statement failed, SQLCODE = -902
Dynamic SQL Error
-Invalid command
-Database trigger type can't be changed
After line 0 in file r.sql

@firebird-automations
Copy link
Collaborator Author

Commented by: Sviridov Sergey (sergsw)

If use client connection "utf8" that reproduce

Example code
...
static const char *szClientCharset = "UTF8";

//with Descr BUG
static const wchar_t *szAlterDBTriggerW = L"ALTER TRIGGER DBTRIGGER ACTIVE\r\nON CONNECT POSITION 0\r\nAS\r\n/* дескрипшн переменной триггера БД\r\nдескрипшн переменной триггера БД строка 2\r\ndatabase trigger description\r\ndatabase trigger description string 2 */\r\nDECLARE VARIABLE NEW_VAR SMALLINT DEFAULT 4;\r\n/* дескрипшн переменной триггера БД\r\nдескрипшн переменной триггера БД строка 2\r\ndatabase trigger description\r\ndatabase trigger description string 2 */\r\nDECLARE VARIABLE NEW_VAR1 VARCHAR(30) CHARACTER SET UTF8 COLLATE UTF8 DEFAULT 'Дефолт Default';\r\nDECLARE VARIABLE EFWEF TYPE OF VARCHAR_DOMAIN2;\r\n/* дескрипшн переменной триггера БД\r\nдескрипшн переменной триггера БД строка 2\r\ndatabase trigger description\r\ndatabase trigger description string 2 */\r\nDECLARE VARIABLE NEW_VAR2 TYPE OF VARCHAR_DOMAIN DEFAULT 'Дефолт Default';\r\n/* дескрипшн переменной триггера БД\r\nдескрипшн переменной триггера БД\r\ndatabase trigger description\r\ndatabase trigger description string 2 */\r\nDECLARE VARIABLE NEW_VAR3 TYPE OF SMALLINT_DOMAIN DEFAULT 3;\r\nBEGIN\r\n /* Database Trigger body */\r\nEND;";
...
Len = WideCharToMultiByte(CP_UTF8, 0, szAlterDBTriggerW, wcslen(szAlterDBTriggerW), NULL, 0, NULL, false);
szNonUnicode = new char[Len+1];
ZeroMemory(szNonUnicode, (Len+1)*sizeof(char));
WideCharToMultiByte(CP_UTF8, 0, szAlterDBTriggerW, wcslen(szAlterDBTriggerW), szNonUnicode, Len, NULL, false);
...
isc_dsql_execute_immediate(sv, &tmpDbHandle, &tmpTrHandle, wcslen(FSqlTextW), (char*)FSqlTextW,3,NULL) // <- Return non zero value

@firebird-automations
Copy link
Collaborator Author

Commented by: Sviridov Sergey (sergsw)

Examle to execute

@firebird-automations
Copy link
Collaborator Author

Modified by: Sviridov Sergey (sergsw)

Attachment: ibtestunicode.zip [ 10760 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 2.1 Beta 1 [ 10141 ]

Version: 2.1 RC1 [ 10201 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 13823 ] => Firebird [ 14126 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 2.1 RC2 [ 10250 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: See also tests that manipulates with NULL fields/domains and check results:
CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent
CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work)
CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Test Details: See also tests that manipulates with NULL fields/domains and check results:
CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent
CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work)
CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR)

=>

See also tests that manipulates with NULL fields/domains and check results:
CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent;
CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work);
CORE4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs);
CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR);

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