1. Michael Granger
  2. ruby-pg

Issues

Issue #148 invalid

Subquery column can't get values from teiid server. but use psql can get subquery column result correctly.

xds2000
created an issue

I can use psql client tools to query subquery column sql from teiid server(consider as postgres server), but what if i use ruby script and ruby-pg 0.14.1

test code:

#!/usr/bin/env ruby

require 'pg'

conn = PG.connect('vdb.xxx.com', 35432, '', '', 'EngVDBF', 'teiid', 'teiid')
$stderr.puts '---',
        RUBY_DESCRIPTION,
        PG.version_string( true ),
        "Server version: #{conn.server_version}",
        "Client version: #{PG.respond_to?( :library_version ) ? PG.library_version : 'unknown'}",
        '---'

        result = conn.exec( "
                        select t.EffectiveId as ticketid,
                        t.Status,t.Subject,
                        ou.name as Owner,
                        cu.name as Creator,
                        FORMATTIMESTAMP(t.Created,'yyyy-MM-dd') as Created,
                        FORMATTIMESTAMP(t.Resolved,'yyyy-MM-dd') as Resolved,
                        (
                         select ofv.Content as BudgetType
                         from RT4S.ObjectCustomFieldValues ofv
                         left join RT4S.CustomFields cf on cf.id = CustomField
                         where ofv.ObjectId=t.EffectiveId
                         and ofv.ObjectType='RT\:\:Ticket'
                        and cf.name = 'Budget Type'
                         and ofv.Disabled=0 
                        ) as BudgetType
                        from RT4S.Tickets t
                        left join RT4S.Users ou on ou.id = t.Owner
                        left join RT4S.Users cu on cu.id = t.Creator
                        left join RT4S.queues q on q.id = t.queue
                        where
        t.id = t.EffectiveId
        and q.name='eng-ops-purchasing'
        and t.Created >= {ts '2012-01-01 00:00:00'} and t.Created <= curdate()
        and t.EffectiveId = 153544
        " )

        $stderr.puts %Q{Expected this to return: ["select * from rt4s"]}
        p result.field_values( 'BudgetType' )

result:


ruby 1.9.3p194 (2012-04-20 revision 35410) [x86_64-linux] PG 0.14.1 (build ef533f731814) Server version: 80104 Client version: unknown


Expected this to return: ["select * from rt4s"] [""]

but in database, the budgettype column have value. i use above same sql in psql client, i can get the value from the budgettype column.

Comments (12)

  1. Lars Kanis

    Subquery expressions don't work different to other types of expressions from pg's point of view. Maybe different schema path or locale settings to that of psql?

    In order to get more help, you need to reduce the complexity of your query or provide the CREATE TABLE, CREATE FUNCTION and INSERT statements needed to reproduce the issue.

  2. xds2000 reporter

    Lars Kanis thanks for your answer patiently. the complicate environment block me can't easy to provide CREATE TABLE, CREATE FUNCTION and INSERT statements quickly. i need some spare time to setup the environment and reproduce the issue. thanks a lot. when i finished, i will paste here ASAP.

  3. xds2000 reporter

    Lars Kanis today i finally setup the test environment and also reproduce this bug.

    psql (9.2.2) , postgresql 9.2.2

    psql test sql

    CREATE TABLE Tickets(
    id bigserial primary key,
    EffectiveId integer, Queue integer, Status varchar(64) NOT NULL, Created timestamp default NULL );

    CREATE TABLE ObjectCustomFieldValues( id integer, CustomField integer, ObjectType varchar(255) NOT NULL, ObjectId integer, Content varchar(255), Disabled smallint );

    CREATE TABLE CustomFields( id integer, Name varchar(200) );

    CREATE TABLE Queues( id integer, Name varchar(200) );

    insert into Queues(id,name) values(1,'eng-ops-purchasing');

    insert into ObjectCustomFieldValues(id,CustomField,ObjectType,ObjectId,Content,Disabled) values (1,1,'RT\:\:Ticket',153544,'Capex',0);

    insert into CustomFields(id,name) values(1,'Budget Type');

    insert into Tickets(EffectiveId,Queue,Status,Created) values(153544,1,'open',TIMESTAMP '2012-05-16 15:36:38');

    select t.EffectiveId as ticketid, t.Status, to_char(t.Created,'yyyy-MM-dd') as Created, ( select ofv.Content as BudgetType from ObjectCustomFieldValues ofv left join CustomFields cf on cf.id = CustomField where ofv.ObjectId=t.EffectiveId and ofv.ObjectType='RT\:\:Ticket' and cf.name = 'Budget Type' and ofv.Disabled= 0 ) as BudgetType from Tickets t left join queues q on q.id = t.queue where q.name='eng-ops-purchasing' and t.Created >= TIMESTAMP '2012-01-01 00:00:00' and t.Created <= now() and t.EffectiveId = 153544

    test result:

    [~/Code/test-teiid] $ ruby teiid-ruby-local.rb

    ruby 1.9.3p194 (2012-04-20 revision 35410) [x86_64-darwin11.4.0] PG 0.14.1 (build ef533f731814) Server version: 90202 Client version: 90202


    Expected this to return: ["select * from tickets"] ["ticketid", "status", "created", "budgettype"] ["153544"] ["open"] ["2012-05-16"] [""]

    in psql result is different: test=# select t.EffectiveId as ticketid, test-# t.Status, test-# to_char(t.Created,'yyyy-MM-dd') as Created, test-# ( test(# select ofv.Content as BudgetType test(# from ObjectCustomFieldValues ofv test(# left join CustomFields cf on cf.id = CustomField test(# where ofv.ObjectId=t.EffectiveId test(# and ofv.ObjectType='RT\:\:Ticket' test(# and cf.name = 'Budget Type' test(# and ofv.Disabled= 0 test(# ) as BudgetType test-# from Tickets t test-# left join queues q on q.id = t.queue test-# where test-# q.name='eng-ops-purchasing' test-# and t.Created >= TIMESTAMP '2012-01-01 00:00:00' test-# and t.Created <= now() test-# and t.EffectiveId = 153544 test-# test-# ; ticketid | status | created | budgettype ----------+--------+------------+------------ 153544 | open | 2012-05-16 | Capex (1 row)

  4. xds2000 reporter

    Maybe this is not bug. it seem i need use another way to get value conn.send_query("...") res = conn.get_result p res. field_values("BudgetType")

    currently i have no testing env to confirm it. please hold on.

  5. Log in to comment