Thread

Bug in subselect

pierluigi.frullani@frumar.it — 1999-03-03T19:29:45Z

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :        Pierluigi Frullani
Your email address      :        pigi@frumar.it


System Configuration
---------------------
  Architecture (example: Intel Pentium)         :  Intel Pentium
  Architecture       Other                      :  Ultra 250 

  Operating System (example: Linux 2.0.26 ELF)  :  Linux 2.0.33  ELF
  Operating System                              :  Solaris 2.6  

  PostgreSQL version (example: PostgreSQL-6.4.2)  :   PostgreSQL-6.4.2
  PostgreSQL version                              :   PostgreSQL-6.3

  Compiler used (example:  gcc 2.8.0)           : gcc 2.7.2.3
  For solaris we used the bin distrib.


Please enter a FULL description of your problem:
------------------------------------------------
In a select with a sub select if you use a true condition as " AND 1 = 1 "
in the 6.4.2. you will get no result and in the 6.3 you will get coredump

This is needed when we compose the query string and we have no other condition.
In informix and oracle it work.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------
 we use this select :

SELECT chiamate.n_chiam,  chiamate.probl,  clienti.rag_soc,
       chiamate.contatto,  chiamate.severita,  
       tabseverita.desseverita,  esecutori.cognome,  
       stato_chiamata.des_stato ,  chiamate.data_aper,  
       tabseverita.colore ,  chiamate.dta_primo_cont  
FROM chiamate,chiamatedet,commesse,esecutori,clienti, 
     tabseverita,stato_chiamata  
WHERE chiamatedet.n_chiam = chiamate.n_chiam  
 AND chiamatedet.data_ult =  (select max(b.data_ult)  
                                from chiamatedet  b
                                where b.n_chiam= chiamate.n_chiam
                                AND 1 = 1)        <=== here the problem.
 AND chiamate.cod_com = commesse.cod_com 
 AND chiamatedet.stato_chiamata=stato_chiamata.cod_stato 
 AND chiamatedet.cod_ese = esecutori.cod_ese  
 AND commesse.cod_soc_cli = clienti.cod_societa  
 AND tabseverita.codseverita = chiamate.severita  
 AND stato_chiamata.flag_stato='O'  
ORDER BY chiamate.n_chiam

If we do not use the showed condition the select will work .

Here attached there is the dump of our database