On 4/1/15, Marcel Keller <marcel-k at gmx.ch> wrote:
> Hi,
>
> Consider the following database:
>
> sqlite> .schema
> CREATE TABLE counter (i integer);
> CREATE TABLE expenses (date float, month text, amount numeric);
> sqlite> select * from counter;
> 0
> 1
> 2
> sqlite> select * from expenses;
> 2457113.5163101||100
>
> In the latest binary from the website, I get the following:
>
> sqlite> .version
> SQLite 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
> sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as
> month, sum(amount) from expenses join counter where i < 12 group by month;
> 2015-06|300
>
> However, with an earlier version, I get:
>
> sqlite> .version
> SQLite 3.7.15.2 2013-01-09 11:53:05
> c0e09560d26f0a6456be9dd3447f5311eb4f238f
> sqlite> select strftime("%Y-%m",date,'start of month',i || ' months') as
> month, sum(amount) from expenses join counter where i < 12 group by month;
> 2015-04|100
> 2015-05|100
> 2015-06|100
>
> It seems that the meaning of "group by month" changed from referring to
> "as mount" to expenses.month. Is this intentional?
>Yes. This was a bug fix. The current behavior agrees with the behavior of MySQL, Oracle, PostgreSQL, and SQL Server. Actually, your query will not run on most other database engines. But the following example shows that the new behavior is correct: CREATE TABLE t1(a INT,b INT); INSERT INTO t1(a,b) VALUES(2,1),(3,10),(4,100),(5,1000); SELECT CAST(a/2 AS int) AS a, sum(b) FROM t1 GROUP BY a; -- D. Richard Hipp drh at sqlite.org
