{"action":"created","issue":{"url":"https://api.github.com/repos/linq2db/linq2db/issues/1380","repository_url":"https://api.github.com/repos/linq2db/linq2db","labels_url":"https://api.github.com/repos/linq2db/linq2db/issues/1380/labels{/name}","comments_url":"https://api.github.com/repos/linq2db/linq2db/issues/1380/comments","events_url":"https://api.github.com/repos/linq2db/linq2db/issues/1380/events","html_url":"https://github.com/linq2db/linq2db/issues/1380","id":376710478,"node_id":"MDU6SXNzdWUzNzY3MTA0Nzg=","number":1380,"title":"Invalid SQL Generated for CTE with Count and Distinct","user":{"login":"hahn-kev","id":4575355,"node_id":"MDQ6VXNlcjQ1NzUzNTU=","avatar_url":"https://avatars2.githubusercontent.com/u/4575355?v=4","gravatar_id":"","url":"https://api.github.com/users/hahn-kev","html_url":"https://github.com/hahn-kev","followers_url":"https://api.github.com/users/hahn-kev/followers","following_url":"https://api.github.com/users/hahn-kev/following{/other_user}","gists_url":"https://api.github.com/users/hahn-kev/gists{/gist_id}","starred_url":"https://api.github.com/users/hahn-kev/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hahn-kev/subscriptions","organizations_url":"https://api.github.com/users/hahn-kev/orgs","repos_url":"https://api.github.com/users/hahn-kev/repos","events_url":"https://api.github.com/users/hahn-kev/events{/privacy}","received_events_url":"https://api.github.com/users/hahn-kev/received_events","type":"User","site_admin":false},"labels":[{"id":533878923,"node_id":"MDU6TGFiZWw1MzM4Nzg5MjM=","url":"https://api.github.com/repos/linq2db/linq2db/labels/bug","name":"bug","color":"b60205","default":true},{"id":509185697,"node_id":"MDU6TGFiZWw1MDkxODU2OTc=","url":"https://api.github.com/repos/linq2db/linq2db/labels/linq","name":"linq","color":"d93f0b","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2018-11-02T08:20:22Z","updated_at":"2018-11-02T14:30:03Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"When running the following test (setup in `CteTests`) against sql server and SQLite Invalid SQL is generated\r\n```C#\r\n[Test]\r\npublic void Test5([CteContextSource] string context)\r\n{\r\n\tusing (var db = GetDataContext(context))\r\n\t{\r\n\t\tvar cte1 = db.GetTable<Child>()\r\n\t\t\t.Where(c => c.ParentID > 1)\r\n\t\t\t.Select(child => new\r\n\t\t\t{\r\n\t\t\t\tchild.ParentID,\r\n\t\t\t\tchild.ChildID\r\n\t\t\t}).Distinct()\r\n\t\t\t.AsCte();\r\n\t\tvar query = from p in db.Parent\r\n\t\t\tjoin c in cte1 on p.ParentID equals c.ParentID\r\n\t\t\tjoin c2 in cte1 on p.ParentID equals c2.ParentID\r\n\t\t\tselect p;\r\n\r\n\t\tvar cte1_ = db.GetTable<Child>().Where(c => c.ParentID > 1).Select(child => new\r\n\t\t{\r\n\t\t\tchild.ParentID,\r\n\t\t\tchild.ChildID\r\n\t\t}).Distinct();\r\n\r\n\t\tvar expected =\r\n\t\t\tfrom p in db.Parent\r\n\t\t\tjoin c in cte1_ on p.ParentID equals c.ParentID\r\n\t\t\tjoin c2 in cte1_ on p.ParentID equals c2.ParentID\r\n\t\t\tselect p;\r\n\r\n\t\tAssert.AreEqual(expected.Count(), query.Count());\r\n\t}\r\n}\r\n```\r\n\r\nthe SQL generated for the CTE query is as follows\r\n```SQL\r\n-- SqlServer.2012\r\nWITH [CTE_1] ([ParentID])\r\nAS\r\n(\r\n\tSELECT DISTINCT\r\n\t\t[t1].[ParentID],\r\n\t\t[t1].[ChildID]\r\n\tFROM\r\n\t\t[Child] [t1]\r\n\tWHERE\r\n\t\t[t1].[ParentID] > 1\r\n)\r\nSELECT\r\n\tCount(*) as [cnt]\r\nFROM\r\n\t[Parent] [p]\r\n\t\tINNER JOIN [CTE_1] [c1] ON [p].[ParentID] = [c1].[ParentID]\r\n\t\tINNER JOIN [CTE_1] [c2] ON [p].[ParentID] = [c2].[ParentID]\r\n```\r\nAs you can see, CTE_1 only declares `ParentID` as CTE columns, but `ChildID` is also selected because it has the `DISTINCT` clause, it looks like the CTE isn't respecting the changes caused by the `DISTINCT` clause. \r\n\r\nAs a work around I can not use a CTE for now.\r\n\r\n\r\n```\r\nException message:\r\nSystem.Data.SqlClient.SqlException : 'CTE_1' has more columns than were specified in the column list.\r\n```\r\n\r\n### Steps to reproduce\r\nCopy paste the snippet above into `CteTests` and run.\r\n\r\n\r\n### Environment details\r\nlinq2db version: *2.5.0*\r\nDatabase Server: *SqlServer*\r\n"},"comment":{"url":"https://api.github.com/repos/linq2db/linq2db/issues/comments/435398375","html_url":"https://github.com/linq2db/linq2db/issues/1380#issuecomment-435398375","issue_url":"https://api.github.com/repos/linq2db/linq2db/issues/1380","id":435398375,"node_id":"MDEyOklzc3VlQ29tbWVudDQzNTM5ODM3NQ==","user":{"login":"sdanyliv","id":783759,"node_id":"MDQ6VXNlcjc4Mzc1OQ==","avatar_url":"https://avatars1.githubusercontent.com/u/783759?v=4","gravatar_id":"","url":"https://api.github.com/users/sdanyliv","html_url":"https://github.com/sdanyliv","followers_url":"https://api.github.com/users/sdanyliv/followers","following_url":"https://api.github.com/users/sdanyliv/following{/other_user}","gists_url":"https://api.github.com/users/sdanyliv/gists{/gist_id}","starred_url":"https://api.github.com/users/sdanyliv/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sdanyliv/subscriptions","organizations_url":"https://api.github.com/users/sdanyliv/orgs","repos_url":"https://api.github.com/users/sdanyliv/repos","events_url":"https://api.github.com/users/sdanyliv/events{/privacy}","received_events_url":"https://api.github.com/users/sdanyliv/received_events","type":"User","site_admin":false},"created_at":"2018-11-02T14:30:03Z","updated_at":"2018-11-02T14:30:03Z","author_association":"MEMBER","body":"@hahn-kev, i have done with issue. Wait for next release or use code from the PR. "}}
{"actor":{"display_login":"sdanyliv"}}