4

I'm trying to understand a strange behaviour of GROUP BY on SQLite v2.8.17

Here is the code :

<?php
$selectMaxQuery = $this->_db->prepare('SELECT COUNT(*) AS c FROM (SELECT MAX(groupCode) FROM docs GROUP BY groupCode)');
$selectQuery = $this->_db->prepare('   SELECT COUNT(*) AS c FROM (SELECT     groupCode  FROM docs GROUP BY groupCode)');
$selectMaxQuery->exec();
$selectQuery->exec();
var_dump($selectMaxQuery->fetch()->c, $selectQuery->fetch()->c);

Here is the result :

string(3) "614"
string(3) "797"

Everywhere I go on the internet, it says that the GROUP BY behaviour is to merge several rows into one. Without using an aggregate function, it should give me an error or pick a random value for each rows that is nor in GROUP BY nor in aggregate function.

The result seems to be different from what I understand.

Can someone explain me what I'm missing here ?

CL.
  • 173,858
  • 17
  • 217
  • 259
zenko
  • 147
  • 1
  • 8
  • But you have the column in GROUP BY so there is no problem and won't pick a random value. Also proper DB engines won't give random rows, MySQL by default does. – Sami Kuhmonen Mar 17 '17 at 16:34
  • This is what I said, right ? `for each rows that is nor in GROUP BY nor in aggregate`. Here it's in GROUP BY so I don't understand why the result is different using or not an aggregate function of the grouped field? – zenko Mar 17 '17 at 16:42
  • 1
    The number of rows returned by both sub-selects should be identical because you are grouping on the same field. So the count(*) should also be identical unless if rows are being added as you run the query. Your results make no sense to me. – Anand Mar 17 '17 at 16:49
  • My initial guess was that this was some inconsistent treatment of `null`, but I've not been able to recreate the effect over at SQLFiddle on that basis. But then I don't know how realistic my trial schema/data were since you haven't given us those. For now, I'm inclined to agree with CL's answer, though it would be nice to have a reference to the bug and fix. I've not been able to dig that out. – Steve Lovell Mar 17 '17 at 19:39
  • @SteveLovell : Just have to download php5.2.10 and you're good to go. ;) FYI : I'd like to upgrade PHP and/or SQLite but it's not up to me... – zenko Mar 20 '17 at 11:13

1 Answers1

2

This is a bug.

Which was fixed over ten years ago; SQLite 2.8.17 was released in 2005. You should reconsider your choice of software.

CL.
  • 173,858
  • 17
  • 217
  • 259