{"action":"created","issue":{"url":"https://api.github.com/repos/jeremyevans/sequel/issues/1574","repository_url":"https://api.github.com/repos/jeremyevans/sequel","labels_url":"https://api.github.com/repos/jeremyevans/sequel/issues/1574/labels{/name}","comments_url":"https://api.github.com/repos/jeremyevans/sequel/issues/1574/comments","events_url":"https://api.github.com/repos/jeremyevans/sequel/issues/1574/events","html_url":"https://github.com/jeremyevans/sequel/issues/1574","id":383117566,"node_id":"MDU6SXNzdWUzODMxMTc1NjY=","number":1574,"title":"Adapter TinyTDS is struggling with table alias in model dataset","user":{"login":"QuAcK3RjAcK","id":7591627,"node_id":"MDQ6VXNlcjc1OTE2Mjc=","avatar_url":"https://avatars0.githubusercontent.com/u/7591627?v=4","gravatar_id":"","url":"https://api.github.com/users/QuAcK3RjAcK","html_url":"https://github.com/QuAcK3RjAcK","followers_url":"https://api.github.com/users/QuAcK3RjAcK/followers","following_url":"https://api.github.com/users/QuAcK3RjAcK/following{/other_user}","gists_url":"https://api.github.com/users/QuAcK3RjAcK/gists{/gist_id}","starred_url":"https://api.github.com/users/QuAcK3RjAcK/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/QuAcK3RjAcK/subscriptions","organizations_url":"https://api.github.com/users/QuAcK3RjAcK/orgs","repos_url":"https://api.github.com/users/QuAcK3RjAcK/repos","events_url":"https://api.github.com/users/QuAcK3RjAcK/events{/privacy}","received_events_url":"https://api.github.com/users/QuAcK3RjAcK/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-11-21T13:26:57Z","updated_at":"2018-11-21T19:02:08Z","closed_at":"2018-11-21T19:02:07Z","author_association":"NONE","body":"### Complete Description of Issue\r\nI have a small model with a default dataset. But the query i build is not working but in IRB without the model the query works fine. What im doing wrong here?\r\n\r\n\r\n### Simplest Possible Self-Contained Example Showing the Bug\r\n*Model*\r\n```\r\nclass Ipod < Sequel::Model(DB_MDM[:dbo__device])\r\n\r\n  dataset_module do\r\n    def only_obf_ipods\r\n      DB_MDM[:dbo__device___d].left_join(:dbo__device_property___dp, dp__device_id: :d__device_id).where(dp__name: 'DEVICE_NAME').where(Sequel.like(:dp__value, '%iPod%')).where(Sequel.like(:d__last_username, '%mdmvkst____%'))#.order(:d__last_username)\r\n    end\r\n  end\r\n  set_dataset(self.only_obf_ipods)\r\n\r\n  FILTER_VALUES = {\r\n    all: 'alle',\r\n    active: 'aktive',\r\n    inactive: 'inaktive'\r\n  }.freeze\r\n\r\n  def self.active\r\n    self.select{|ipod| ipod.last_activity >= (Date.today - 7).to_time}\r\n  end\r\n\r\n  def self.inactive\r\n    self.select{|ipod| ipod.last_activity < (Date.today - 7).to_time}\r\n  end\r\nend\r\n```\r\n*IRB*\r\n```\r\n$ irb\r\nirb(main):001:0> require_relative 'management'\r\n=> true\r\nirb(main):002:0> Ipod.first\r\nSequel::DatabaseError: TinyTds::Error: The column 'DEVICE_ID' was specified multiple times for 'D'.\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:210:in `fields'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:210:in `block in fetch_rows'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:65:in `block in execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `block in synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/connection_pool/threaded.rb:87:in `hold'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:34:in `execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:1083:in `execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:207:in `fetch_rows'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:151:in `each'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:85:in `columns!'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:75:in `columns'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:242:in `columns'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:809:in `block (2 levels) in get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:725:in `block in check_non_connection_error'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/transactions.rb:151:in `block in transaction'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `block in synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/connection_pool/threaded.rb:91:in `hold'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/transactions.rb:145:in `transaction'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:725:in `check_non_connection_error'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:809:in `block in get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:842:in `call'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:842:in `get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:644:in `set_dataset'\r\n        from C:/Projekte/localware/management/models/ipod.rb:8:in `<class:Ipod>'\r\n        from C:/Projekte/localware/management/models/ipod.rb:1:in `<top (required)>'\r\n        from C:/Ruby226/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:122:in `require'\r\n        from C:/Ruby226/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:122:in `require'\r\n        from C:/Projekte/localware/management/management.rb:222:in `const_missing'\r\n        from (irb):2\r\n        from C:/Ruby226/bin/irb:11:in `<main>'\r\n\r\nirb(main):003:0> DB_MDM[:dbo__device___d].left_join(:dbo__device_property___dp, dp__device_id: :d__device_id).where(dp__name: 'DEVICE_NAME').where(Sequel.like(:dp__value, '%iPod%')).where(Sequel.like(:d__last_username, '%mdmvkst____%')).first\r\n=>{:device_id=>1890, :serial_number=>\"xxxxx\", :imei=>nil, :wifi_mac_addr=>\"F0:76:6F:2A:73:13\", :bluetooth_mac_addr=>\"F0:76:6F:1F:AA:EF\", :activesync_id=>\"I00DT9QJO57S7E6QJSPJMKOFGS\", :strong_id=>\"PNMJ6Q5H\", :last_inventory_time=>2018-11-21 10:24:28 +0100, :first_connection_date=>2018-08-15 09:28:42 +0200, :last_auth_date=>2018-11-21 10:24:31 +0100, :last_username=>\"xxxxx\", :last_profile_invent_time=>2018-11-21 10:24:32 +0100, :os_family=>\"iOS\", :last_user_id=>492, :whitelist_compliant=>1, :blacklist_compliant=>1, :device_properties_timestamp=>2018-08-15 09:29:48 +0200, :all_whitelist_compliant=>1, :last_activity=>2018-11-21 10:24:31 +0100, :managed=>1, :cookie_token=>nil, :cookie_exp=>nil, :profile_status=>3, :mam_device_id=>nil, :device_token=>nil, :device_type=>nil, :type_inst=>nil, :app_lock=>0, :app_wipe=>0, :mdm_known=>1, :mam_registered=>0, :mam_ready=>0, :dep_account_id=>1, :dep_profile_id=>1, :property_id=>111202, :name=>\"DEVICE_NAME\", :value=>\"iPod touch\", :is_internal=>0}\r\n```\r\n\r\nIn addition my workaround at the moment was to convert that query in SQL and it is working, but now i need more dynamic in that query so i want to use query again.\r\n```\r\n      # sql = %{\r\n      #         SELECT * FROM [DBO].[DEVICE] AS [D]\r\n      #         LEFT JOIN [DBO].[DEVICE_PROPERTY] AS [DP]\r\n      #                   ON ([DP].[DEVICE_ID] = [D].[DEVICE_ID])\r\n      #         WHERE (([DP].[NAME] = N'DEVICE_NAME') AND\r\n      #               (([DP].[VALUE] COLLATE Latin1_General_CS_AS) LIKE (N'%iPod%' COLLATE Latin1_General_CS_AS) ESCAPE N'\\\\') AND\r\n      #               (([D].[LAST_USERNAME] COLLATE Latin1_General_CS_AS) LIKE (N'%mdmvkst____@%' COLLATE Latin1_General_CS_AS) ESCAPE N'\\\\'))))\r\n      #         ORDER BY [D].[LAST_USERNAME]\r\n      #       }.gsub(/\\s+/, \" \").strip\r\n      # DB_MDM[sql]\r\n```\r\n\r\nAfter that maybe there is a second issue. When i add `.order(:d__last_username)` to my query above. \r\nI get following error:\r\n```\r\nirb(main):002:0> Ipod.first\r\nSequel::DatabaseError: TinyTds::Error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:210:in `fields'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:210:in `block in fetch_rows'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:65:in `block in execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `block in synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/connection_pool/threaded.rb:87:in `hold'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:34:in `execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:1083:in `execute'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/adapters/tinytds.rb:207:in `fetch_rows'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:151:in `each'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:85:in `columns!'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/dataset/actions.rb:75:in `columns'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:242:in `columns'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:809:in `block (2 levels) in get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:725:in `block in check_non_connection_error'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/transactions.rb:151:in `block in transaction'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `block in synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/connection_pool/threaded.rb:91:in `hold'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/connecting.rb:253:in `synchronize'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/database/transactions.rb:145:in `transaction'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:725:in `check_non_connection_error'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:809:in `block in get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:842:in `call'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:842:in `get_db_schema'\r\n        from C:/Ruby226/lib/ruby/gems/2.2.0/gems/sequel-5.10.0/lib/sequel/model/base.rb:644:in `set_dataset'\r\n        from C:/Projekte/localware/management/models/ipod.rb:8:in `<class:Ipod>'\r\n        from C:/Projekte/localware/management/models/ipod.rb:1:in `<top (required)>'\r\n        from C:/Ruby226/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:122:in `require'\r\n        from C:/Ruby226/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:122:in `require'\r\n        from C:/Projekte/localware/management/management.rb:222:in `const_missing'\r\n        from (irb):2\r\n        from C:/Ruby226/bin/irb:11:in `<main>'\r\n```\r\nAgain as plain query with my Database connection it is working fine.\r\n\r\n\r\nCause of the working queries with plain DB connection i suspect the error is a combination of the model dataset and the adapter logic.\r\n\r\nThx in advance!"},"comment":{"url":"https://api.github.com/repos/jeremyevans/sequel/issues/comments/440776221","html_url":"https://github.com/jeremyevans/sequel/issues/1574#issuecomment-440776221","issue_url":"https://api.github.com/repos/jeremyevans/sequel/issues/1574","id":440776221,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0MDc3NjIyMQ==","user":{"login":"jeremyevans","id":3846,"node_id":"MDQ6VXNlcjM4NDY=","avatar_url":"https://avatars3.githubusercontent.com/u/3846?v=4","gravatar_id":"","url":"https://api.github.com/users/jeremyevans","html_url":"https://github.com/jeremyevans","followers_url":"https://api.github.com/users/jeremyevans/followers","following_url":"https://api.github.com/users/jeremyevans/following{/other_user}","gists_url":"https://api.github.com/users/jeremyevans/gists{/gist_id}","starred_url":"https://api.github.com/users/jeremyevans/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jeremyevans/subscriptions","organizations_url":"https://api.github.com/users/jeremyevans/orgs","repos_url":"https://api.github.com/users/jeremyevans/repos","events_url":"https://api.github.com/users/jeremyevans/events{/privacy}","received_events_url":"https://api.github.com/users/jeremyevans/received_events","type":"User","site_admin":false},"created_at":"2018-11-21T19:02:07Z","updated_at":"2018-11-21T19:02:07Z","author_association":"OWNER","body":"The error message is probably caused because you are selecting multiple columns with the same alias.  You should use `select` on the dataset you are passing to `set_dataset` and give each column in the dataset a unique alias.\r\n\r\nThe ORDER BY issue is because you can't use ORDER BY in subqueries, and Sequel wraps your dataset in a subquery because model datasets are designed to only select from a single table or subquery.\r\n\r\nNote that this is a request for help using Sequel, so in the future you should make requests like this on the sequel-talk Google Group or the IRC channel.  Please only use GitHub Issues to report bugs in Sequel itself, as specified by the contribution guidelines."}}
{"actor":{"display_login":"jeremyevans"}}