Обсуждение: garbage data back
I was doing a SELECT * FROM view WHERE field LIKE pattern
![image.png](/media/maillist_attaches/pgsql-general/2023/03/6/CAA_1=91wec476kHOYbMWqL=1ZUR1MTiGJZJ=dain1g+OBgA0ng@mail.gmail.com/image.png)
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'
where "FileKey" LIKE '%1317'
![image.png](/media/maillist_attaches/pgsql-general/2023/03/6/CAA_1=91wec476kHOYbMWqL=1ZUR1MTiGJZJ=dain1g+OBgA0ng@mail.gmail.com/image.png)
I'm stumped. Any guesses?
Вложения
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
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.
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"
...
(("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](/media/maillist_attaches/pgsql-general/2023/03/6/CAA_1=91Aej78jB__tgpfwWB_j_g1LULj7Yx9wUPVA2DeJbZ5wQ@mail.gmail.com/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
Вложения
On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:
LOLJoke's on me.Here's the relevant part of the viewSELECT ..."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.
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.
Вложения
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
In that picture, it's clear what is happening.![image.png](/media/maillist_attaches/pgsql-general/2023/03/6/CAA_1=93Y9JLdX=zBYt+LbyBp4Z4aGw+hz4q6aYJXW4A2G_nWNw@mail.gmail.com/image-1.png)
Here, again, is the result that had me stumped.
![image.png](/media/maillist_attaches/pgsql-general/2023/03/6/CAA_1=93Y9JLdX=zBYt+LbyBp4Z4aGw+hz4q6aYJXW4A2G_nWNw@mail.gmail.com/image-1.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:LOLJoke's on me.Here's the relevant part of the viewSELECT ..."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.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.
Вложения
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
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 ANALYZEREINDEX (on any underlying table indexes)Show the version you are running.David J.