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

Query error in Firebird [DNET427] #425

Closed
firebird-automations opened this issue Apr 30, 2012 · 18 comments
Closed

Query error in Firebird [DNET427] #425

firebird-automations opened this issue Apr 30, 2012 · 18 comments

Comments

@firebird-automations
Copy link

Submitted by: Damasio (andersondamasio)

Block progress on DNET711

Attachments:
METADATA.SQL
WebApplication1.zip

"Dynamic SQL Error
SQL error code = -104
Token unknown - line 8, column 3
OUTER"

protected void LinqDataSourcePedidos_QueryCreated(object sender, QueryCreatedEventArgs e)
{
var q = e.Query.Cast<PED>();
var result = (from t in q
select new
{
PEDPOS_POSICAO = (from pedpos in t.PEDPOS
where
t.PED_ID == pedpos.PED_ID &&
pedpos.PEDPOS_ID ==
t.PEDPOS.FirstOrDefault().PEDPOS_ID
select new
{
pedpos.PEDPOS_POSICAO
}).FirstOrDefault().PEDPOS_POSICAO,
PED_ID = t.PED_ID
});
e.Query = result;
}

@firebird-automations
Copy link
Author

Modified by: Damasio (andersondamasio)

priority: Major [ 3 ] => Critical [ 2 ]

Component: Entity Framework support [ 10110 ]

Component: http://ADO.NET Provider [ 10041 ] =>

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Without the model I can hardly do anything about it.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

priority: Critical [ 2 ] => Major [ 3 ]

@firebird-automations
Copy link
Author

Commented by: Damasio (andersondamasio)

okay. I will build a model to give you.

@firebird-automations
Copy link
Author

Commented by: Damasio (andersondamasio)

Attached is the data.

The error occurs in the code example below:

public IQueryable SelectAllPED()
{
Entities entities = new Entities();
var result = (from t in entities.PED
select new
{
PED_ID = t.PED_ID,
PED_DATA = t.PED_DATA,
PED_HORA = t.PED_HORA,
PED_CLI_CPFCNPJ = t.PED_CLI_CPFCNPJ,
PED_CLI_NOME = t.PED_CLI_NOME,
PEDPOS_POSICAO = (from pp in t.PEDPOS
select new
{
pp.PEDPOS_ID,
pp.PEDPOS_POSICAO
}).OrderByDescending(o => o.PEDPOS_ID).FirstOrDefault().PEDPOS_POSICAO,

                      \}\);
        return result;
    \}

Result:
Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 9, column 1\r\nOUTER

///////////////////////SQL generated////////////////////////
SELECT
"B"."PED_ID" AS "PED_ID",
"B"."PED_DATA" AS "PED_DATA",
"B"."PED_HORA" AS "PED_HORA",
"B"."PED_CLI_CPFCNPJ" AS "PED_CLI_CPFCNPJ",
"B"."PED_CLI_NOME" AS "PED_CLI_NOME",
"F"."PEDPOS_POSICAO" AS "PEDPOS_POSICAO"
FROM "PED" AS "B"
OUTER APPLY (SELECT FIRST (1) "C"."PEDPOS_ID" AS "PEDPOS_ID", "C"."PEDPOS_POSICAO" AS "PEDPOS_POSICAO"
FROM ( SELECT
"E"."PEDPOS_ID" AS "PEDPOS_ID",
"E"."PEDPOS_POSICAO" AS "PEDPOS_POSICAO"
FROM "PEDPOS" AS "E"
WHERE "B"."PED_ID" = "E"."PED_ID"
) AS "C"
ORDER BY "C"."PEDPOS_ID" DESC ) AS "F"

/////

Test also with Firebird 2.7.7

I hope to be helping

thank you very much

@firebird-automations
Copy link
Author

Modified by: Damasio (andersondamasio)

Attachment: METADATA.SQL [ 12160 ]

Attachment: WebApplication1.zip [ 12161 ]

@firebird-automations
Copy link
Author

Commented by: @cincuranet

OUTER APPLY (as well as CROSS APPLY) isn't supported by Firebird. And it's not easy to rewrite the query *in general* using JOINs esp. if you're inside code translating the query into SQL. More reading i.e. http://blogs.devart.com/dotconnect/entity-framework-user-request-review.html#fourth or http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ .

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: In Progress [ 3 ] => Closed [ 6 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Author

Commented by: @cincuranet

The bottom line is, that you have to rewrite your query.

@firebird-automations
Copy link
Author

Commented by: Damasio (andersondamasio)

I tried several other ways to achieve the same result, but always with an error.

Only if you had not (OrderByDescending (o => o.PEDPOS_ID)), but I need it.

unfortunately I'm in trouble.

You any alternative to a linq command that does not generate the error?

@firebird-automations
Copy link
Author

Commented by: @cincuranet

This is not a support forum. Sorry. Please ask somewhere else.

@firebird-automations
Copy link
Author

Commented by: Damasio (andersondamasio)

Okay I'm sorry. But the error in question generated by the "entity" would not be a bug in the case?

@firebird-automations
Copy link
Author

Commented by: Nick Coad (nickcoad)

Can you explain why this is marked as "Won't Fix"? The Firebird provider is generating SQL that is not supported by Firebird, surely that is a bug that warrants looking at? Disappointed to see this still unfixed after 4 years.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

I did: DNET427?focusedCommentId=25346&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#⁠action_25346

@firebird-automations
Copy link
Author

Commented by: Nick Coad (nickcoad)

Jiri, your provider is generating invalid Firebird SQL, that's the real issue. We all understand that OUTER APPLY is not supported by Firebird, and the bug we're reporting is that your provider is generating a query that contains OUTER APPLY, despite this being unsupported on Firebird.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue block progress on DNET711 [ DNET711 ]

@firebird-automations
Copy link
Author

Commented by: @cincuranet

DNET711

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

No branches or pull requests

2 participants