Issue 19884 - mysql column name alias and filter trouble
Summary: mysql column name alias and filter trouble
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1 RC
Hardware: PC Windows 2000
: P3 Trivial (vote)
Target Milestone: OOo 2.0
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-09-22 09:51 UTC by Unknown
Modified: 2006-05-31 14:29 UTC (History)
1 user (show)

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

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description Unknown 2003-09-22 09:51:16 UTC 
Hi,
I have a problem with filtering a data-source. I am using the column alias for 
all record fields. (SELECT COLNAME AS ALIAS_COLNAME)when i want to set some 
filter rules, e.g. zip>=97000 AND zip<98000 it doesn't work. The filter 
probably creates a new SELECT-statement and adapts the WHERE clause, since an 
error pops up which reads: 
"unknown column 'ALIAS_COLNAME'"
I am using mysql-connector-java-3.0.8-stable-bin in the programm\classes 
directory.(although it seems to be a plain name mapping problem, since sorting 
by name or autofilter works perfectly)

Mirko Schmitt
Comment 1 Frank Schönheit 2003-09-22 10:03:22 UTC 
correcting sub component (see
http://www.openoffice.org/issues/describecomponents.cgi?component=Database%20access,
please), and default owner
Comment 2 marc.neumann 2003-09-22 11:24:05 UTC 
Hi Mirko,

First a (just for info) a part from the mysql documentation.  
>>>>>><<<<<<
A.5.4 Problems with alias

You can use an alias to refer to a column in the GROUP BY, ORDER BY,
or in the HAVING part. Aliases can also be used to give columns better
names:

SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;

Note that standard SQL doesn't allow you to refer to an alias in a
WHERE clause. This is because when the WHERE code is executed the
column value may not yet be determined. For example, the following
query is illegal:

SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be
included in the GROUP BY part while HAVING is used to decide which
rows from the result set should be used. 

>>>>>><<<<<<

However there is a problem with the filter because it use the where
clause with the alias name.

So I send it to the right developer, set the target and set it to new.

Bye Marc
Comment 3 ocke.janssen 2003-09-24 14:42:34 UTC 
.
Comment 4 marc.neumann 2003-10-16 08:28:49 UTC 
.
Comment 5 ocke.janssen 2004-01-28 13:58:50 UTC 
.
Comment 6 ocke.janssen 2004-01-29 12:28:27 UTC 
fixed in cws oj08
Comment 7 hans_werner67 2004-02-02 12:33:27 UTC 
change subcomponent to 'none'
Comment 8 ocke.janssen 2004-02-02 14:07:15 UTC 
.
Comment 9 marc.neumann 2004-03-01 15:16:20 UTC 
not fixed in OJ08
Comment 10 marc.neumann 2004-03-01 15:16:43 UTC 
reassign to OJ
Comment 11 marc.neumann 2004-03-01 15:17:08 UTC 
reassign to OJ
Comment 12 ocke.janssen 2004-03-02 08:08:01 UTC 
Back to you. You found just another bug. :-)
Comment 13 marc.neumann 2004-03-02 08:11:36 UTC 
set to fixed
Comment 14 marc.neumann 2004-03-02 08:12:33 UTC 
verify in CWS oj08
This 'filter' works now, however there is still the other bug for the sort trouble.
Comment 15 marc.neumann 2004-03-02 08:27:21 UTC 
the new sort issue is issue 26031
Comment 16 marc.neumann 2004-04-27 10:00:30 UTC 
Hi,

fixed in current developer build -> close.
The current developer build can be found at
http://download.openoffice.org/680/index.html
Feel free to reopen if this issue is not fixed in the developer build.

Bye Marc