not sure if this is Permissionable or Cakephp, but I get a sql warning on $this->paginate

[mixersoft]

not sure if this is Permissionable or Cakephp, but I get a sql warning on $this->paginate

[jmcneese]

does this still happen? if so can you submit a test showing it failing?

[Biggersmile]

I assume you use MS SQL.

You problem exists because the query was written for MySQL.

Assume you have a aggregate function in your select clause.
At this moment in In MS SQL it is not allowed to have columns in the select statement which are not used in any aggegated function or
not added to the group by clause. These queries have undefined results.

And this is the whole problem in MySQL. MySQL has two modes for group by queries. By default a less strict mode is used.
This allows you to use columns in your select statement without adding these columns to your group by queries.
MySQL makes some assumptions and comes up with a result.

If you add sql_mode=ONLY_FULL_GROUP_BY to your Mysql configuation my.ini file in Windows or in a .cnf in linux/unix.
MySQL won't allow these wrong groupby queries anymore.

A link with information to this matter: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Below I noted an example:

Example data:

MS SQL
CREATE TABLE [dbo].[Tree]([id] [int] NOT NULL,
[name] [nchar]%2810%29 NOT NULL,
[leaves] [int] NOT NULL,
[type] [nchar]%2810%29 NOT NULL) ON [PRIMARY]

INSERT [dbo].[Tree]([id], [name], [leaves], [type]) VALUES (1, N'Appletree ', 10, N'Fruit ')
INSERT [dbo].[Tree]([id], [name], [leaves], [type]) VALUES (2, N'Birch ', 15, N'Needleless')
INSERT [dbo].[Tree]([id], [name], [leaves], [type]) VALUES (3, N'Oak ', 5, N'Needleless')
INSERT [dbo].[Tree]([id], [name], [leaves], [type]) VALUES (4, N'Pinetree ', 7, N'Needles ')
INSERT [dbo].[Tree]([id], [name], [leaves], [type]) VALUES (5, N'Spruce ', 3, N'Needles ')

MySQL

CREATE TABLE IF NOT EXISTS tree (
id int(11) NOT NULL,
name varchar(10) NOT NULL,
leaves int(11) NOT NULL,
type varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tree (id, name, leaves, type) VALUES
(1, 'Appletree', 10, 'Fruit'),
(2, 'Birch', 15, 'Needleless'),
(3, 'Oak', 5, 'Needleless'),
(4, 'Pinetree', 7, 'Needles'),
(5, 'Spruce', 3, 'Needles');

Incorrect Example Query:

MS SQL

SELECT type, MAX(leaves)
FROM [dbo].[Tree]

MySQL

SELECT TYPE, MAX( leaves )
FROM Tree

Results

MS SQL

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Tree.type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

MySQL

Type MAX(leaves)
Fruit 15

Correct Example Query:

MS SQL

SELECT type, MAX(leaves)
FROM [dbo].[Tree]
GROUp BY type

MySQL

SELECT TYPE, MAX( leaves )
FROM Tree
GROUp BY type

Results
Both queries return, as you would expect.

Fruit 10
Needleless 15
Needles 7