Re: puzzling perl DBI vs psql problem

On Mar 13, 2014, at 12:18 PM, Susan Cassidy <susan(dot)cassidy(at)decisionsciencescorp(dot)com> wrote:

> I have a weird issue that I can't figure out.
>
> If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line.
>
> Any ideas why that would be?
>
> The query is:
> SELECT st.description, st.scene_thing_instance_id,
> st.scene_id, sc.description, st.scene_thing_id, s.description,
> st.position_x, st.position_y, st.position_z,
> CASE
> when (st.description = 'absolute root'::text) then 1
> when (st.description ilike 'root%') then 2
> else 3
> END as s1, s.shape_name_id, sn.shape_name
> from scene_thing_instances st
> left join scene_things s on st.scene_thing_id = s.scene_thing_id
> left join scenes sc on st.scene_id = sc.scene_id
> left outer join shape_names sn on s.shape_name_id = sn.shape_name_id
>
> order by s1, st.description
>
> I get 14 rows back via psql, but I only get 5 rows back via DBI. It's very puzzling.
>
> I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to.

How are you quoting the string in perl, and are you running with use strict?

My first thought would be that you’re not running the query you think you are - logging it at the postgresql side will let you check that (or if that’s not possible, DBI’s trace methods can help).

Cheers,
Steve