JOIN queries return SQL error code -104
---------------------------------------
Key: DNET-432
URL: http://tracker.firebirdsql.org/browse/DNET-432
Project: .NET Data provider
Issue Type: Bug
Components: ADO.NET Provider
Affects Versions: 2.7.5
Environment: Firebird Server 2.5, Firebird NETProvider 2.7.5, Firebird
DDEX Provider 2.0.5, Windows 7, Microsoft Lightswitch
Reporter: José María Sánchez
Assignee: Jiri Cincura
Priority: Critical
Hi all,
I am working on a project to create a front-end application for a Firebird
database with Microsoft Lightswitch. I am using Firebird Server 2.5, Firebird
NETProvider 2.7.5 and Firebird DDEX Provider 2.0.5. Some days ago I found a
problem that has puzzled me for several days. I think that the issue is due to
a problem in the NETProvider, but it also could be related to the information
that the DDEX provider sends to NETProvider for generating the SQL queries.
I report it because I think it may be a bug and maybe could help others to
solve a similar problem.
Simple SQL queries for screens displaying data from a single table seem to
generate correctly, but I found a problem in complex JOIN queries for screens
that display information from different related tables using Foreign Keys.
In my Firebird database I have several related tables. I include some of them
as an example.
/* Original table name is "memos" */
CREATE TABLE "memos" (
"id_memo" INTEGER CONSTRAINT "IDPK_id_memo" NOT NULL PRIMARY KEY /* "id_memo"
*/ ,
"id_text_grup" INTEGER NOT NULL /* "id_text_grup" */
);
/* Original table name is "memos_data" */
CREATE TABLE "memos_data" (
"id_memo_data" INTEGER CONSTRAINT "IDPK_id_memo_data" NOT NULL PRIMARY KEY /*
"id_memo_data" */ ,
"id_memo" INTEGER NOT NULL /* "id_memo" */ ,
"id_idioma" INTEGER NOT NULL /* "id_idioma" */ ,
"text" BLOB SUB_TYPE 1 /* "text" */
);
/* Original table name is "texts_grups" */
CREATE TABLE "texts_grups" (
"id_text_grup" INTEGER CONSTRAINT "IDPK_id_text_grup" NOT NULL PRIMARY KEY /*
"id_text_grup" */ ,
"nom_grup" VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8 /* "nom_grup" */
);
/* Original table name is "idiomes" */
CREATE TABLE "idiomes" (
"id_idioma" INTEGER CONSTRAINT "IDPK_id_idioma" NOT NULL PRIMARY KEY /*
"id_idioma" */ ,
"tag" CHAR(3) CHARACTER SET UTF8 COLLATE UTF8 /* "tag" */ ,
"descrip" VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8 /* "descrip" */
);
ALTER TABLE "memos" ADD CONSTRAINT "FK_memos_id_text_grup" FOREIGN KEY
("id_text_grup") REFERENCES "texts_grups"("id_text_grup") ON UPDATE CASCADE;
ALTER TABLE "memos_data" ADD CONSTRAINT "FK_memos_data_id_idioma" FOREIGN KEY
("id_idioma") REFERENCES "idiomes"("id_idioma") ON UPDATE CASCADE;
ALTER TABLE "memos_data" ADD CONSTRAINT "FK_memos_data_id_memo" FOREIGN KEY
("id_memo") REFERENCES "memos"("id_memo") ON UPDATE CASCADE;
The NETProvider returns the following SQL error code to my Lightswitch
application.
FirebirdSql.Data.FirebirdClient Information: 0 : Command:
SELECT
"A"."Extent1"."id_memo" AS "id_memo",
"A"."Extent1"."id_text_grup" AS "id_text_grup",
"A"."Extent2"."id_text_grup" AS "id_text_grup1",
"A"."Extent2"."nom_grup" AS "nom_grup"
FROM ( SELECT FIRST (45) SKIP (0) "C"."id_memo" AS "id_memo",
"C"."id_text_grup" AS "id_text_grup1", "D"."id_text_grup" AS "id_text_grup2",
"D"."nom_grup" AS "nom_grup"
FROM "memos" AS "C"
INNER JOIN "texts_grups" AS "D" ON "C"."id_text_grup" =
"D"."id_text_grup"
ORDER BY "C"."id_memo" ASC
) AS "A"
FirebirdSql.Data.FirebirdClient Information: 0 : Parameters:
[Microsoft.LightSwitch.DataService][Application:Error][LightSwitchApplication.SodecaCatalogDatasource:memos_All]
An exception has occurred:
Microsoft.LightSwitch.DataServiceOperationException: Se produjo un error al
ejecutar la definición del comando. Vea la excepción interna para obtener
detalles. ---> System.Data.EntityCommandExecutionException: Se produjo un error
al ejecutar la definición del comando. Vea la excepción interna para obtener
detalles. ---> FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 14
. ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 14
That query is not correct. It should be something like:
SELECT
"A"."id_memo" AS "id_memo",
"A"."id_text_grup1" AS "id_text_grup",
"A"."id_text_grup2" AS "id_text_grup1",
"A"."nom_grup" AS "nom_grup"
FROM ( SELECT FIRST (45) SKIP (0) "C"."id_memo" AS "id_memo",
"C"."id_text_grup" AS "id_text_grup1", "D"."id_text_grup" AS "id_text_grup2",
"D"."nom_grup" AS "nom_grup"
FROM "memos" AS "C"
INNER JOIN "texts_grups" AS "D" ON "C"."id_text_grup" =
"D"."id_text_grup"
ORDER BY "C"."id_memo" ASC
) AS "A"
So to get a better idea of what was going on, I downloaded the trunk source
code, studied it for some days and found a possible solution to this problem.
I am not really sure if this is the best way to solve the issue but now all is
working as expected.
The generation process for this query is something like:
1) the inner query is generated JOINING tables "C" and "D"
2) next the provider generates an intermediate query for the result of the
previous JOIN as "B"
3) finally the global query is generated. As it requires no more information
and to simplify the final query, "B" is reused and renamed to "A"
Debugging the process I saw that the information for the JoinSymbol required to
generate "A" was somehow lost in the process. That produces this issue because
the provider does not have all the internal join information ("C" and "D")
needed to create the external query for "A".
I changed the following and seem to have gotten it to work:
NETProvider\source\FirebirdSql\Data\Entity\SqlGenerator.cs
SqlSelectStatement CreateNewSelectStatement(SqlSelectStatement oldStatement,
string inputVarName, TypeUsage inputVarType, bool finalizeOldStatement,
out Symbol fromSymbol)
{
fromSymbol = null;
// Finalize the old statement
if (finalizeOldStatement && oldStatement.Select.IsEmpty)
{
List<Symbol> columns = AddDefaultColumns(oldStatement);
// Thid could not have been called from a join node.
Debug.Assert(oldStatement.FromExtents.Count == 1);
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the
next block
// of code.
JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as
JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: oldStatement.FromExtents will not do, since it
might
// just be an alias of joinSymbol, and we want an
actual JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName,
inputVarType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking
scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = columns;
newJoinSymbol.FlattenedExtentList =
oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
}
//JMS-START: THESE ARE MY LINES
if (fromSymbol == null)
{
// if the oldStatement has a join as its input, ...
// clone the join symbol, so that we "reuse" the
// join symbol. Normally, we create a new symbol - see the next
block
// of code.
JoinSymbol oldJoinSymbol = oldStatement.FromExtents[0] as
JoinSymbol;
if (oldJoinSymbol != null)
{
// Note: oldStatement.FromExtents will not do, since it might
// just be an alias of joinSymbol, and we want an actual
JoinSymbol.
JoinSymbol newJoinSymbol = new JoinSymbol(inputVarName,
inputVarType, oldJoinSymbol.ExtentList);
// This indicates that the oldStatement is a blocking scope
// i.e. it hides/renames extent columns
newJoinSymbol.IsNestedJoin = true;
newJoinSymbol.ColumnList = null;
newJoinSymbol.FlattenedExtentList =
oldJoinSymbol.FlattenedExtentList;
fromSymbol = newJoinSymbol;
}
}
//JMS-END: THESE ARE MY LINES
if (fromSymbol == null)
{
// This is just a simple extent/SqlSelectStatement,
// and we can get the column list from the type.
fromSymbol = new Symbol(inputVarName, inputVarType);
}
// Observe that the following looks like the body of
Visit(ExtentExpression).
SqlSelectStatement selectStatement = new SqlSelectStatement();
selectStatement.From.Append("( ");
selectStatement.From.Append(oldStatement);
selectStatement.From.AppendLine();
selectStatement.From.Append(") ");
return selectStatement;
}
Maybe this is not the best solution to solve the problem, but I include it as a
possible solution.
Thank you very much for this wonderful project Jiri.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
