[PHP] [PHP-DEV] SQLite - Unwanted values using group-by

[Simon Schick]

Hi, All

May you have an idea ...

Here's the full code-example:
http://viper-7.com/M5mldG

I have the following SQL command:

SELECT max(r.month+r.year*100), r.year, r.month
FROM base b LEFT JOIN remote r ON b.id = r.remote_id
GROUP BY r.remote_id

Now I expect that the first column in the results should look like a
combination of the second and third one .. so f.e. this:
array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" ["year"]=>
string(4) "2012" ["month"]=> string(2) "01" }

But instead I get this result:
array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" ["year"]=>
string(4) "2011" ["month"]=> string(2) "12" }

I tested it on Windows7 (PHP 5.4.4, SQLiteLib v3.7.7.1), where I get the
result as shown above ... and I've also tested it on a fresh installation
on LinuxArch (PHP 5.4.4 and SQLiteLib v3.7.13) - where it works as
expected! A college of mine has tested it on OSX (PHP 5.3.7 and SQLiteLib
v3.7.13) and he gets the same result as I on the windows version.
Someone of you have an idea?
Maybe had the same problem before ... what could it be?

I thought it might a problem of the SQLite-lib but both, the LinuxArch and
the OSX system, have the same SQLite version - and both, the windows and
the LinuxArch version, have the same PHP-version.

Even if the example just uses PDO, I've also tested this using the native
SQLite3 lib and I got at least the same result on Windows. Haven't tested
the other systems with the native SQLite3 lib.

Bye
Simon

Re: [PHP] [PHP-DEV] SQLite - Unwanted values using group-by

[Matijn Woudt]

On Sun, Jul 8, 2012 at 12:07 AM, Simon Schick  wrote:
> Hi, All
>
> May you have an idea ...
>
> Here's the full code-example:
> http://viper-7.com/M5mldG
>
> I have the following SQL command:
>
> SELECT max(r.month+r.year*100), r.year, r.month
> FROM base b LEFT JOIN remote r ON b.id = r.remote_id
> GROUP BY r.remote_id
>
> Now I expect that the first column in the results should look like a
> combination of the second and third one .. so f.e. this:
> array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" ["year"]=>
> string(4) "2012" ["month"]=> string(2) "01" }
>
> But instead I get this result:
> array(3) { ["max(r.month+r.year*100)"]=> string(6) "201201" ["year"]=>
> string(4) "2011" ["month"]=> string(2) "12" }

Both of the results are valid outcomes. I think you don't understand
the GROUP BY clause well enough. The parameters in the SELECT clause,
should be either
1) an aggregate function (like the max function you're using)
2) one of the parameters in the GROUP BY clause.
If not one of the above, it will return 'random' values for r.month
and r.year. (probably the first it finds, which might differ in your
test cases)

- Matijn



- Matijn

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php