Menu
▾
▴
[Nhibernate-commit] SF.net SVN: nhibernate:[4370] trunk/nhibernate/src/NHibernate
|
From: <fab...@us...> - 2009-05-23 05:19:10
|
Revision: 4370
http://nhibernate.svn.sourceforge.net/nhibernate/?rev=4370&view=rev
Author: fabiomaulo
Date: 2009-05-23 05:18:39 +0000 (Sat, 23 May 2009)
Log Message:
-----------
Fix NH-1797
Modified Paths:
--------------
trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
Modified: trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs
===================================================================
--- trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2009-05-23 05:17:40 UTC (rev 4369)
+++ trunk/nhibernate/src/NHibernate/Dialect/MsSql2005Dialect.cs 2009-05-23 05:18:39 UTC (rev 4370)
@@ -44,6 +44,12 @@
/// </remarks>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
{
+ //dont do this paging code if there is no offset, use the
+ //sql 2000 dialect since it wont just uses a top statement
+ if (offset == 0)
+ {
+ return base.GetLimitString(querySqlString, offset, last);
+ }
// we have to do this in order to support parameters in order clause, the foramt
// that sql 2005 uses for paging means that we move the parameters around, which means,
// that positions are lost, so we record them before making any changes.
Modified: trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs
===================================================================
--- trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2009-05-23 05:17:40 UTC (rev 4369)
+++ trunk/nhibernate/src/NHibernate.Test/DialectTest/MsSql2005DialectFixture.cs 2009-05-23 05:18:39 UTC (rev 4370)
@@ -15,28 +15,28 @@
{
MsSql2005Dialect d = new MsSql2005Dialect();
- SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), 0, 10);
+ SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15);
@@ -59,40 +59,51 @@
"SELECT TOP 20 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > 10 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
- str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), 0, 10);
+ str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
[Test]
+ [Description("should use only TOP clause if there is no offset")]
+ public void OnlyOffsetLimit()
+ {
+ var d = new MsSql2005Dialect();
+
+ SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), 0, 10);
+ System.Console.WriteLine(str);
+ Assert.That(str.ToString(), Is.EqualTo("select distinct top 10 c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"));
+ }
+
+ [Test]
public void NH1187()
{
MsSql2005Dialect d = new MsSql2005Dialect();
SqlString result =
- d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), 0, 10);
+ d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), 1, 10);
System.Console.WriteLine(result);
- Assert.AreEqual("SELECT TOP 10 desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row", result.ToString());
+ Assert.AreEqual("SELECT TOP 10 desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row", result.ToString());
// The test use the function "cast" because cast need the keyWork "as" too
SqlString str =
- d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), 0, 10);
+ d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), 1, 10);
System.Console.WriteLine(str);
Assert.AreEqual(
- "SELECT TOP 10 id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 0 ORDER BY query.__hibernate_sort_row",
+ "SELECT TOP 10 id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 1 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
[Test]
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
