php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #19045 SELECT DISTINCT with just ONE table doesn't work
Submitted: 2002-08-22 09:23 UTC Modified: 2003-01-20 04:00 UTC
Votes:4
Avg. Score:2.2 ± 0.8
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: christophe dot bidaux at netcourrier dot com Assigned:
Status: No Feedback Package: ODBC related
PHP Version: 4.2.2 OS: IIS4 on NT4
Private report: No CVE-ID: None
 [2002-08-22 09:23 UTC] christophe dot bidaux at netcourrier dot com
My problem is the same described in the 13167 and 7789 bug reports, but I don't know how to change a bug report status...

I have exactly the same problem with the currently last PHP version/CGI (4.2.2) : I have no result (IIS "stops" the script, and the page is never sended to the client) with an ODBC/Oracle query with a "select distinct".

But, I have something to add : the problem appears with a query with just ONE table in the "select distinct", but not, in the same conditions with a query with two tables linked in the "select distinct".

----------
$gp=odbc_pconnect("INTRANET gp","user","password");
$requete="(?)";
$db_query=odbc_exec($gp,$requete);
odbc_fetch_into($db_query,$db_array);

with (?)="select distinct POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE", it doesn't work.

with (?)="select POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE" or (?)="select distinct GROUPE, POSTE, LBLPOSTE from TEMPSCUMULES, GROUPE_MACHINE where POSTE=MACHINE and SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by GROUPE, POSTE", it works fine.

The driver is "Oracle ODBC Driver 8.00.06.00"

Merci.

Christophe

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-08-28 22:52 UTC] kalowsky@php.net
i don't see a select distinct happening on one table here, are you sure the proper code is documented below?
 [2002-08-29 02:45 UTC] christophe dot bidaux at netcourrier dot com
For understanding my examples, you have to replace (?) in '$requete="(?)";' by the query string given below.

The first query string is a select distinct with one table (TEMPSCUMULES), and it doesn't work. The second one is a select (no distinct) with one table, and it works fine . And the last one is a select distinct with two tables (TEMPSCUMULES and GROUPE_MACHINE), and it works fine too.
 [2002-08-29 16:52 UTC] kalowsky@php.net
$stmt1 ="select distinct POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by POSTE", it doesn't work.

$stmt2 ="select POSTE, LBLPOSTE from TEMPSCUMULES where SOCIETE='001'and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and
'20020731' order by POSTE" 

$stmt3 ="select distinct GROUPE, POSTE,
LBLPOSTE from TEMPSCUMULES, GROUPE_MACHINE where POSTE=MACHINE and
SOCIETE='001' and ATELIER='40' and to_char(JOURNEE,'YYYYMMDD') between '20020701' and '20020731' order by GROUPE, POSTE"

so $stmt2, and $stmt3 work fine.

Have you tried with a simplier select query?  
 [2002-08-30 03:10 UTC] christophe dot bidaux at netcourrier dot com
it doesn't work with :

$requete="select distinct POSTE from TEMPSCUMULES";

and it works with :

$requete="select POSTE from TEMPSCUMULES";
 [2002-08-31 00:17 UTC] kalowsky@php.net
Interestingly enough some digging into this results in the following data:
The ODBC standard exec only supports the following options for a SELECT statement:  FROM, WHERE, GROUP BY, HAVING, UNION, and ORDER BY.  

So technically doing a SELECT DISTINCT shouldn't ever work.  Although your example seems to disprove this theory.  I'm going to have to do more looking into this as I get time... as I don't think this should be an issue.  It should be a SQL Driver issue, not an ODBC Driver issue.  
 [2002-10-02 12:28 UTC] kalowsky@php.net
Any chance you can re-do this with logging turned on?  I did some research into this, and believe I've figured it out.... although I'd like to see if your actions fall into the same series of steps as mine.
 [2002-10-03 01:40 UTC] christophe dot bidaux at netcourrier dot com
how I can turn the logging on ?
 [2002-10-03 07:48 UTC] kalowsky@php.net
Open your ODBC Administrator and there should be tab that has a little check box that says "Turn Logging On" I believe it's on the same tab as the Pooling information.   
 [2002-10-04 07:03 UTC] christophe dot bidaux at netcourrier dot com
I didn't find any tab with 'logging on' checkbox in ODBC Administrator program ; so, I changed the value of the 'Driver Logging' parameter to 17 in the oraodbc.ini file.

I hope it can help you...

Merci.


// logging results when the request works fine (with two joined tables)

Oracle ODBC 32 Bit Driver Version         08.00.6000
Oracle ODBC 32 Bit Driver File Version    08.00.6000

....0X0013BA50: php                 0X00000000
....0X0013F078: php                 0X00000000
....0X0013F8D0: php                 0X00000000
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES, GROUPE_MACHINE where poste=machine
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES, GROUPE_MACHINE where poste=machine
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES, GROUPE_MACHINE where poste=machine
....0X0013FC70: Rows Fetched =      0X0000006E


// logging results when the request doesn't work fine (with one table)

Oracle ODBC 32 Bit Driver Version         08.00.6000
Oracle ODBC 32 Bit Driver File Version    08.00.6000

....0X0013BA50: php                 0X00000000
....0X0013F078: php                 0X00000000
....0X0013F8D0: php                 0X00000000
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES
....0X0013FC70: SELECT ROWID from TEMPSCUMULES
....0X0013FC70: SELECT ROWID,distinct SOCIETE, POSTE from TEMPSCUMULES WHERE ROWID=''
....0X0013FC70: select distinct SOCIETE, POSTE from TEMPSCUMULES
 [2002-11-15 18:11 UTC] kalowsky@php.net
Any chance you can check to see if this is still happening for you with the 4.3RC?

 [2002-11-26 19:59 UTC] sniper@php.net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.


 [2002-12-09 11:06 UTC] christophe dot bidaux at netcourrier dot com
I made the test again with PHP Version 4.3.0RC2, and I have the same results ; with two tables it works, but not with one.
 [2003-01-06 12:32 UTC] kalowsky@php.net
Can you send me a sample schema/dataset to immitate your behavior exactly?  
 [2003-01-20 04:00 UTC] sniper@php.net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.


 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Oct 15 10:01:27 2024 UTC