Bugs item #621270, was opened at 2002-10-10 05:07
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=621270&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Open
Resolution: Accepted
Priority: 8
Submitted By: Alexei Yudichev (sflexus)
Assigned to: Nobody/Anonymous (nobody)
Summary: invalid SQL is generated for MSSQL
Initial Comment:
I have the following EJBQL expression:
SELECT
DISTINCT OBJECT(c) FROM StoreCategory c, IN (c.locales) l
WHERE l.code=?1 AND c.images IS NOT EMPTY
which
for postgresql translates into:
Executing SQL: SELECT
DISTINCT t0_c.id FROM storecategory t0_c, mmslocale t1_l,
storecategory_locales_m_18q3els
t2_c_locales_RELATION_TABLE, mmsimage t3_c_images,
mmsimage_storecategorie_1diogue
t4_c_images_RELATION_TABLE WHERE (t1_l.code = ? AND
TRUE) AND
(t0_c.id=t4_c_images_RELATION_TABLE.StoreCategory
AND
t3_c_images.id=t4_c_images_RELATION_TABLE.MMSImage
AND
t0_c.id=t2_c_locales_RELATION_TABLE.StoreCategory
AND
t1_l.code=t2_c_locales_RELATION_TABLE.MMSLocale)
and
executes normally.
But for MSSQL I get:
SELECT
DISTINCT t0_c.id FROM StoreCategory t0_c, MMSLocale t1_l,
StoreCategory_locales_MMSLocale_storeCategories
t2_c_locales_RELATION_TABLE, MMSImage t3_c_images,
MMSImage_storeCategories_StoreCategory_images
t4_c_images_RELATION_TABLE WHERE (t1_l.code = ? AND
1) AND
(t0_c.id=t4_c_images_RELATION_TABLE.StoreCategory
AND
t3_c_images.id=t4_c_images_RELATION_TABLE.MMSImage
AND
t0_c.id=t2_c_locales_RELATION_TABLE.StoreCategory
AND
t1_l.code=t2_c_locales_RELATION_TABLE.MMSLocale)
which
leads to
java.sql.SQLException: [Microsoft][SQLServer
2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near
')'.
Why not using "AND 1=1" instead of "AND 1"?
----------------------------------------------------------------------
Comment By: Jeremy Boynes (jboynes)
Date: 2002-11-13 11:29
Message:
Logged In: YES
user_id=378919
"I bet the current implementation will generate something
that works more like an AND instead of an OR."
It generates (sorry about changing the names):
SELECT t0_p.id
FROM Parent t0_p, Child t1_p_children
WHERE (1 OR t0_p.value = ?) -- <---- oops
AND (t0_p.id=t1_p_children.parent)
which basically makes the condition irrelevant.
Generating a subquery by default seems less problematic and
resulted in the same execution plan (at least on Oracle).
One solution for MySQL would be to always use a LEFT JOIN
(rather than the current INNER JOIN) and check NULL/NOT NULL
for a field from the child's PK:
SELECT DISTINCT t0_p.id
FROM Parent t0_p
LEFT JOIN Child t1_p_children
ON (t1_p_children.parent = t0_p.id)
WHERE (t0_p.value = ?)
OR (t1_p_children.id IS NOT NULL)
Not the most performant solution but I think it works for
all cases - it would be possible though to convert back to
the inner join if the compiler could detect there was no OR
condition present.
I can work on a patch to do this if it would help.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-11-13 09:26
Message:
Logged In: YES
user_id=251431
You're right. Alex is going to put in '(1=1)' instead of
the true mapping for IS NOT EMPTY.
I think this will work for a temporary solution, but I now
believe more than ever that the current IS NOT EMPTY
generated SQL is broken. I bet the following query will not
generate the expected results:
SELECT OBJECT(o)
FROM order o
WHERE o.lineItems IS NOT EMPTY
OR o.total > 0
I bet the current implementation will generate something
that works more like an AND instead of an OR. The IS EMPTY
operator was really designed for a sub query and the default
should be to generate a sub query regardless of the presence
of a NOT. Of course this creates a problem for mySQL, so
we'll have to code around that.
----------------------------------------------------------------------
Comment By: Jeremy Boynes (jboynes)
Date: 2002-10-31 11:16
Message:
Logged In: YES
user_id=378919
I might be confused here but I don't think the IS NOT EMPTY
clause should be using the value from <true-mapping>
The root of this seems to be the lack of a boolean datatype
in standard SQL allowing vendors to implement it differently
(e.g. as NUMBER(1) in Oracle, BIT in MS SQL, BOOLEAN in
PostgreSQL). The <true-mapping> parameter is needed to map
Java Boolean values to a literal of whatever datatype is
being used in the db.
SQL does define boolean operators but they take boolean
expressions and as there are no standard boolean literals
you can't write "AND TRUE".
The current JDBCEJBQLCompiler needs to do this for the IS
NOT EMPTY clause. This can't be the value defined by
<true-mapping> for assignment as, strictly, that is not a
boolean expression (except for db's like PostgreSQL that
have such an extension). Instead it should be a SQL boolean
expression e.g. "(1=1)"
Three solutions come to mind:
* Make the JDBCEJBQLCompiler smarter so it doesn't generate
the AND clause at all for IS NOT EMPTY
* Add a new entry to the metadata allowing a boolean
expression evaluating to true to be defined per database.
* Have JDBCEJBQLCompiler emit a constant SQL expression for
the IS NOT EMPTY case: "(1=1)" works for any database I can
think of even if it looks ugly. This seems simplest (one
line change to JDBCEJBQLCompiler)
With any of these, the <true-mapping> for MS SQL should
remain as 1 for compatibility with BIT columns. Unlike
MySQL, in MS SQL BIT is not comptible with boolean
expressions so
select * from x where aBitColumn = (1=1)
insert x values((1=1))
update x set aBitColumn = (1=1)
all fail
----------------------------------------------------------------------
Comment By: Dirk M. Sohn (dsohn)
Date: 2002-10-21 13:03
Message:
Logged In: YES
user_id=595100
He was right due to my tests.
I had the same problem with a NOT EMPTY over a relation
and MS SQLSERVER 2000.
JBoss-3.0.0 generated AND (TRUE)
JBoss-3.0.3 genetared AND (1)
but
AND (1=1) does the work.
I changed <true-mappping> in standardjbosscmp-jdbc.xml to
<type-mapping>
<name>MS SQLSERVER2000</name>
<true-mapping>(1=1)</true-mapping>
and it works now.
There migth be the same problems in Bug #626468 too.
----------------------------------------------------------------------
Comment By: Alexei Yudichev (sflexus)
Date: 2002-10-13 23:19
Message:
Logged In: YES
user_id=345880
I've just tested
SELECT * FROM LIST$COMP_SUBCATS WHERE
SUBCATEGORYID1=(1=1)
And got "Line 1: Incorrect syntax near
'='" error message. So seems ejbql compiler needs to be redesigned a bit
in order to distinguish booleans as in the original query (WHERE 1) and
booleans as in this query (WHERE SUBCATEGORYID1=(1=1)).
----------------------------------------------------------------------
Comment By: Alexey Loubyansky (loubyansky)
Date: 2002-10-12 07:42
Message:
Logged In: YES
user_id=543482
Have you really tested it with MS SQLSERVER and has it
failed?
I don't have MS SQLSERVER to test. But I've tested it
against MySQL that have true-mapping the same as MS
SQLSERVER does. And it works fine:
select * from document where sent=(1=1)
Field 'sent' is boolean and represented in the db as '1'.
----------------------------------------------------------------------
Comment By: Alexei Yudichev (sflexus)
Date: 2002-10-11 14:50
Message:
Logged In: YES
user_id=345880
Isn't the same boolean mapping used for dealing with boolean types in
queries like
SELECT ID FROM FOO WHERE
SOME_BOOLEAN_FIELD=?1
?
If so, substituting (1=1) will
crash such queries:
SELECT ID FROM FOO WHERE
SOME_BOOLEAN_FIELD=(1=1)
That's why I posted this as a bug.
----------------------------------------------------------------------
Comment By: Alexey Loubyansky (loubyansky)
Date: 2002-10-11 01:20
Message:
Logged In: YES
user_id=543482
If I got you right, it's the problem with true-mapping.
Currently, for MS SQLSERVER:
<true-mapping>1</true-mapping>
<false-mapping>0</false-mapping>
I am not familiar with MS SQLSERVER but if you are sure it
should be
<true-mapping>(1=1)</true-mapping>
<false-mapping>(1=0)</false-mapping>
like for Hypersonic, I'll fix it.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=621270&group_id=22866
-------------------------------------------------------
This sf.net email is sponsored by: Are you worried about
your web server security? Click here for a FREE Thawte
Apache SSL Guide and answer your Apache SSL security
needs: http://www.gothawte.com/rd523.html
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
