Query result overriding in db<>fiddle and xtuple

Question

This a follow-up to a question from 9/15/25: Query that checks if an item name contains a substring, and if true, perform a second query that grabs an image with a specific name

I have a query that grabs image data from tables joined on substrings. The item substring I need to match changes depending on the item so I need to run multiple queries. Example: https://dbfiddle.uk/ZrUkfuab

create table image(
    image_name text,
    image_data bytea
);

INSERT INTO image
VALUES     ( 'PRODUCT ANALOG DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')),
        ('PRODUCT ANALOG DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')),
        ('PRODUCT MAIN DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')),
        ('PRODUCT FRAM DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')),
        ('PRODUCT AAAA DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')),
        ('PRODUCT BD DATASHEET',
         Convert_to(Md5(Random() :: text), 'UTF8')); 

create table wo(wo_id int,item_number text);
insert into wo values(1,'PRODUCT - MAIN BD - FRAM-MSTRW/O FBR')
                ,(2,'PRODUCT-ANLG-GIC2B-2AIN')
                ,(3,'PRODUCT - ANALOG BD - 4CT/4AIN')
                ,(4,'PRODUCT-AAAA-BBBBB-4XX');

create table Patterns(Pattern_id int, PatternString text);
insert into Patterns values(1,'^PRODUCT - MAIN BD -.(\w+).*')
                ,(2,'^PRODUCT-ANLG.(\w+).*')
                ,(3,'^PRODUCT -.(\w+).*')
                ,(4,'^PRODUCT.(\w+).*');

This successfully returns the image data:

SELECT item_number, image_name, image_data 
FROM wo
JOIN image ON REGEXP_REPLACE(image_name,'^PRODUCT.(\w+).*','\1')
             =REGEXP_REPLACE(item_number,(SELECT PatternString 
                                          FROM Patterns 
                                          WHERE Pattern_id = 3),'\1')
WHERE wo_id=3;

A different pattern results in zero matches, no results:

SELECT item_number, image_name, image_data 
FROM wo
JOIN image ON REGEXP_REPLACE(image_name,'^PRODUCT.(\w+).*','\1')
             =REGEXP_REPLACE(item_number,(SELECT PatternString 
                                          FROM Patterns 
                                          WHERE Pattern_id = 4),'\1')
WHERE wo_id=3;

This third block combines both queries. The query that doesn’t return anything is first, followed by one that does. This block successfully returns the image data, from the second query in it.

SELECT item_number, image_name, image_data 
FROM wo
JOIN image ON REGEXP_REPLACE(image_name,'^PRODUCT.(\w+).*','\1')
             =REGEXP_REPLACE(item_number,(SELECT PatternString 
                                          FROM Patterns 
                                          WHERE Pattern_id = 4),'\1')
WHERE wo_id=3;

SELECT item_number, image_name, image_data 
FROM wo
JOIN image ON REGEXP_REPLACE(image_name,'^PRODUCT.(\w+).*','\1')
             =REGEXP_REPLACE(item_number,(SELECT PatternString 
                                          FROM Patterns 
                                          WHERE Pattern_id = 3),'\1')
WHERE wo_id=3;

However, when I flip the order of queries in this block, no image data is returned. The (lack of) result of the second query seems to be overriding the result of the first query.

How to get around it? For loops? Case Expressions? CTE’s?

I really don’t want to give each query its own dedicated block because it would make organizing the report template a complete nightmare.

I don't understand your problem. What is the expected result? Please provide sample input data and expected result as markdown tables in your question. It must be clear without fiddle sites or other links. And the fiddle doesn't tell me what you want. Your description doesn't, too. It is confusing. — Jonas Metzler
– Jonas Metzler, Commented Sep 19 at 4:16
Vote to reopen. As written, this question is not lacking any information it needs to be answered. There was sufficient detail to answer from the start. It's a problem with db<>fiddle itself (possibly and identical one with xtuple), which makes it conveniently, trivially, perfectly clear and reproducible: anyone visiting this thread has full access to the exact thing OP has a problem with. — Zegarek
– Zegarek, Commented Sep 19 at 14:06
@Zegarek I disagree. Questions should be answereable standalone, this means it has to include both sample input data and expected result. If the fiddle url they posted gets invalid, their question is alomst useless. Furthermore, the results of the queries in the fiddle differ if you change the Postgres version there. So in my opinion, the question needs a lot of improvement and further details. — Jonas Metzler
– Jonas Metzler, Commented Sep 20 at 8:12
@JonasMetzler Self-containment addressed in latest edit, DDL added in a collapsible snippet. The post was tagged postgresql-9.6 from the start, so changing the version to make it go away isn't an argument against the question but rather one of potential solutions. An unlikely one, because OP would have to upgrade xtuple to make that work in their target environment, but a good one nevertheless. — Zegarek
– Zegarek, Commented Sep 22 at 11:16

Zegarek · Accepted Answer · 2025-09-19 05:49:48Z

The result of the second query is clearly overriding the result of the first query.

That's a quirk of db<>fiddle, it sometimes hides the results of multiple queries sharing a cell, leaving just the last one:
_{demo at db<>fiddle}

select 1;
select 2;


2

You can work around this by splitting up your cells so that in each one, there's at most one statement that's supposed to return something: https://dbfiddle.uk/dPaR9opH

I really don’t want to give each query its own dedicated block because it would make organizing the report template a complete nightmare.

Postgres doesn't do this but I wouldn't be surprised if xtuple only used the last query from that dialog, similar to db<>fiddle. Check with their support and documentation or run some tests in xtuple. If you can confirm that's the case, you do need to merge your multiple statements into one, with CTEs and subqueries.

If your goal was to join on multiple patterns, join the Patterns table:

SELECT item_number
     , image_name
     , image_data
     , PatternString
FROM wo
JOIN Patterns ON true /* ON Pattern_id IN (3,4) */
JOIN image 
  ON REGEXP_REPLACE(image_name,'^PRODUCT.(\w+).*','\1')
    =REGEXP_REPLACE(item_number,PatternString,'\1')
WHERE wo_id= 3/*<? value("wo_id") ?> */;

You can add more WHERE or ON conditions to use specific ones.

Important reminder, already mentioned in the previous thread: check your ERD diagram and make sure there's no other way to join your tables on regular, hopefully numeric identifiers, through a chain of Primary Key=Foreign Key. Joining on patterns and substrings is extremely inefficient.

If you designed the Patterns table, note that Postgres folds unquoted identifiers to lowercase, so its real name is patterns unless you create it as "Patterns". Same for PatternString and Pattern_id. You're also mixing PascalCase with snake_case, a bit randomly.

I actually discovered it in Xtuple first. The image wasn’t appearing consistently, so I tried switching the order of the queries around and discovered that the image would only appear if the successful query was last. I then used dbfiddle to confirm the behavior and use it for my example here. Anyway, “JOIN patterns ON true” didn’t work, but “JOIN patterns ON pattern_id IN (1,2,3,4)” did. I didn’t know I could do either of those things. I don’t really understand how the SQL code knows to loop through all of the id’s and stop when it gets a match, but I’ll take it. Thank you so much again.
In declarative languages you just state what you need, and it's down to the system (rewriter, optimiser, planner, executor parts of Postgres in this case) to decide how to do it in an optimal way and then do it.

Collectives™ on Stack Overflow

Query result overriding in db<>fiddle and xtuple

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related