It is too slow about statement like :select ... from A,B full out join C on ... where A.Field1A=B.Field1B and A.Field2A='...' [CORE1177]

[firebird-automations]

Submitted by: flyingfb (flyingfb)

In FB2.0, we can execute correctly the following statement generated by DELPHI7:

SELECT 0, '', '', A\.RDB$RELATION\_NAME, A\.RDB$INDEX\_NAME, B\.RDB$FIELD\_NAME, B\.RDB$FIELD\_POSITION, '', 0, A\.RDB$INDEX\_TYPE, '', A\.RDB$UNIQUE\_FLAG, C\.RDB$CONSTRAINT\_NAME, C\.RDB$CONSTRAINT\_TYPE FROM RDB$INDICES A, RDB$INDEX\_SEGMENTS B FULL OUTER JOIN RDB$RELATION\_CONSTRAINTS C ON A\.RDB$RELATION\_NAME = C\.RDB$RELATION\_NAME AND C\.RDB$CONSTRAINT\_TYPE = 'PRIMARY KEY' WHERE \(A\.RDB$SYSTEM\_FLAG <\> 1 OR A\.RDB$SYSTEM\_FLAG IS NULL\) AND \(A\.RDB$INDEX\_NAME = B\.RDB$INDEX\_NAME\) AND \(A\.RDB$RELATION\_NAME = UPPER\('SYS\_Module'\)\) ORDER BY RDB$INDEX\_NAME

But it is too slow ! I think I need to make it clear that we have 1117 Tables, 219 Views, 36 Procedures, 245 Triggers, 148 Generators, 2332 Indices in our Database.

It is lucky when we upgrade to FB2.0.1 and execute the same statement, it return error message:

Undefined name. Dynamic SQL Error. SQL error code = -204. Ambiguous field name between table RDB$INDICES and table RDB$INDEX_SEGMENTS and table RDB$RELATION_CONSTRAINTS . RDB$INDEX_NAME.

But when we change "ORDER BY RDB$INDEX_NAME" to "ORDER BY A.RDB$INDEX_NAME", and execute the statement it can be executed but is also too slow.

Can "where" part of the statement be taken action before "out join" part? Here, the "where" part have no relationship with the "out join" part.

[firebird-automations]

Modified by: flyingfb (flyingfb)

priority: Major \[ 3 \] =\> Minor \[ 4 \] Version: 2\.0\.0 \[ 10091 \] Component: Engine \[ 10000 \] description: In FB2\.0, we can execute correctly the following statement generated by DELPHI7: SELECT 0, '', '', A\.RDB$RELATION\_NAME, A\.RDB$INDEX\_NAME, B\.RDB$FIELD\_NAME, B\.RDB$FIELD\_POSITION, '', 0, A\.RDB$INDEX\_TYPE, '', A\.RDB$UNIQUE\_FLAG, C\.RDB$CONSTRAINT\_NAME, C\.RDB$CONSTRAINT\_TYPE FROM RDB$INDICES A, RDB$INDEX\_SEGMENTS B FULL OUTER JOIN RDB$RELATION\_CONSTRAINTS C ON A\.RDB$RELATION\_NAME = C\.RDB$RELATION\_NAME AND C\.RDB$CONSTRAINT\_TYPE = 'PRIMARY KEY' WHERE \(A\.RDB$SYSTEM\_FLAG <\> 1 OR A\.RDB$SYSTEM\_FLAG IS NULL\) AND \(A\.RDB$INDEX\_NAME = B\.RDB$INDEX\_NAME\) AND \(A\.RDB$RELATION\_NAME = UPPER\('SYS\_Module'\)\) ORDER BY RDB$INDEX\_NAME But it is too slow \! I think I need to make it clear that we have 1117 Tables, 219 Views, 36 Procedures, 245 Triggers, 148 Generators, 2332 Indices in our Database\. It is lucky when we we upgrade to FB2\.0\.1, it return error message: Undefined name\. Dynamic SQL Error\. SQL error code = \-204\. Ambiguous field name between table RDB$INDICES and table RDB$INDEX\_SEGMENTS and table RDB$RELATION\_CONSTRAINTS \. RDB$INDEX\_NAME\. But when we change "ORDER BY RDB$INDEX\_NAME" to "ORDER BY A\.RDB$INDEX\_NAME", and execute the statement it is also too slow\. Can "where" part of the statement be taken action before "out join" part? Here, the "where" part have no relationship with the "out join" part\. =\> In FB2\.0, we can execute correctly the following statement generated by DELPHI7: SELECT 0, '', '', A\.RDB$RELATION\_NAME, A\.RDB$INDEX\_NAME, B\.RDB$FIELD\_NAME, B\.RDB$FIELD\_POSITION, '', 0, A\.RDB$INDEX\_TYPE, '', A\.RDB$UNIQUE\_FLAG, C\.RDB$CONSTRAINT\_NAME, C\.RDB$CONSTRAINT\_TYPE FROM RDB$INDICES A, RDB$INDEX\_SEGMENTS B FULL OUTER JOIN RDB$RELATION\_CONSTRAINTS C ON A\.RDB$RELATION\_NAME = C\.RDB$RELATION\_NAME AND C\.RDB$CONSTRAINT\_TYPE = 'PRIMARY KEY' WHERE \(A\.RDB$SYSTEM\_FLAG <\> 1 OR A\.RDB$SYSTEM\_FLAG IS NULL\) AND \(A\.RDB$INDEX\_NAME = B\.RDB$INDEX\_NAME\) AND \(A\.RDB$RELATION\_NAME = UPPER\('SYS\_Module'\)\) ORDER BY RDB$INDEX\_NAME But it is too slow \! I think I need to make it clear that we have 1117 Tables, 219 Views, 36 Procedures, 245 Triggers, 148 Generators, 2332 Indices in our Database\. It is lucky when we upgrade to FB2\.0\.1 and execute the same statement, it return error message: Undefined name\. Dynamic SQL Error\. SQL error code = \-204\. Ambiguous field name between table RDB$INDICES and table RDB$INDEX\_SEGMENTS and table RDB$RELATION\_CONSTRAINTS \. RDB$INDEX\_NAME\. But when we change "ORDER BY RDB$INDEX\_NAME" to "ORDER BY A\.RDB$INDEX\_NAME", and execute the statement it can be executed but is also too slow\. Can "where" part of the statement be taken action before "out join" part? Here, the "where" part have no relationship with the "out join" part\.

[firebird-automations]

Modified by: flyingfb (flyingfb)

Version: 2\.0\.0 \[ 10091 \] =\>

[firebird-automations]

Modified by: @pcisar

Workflow: jira \[ 11711 \] =\> Firebird \[ 15504 \]

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \]