Re: [JBoss-user] JBoss Generating Some Bad SQL (SQL Extensions Proposal)
SELECT DISTINCT x FROM tab GIVE ORDER BY y A GO, BUT DONT WORRY IF YOU GET CONFUSED, JUST MAKE THINGS A LITTLE MORE ORDERLY THAN THEY WOULD HAVE BEEN IF I DIDNT SAY ORDER BY; :-) - Original Message - From: Dave Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 10, 2003 6:24 PM Subject: Re: [JBoss-user] JBoss Generating Some Bad SQL If you do not care how it is ordered then why bother with the order by clause? The database is correct , you are not. Dain Sundstrom wrote: On Monday, February 10, 2003, at 01:53 PM, Dave Smith wrote: No, it isn't. Consider SELECT DISTINCT x FROM tab ORDER BY y; Assuming there are multiple values of y for any given x, how would you expect the result to be sorted? It's ill-defined. There is nothing ill defined by this. You want all of the distinct entries of x ordered by y. If two x values have the different y values. I simply don't care which one you choose to order by. Sure you can get non deterministic results, but there is enough information to execute the query. -dain --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
Dave that is not what I said. In the case that there happens to be two rows with the same x value and different y values, I don't care. Otherwise I do. Bases on what you have posted, how do you think we should handle this case in EJB land? Should we just throw an error saying too bad? One of the big short comings of EJB-QL is you can't select more then one field. We will be adding a the ability to select multiple columns to JBossQL in JB4, but what should we do in the mean time, and how should we treat this for EJB-QL in JB4? Anyway, I think it is completely obvious what the user wanted when he/ she wrote the query. -dain On Monday, February 10, 2003, at 08:24 PM, Dave Smith wrote: If you do not care how it is ordered then why bother with the order by clause? The database is correct , you are not. Dain Sundstrom wrote: On Monday, February 10, 2003, at 01:53 PM, Dave Smith wrote: No, it isn't. Consider SELECT DISTINCT x FROM tab ORDER BY y; Assuming there are multiple values of y for any given x, how would you expect the result to be sorted? It's ill-defined. There is nothing ill defined by this. You want all of the distinct entries of x ordered by y. If two x values have the different y values. I simply don't care which one you choose to order by. Sure you can get non deterministic results, but there is enough information to execute the query. -dain --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
User writes ORDER BY y and gets something not ordered by y, but rather by the DISTINCT's selection order on x, which can change from call to call based on activity on the tab table. That's what the user will get in the situation that x's map to multiple y's. It's may be obvious what the user wanted (an ordered set on y), but not what the user will get (a random set on x). Perhaps I've not understood the concern here, but that seems like pretty bad SQL to me. A user is always empowered to request misleading data from a database, though. That's good marketing. Anyway, I think it is completely obvious what the user wanted when he/ she wrote the query. SELECT DISTINCT x FROM tab ORDER BY y; --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
Let me restate my response. I think that the databases that do not support having columns in the order that are not in the select are lame. There is no reason why it can not figure out which column it will need to sort and add these to the select under the covers. This is simply a complaint of mine. Jeremy has already changed our sql generator to generate the sql so the lame database implementations will work. -dain On Monday, February 10, 2003, at 02:24 AM, KRALIK Vladimir wrote: I think, this is another problem. Message say : ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list Which mean, that columns used in ORDER BY-expression are not used in TARGET. So in that select : SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC Target : DISTINCT t0_s.rec_num Order by : t0_s.date_of_request I've experience with INFORMIX, and this select is wrong for INFORMIX. vlk -Original Message- From: Dain Sundstrom [mailto:[EMAIL PROTECTED]] Sent: 8. februara 2003 16:47 To: [EMAIL PROTECTED] Subject: Re: [JBoss-user] JBoss Generating Some Bad SQL We have the same problem with Oracle. I personally think this is a bug with both of the databases. --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
Send me the schema's and I'll post a question on the pg lists, asking why. On Fri, 2003-02-07 at 16:11, Hunter Hillegas wrote: I am running my dev environment on JBoss 3.2 RC1... I am using JBossQL to override a query. My JBossQL statement is as follows: SELECT DISTINCT OBJECT(s) FROM SampleRequestBean s, IN (s.lineItems) li WHERE li.fulfillmentStatus = 'Shipped' AND li.followupDate = ?1 AND (li.customerProspectFeedback IS NULL OR li.customerProspectFeedback = '') ORDER BY s.dateOfRequest DESC This generates this SQL: SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC My database (PostgreSQL 7.2 and 7.3) complains as such: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list So... Any suggestion on how I can tweak this to get the correct SQL out of JBossCMP? Gracias! Hunter --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
On Monday, February 10, 2003, at 01:53 PM, Dave Smith wrote: No, it isn't. Consider SELECT DISTINCT x FROM tab ORDER BY y; Assuming there are multiple values of y for any given x, how would you expect the result to be sorted? It's ill-defined. There is nothing ill defined by this. You want all of the distinct entries of x ordered by y. If two x values have the different y values. I simply don't care which one you choose to order by. Sure you can get non deterministic results, but there is enough information to execute the query. -dain --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
If you do not care how it is ordered then why bother with the order by clause? The database is correct , you are not. Dain Sundstrom wrote: On Monday, February 10, 2003, at 01:53 PM, Dave Smith wrote: No, it isn't. Consider SELECT DISTINCT x FROM tab ORDER BY y; Assuming there are multiple values of y for any given x, how would you expect the result to be sorted? It's ill-defined. There is nothing ill defined by this. You want all of the distinct entries of x ordered by y. If two x values have the different y values. I simply don't care which one you choose to order by. Sure you can get non deterministic results, but there is enough information to execute the query. -dain --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
My request .. For this query .. SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC I get this error. ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list This seems like a bug. Why do I need date_of_request in the select section? SQL Spec? The response .. No, it isn't. Consider SELECT DISTINCT x FROM tab ORDER BY y; Assuming there are multiple values of y for any given x, how would you expect the result to be sorted? It's ill-defined. regards, tom lane On Fri, 2003-02-07 at 16:11, Hunter Hillegas wrote: I am running my dev environment on JBoss 3.2 RC1... I am using JBossQL to override a query. My JBossQL statement is as follows: SELECT DISTINCT OBJECT(s) FROM SampleRequestBean s, IN (s.lineItems) li WHERE li.fulfillmentStatus = 'Shipped' AND li.followupDate = ?1 AND (li.customerProspectFeedback IS NULL OR li.customerProspectFeedback = '') ORDER BY s.dateOfRequest DESC This generates this SQL: SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC My database (PostgreSQL 7.2 and 7.3) complains as such: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list So... Any suggestion on how I can tweak this to get the correct SQL out of JBossCMP? Gracias! Hunter --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
We have the same problem with Oracle. I personally think this is a bug with both of the databases. I see no reason why the query processor can't figure out the correct way to form a internal query object; we can. I'll ask Jeremy if he wants to work this out in in 3.2. Otherwise you'll have to wait for 4.0. -dain On Friday, February 7, 2003, at 09:03 PM, Rod Macpherson wrote: PostgreSQL does not allow an aggregated field reference to appear in the where clause. SELECT DISTINCT t0_s.rec_num ... WHERE ... t0_s.rec_num=t1 ... Guess DISTINCT by itself is considered an aggregate rather than a pre-aggregate filter. Possible solutions: a) fix PostgreSQL so that it does not treat DISTINCT as a bona fide aggregate in an of itself; b) fix PostgreSQL so that it does not impose the aggregated column cannot appear in where clause limitation; c) remote DISTINCT from your query since rec_num ottabee unique anyway. - Original Message - From: Hunter Hillegas [EMAIL PROTECTED] To: JBoss User [EMAIL PROTECTED] Sent: Friday, February 07, 2003 1:11 PM Subject: [JBoss-user] JBoss Generating Some Bad SQL I am running my dev environment on JBoss 3.2 RC1... I am using JBossQL to override a query. My JBossQL statement is as follows: SELECT DISTINCT OBJECT(s) FROM SampleRequestBean s, IN (s.lineItems) li WHERE li.fulfillmentStatus = 'Shipped' AND li.followupDate = ?1 AND (li.customerProspectFeedback IS NULL OR li.customerProspectFeedback = '') ORDER BY s.dateOfRequest DESC This generates this SQL: SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC My database (PostgreSQL 7.2 and 7.3) complains as such: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list So... Any suggestion on how I can tweak this to get the correct SQL out of JBossCMP? Gracias! Hunter --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] JBoss Generating Some Bad SQL
PostgreSQL does not allow an aggregated field reference to appear in the where clause. SELECT DISTINCT t0_s.rec_num ... WHERE ... t0_s.rec_num=t1 ... Guess DISTINCT by itself is considered an aggregate rather than a pre-aggregate filter. Possible solutions: a) fix PostgreSQL so that it does not treat DISTINCT as a bona fide aggregate in an of itself; b) fix PostgreSQL so that it does not impose the aggregated column cannot appear in where clause limitation; c) remote DISTINCT from your query since rec_num ottabee unique anyway. - Original Message - From: Hunter Hillegas [EMAIL PROTECTED] To: JBoss User [EMAIL PROTECTED] Sent: Friday, February 07, 2003 1:11 PM Subject: [JBoss-user] JBoss Generating Some Bad SQL I am running my dev environment on JBoss 3.2 RC1... I am using JBossQL to override a query. My JBossQL statement is as follows: SELECT DISTINCT OBJECT(s) FROM SampleRequestBean s, IN (s.lineItems) li WHERE li.fulfillmentStatus = 'Shipped' AND li.followupDate = ?1 AND (li.customerProspectFeedback IS NULL OR li.customerProspectFeedback = '') ORDER BY s.dateOfRequest DESC This generates this SQL: SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s, sample_request_line_item t1_li WHERE (t1_li.fulfillment_status = 'Shipped' AND t1_li.followup_date = '2003-02-04 20:00:00.0+00' AND (t1_li.customer_prospect_feedback IS NULL OR t1_li.customer_prospect_feedback = '')) AND (t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC My database (PostgreSQL 7.2 and 7.3) complains as such: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list So... Any suggestion on how I can tweak this to get the correct SQL out of JBossCMP? Gracias! Hunter --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user --- This SF.NET email is sponsored by: SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See! http://www.vasoftware.com ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
