Bugs item #621270, was opened at 2002-10-10 14: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: None
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: Dirk M. Sohn (dsohn)
Date: 2002-10-21 22: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-14 08: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 16: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 23: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 10: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 emial is sponsored by: Influence the future
of Java(TM) technology. Join the Java Community
Process(SM) (JCP(SM)) program now.
http://ad.doubleclick.net/clk;4699841;7576298;k?http://www.sun.com/javavote
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
