You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Kevin Sonney <ke...@webslingerz.com> on 2000/05/18 15:19:11 UTC
Re:
On Thu, 18 May 2000, Sebastien Koechlin I-VISION wrote:
> I have problems with sqltaglib and <sql:count-attribute>,
> My query looks like :
> SELECT DISTINCT firstname, lastname FROM status;
>
> to count rows, I think this query is translated into:
> SELECT COUNT(distinct firstname) FROM emp;
Which is valid, except the table name got changed. I'm guessing you meant
"FROM status". There isn't any code in the SQLTaglib to change the FROM
clause of a query.
> which is wrong,
> Oracle return the wrong result
> and Postgresql an error.
According to SQL92, "SELECT COUNT(DISTINCT column) FROM table;" is a legal
query. Does this work properly in the sql CLI for the databases, or using
JDBC outside of Cocoon (i.e. in a class that *JUST* runs thew
query)? AFAIK, this is perfectly legal, and if it is returning an error,
ot incorrect results, then either the Database or the JDBC driver is
possibly to blame. I can test on Oracle, but I don't happen to ahve a
postgreSQL server set up at the moment.
> This query does not work either with postgresql:
> SELECT firstname FROM emp ORDER BY firstname;
> because
> SELECT COUNT(firstname) FROM emp ORDER BY firstname;
> does : "Illegal use of aggregates or non-group column in target list",
> order by shouldn't be used.
Hmmm. Donald? This is possibly a real bug. OTOH, in order to prevent this,
you'll have to find the end of the "where" clause by checking for any
modifiers (ORDER, GOUP, etc) and cutting the substring off there.
Anyone try this type of query elsewhere (oracle, mysql, db/2) and see a
similar result, or is this just postgreSQL?
> This last one does not work too:
> SELECT emp.*;
>
> but it's not a well writen query.
Correct. a proper query is :
SELECT emp.* FROM emp;
I try not to use "SELECT table.column " clauses unless I'm querying
multiple tables, myself. I don't even think the last clause is even valid
SQL, but I could be wrong.
--
- Kevin Sonney
kevin@webslingerZ.com
Re:
Posted by Kevin Sonney <ke...@webslingerz.com>.
On Thu, 18 May 2000, Sebastien Koechlin I-VISION wrote:
> Yes, sorry, I mean
> SELECT COUNT(DISTINCT firstname) FROM status;
>
> which is wrong, example:
> FIRSTNAME LASTNAME
> bob yellow
> bob green
> joe yellow
> bob yellow
>
> SELECT DISTINCT firstname, lastname FROM status
> would return 3 rows,
> but SELECT COUNT(DISTINCT firstname) FROM status
> would return 2.
According to your data there are only two distingy firstnames, which makes
the results of the second query correct (bob and joe). However, should the
query be :
SELECT COUN(DISTINCT firstname, lastname) FROM status
you should get a return value of 3 (bob green, bob yello, and joe yellow).
Now, unless you're trying to indicate that the SQLTagLib is running
SELECT COUNT(DISTINCT firstname) FROM status;
instead of
SELECT COUNT(DISTINCT firstname, lastname) FROM status;
but I'd want to see the generated code to see how that's happening.
> But, I just look at the CSV tree and it's not the same:
> SELECT DISTINCT firstname, lastname FROM status
> is translated into
> SELECT (*) FROM status;
Which allows for all distinct row in the table.
> PostgreSQL does not allow this, the interactive client does the same
> error.
Hmm. I'll have to check the SQL docs I've got for legality. dno't think
you *CAN* order a SELECT COUNT(*) query, as it doesn't really return
multiple rows.
> It works with Oracle, but is it a valid SQL92 (or other) query ?
I can't seem to find anything to indicate ythat it is. I'll put together a
patch and pass ti along to the list(s) for testing.
--
- Kevin Sonney
kevin@webslingerZ.com
Re:
Posted by Sebastien Koechlin I-VISION <sk...@n-soft.com>.
Kevin Sonney a écrit :
>
> On Thu, 18 May 2000, Sebastien Koechlin I-VISION wrote:
> > I have problems with sqltaglib and <sql:count-attribute>,
> > My query looks like :
> > SELECT DISTINCT firstname, lastname FROM status;
> >
> > to count rows, I think this query is translated into:
> > SELECT COUNT(distinct firstname) FROM emp;
>
> Which is valid, except the table name got changed. I'm guessing you meant
> "FROM status". There isn't any code in the SQLTaglib to change the FROM
> clause of a query.
Yes, sorry, I mean
SELECT COUNT(DISTINCT firstname) FROM status;
which is wrong, example:
FIRSTNAME LASTNAME
bob yellow
bob green
joe yellow
bob yellow
SELECT DISTINCT firstname, lastname FROM status
would return 3 rows,
but SELECT COUNT(DISTINCT firstname) FROM status
would return 2.
But, I just look at the CSV tree and it's not the same:
SELECT DISTINCT firstname, lastname FROM status
is translated into
SELECT (*) FROM status;
> According to SQL92, "SELECT COUNT(DISTINCT column) FROM table;" is a legal
> query. Does this work properly in the sql CLI for the databases, or using
> JDBC outside of Cocoon (i.e. in a class that *JUST* runs thew
> query)? AFAIK, this is perfectly legal, and if it is returning an error,
> ot incorrect results, then either the Database or the JDBC driver is
> possibly to blame.
PostgreSQL does not allow this, the interactive client does the same
error.
> > This query does not work either with postgresql:
> > SELECT firstname FROM emp ORDER BY firstname;
> > because
> > SELECT COUNT(firstname) FROM emp ORDER BY firstname;
> > does : "Illegal use of aggregates or non-group column in target list",
> > order by shouldn't be used.
>
> Hmmm. Donald? This is possibly a real bug. OTOH, in order to prevent this,
> you'll have to find the end of the "where" clause by checking for any
> modifiers (ORDER, GOUP, etc) and cutting the substring off there.
>
> Anyone try this type of query elsewhere (oracle, mysql, db/2) and see a
> similar result, or is this just postgreSQL?
It works with Oracle, but is it a valid SQL92 (or other) query ?
> I try not to use "SELECT table.column " clauses unless I'm querying
> multiple tables, myself. I don't even think the last clause is even valid
> SQL, but I could be wrong.
I don't think so.
--
Sebastien Koechlin
Re:
Posted by sudhi <su...@planet.net>.
I have another question, I dont know whether it is supposed to work this
way or not.
---------------xml snippet ---------------------
<query connection="foo_connection" count-attribute="count"
id-attribute="id">
<xsp:expr>query</xsp:expr>
</query>
---------------xml snippet ---------------------
The following query works fine, where the query is some thing like this
"select * from <table>".
When I get the result back I get the count attribute in rowset.
If I change the query like this "select * from <table> limit 0,5"
It works fine (I get count attribute in rowset).
If I change the query to "select * from <table> limit 5,5".
I dont get any count attribute in rowset element.
I am using
Mysql,
Cocoon 1.7.2,
Apache Jserv1.1
Apache 1.3.12 on Windows NT
Is it a problem or Am I doing something wrong.
~Sudhi