wrongly blocked query by "Invalid expression in the select list (not contained
in either an aggregate function or the GROUP BY clause)" for some date operation
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-3480
URL: http://tracker.firebirdsql.org/browse/CORE-3480
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.1.4
Reporter: Karol Bieniaszewski
table definition
CREATE TABLE DOC_MAG
(ID INTEGER NOT NULL PRIMARY KEY,
DATA_DOC DATE NOT NULL,
WART_NETT NUMERIC(16, 2)
)
---------------------------------------------------------
simplified query to show problem
SELECT
EXTRACT(YEAR FROM DM.DATA_DOC) AS Y, EXTRACT(MONTH FROM DM.DATA_DOC) AS
M ,
(SELECT SUM(DMP.WART_NETT)
FROM
DOC_MAG DMP
WHERE
(DMP.DATA_DOC>=
CAST(EXTRACT(YEAR FROM DM.DATA_DOC) || '-' || EXTRACT(MONTH FROM
DM.DATA_DOC) || '-01' AS DATE)
AND
/* here is the problem */
DMP.DATA_DOC<CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO DM.DATA_DOC))
|| '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO DM.DATA_DOC) ) || '-01' AS DATE)
)
/* end of problem */
) AS S
FROM
DOC_MAG DM
GROUP BY EXTRACT(YEAR FROM DM.DATA_DOC), EXTRACT(MONTH FROM
DM.DATA_DOC)
ORDER BY 1 DESC, 2 DESC
-----------------------------------------
when i run this query i got
SQL Message : -104
Invalid token
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)
-------------------------------------------------------------------------------------------
if i remove group by clause query run ok
also if i change query to this: only i change date cast from explicit string
'2011-01-01'
SELECT
EXTRACT(YEAR FROM DM.DATA_DOC) AS Y, EXTRACT(MONTH FROM DM.DATA_DOC) AS
M ,
(SELECT SUM(DMP.WART_NETT)
FROM
DOC_MAG DMP
WHERE
(DMP.DATA_DOC>=
CAST(EXTRACT(YEAR FROM DM.DATA_DOC) || '-' || EXTRACT(MONTH FROM
DM.DATA_DOC) || '-01' AS DATE)
AND
/* here is the problem */
DMP.DATA_DOC<CAST('2011-01-01' AS DATE)
)
/* end of problem */
) AS S
FROM
DOC_MAG DM
GROUP BY EXTRACT(YEAR FROM DM.DATA_DOC), EXTRACT(MONTH FROM
DM.DATA_DOC)
ORDER BY 1 DESC, 2 DESC
i suppose that this is related to DateAdd function in this query
becouse first check "DMP.DATA_DOC>=" work ok only second "DMP.DATA_DOC<" not
work
i test this also on FB.2.1.4.18408
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Achieve unprecedented app performance and reliability
What every C/C++ and Fortran developer should know.
Learn how Intel has extended the reach of its next-generation tools
to help boost performance applications - inlcuding clusters.
http://p.sf.net/sfu/intel-dev2devmay
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
