Issue Details (XML | Word | Printable)

Key: CORE-1915
Type: Bug Bug
Status: Closed Closed
Resolution: Duplicate
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Weverton Gomes
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Problem when selecting a view who has subquery

Created: 26/May/08 03:27 PM   Updated: 18/Oct/16 06:04 PM
Component/s: Engine
Affects Version/s: 2.1.0
Fix Version/s: None

Environment: Pentium Core 2 Duo 2.2 Ghz, 2GB Ram, Windows XP SP2, Firebird 2.1.0.17798 running as application on TCP port 3052.
Issue Links:
Duplicate
 

QA Status: No test


 Description  « Hide
I created a view who has a subquery for the field 'VDTAENTSAIMOV':

CREATE VIEW VIEMOVCTBMOV1 (
  VDTAENTSAIMOV,
  VCODEMPMAT,
  VCODEMP,
  VSEQMOVCBT,
  VSEQMOV,
  VMESANOEXEMOV,
  VCODLANPAD,
  VAGRLANPAD,
  VANOEXECNT,
  VVLRCTBMOVCTB,
  VCIFFOBFREMOV,
  VCNTLCX,
  VSEQMATMOVCTB,
  VNUMMOV,
  VNUMTITMOVPGRREC,
  VDTAPAGMOV
)
AS
select
      (SELECT CASE WHEN M.tipserval = 'E'
                   THEN M.dtaentsaimov
                   ELSE M.dtaemimov
              END
       FROM TBLMOV M
       WHERE M.CODEMP = C.CODEMP
       AND M.SEQMOV = C.SEQMOV
       AND M.MESANOEXEMOV = C.MESANOEXEMOV),
       C.codempmat,
       C.codemp,
       C.seqmovcbt,
       C.seqmov,
       C.mesanoexemov,
       C.codlanpad,
       (SELECT P.agrlanpad
        FROM TBLLANPAD P
        WHERE P.CODEMP = C.CODEMP
        AND P.ANOEXECNT = C.ANOEXECNT
        AND P.CODLANPAD = C.CODLANPAD),
       C.anoexecnt,
       C.vlrctbmovctb,
       C.ciffobfremov,
       C.cntlcx,
       COALESCE(C.seqmatmovctb,C.seqmovcbt),
       (SELECT M1.nummov
        FROM TBLMOV M1
        WHERE M1.CODEMP = C.CODEMP
        AND M1.SEQMOV = C.SEQMOV
        AND M1.MESANOEXEMOV = C.MESANOEXEMOV),
       C.NUMTITMOVPGRREC,
       C.dtapgtmov
FROM TBLMOVCTB C;

When I try to execute the query below, system gives me the following error "Unsupported field type specified in BETWEEN predicate.":

SELECT DISTINCT V.vSeqMatMovCtb, V.vCodEmpMat
      FROM VIEMOVCTBMOV1 V
      WHERE V.vCodEmp = :viCodEmp
        AND (V.vDtaEntSaiMov BETWEEN '01.01.2008' AND '31.01.2008'
         OR V.vDtaPagMov BETWEEN '01.01.2008' AND '31.01.2008')
        AND V.vAgrLanPad <> 'O'

If I exchange the subquery for a normal field, the query above works normally. I already has tried put a CAST AS DATE on subquery, but didn't work too. This works fine on Firebird 1.5 and 2.0.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 26/May/08 03:44 PM
I don't see where "vDtaEntSaiMov " is defined in the View...


Weverton Gomes added a comment - 26/May/08 03:55 PM
"vDtaEntSaiMov" is the first field of view. Maybe you didn't see because it is in capital letters on DDL (VDTAENTSAIMOV).

Sean Leyne added a comment - 26/May/08 04:30 PM
What are the data types for the "M.dtaentsaimov" and "M.dtaemimov" columns?

Dmitry Yemanov added a comment - 26/May/08 11:23 PM
This issue duplicates CORE-1095.

Are you 100% sure it works on v1.5 and v2.0? I'm asking because subqueries were never allowed for BETWEEN.

Weverton Gomes added a comment - 27/May/08 07:46 AM
Sean, the type of columns is DATE.

Dmitry, yes I'm sure, because we have used this for a long time. I think before FB executed the query on view results and, there, we have the field and don't the subquery anymore.