Thread: not reducing same values in IN

Hello,

I'd like to ask some pg developer how complicated task would
be to patch query processing (most likely parser) to allow
for the following non-sql change. For a certain scientific
application where a bootstrapping of data is needed I would
like to select from a database "with replacement", i.e.
to be able to send "SELECT col FROM tab WHERE col IN (5,5)"
and receive two line of output (two fives) where only one
is stored in the database. One way is to workaround by 
with SELECT ... col IN (5) UNION ALL SELECT ... col IN (5),
for complex queries however, I run into difficulties.
Would a rather small patch at some point in the source code 
made this possible or it sounds a bit unrealistic? 
Thank you.

--Vojtech

vojtech@hafro.is writes:
> I'd like to ask some pg developer how complicated task would
> be to patch query processing (most likely parser) to allow
> for the following non-sql change. For a certain scientific
> application where a bootstrapping of data is needed I would
> like to select from a database "with replacement", i.e.
> to be able to send "SELECT col FROM tab WHERE col IN (5,5)"
> and receive two line of output (two fives) where only one
> is stored in the database.

This does not seem like an easy change at all.  According to the
SQL semantics, WHERE is a filter condition that indicates whether
to pass on the current row (if TRUE) or not (if not TRUE).
Rejiggering the behavior as you suggest would mean a fundamental
alteration in semantic assumptions --- I don't even want to think
about what it might break.

> One way is to workaround by 
> with SELECT ... col IN (5) UNION ALL SELECT ... col IN (5),
> for complex queries however, I run into difficulties.

Fixing your app to generate the UNION ALL seems like a much more
promising approach ...
        regards, tom lane