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

Jump to bottom

cmd update affect field not in command [CORE6146] #6395

Closed
firebird-automations opened this issue Sep 19, 2019 · 13 comments
Closed

cmd update affect field not in command [CORE6146] #6395

firebird-automations opened this issue Sep 19, 2019 · 13 comments
Labels
affect-version: 3.0.4 component: engine priority: critical resolution: wontfix type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

Attachments:
Otimizacao.part01.rar
Otimizacao.part02.rar
Otimizacao.part03.rar

I created a new database with only one table. and I pump 480,000 records.
I do not have TRIGGERS, NOTHING.

only:
CREATE TABLE NOTAVENDA (
COD_NOTAVENDA INTEGER NOT NULL,
CODCFOP_NOTAVENDA INTEGER,
XMLNFE_NOTAVENDA BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

FIELD CODCFOP_NOTAVENDA HAS SOME NULLS.

WHEN I RUN COMMAND " UPDATE NOTAVENDA SET CODCFOP_NOTAVENDA = CODCFOP_NOTAVENDA WHERE (CODCFOP_NOTAVENDA > 0) "

the FIELD XML becomes null in more than 300 records. (SELECT COUNT(*) FROM NOTAVENDA WHERE (XMLNFE_NOTAVENDA IS NULL))

BUT IF I USE COALESCE
WHERE COALESCE((CODCFOP_NOTAVENDA,0) > 0)

NO PROBLEM OCCURS.
@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

This is a support issue, which should be handled via the Firebird Support mailing list (see http://firebirdsql.org/en/mailing-lists/)

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

add arquivo .rar

@firebird-automations
Copy link
Collaborator Author

Modified by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

Attachment: Otimizacao.part01.rar [ 13383 ]

description: I created a new database with only one table. and I pump 480,000 records.
I do not have TRIGGERS, NOTHING.

only:
CREATE TABLE NOTAVENDA (
COD_NOTAVENDA INTEGER NOT NULL,
CODCFOP_NOTAVENDA INTEGER,
XMLNFE_NOTAVENDA BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

FIELD CODCFOP_NOTAVENDA HAS SOME NULLS.

WHEN I RUN COMMAND " UPDATE NOTAVENDA SET CODCFOP_NOTAVENDA = CODCFOP_NOTAVENDA WHERE (CODCFOP_NOTAVENDA > 0) "

FIELD XML becomes null in more than 300 records.

BUT IF I USE COALESCE
WHERE COALESCE((CODCFOP_NOTAVENDA,0) > 0)

NO PROBLEM OCCURS.

=>

I created a new database with only one table. and I pump 480,000 records.
I do not have TRIGGERS, NOTHING.

only:
CREATE TABLE NOTAVENDA (
COD_NOTAVENDA INTEGER NOT NULL,
CODCFOP_NOTAVENDA INTEGER,
XMLNFE_NOTAVENDA BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

FIELD CODCFOP_NOTAVENDA HAS SOME NULLS.

WHEN I RUN COMMAND " UPDATE NOTAVENDA SET CODCFOP_NOTAVENDA = CODCFOP_NOTAVENDA WHERE (CODCFOP_NOTAVENDA > 0) "

the FIELD XML becomes null in more than 300 records. (SELECT COUNT(*) FROM NOTAVENDA WHERE (XMLNFE_NOTAVENDA IS NULL))

BUT IF I USE COALESCE
WHERE COALESCE((CODCFOP_NOTAVENDA,0) > 0)

NO PROBLEM OCCURS.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Anderson,

description looks improbable, sorry.
If you provide us with reproducible test case, ticket will be re-opened.

@firebird-automations
Copy link
Collaborator Author

Modified by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

Attachment: Otimizacao.part02.rar [ 13384 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

Attachment: Otimizacao.part03.rar [ 13385 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

hello Vlad,

I agree with you.
I didn't believe it myself.
I did several tests.
This is happening on a number of different bases.
I add attachments for testing.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

I can't confirm it:

SQL> select count(*), sum(octet_length(XMLNFE_NOTAVENDA)) from notavenda;

            COUNT                   SUM

===================== =====================
48755 524022283

SQL> select count(*), sum(octet_length(XMLNFE_NOTAVENDA)) from notavenda where XMLNFE_NOTAVENDA is not null;

            COUNT                   SUM

===================== =====================
48755 524022283

SQL> commit;
SQL>
SQL> UPDATE NOTAVENDA SET CODCFOP_NOTAVENDA = CODCFOP_NOTAVENDA WHERE (CODCFOP_NOTAVENDA > 0);
SQL> select count(*), sum(octet_length(XMLNFE_NOTAVENDA)) from notavenda;

            COUNT                   SUM

===================== =====================
48755 524022283

SQL> select count(*), sum(octet_length(XMLNFE_NOTAVENDA)) from notavenda where XMLNFE_NOTAVENDA is not null;

            COUNT                   SUM

===================== =====================
48755 524022283

SQL> commit;
SQL> select count(*), sum(octet_length(XMLNFE_NOTAVENDA)) from notavenda where XMLNFE_NOTAVENDA is not null;

            COUNT                   SUM

===================== =====================
48755 524022283

SQL> commit;
SQL> select count(*) from notavenda where XMLNFE_NOTAVENDA is null;

            COUNT

=====================
0

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Sounds similar to CORE6090.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Dmitry,

yes, but it is not possible to reproduce with fresh restored database ;)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Fresh restored database cannot have multiple table formats (unless it has computed fields), so it really shouldn't be reproducible there.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 3.0.4 component: engine priority: critical resolution: wontfix type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations