Still getting duplicate fields in group by with expressions and error if using union also

[primozcerar]

Describe your issue

I saw that some bug fixes regarding duplicate fields were included in latest releases and they have fixed some errors where using conditional statements in group by. I have now come across a query that uses date part extraction in the group by clause and it still produces duplicate fields. When combining this query with another using UnionAll it produces an exception.

Exception 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)

Stack trace:
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
   at LinqToDB.Data.DataConnection.ExecuteReader(CommandBehavior commandBehavior)
   at LinqToDB.Data.DataConnection.ExecuteDataReader(CommandBehavior commandBehavior)
   at LinqToDB.Data.DataConnection.QueryRunner.ExecuteReader()
   at LinqToDB.Linq.QueryRunner.<ExecuteQuery>d__11`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ePN.App.Controllers.TestController.TestL2DB() in C:\AzureDevOps\ePN\ePN.App\Controllers\TestController.cs:line 31

Steps to reproduce

    public class TestController : Controller
    {
        private readonly ISqlDataConnection _conn;

        public TestController(ISqlDataConnection conn)
        {
            _conn = conn;
        }

        public IActionResult TestL2DB()
        {
            var q1 = _conn.DB.GetTable<TestNalog>().Where(n => n.DATUM < new DateTime(2019, 1, 1)).GroupBy(n => new { n.DATUM.GetValueOrDefault().Year, n.DATUM.GetValueOrDefault().Month }, (k, n) => new { k.Year, k.Month, Sum = n.Sum(nal => nal.SKUPAJ) }).ToList(); //Executes but has some duplicate fields for the year and month
            var q2 = _conn.DB.GetTable<TestNalog>().Where(n => n.DATUM < new DateTime(2019, 1, 1)).GroupBy(n => new { n.DATUM.GetValueOrDefault().Year, n.DATUM.GetValueOrDefault().Month }, (k, n) => new { k.Year, k.Month, Sum = n.Sum(nal => nal.SKUPAJ) })
                .UnionAll(_conn.DB.GetTable<TestNalog>().Where(n => n.DATUM >= new DateTime(2019, 1, 1)).GroupBy(n => new { n.DATUM.GetValueOrDefault().Year, n.DATUM.GetValueOrDefault().Month }, (k, n) => new { k.Year, k.Month, Sum = n.Sum(nal => nal.SKUPAJ) }))
                .ToList(); //throws an exception
            return Ok();
        }
    }

    [Table("EPN_NALOG")]
    public class TestNalog
    {
        [Column, NotNull, PrimaryKey] public int? LETO { get; set; }
        [Column, NotNull, PrimaryKey] public int? STEVILKA { get; set; }
        [Column, Nullable] public DateTime? DATUM { get; set; }
        [Column, Nullable] public decimal? SKUPAJ { get; set; } // double precision
    }

First query SQL:

DECLARE @DATUM_2 TimeStamp -- DateTime
SET     @DATUM_2 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_3 TimeStamp -- DateTime
SET     @DATUM_3 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM TimeStamp -- DateTime
SET     @DATUM = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_1 TimeStamp -- DateTime
SET     @DATUM_1 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_4 TimeStamp -- DateTime
SET     @DATUM_4 = CAST('2019-01-01' AS timestamp)

SELECT
	t1.c1,
	t1.c2,
	Sum(t1.SKUPAJ)
FROM
	(
		SELECT
			Cast(Floor(Extract(year from Coalesce(n.DATUM, Cast(@DATUM_2 as TimeStamp)))) as int) as Key_1,
			Cast(Floor(Extract(month from Coalesce(n.DATUM, Cast(@DATUM_3 as TimeStamp)))) as int) as Key_2,
			Cast(Floor(Extract(year from Coalesce(n.DATUM, Cast(@DATUM as TimeStamp)))) as int) as c1,
			Cast(Floor(Extract(month from Coalesce(n.DATUM, Cast(@DATUM_1 as TimeStamp)))) as int) as c2,
			n.SKUPAJ
		FROM
			EPN_NALOG n
		WHERE
			n.DATUM < @DATUM_4
	) t1
GROUP BY
	t1.Key_1,
	t1.Key_2,
	t1.c1,
	t1.c2

Second query SQL:

DECLARE @DATUM_2 TimeStamp -- DateTime
SET     @DATUM_2 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_3 TimeStamp -- DateTime
SET     @DATUM_3 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM TimeStamp -- DateTime
SET     @DATUM = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_1 TimeStamp -- DateTime
SET     @DATUM_1 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_4 TimeStamp -- DateTime
SET     @DATUM_4 = CAST('2019-01-01' AS timestamp)
DECLARE @DATUM_7 TimeStamp -- DateTime
SET     @DATUM_7 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_8 TimeStamp -- DateTime
SET     @DATUM_8 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_5 TimeStamp -- DateTime
SET     @DATUM_5 = CAST('0001-01-01' AS timestamp)
DECLARE @DATUM_6 TimeStamp -- DateTime
SET     @DATUM_6 = CAST('0001-01-01' AS timestamp)

SELECT
	t2.Year_1,
	t2.Month_1,
	t2.Sum_1
FROM
	(
		SELECT
			t1.Year_1,
			t1.Month_1,
			Sum(t1.SKUPAJ) as Sum_1
		FROM
			(
				SELECT
					Cast(Floor(Extract(year from Coalesce(n.DATUM, Cast(@DATUM_2 as TimeStamp)))) as int) as Key_1,
					Cast(Floor(Extract(month from Coalesce(n.DATUM, Cast(@DATUM_3 as TimeStamp)))) as int) as Key_2,
					Cast(Floor(Extract(year from Coalesce(n.DATUM, Cast(@DATUM as TimeStamp)))) as int) as Year_1,
					Cast(Floor(Extract(month from Coalesce(n.DATUM, Cast(@DATUM_1 as TimeStamp)))) as int) as Month_1,
					n.SKUPAJ
				FROM
					EPN_NALOG n
				WHERE
					n.DATUM < @DATUM_4
			) t1
		GROUP BY
			t1.Key_1,
			t1.Key_2
	) t2
UNION ALL
SELECT
	t4.Year_1,
	t4.Month_1,
	t4.Sum_1
FROM
	(
		SELECT
			t3.Year_1,
			t3.Month_1,
			Sum(t3.SKUPAJ) as Sum_1
		FROM
			(
				SELECT
					Cast(Floor(Extract(year from Coalesce(n_1.DATUM, Cast(@DATUM_7 as TimeStamp)))) as int) as Key_1,
					Cast(Floor(Extract(month from Coalesce(n_1.DATUM, Cast(@DATUM_8 as TimeStamp)))) as int) as Key_2,
					Cast(Floor(Extract(year from Coalesce(n_1.DATUM, Cast(@DATUM_5 as TimeStamp)))) as int) as Year_1,
					Cast(Floor(Extract(month from Coalesce(n_1.DATUM, Cast(@DATUM_6 as TimeStamp)))) as int) as Month_1,
					n_1.SKUPAJ
				FROM
					EPN_NALOG n_1
				WHERE
					n_1.DATUM >= @DATUM_4
			) t3
		GROUP BY
			t3.Key_1,
			t3.Key_2
	) t4

Environment details

Linq To DB version: 4.1.1

Database (with version): Firebird 2.5

ADO.NET Provider (with version): FirebirdSql.Data.FirebirdClient 9.0.2

Operating system: Win10

.NET Version: .net 6

[primozcerar]

Just a follow up on this. Adding an order by clause on the group key fields after group by will result in additional duplicate fields and fail. I believe this happens when GetValueOrDefault is used in group by key, since I have a similar query without it and that works correctly.