Issue Details (XML | Word | Printable)

Key: DNET-427
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Damasio
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Query error in Firebird

Created: 30/Apr/12 01:40 PM   Updated: 04/Nov/16 06:22 AM
Component/s: Entity Framework support
Affects Version/s: 2.7.5
Fix Version/s: None

File Attachments: 1. File METADATA.SQL (3 kB)
2. Zip Archive WebApplication1.zip (589 kB)

Environment: C# Entity Framework - Visual Studio .Net 4.0
Issue Links:
Depend
 


 Description  « Hide
"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;
        }




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 19/May/12 12:44 PM
Without the model I can hardly do anything about it.

Damasio added a comment - 19/May/12 08:48 PM
okay. I will build a model to give you.

Damasio added a comment - 25/May/12 03:31 AM
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



Jiri Cincura added a comment - 25/May/12 09:25 AM
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/ .

Jiri Cincura added a comment - 25/May/12 11:40 AM
The bottom line is, that you have to rewrite your query.

Damasio added a comment - 25/May/12 12:53 PM

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?

Jiri Cincura added a comment - 25/May/12 02:20 PM
This is not a support forum. Sorry. Please ask somewhere else.

Damasio added a comment - 25/May/12 02:25 PM
Okay I'm sorry. But the error in question generated by the "entity" would not be a bug in the case?

Nick Coad added a comment - 04/Oct/16 04:16 AM
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.


Nick Coad added a comment - 04/Nov/16 02:38 AM
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.