1

Resolved

Random sorting does not work with RDBMS other than MS SQL

description

Because it's hardcoded to use the newid() function, which only exists in MS SQL.
                if (sortFactory.Randomize) {
                    //sb.Append(" newid()");
                    var dialect = _session.GetSessionImplementation().Factory.Dialect;

                    if (dialect.GetType().Name.IndexOf("MySQL", StringComparison.OrdinalIgnoreCase) != -1)
                    {
                        // MySQL uses RAND()
                        sb.Append("rand()");
                    }
                    else if (dialect.GetType().Name.IndexOf("PostgreSQL", StringComparison.OrdinalIgnoreCase) != -1)
                    {
                        // PostgreSQL uses random()
                        sb.Append("random()");
                    }
                    else if (dialect.GetType().Name.IndexOf("SQLite", StringComparison.OrdinalIgnoreCase) != -1)
                    {
                        // SQLite also uses random()
                        sb.Append("random()");
                    }
                    else
                    {
                        // All other DBs use NEWID() for now
                        sb.Append("newid()");
                    }
                }
                else {
                    sb.Append(sort.Item1.Name).Append(".").Append(sortFactory.PropertyName);
                    if (!sortFactory.Ascending) {
                        sb.Append(" desc");
This is definitely not the way to do it, but illustrates the problem.

It seems NHibernate does not have any mappings for the random sorting. So it explains why newid() is in the code, instead of .RandomSort() or something.

I don't know how to fix it, however, we've been using this for quite a while now.

comments

sebastienros wrote Jul 6, 2013 at 5:26 PM

Note to the fixer: try to use the current dialect information, or extend db the provider.

sebastienros wrote Jul 8, 2013 at 2:22 AM

Fixed in changeset f83c60d83bed

sfmskywalker wrote Mar 28 at 12:28 AM

Fixed in changeset af71604f8c0cc4a6550d554f45ef1793a684b1d5

hkui wrote Mar 28 at 6:07 AM

sfmskywalker: Where can i view that changeset?