#2038 Error level severe raised from table reference

Milestone: R2.5.2

Status: closed-works-for-me

Owner: Jorg Janke

Labels: Application Dictionary (310)

Priority: 5

Updated: 2007-04-12

Created: 2005-11-15

Creator:

Private: No

Version 2.5.2e

Hello,
I created two table references based on a view with a
FROM clause in the WHERE clause of the table reference
that cause a severe error message in the log while there is
nothing wrong. This is causing confusion.
I created two versions, but both raise the same error.
When I looked in the code in the file
compiere-all\dbPort\src\org\compiere\model\
AccesSqlParser.java in the method getTableInfo then I saw
that when there is another FROM in the sql statement this
error is logged as a severe error.
The query is ok and working in the window so maybe it is
better to remove this or log with a lower level, since it is not
useful information and users think something is not working
properly.

Code

if (fromIndex != sql.lastIndexOf(FROM))
log.log
(Level.SEVERE, "getTableInfo - More than one FROM
clause - " + sql);

Error messages

With subclause

*** 2005-11-15 11:35:29.827 Compiere Log (CLogConsole)
***
11:35:29.827 Compiere.startup: Compiere(r) Release
2.5.2e_2005-08-01 - Smart ERP & CRM - (c) 1999-2005
Compiere (r); Implementation: 2.5.2e 20050805-1127 -
ComPiere Inc. (C) 1999-2005 Jorg Janke
11:35:29.847 Compiere.startup: C:\Compiere2\ - Java
HotSpot(TM) Client VM 1.4.2_04-b04 - Windows XP 5.1
Service Pack 2
11:35:30.968 Ini.loadProperties: debbie.properties #31
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r WHERE ur.AD_Role_ID in (##)) ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r WHERE ur.AD_Role_ID in (##)) [12]
===========> MRole.addAccessSQL: TableName not
correctly parsed - TableNameIn=XX_HTC1_EMPLOYEE_V -
AccessSqlParser
[AD_Role=r|XX_HTC1_EMPLOYEE_V=WHERE|1] - #1
XX_HTC1_EMPLOYEE_V=WHERE
= SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur WHERE
ur.AD_Role_ID in (SELECT r.AD_Role_ID FROM AD_Role
r WHERE r.Name like '%P-
Manager%')) ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r WHERE ur.AD_Role_ID in (##)) ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r WHERE ur.AD_Role_ID in (##)) [12]
===========> MRole.addAccessSQL: TableName not
correctly parsed - TableNameIn=XX_HTC1_EMPLOYEE_V -
AccessSqlParser
[AD_Role=r|XX_HTC1_EMPLOYEE_V=WHERE|1] - #1
XX_HTC1_EMPLOYEE_V=WHERE
= SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur WHERE
ur.AD_Role_ID in (SELECT r.AD_Role_ID FROM AD_Role
r WHERE r.Name like '%Fi
eld Manager%')) ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]

With Join

===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r INNER JOIN AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID
WHERE r.Name like '%P-Manager%') ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]
===========> AccessSqlParser.getTableInfo: parse -
could not remove ON XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur, AD_Role r ON
ur.AD_Role_ID=r.AD_Role_ID [12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r INNER JOIN AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID
WHERE r.Name like '%P-Manager%') [12]
===========> AccessSqlParser.getTableInfo: parse -
could not remove ON XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur, AD_Role r ON
ur.AD_Role_ID=r.AD_Role_ID [12]
===========> MRole.addAccessSQL: TableName not
correctly parsed - TableNameIn=XX_HTC1_EMPLOYEE_V -
AccessSqlParser
[XX_HTC1_EMPLOYEE_V=WHERE,AD_Role=r|0] - #1
XX_HTC1_EMPLOYEE_V=WHERE
= SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur INNER JOIN
AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID WHERE
r.Name like '%P-Manager%
') ORDER BY XX_HTC1_EMPLOYEE_V.C_BPartner_Name
[12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r INNER JOIN AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID
WHERE r.Name like '%Field Manager%') ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]
===========> AccessSqlParser.getTableInfo: parse -
could not remove ON XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur, AD_Role r ON
ur.AD_Role_ID=r.AD_Role_ID [12]
===========> AccessSqlParser.getTableInfo:
getTableInfo - More than one FROM clause - SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r INNER JOIN AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID
WHERE r.Name like '%Field Manager%') [12]
===========> AccessSqlParser.getTableInfo: parse -
could not remove ON XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur, AD_Role r ON
ur.AD_Role_ID=r.AD_Role_ID [12]
===========> MRole.addAccessSQL: TableName not
correctly parsed - TableNameIn=XX_HTC1_EMPLOYEE_V -
AccessSqlParser
[XX_HTC1_EMPLOYEE_V=WHERE,AD_Role=r|0] - #1
XX_HTC1_EMPLOYEE_V=WHERE
= SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles ur INNER JOIN
AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID WHERE
r.Name like '%Field Mana
ger%') ORDER BY
XX_HTC1_EMPLOYEE_V.C_BPartner_Name [12]

Discussion

Kathy Pink - 2005-11-15

assigned_to: nobody --> jjanke
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2005-11-16

status: open --> pending
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2005-11-16

Logged In: YES
user_id=87038

