Order of returned fields in a trivial fetch()

[wandhydrant]

Hello. I try to get my feet wet with RDBI, but I have an extremely basic and confusing problem. I run Debian Squeeze. I have a very simple PostgreSQL 8.4 database :

CREATE TABLE my_projects (
       id SERIAL PRIMARY KEY,
       name VARCHAR(255) UNIQUE
);
INSERT INTO my_projects (name) VALUES ('testproject');
SELECT * FROM my_projects;
 id |    name     
----+-------------
  1 | testproject
(1 row)

With RDBI (gems 0.9.1), I get a completely messed up order of fields for my trivial statement:

require 'rubygems'
require 'rdbi-driver-postgresql'
dbh = RDBI.connect(:PostgreSQL, :host => 'localhost', :user => 'myuser', :database => 'mydb', :password => 'mysecret')
dbh.execute("SELECT id, name FROM my_projects").fetch
#=> [["testproject", "1"]]
dbh.execute("SELECT id, name FROM my_projects").fetch(1, :Struct)
#=> [#<struct #<Class:0x7f8619f62d80> id="testproject", name="1">]
# Note how the order stays the same, but the "1" suddenly switches from a String to an Integer :
dbh.execute("SELECT name, id FROM my_projects").fetch
#=> [["testproject", 1]]
dbh.execute("SELECT name, id FROM my_projects").fetch(1, :Struct)
#=> [#<struct #<Class:0x7f861a00fb48> name="testproject", id=1>]

With Ruby-DBI (gems dbi 0.4.5, dbd-pg 0.3.9) things work as expected:

require 'rubygems'
require 'dbi'
dbh = DBI.connect('DBI:Pg:mydb', 'myuser', 'mysecret')
dbh.execute("SELECT id, name FROM my_projects").fetch
#=> [1, "testproject"]
dbh.execute("SELECT name, id FROM my_projects").fetch
#=> ["testproject", 1]

Did I manage to do something wrong in these few lines, or did I really find a bug (maybe with the particular PostgreSQL datatype "serial") ?

[pilcrow]

Thank you for the detailed report. Please try RDBI from the master branch.

I cannot reproduce your symptoms, neither field reordering nor type conversion, with the following configuration:

$ ruby wandhydrant-20110609.rb postgres
ruby 1.9.2
rdbi 1.0.0
epoxy 0.3.1
typelib 0.1.0
rdbi-driver-postgresql 0.9.1
pg 0.9.0
methlab 0.1.0

SELECT id, name FROM my_projects
    [[1, "testproject"]]
    [#<struct id=1, name="testproject">]
SELECT name, id FROM my_projects
    [["testproject", 1]]
    [#<struct name="testproject", id=1>]

Test code is:

$ cat wandhydrant-20110609.rb
#!/usr/bin/env ruby

require 'rdbi'
require 'rdbi/driver/postgresql'

puts 'ruby ' + RUBY_VERSION
Gem.loaded_specs.each do |name, gem|
  puts name + ' ' + gem.version.to_s
end
puts

conn_args = { :database => ARGV[0], :user => ARGV[1] }
conn_args[:password] = ARGV[2] if ARGV.length > 2

fields = %w(id name)
RDBI::connect(:PostgreSQL, conn_args) do |dbh|
  [ fields, fields.reverse ].each do |cols|
    sql = "SELECT " + cols.join(', ') + " FROM my_projects"
    puts sql
    puts "\t" + dbh.execute(sql).fetch.inspect
    puts "\t" + dbh.execute(sql).fetch(1, :Struct).inspect
  end
end

[wandhydrant]

Goodness, this is already one and a half years ago? Shame on me!

This bug is certainly due to my Ruby 1.8.7 installation. Debian Squeeze comes with that, and with Ruby 1.9.2p0. Though I cannot say at which level in the stack the bug is located. Well, it does not seem that many others are concerned...

ruby 1.8.7
pg 0.9.0
typelib 0.1.0
methlab 0.1.0
epoxy 0.3.1
rdbi 0.9.1
rdbi-driver-postgresql 0.9.1

SELECT id, name FROM my_projects
    [["testproject", "1"]]
    [#<struct #<Class:0x7ffe3a949568> id="testproject", name="1">]
SELECT name, id FROM my_projects
    [["testproject", 1]]
    [#<struct #<Class:0x7ffe3a942df8> name="testproject", id=1>]```

and

ruby 1.9.2
rdbi 0.9.1
methlab 0.1.0
epoxy 0.3.1
typelib 0.1.0
rdbi-driver-postgresql 0.9.1
pg 0.9.0

SELECT id, name FROM my_projects
    [[1, "testproject"]]
    [#<struct id=1, name="testproject">]
SELECT name, id FROM my_projects
    [["testproject", 1]]
    [#<struct name="testproject", id=1>]

[grantj-re3]

FYI, I have an identical problem on a work machine running RHEL 6.5. I guess I'm not expecting this to be fixed as ruby 1.8.7 is pretty old (and I am not a Sys Admin or DB Admin on the machine). However I thought you might be interested in another report with yet another list of gem versions. Cheers.

ruby -v
ruby 1.8.7 (2011-06-30 patchlevel 352) [x86_64-linux]
psql --version
psql (PostgreSQL) 8.4.20

ruby 1.8.7
typelib 0.1.0
rdbi-driver-postgresql 0.9.2
pg 0.17.1
epoxy 0.3.1
rdbi 1.1.0

[vschiavoni]

Consider that 1.8.7 reached EOL and it has been retired: https://www.ruby-lang.org/en/news/2013/06/30/we-retire-1-8-7/ so do not expect bug fixes for that specific version.