I would have never expected a parser token error if the query syntax is
correct but the plan itself is bad. That would have been a run-time, not
compile-time error!
And I just verified that what I am saying looks like an FB bug. Here is a
very simple example that reproduces the bug:
create table
Hello, Alec!
AS> 2. Changed the query to "A INNER JOIN B ORDER BY X PLAN P", where P is the
AS> plan generated by FB in #1. Received "Unexpected token PLAN" error.
AS> 3. Change the query to "A INNER JOIN B PLAN P ORDER BY X" - success!
AS> Why does #3 succeed but #2 fail?
because inner join is
[Non-text portions of this message have been removed]
Dmitry,
If you look at the plans in my original post they both have "PHYSICAL_COPY
ORDER "IDX_214/CmDhH936xtHXcXNQKg=="
INDEX" just like you suggested. Sorry for the misleading table and index
names.
Once again, the plan I used was generated by FB query optimizer itself! I
did NOT create it by ha
Hello, Alec!
AS> The question is why FB complains about PLAN token after ORDER BY clause?
the answer is that PLAN is wrong. ORDER BY in query affects PLAN, it
will contain PLAN SORT or TABLE ORDER INDEX specification. Your PLAN
specification is crap, sorry for rude answer.
As I said before, you n
Dmitry,
We create indexes programmatically and we we generate their names based on
their definition, such the columns they index. In fact the plan I am trying
to force the query optimizer to use is exactly the plan that was generated
by query optimizer when I replaced INNER with LEFT JOIN in the q
Hello, Alec!
Saturday, June 2, 2012, 11:28:52 PM, you wrote:
AS> specify a plan for a query that has ORDER BY clause. I get a "invalid
AS> token PLAN" error if I specify the plan after ORDER BY, but it works
AS> COPY INDEX ("PK_ZM6SRonqR8AHSQuCISgvnQ=="))
and, finally, who told you about "COPY"
Hello, Alec!
Saturday, June 2, 2012, 11:28:52 PM, you wrote:
AS> Hello,
AS> I am using FB 2.5 and execute SQL using Flamerobin. I am trying to
AS> specify a plan for a query that has ORDER BY clause. I get a "invalid
AS> token PLAN" error if I specify the plan after ORDER BY, but it works
AS> OK
Hello,
I am using FB 2.5 and execute SQL using Flamerobin. I am trying to
specify a plan for a query that has ORDER BY clause. I get a "invalid
token PLAN" error if I specify the plan after ORDER BY, but it works
OK if I specify the plan before ORDER BY. But the plan contains
instructions for ORDE
On 2-6-2012 19:47, softdestek wrote:
> ACCOUNTS.DEBT is double
> ACCOUNTS.RECEIVABLES is double
>
> MY QUERY
> SELECT
>SUM(ACCOUNTS.DEBT),
>SUM(ACCOUNTS.RECEIVABLES),
>sum(ACCOUNTS.DEBT)-SUM(ACCOUNTS.RECEIVABLES)
> FROM ACCOUNTS
> WHERE ACCOUNTS.CODE='100'
>
>
>
> 1) SQL
Hello Michael and Dmitry,
Michael, the index on PHYSICAL_COPY."COMMIT_NUMBER" is ascending, so in
theory it should be used.
Dmitry, I simplified the query by removing one join and selecting just one
field. The query is still very slow but the temporary sort file was only
130MB. This is good, but
ACCOUNTS.DEBT is double
ACCOUNTS.RECEIVABLES is double
MY QUERY
SELECT
SUM(ACCOUNTS.DEBT),
SUM(ACCOUNTS.RECEIVABLES),
sum(ACCOUNTS.DEBT)-SUM(ACCOUNTS.RECEIVABLES)
FROM ACCOUNTS
WHERE ACCOUNTS.CODE='100'
1) SQL RESULT
SUM(ACCOUNTS.DEBT) 2708021,6
I just solved my own problem, and wanted to help anyone else with the same
problem!
I'm trying to write a UDF that returns memory allocated with ib_util_malloc,
that can be freed with the FREE_IT directive.
I #included ib_util.h (after ibase.h), and added ib_util_ms.lib as an
additional depend
Hello, Alec!
Saturday, June 2, 2012, 2:31:36 AM, you wrote:
AS> We tracked down the query that generated a 10GB temp file running
AS> against a 1.5GB database. Can anybody explain why the query is not
AS> using an index on PHYSICAL_COPY."COMMIT_NUMBER"?
the sort file is big because you are tryin
Alec Swan schrieb am 01.06.2012 um 16:31 (-0600):
>
> We tracked down the query that generated a 10GB temp file running
> against a 1.5GB database. Can anybody explain why the query is not
> using an index on PHYSICAL_COPY."COMMIT_NUMBER"?
> Prepare time: 3.969s
> PLAN SORT (JOIN (COPY INDEX (IDX
15 matches
Mail list logo
