Thread: garbage data back

garbage data back

From
Brad White
Date:
I was doing a SELECT * FROM view WHERE field LIKE pattern
and getting garbage data back.

Turns out it is filtering on a different field.

As you can see here, when attempting to filter on the FileKey, it is actually filtering on the Order Item ID field.

select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'

image.png
I'm stumped. Any guesses?
Attachment

Re: garbage data back

From
Adrian Klaver
Date:
On 3/6/23 12:17, Brad White wrote:
> I was doing a SELECT * FROM view WHERE field LIKE pattern
> and getting garbage data back.
> 
> Turns out it is filtering on a different field.
> 
> As you can see here, when attempting to filter on the FileKey, it is 
> actually filtering on the Order Item ID field.
> 
> select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> where "FileKey" LIKE '%1317'
> 
> image.png
> I'm stumped. Any guesses?


What is the view definition?

Is there a table named vw_rptInvc_Permits?

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: garbage data back

From
"David G. Johnston"
Date:
On Mon, Mar 6, 2023 at 1:18 PM Brad White <b55white@gmail.com> wrote:

As you can see here, when attempting to filter on the FileKey, it is actually filtering on the Order Item ID field.

select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'

I'm stumped. Any guesses?

Agreed, what you've shown doesn't make sense.

Try:
EXPLAIN ANALYZE
REINDEX (on any underlying table indexes)

Show the version you are running.

David J.

Fwd: garbage data back

From
Brad White
Date:
LOL

Joke's on me.

Here's the relevant part of the view

SELECT ...
    "Order Items"."ID" AS "OrderItemID",
    ...
    (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
image.png

On Mon, Mar 6, 2023 at 2:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/6/23 12:17, Brad White wrote:
> I was doing a SELECT * FROM view WHERE field LIKE pattern
> and getting garbage data back.
>
> Turns out it is filtering on a different field.
>
> As you can see here, when attempting to filter on the FileKey, it is
> actually filtering on the Order Item ID field.
>
> select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> where "FileKey" LIKE '%1317'
>
> image.png
> I'm stumped. Any guesses?


What is the view definition?

Is there a table named vw_rptInvc_Permits?

--
Adrian Klaver
adrian.klaver@aklaver.com

Attachment

Re: garbage data back

From
"David G. Johnston"
Date:
On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:
LOL

Joke's on me.

Here's the relevant part of the view

SELECT ...
    "Order Items"."ID" AS "OrderItemID",
    ...
    (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
image.png

I don't understand what you are questioning...FileKey is a hyphenated concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is just the later - and the suffix of the former matches the later.

David J.
Attachment

Re: Fwd: garbage data back

From
Adrian Klaver
Date:
On 3/6/23 12:48 PM, Brad White wrote:
> LOL
> 
> Joke's on me.
> 
> Here's the relevant part of the view
> 
> SELECT ...
>      "Order Items"."ID" AS "OrderItemID",
>      ...
>      (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"\

Because "Orders"."ID"  is different then "Order Items"."ID"?

> 
> Wait, then why are the Item IDs different?
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: garbage data back

From
Brad White
Date:
In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
image.png
What threw me was that the field I was looking for had the format of 6d-4d and this field appeared to have the same format.
But once you expand it, you can see that it has a 6d-7d format and isn't the field I'm looking for at all.  I was trying to take a shortcut by finding the data in the table instead of digging in and looking up what field was used in the report. And it appeared at first that I had found it.  ¯\_(?)_/¯

On Mon, Mar 6, 2023 at 2:54 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:
LOL

Joke's on me.

Here's the relevant part of the view

SELECT ...
    "Order Items"."ID" AS "OrderItemID",
    ...
    (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
image.png

I don't understand what you are questioning...FileKey is a hyphenated concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is just the later - and the suffix of the former matches the later.

David J.
Attachment

Re: garbage data back

From
Adrian Klaver
Date:
On 3/6/23 1:27 PM, Brad White wrote:
> In that picture, it's clear what is happening.
> Here, again, is the result that had me stumped.
> image.png
> What threw me was that the field I was looking for had the format of 
> 6d-4d and this field appeared to have the same format.
> But once you expand it, you can see that it has a 6d-7d format and isn't 
> the field I'm looking for at all.  I was trying to take a shortcut by 
> finding the data in the table instead of digging in and looking up what 
> field was used in the report. And it appeared at first that I had found 
> it.  ¯\_(?)_/¯

psql is your friend.




-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: garbage data back

From
Brad White
Date:
David,

Thanks!
'EXPLAIN ANALYZE' is very slick.
Promises to be very helpful.
As soon as I saw the output, it was obvious where my problem was.

Brad.

On Mon, Mar 6, 2023 at 2:30 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Mar 6, 2023 at 1:18 PM Brad White <b55white@gmail.com> wrote:

As you can see here, when attempting to filter on the FileKey, it is actually filtering on the Order Item ID field.

select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'

I'm stumped. Any guesses?

Agreed, what you've shown doesn't make sense.

Try:
EXPLAIN ANALYZE
REINDEX (on any underlying table indexes)

Show the version you are running.

David J.