Hi,
> sound like a database bug.
>
> Perhaps you could post a small example how to reproduce the problem?
OK, here it is:
This is my first query - select from two tables where the ID's are
controlled by a subquery (OK, this subquery doesn't make sense at
all, but I wanted to keep the example as simple as possible. I'm
using the aubquery because it has a better runtime). This query
doesn't display any results or errors:
SELECT O.OUTLET_ID
FROM REPORT R, OUTLET O
WHERE R.OUTLET_ID = O.OUTLET_ID
AND R.REPORT_ID IN ( SELECT REPORT_ID FROM REPORT WHERE DAY =
'2007-01-01')
When I replace the subquery, the SQL returns the correct rows.
SELECT O.OUTLET_ID
FROM REPORT R, OUTLET O
WHERE R.OUTLET_ID = O.OUTLET_ID
AND R.DAY = '2007-01-01'
Additionally, when I put the results from the subquery (which produces
some ID's) in the SQL, I also get the correct rows:
SELECT O.OUTLET_ID
FROM REPORT R, OUTLET O
WHERE R.OUTLET_ID = O.OUTLET_ID
AND R.REPORT_ID IN ( 100, 101, ... )
I have tried to reproduce the problem with two other tables, but
were not able to reproduce the problem.
I can reproduce the problem using the REPORT table and another table,
so the problem seems to be related to tis table.
Here are the reults of the EXPLAIN's (just in case):
EXPLAIN
SELECT U.USER_ID
FROM REPORT R, USERS U
WHERE R.PROMOTER_ID = U.USER_ID
AND R.REPORT_ID IN ( SELECT REPORT_ID FROM REPORT WHERE REPORT_ID < 200
)
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
PAGECOUNT
REPORTING REPORT REPORT001 RANGE CONDITION FOR INDEX
597
ONLY INDEX ACCESSED
REPORT_ID (USED INDEX COLUMN)
NO STRATEGY NOW (ONLY AT
EXECUTION TIME)
RESULT IS COPIED ,
COSTVALUE IS 3
EXPLAIN
SELECT U.USER_ID
FROM REPORT R, USERS U
WHERE R.PROMOTER_ID = U.USER_ID
AND R.REPORT_ID IN ( 106,107,108,109,110,111,112,113,114,115,
116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131)
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
PAGECOUNT
R RANGE CONDITION FOR KEY
1689
REPORT_ID (USED KEY COLUMN)
U´ USER_ID JOIN VIA KEY COLUMN
136
TABLE HASHED
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS
3
> Also you could try MaxDB version 7.5.0.34 which is already released
> on the download server. Maybe the bug is already fixed.
I will try this as well, thanks. Interesting enough is, that all queries
are working on the 7.5.00.05 version. Could this be a result of restoring
the 7.5.00.05 database on the 7.5.0.26 server?
Thanks,
Carsten
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