Just give me the "raw" statement to be processed. We
certainly handle that and the most likely error causes are
improper syntax (i.e. all SQL reserved Upper Case all data
base names mixed case)

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2005-11-21

Logged In: YES
user_id=1262135

The raw sql is in the error message, but it is not improper
sql syntax. Everything works. But Compiere is checking the
syntax for extra FROM keywords and then logs a SEVERE error
message which is also listed in Preference, Errors
So there not a real error, but Compiere treats it as an
error.

if this line is removed from the code :

if (fromIndex != sql.lastIndexOf(FROM))
log.log
(Level.SEVERE, "getTableInfo - More than one FROM
clause - " + sql);

then the problem is solved

The raw SQL:

SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_
EMPLOYEE_V.C_BPARTNER_NAME,XX_HTC1_EMPLOYE
E_V.IsActive FROM XX_HTC1_EMPLOYEE_V WHERE
XX_HTC1_EMPLOYEE_V.AD_User_ID IN ( SELECT
ur.AD_User_ID FROM AD_User_Roles u
r INNER JOIN AD_Role r ON ur.AD_Role_ID=r.AD_Role_ID
WHERE r.Name like '%Field Manager%')

XX_HTC1_EMPLOYEE_V is an SQL view:

CREATE OR REPLACE VIEW XX_HTC1_EMPLOYEE_V
(AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY,
UPDATED, UPDATEDBY, C_BPARTNER_ID, C_BPARTNER_NAME,
C_BPARTNER_SEARCHKEY,
C_BP_GROUP_ID, AD_USER_ID, AD_USER_NAME, S_RESOURCE_ID,
S_RESOURCE_NAME,
M_PRODUCT_ID, M_PRODUCT_NAME)
AS
SELECT bp.AD_Client_ID, bp.AD_Org_ID, bp.IsActive,
bp.Created, bp.CreatedBy, bp.Updated, bp.UpdatedBy,
bp.C_BPartner_ID,bp.Name,bp.Value,bp.C_BP_Group_ID,
u.AD_User_ID,u.Name,
r.S_Resource_ID,r.Name,
p.M_Product_ID,p.Name
FROM C_BPARTNER bp
INNER JOIN AD_USER u ON
(u.C_BPartner_ID=bp.C_BPartner_ID)
INNER JOIN S_RESOURCE r ON (u.AD_User_ID=r.AD_User_ID)
INNER JOIN M_PRODUCT p ON
(r.S_Resource_ID=p.S_Resource_ID)
WHERE bp.IsEmployee='Y'
/

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2005-11-21

status: pending --> open
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2005-11-21

milestone: 471899 --> R2.5.2
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2005-11-21

Logged In: YES
user_id=87038

Should work. Could you try to re-word the statement to use
EXISTS rather then IN and try it again. This is just
double checking to see if the main table is correctly
identified.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2005-11-22

Logged In: YES
user_id=1262135

Same thing, but - again - it's only the severe message in
the console and the preference>errors list that is
disturbing and confusing.
The query itself works and the listbox in the window
displays the right results.
So i think the best thing is to remove this check from the
code or change it to a warning or info level.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2005-11-25

Logged In: YES
user_id=1262135

The other line comes from MRole method addAccessSQL in
dbport.
I have unquoted it.

// Use First Table
String tableName = "";
if (ti.length > 0)
{
tableName = ti[0].getSynonym();
if (tableName.length() == 0)
tableName = ti
[0].getTableName();
}
if (TableNameIn != null && !tableName.equals
(TableNameIn))
{
// String msg = "TableName not
correctly parsed - TableNameIn="
// + TableNameIn + " - " + asp;
// if (ti.length > 0)
// msg += " - #1 " + ti[0];
// msg += "\n = " + SQL;
// log.log(Level.SEVERE, msg);
// Trace.printStack();
tableName = TableNameIn;
}

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kathy Pink - 2005-11-28

labels: --> Application Dictionary
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2006-01-06

status: open --> closed-works-for-me
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Jorg Janke - 2006-01-06

Logged In: YES
user_id=87038

The sql parses correctly, if you use "proper" syntax - i.e.
(SELECT .. not ( SELECT and ON ():

SELECT
XX_HTC1_EMPLOYEE_V.C_BPartner_ID,NULL,XX_HTC1_EMPLOYEE_V.C_B
PARTNER_NAME,XX_HTC1_EMPLOYEE_V.IsActive FROM
XX_HTC1_EMPLOYEE_V WHERE XX_HTC1_EMPLOYEE_V.AD_User_ID IN
(SELECT ur.AD_User_ID FROM AD_User_Roles ur INNER JOIN
AD_Role r ON (ur.AD_Role_ID=r.AD_Role_ID) WHERE r.Name
like '%Field Manager%')

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2006-01-24

Logged In: YES
user_id=1262135

What kind of SQL standard is the proper syntax?
Is Compiere ANSI SQL compliant?
Looks like it's a sort of subset of ANSI SQL with
undocumented restrictions which makes it difficult to test
in another SQL builder.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Anonymous - 2006-01-24

status: closed-works-for-me --> open-works-for-me
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Kathy Pink - 2007-04-12

status: open-works-for-me --> closed-works-for-me
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Error level severe raised from table reference

Group

Searches

Help

#2038 Error level severe raised from table reference

Code

Error messages

Discussion