{"action":"closed","issue":{"url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1495","repository_url":"https://api.github.com/repos/nhibernate/nhibernate-core","labels_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1495/labels{/name}","comments_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1495/comments","events_url":"https://api.github.com/repos/nhibernate/nhibernate-core/issues/1495/events","html_url":"https://github.com/nhibernate/nhibernate-core/issues/1495","id":282870014,"number":1495,"title":"Linq provider generates different SQL query (with not equal) in version 4 and 5","user":{"login":"rspacjer","id":1004431,"avatar_url":"https://avatars0.githubusercontent.com/u/1004431?v=4","gravatar_id":"","url":"https://api.github.com/users/rspacjer","html_url":"https://github.com/rspacjer","followers_url":"https://api.github.com/users/rspacjer/followers","following_url":"https://api.github.com/users/rspacjer/following{/other_user}","gists_url":"https://api.github.com/users/rspacjer/gists{/gist_id}","starred_url":"https://api.github.com/users/rspacjer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rspacjer/subscriptions","organizations_url":"https://api.github.com/users/rspacjer/orgs","repos_url":"https://api.github.com/users/rspacjer/repos","events_url":"https://api.github.com/users/rspacjer/events{/privacy}","received_events_url":"https://api.github.com/users/rspacjer/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2017-12-18T12:56:06Z","updated_at":"2017-12-18T21:45:48Z","closed_at":"2017-12-18T21:45:48Z","author_association":"NONE","body":"For those two tests:\r\n\r\n```\r\n[Test]\r\npublic void Test_where_not_equal()\r\n{\r\n        var q = from p in db.Products\r\n        where p.ProductId != int.MaxValue\r\n        select new { p.ProductId, p.UnitPrice, HalfPrice = p.UnitPrice + 2 };\r\n\r\n        var l = q.ToList();\r\n}\r\n```\r\n```\r\n[Test]\r\npublic void Test_where_not_equal_query_over()\r\n{\r\n        var q = _session.QueryOver<Product>().Where(x => x.ProductId != int.MaxValue);\r\n\r\n        var l = q.List();\r\n}\r\n```\r\n\r\nnHibernate 4.1.x generates such SQL:\r\n\r\n```\r\n    select\r\n        product0_.ProductId as col_0_0_,\r\n        product0_.UnitPrice as col_1_0_,\r\n        product0_.UnitPrice as col_2_0_ \r\n    from\r\n        Products product0_ \r\n    where\r\n        product0_.ProductId<>@p0;\r\n    @p0 = 2147483647\r\n```\r\nand\r\n```\r\nSELECT\r\n        this_.ProductId as Produ1_32_0_,\r\n        this_.ProductName as Produ2_32_0_,\r\n        this_.SupplierId as Suppl3_32_0_,\r\n        this_.CategoryId as Categ4_32_0_,\r\n        this_.QuantityPerUnit as Quant5_32_0_,\r\n        this_.UnitPrice as UnitP6_32_0_,\r\n        this_.UnitsInStock as Units7_32_0_,\r\n        this_.UnitsOnOrder as Units8_32_0_,\r\n        this_.ReorderLevel as Reord9_32_0_,\r\n        this_.Discontinued as Disc10_32_0_,\r\n        this_.ShippingWeight as Ship11_32_0_ \r\n    FROM\r\n        Products this_ \r\n    WHERE\r\n        not (this_.ProductId = @p0);\r\n    @p0 = 2147483647\r\n```\r\nand nHibernate 5.0.3 generates such SQL:\r\n```\r\n    select\r\n        product0_.ProductId as col_0_0_,\r\n        product0_.UnitPrice as col_1_0_,\r\n        product0_.UnitPrice as col_2_0_ \r\n    from\r\n        Products product0_ \r\n    where\r\n        product0_.ProductId<>@p0 \r\n        or product0_.ProductId is null;\r\n    @p0 = 2147483647\r\n```\r\n\r\nand\r\n\r\n```\r\n    SELECT\r\n        this_.ProductId as productid1_32_0_,\r\n        this_.ProductName as productname2_32_0_,\r\n        this_.SupplierId as supplierid3_32_0_,\r\n        this_.CategoryId as categoryid4_32_0_,\r\n        this_.QuantityPerUnit as quantityperunit5_32_0_,\r\n        this_.UnitPrice as unitprice6_32_0_,\r\n        this_.UnitsInStock as unitsinstock7_32_0_,\r\n        this_.UnitsOnOrder as unitsonorder8_32_0_,\r\n        this_.ReorderLevel as reorderlevel9_32_0_,\r\n        this_.Discontinued as discontinued10_32_0_,\r\n        this_.ShippingWeight as shippingweight11_32_0_ \r\n    FROM\r\n        Products this_ \r\n    WHERE\r\n        not (this_.ProductId = @p0);\r\n    @p0 = 2147483647\r\n```\r\n\r\nAs you can see, there is a difference for the Linq provider between version 4 and 5 in the `where` clauses.\r\nFor version 4 we have: `product0_.ProductId<>@p0;` and for 5 we have:  `product0_.ProductId<>@p0         or product0_.ProductId is null;` This change makes that returned results can be different (I would event thread it as a breaking change between version 4 and 5). Is this by intention or is it a bug?\r\n\r\nYou can also notice that for version 5 the result returned by Ling and QueryOver is different, because those queries differs in the `where` statement (the QueryOver part doesn't have `or product0_.ProductId is null`)\r\n\r\nThanks in advance!"}}
{"actor":{"display_login":"hazzik"}}