Issue 50175 - Error in Query Design with Aliases and Sorting
Summary: Error in Query Design with Aliases and Sorting
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0 Beta
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: ocke.janssen
QA Contact: issues@dba
Keywords: oooqa
Depends on:
Reported: 2005-06-01 13:05 UTC by elchi
Modified: 2013-08-07 15:45 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

sample db showing problem with group by, aggregate function and parameter query (17.09 KB, application/vnd.sun.xml.base)
2006-01-15 15:43 UTC, alex.thurgood
no flags Details
Look at query1 (58.88 KB, application/vnd.sun.xml.base)
2006-01-17 13:18 UTC, drewjensen.inbox
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description elchi 2005-06-01 13:05:39 UTC
Create a query on a Database (in this case LDAP Adress Directory).

Select a field to be queried (Last Name).
Set an alias for this field (Nachname - german word for Last Name).
Set sorting to ascending (for this field).
Execute the query.

The data content could not be loaded.
Unknown column name!

If you remove the alias it works nicely - also if you remove the sorting it
works nicely.

It seems that if you try to give the field an alias AND try to sort the field
this error appears.
Comment 1 christoph.lukasiak 2005-06-07 15:25:22 UTC
change owner
Comment 2 christoph.lukasiak 2005-06-15 12:20:14 UTC
clu->elchi: have you tried it with an other data source type (odbc, jdbc, hsql
etc.) or does it only occure under ldap?

Comment 3 elchi 2005-06-20 06:22:23 UTC
I`m afraid i didn`t test that because we only have a LDAP-Data Source in our
(Samba 3.0 Domain w. LDAP Adress Book)
Comment 4 jcdamgaard 2005-06-21 08:46:46 UTC
I tried connecting to an Access Database with ODBC, and had exactly the same 
error. (Error message: 'too few parameters' 'expected 1')
Comment 5 Frank Schönheit 2005-06-21 08:55:44 UTC
jcdamgaard, I don't think that your problem is related, since you're talking
about a different database, a different connection type, and a different error
message. So, as a note completely unrelated to this issue here, you might want
to try
Comment 6 christoph.lukasiak 2005-09-07 11:06:31 UTC
clu->elchi: has fs hint help you further? seems not to be a bug  - can you
acknowledge that?

Comment 7 christoph.lukasiak 2005-09-27 13:36:53 UTC
no repro, no respond -> close
Comment 8 alex.thurgood 2006-01-15 15:39:31 UTC
Hmm..., try this query in a hsqldb and parameterized querying with a query
already containing an aggregate function (SUM, COUNT, AVG, etc) fails :

SELECT SUM( "collecte"."Litrage" ) AS "Litrage par mois", "collecte"."mois",
"Adhérents"."Société", "Adhérents"."Nom", "Adhérents"."Prénom",
"Adhérents"."Adresse", "Adhérents"."CP", "Adhérents"."Ville" FROM "collecte"
"collecte", "Adhérents" "Adhérents" WHERE ( "collecte"."Producteur" =
"Adhérents"."Nom" ) GROUP BY "collecte"."mois", "Adhérents"."Société",
"Adhérents"."Nom", "Adhérents"."Prénom", "Adhérents"."Adresse",
"Adhérents"."CP", "Adhérents"."Ville" HAVING ( ( "collecte"."mois" = :mois ) )

The above is the query constructed by OOo 2.0.2 (m150) when the query was built
using the GUI query builder.

In order to compare, the following queries work : 

SELECT SUM( "Litrage" ), "Cuve" FROM "collecte" "collecte" GROUP BY "Cuve"

SELECT "Date", "Producteur", "Litrage", "mois" FROM "collecte" "collecte" WHERE
( ( "mois" = :mois ) )

The sample database is included.

Setting oooqa and confirming.

Comment 9 alex.thurgood 2006-01-15 15:40:51 UTC
confirming, setting oooqa, adding myself to cc
Comment 10 alex.thurgood 2006-01-15 15:43:40 UTC
Created attachment 33231 [details]
sample db showing problem with group by, aggregate function and parameter query
Comment 11 alex.thurgood 2006-01-17 13:12:32 UTC
Target set to 2.0.2

If this can't be fixed for 2.0.2 timeframe, then it ought to be fixed in 2.0.3 

Comment 12 drewjensen.inbox 2006-01-17 13:18:45 UTC
Created attachment 33301 [details]
Look at query1
Comment 13 drewjensen.inbox 2006-01-17 13:22:21 UTC
Regarding the parameterized query, in the GUI builder you must add a second
column, from, and for, one of the grouped columns and then use the parameter on it.

I have  attached the same database named Collecte_2 with a query 'Query1' that
shows what I mean, when it is run you are prompted - enter the value 12 for muir
and all is well. This may not FIX the bug, but it is a work around.

Note it will NOT work for a LIKE comparison for some reason, unless you include
the complete columns value.

So if I a column named 'name' and a value of 'Bob'
LIKE B% would return zero records
but LIKE Bob would work, using the above the techniqe

Comment 14 christoph.lukasiak 2006-01-17 15:04:08 UTC
correct target milestone
Comment 15 christoph.lukasiak 2006-01-24 11:17:03 UTC
now i can repro that, like described in a src680m151

clu->oj: i used following LDAP source ( Hostname: ; Base
DN: dc=sun;dc=com ; Port Nr: 389)
short repro: 1. create query and choose any field from table container
2. insert an alias name, choose any sorting order and run query
->Error: The data content could not be loaded. Unknown column name!

clu-> works fine with hsql db
Comment 16 christoph.lukasiak 2006-01-24 11:21:46 UTC
clu->wurzel: this looks like an LDAP problem (does not occure with hsql this
way), so your problem is probably anything else -> please write a seperate task
for it

Comment 17 ocke.janssen 2006-01-24 11:54:09 UTC
Please take care for this one. Thanks.
Comment 18 ocke.janssen 2009-08-19 14:19:03 UTC
Comment 19 ocke.janssen 2009-08-19 14:19:44 UTC
I tried it in DEV300_m55 and it works.
Comment 20 ocke.janssen 2009-08-19 14:20:05 UTC
Closing it.