JOIN table with itself, and filter rows from each table with bitwise operation

Question

I have the following table,

-- Generated with pg_dump, some constraints are missing
CREATE TABLE articulos_factura_venta (
    fila integer NOT NULL,
    cantidad integer NOT NULL,
    color integer NOT NULL,
    talla integer NOT NULL,
    estado integer DEFAULT 2 NOT NULL,
    origen integer,
    factura integer NOT NULL,
    articulo integer NOT NULL,
    precio integer NOT NULL,
    vendedor integer,
    anulado boolean DEFAULT false,
    iva double precision DEFAULT 12.0,
    fecha date DEFAULT ('now'::text)::date NOT NULL
);

and it contains the following rows¹

 fila | cantidad | color | talla | estado | origen | factura | articulo | precio | vendedor | anulado | iva |   fecha    
------+----------+-------+-------+--------+--------+---------+----------+--------+----------+---------+-----+------------
    0 |        1 |     0 |     3 |      6 |     18 |   28239 |     1325 |    455 |        6 | f       |   0 | 2015-04-22
    1 |        1 |     0 |     2 |      6 |     93 |   28239 |     2071 |    615 |        6 | f       |   0 | 2015-04-22
    2 |        1 |     0 |    49 |      6 |     76 |   28239 |     2013 |    545 |        6 | f       |   0 | 2015-04-22
    3 |        1 |     0 |    78 |      6 |     85 |   28239 |     2042 |    235 |        6 | f       |   0 | 2015-04-22
    4 |        1 |     0 |    49 |      6 |     81 |   28239 |     2026 |    615 |        6 | f       |   0 | 2015-04-22
    5 |        1 |     0 |    50 |      6 |     90 |   28239 |     2051 |    755 |        6 | f       |   0 | 2015-04-22
    6 |        1 |     0 |     1 |     38 |     21 |   28239 |     1780 |    495 |        6 | f       |   0 | 2015-04-22
    7 |        1 |    15 |     2 |     38 |     16 |   28239 |     1323 |    845 |        6 | f       |   0 | 2015-04-22
    8 |        1 |     0 |     4 |     38 |     18 |   28239 |     1326 |    455 |        6 | f       |   0 | 2015-04-22
    2 |        1 |     0 |    49 |     22 |     76 |   28239 |     2013 |    545 |        6 | f       |   0 | 2015-04-22

The question is very straight forward, why this query outputs no rows?

SELECT
    filas.factura,
    filas.fila,
    filas.cantidad,
    retirados.cantidad,
    vendidos.cantidad,
    filas.estado
FROM
    articulos_factura_venta AS filas
LEFT JOIN
    articulos_factura_venta AS retirados
    USING (fila, color, talla, origen, factura, articulo, vendedor)
LEFT JOIN
    articulos_factura_venta AS vendidos
    USING (fila, color, talla, origen, factura, articulo, vendedor)
JOIN
    articulos
    ON articulos.codigo = filas.articulo
JOIN
    tallas
    ON tallas.codigo = filas.talla
JOIN
    colores
    ON colores.codigo = filas.color
JOIN
    empleados
    ON empleados.codigo = filas.vendedor
WHERE
    filas.factura = 28239 AND 
    retirados.estado & 16 <> 0 AND 
    vendidos.estado & 8 <> 0 AND
    filas.estado & 4 <> 0
ORDER BY
    filas.estado

I expect this query to subtract cantidad from the row that has fila == 2 the case where estado & 16 <> 0 and hence I expect only one row with fila == 2 and cantidad = 0

NOTE: The bit flags, are not hardcoded, they are an enum that I use in the actual application written with c++.

Table definition

database# \d articulos_factura_venta
  Column  |       Type       |              Modifiers               
----------+------------------+--------------------------------------
 fila     | integer          | not null
 cantidad | integer          | not null
 color    | integer          | not null
 talla    | integer          | not null
 estado   | integer          | not null default 2
 origen   | integer          | 
 factura  | integer          | not null
 articulo | integer          | not null
 precio   | integer          | not null
 vendedor | integer          | 
 anulado  | boolean          | default false
 iva      | double precision | default 12.0
 fecha    | date             | not null default ('now'::text)::date
Indexes:
    "articulos_factura_venta_pkey" PRIMARY KEY, btree (fila, factura, articulo, precio, talla, color, estado)
    "buscar_cantidad_venta_idx" btree (articulo, talla, color, origen)
Foreign-key constraints:
    "cantidades_venta_articulo_fkey" FOREIGN KEY (articulo) REFERENCES articulos(codigo)
    "cantidades_venta_color_fkey" FOREIGN KEY (color) REFERENCES colores(codigo) ON UPDATE CASCADE ON DELETE RESTRICT
    "cantidades_venta_factura_fkey" FOREIGN KEY (factura) REFERENCES ventas(codigo)
    "cantidades_venta_origen_fkey" FOREIGN KEY (origen) REFERENCES compras(codigo) ON UPDATE CASCADE ON DELETE RESTRICT
    "cantidades_venta_talla_fkey" FOREIGN KEY (talla) REFERENCES tallas(codigo) ON UPDATE CASCADE ON DELETE RESTRICT
    "cantidades_venta_vendedor_fkey" FOREIGN KEY (vendedor) REFERENCES empleados(codigo)

^[1]_{The table contains thousands of rows, but I am interested in these rows only, i.e. rows for which factura == 28239.}

`some constraints are missing` ... but constraints are *essential* for building the right query. Please give the complete picture - what you get from `\d articulos_factura_venta` in psql. — Erwin Brandstetter, Apr 23 '15 at 01:57

score 2 · Accepted Answer · edited May 23 '17 at 12:29

Long story short, it might work like this:

SELECT f.factura
     , f.fila
     , f.cantidad
     , r.cantidad
     , v.cantidad
     , f.estado
FROM   articulos_factura_venta f
-- JOIN   articulos a ON a.codigo = f.articulo  -- just noise
-- JOIN   tallas    t ON t.codigo = f.talla
-- JOIN   colores   c ON c.codigo = f.color
JOIN   empleados e ON e.codigo = f.vendedor
LEFT   JOIN articulos_factura_venta r ON r.fila = f.fila
                                     AND r.color = f.color
                                     AND r.talla = f.talla
                                     AND r.origen = f.origen
                                     AND r.factura = f.factura
                                     AND r.articulo = f.articulo
                                     AND r.vendedor = f.vendedor
                                     AND r.estado & 16 <> 0
LEFT   JOIN articulos_factura_venta v ON v.fila = f.fila
                                     AND v.color = f.color
                                     AND v.talla = f.talla
                                     AND v.origen = f.origen
                                     AND v.factura = f.factura
                                     AND v.articulo = f.articulo
                                     AND v.vendedor = f.vendedor
                                     AND v.estado & 8 <> 0
WHERE  f.factura = 28239
AND    f.estado & 4 <> 0
ORDER  BY f.estado;

In particular these added WHERE clauses voided your attempted LEFT JOIN on the respective tables and made it act like a JOIN:

AND r.estado & 16 <> 0
AND v.estado & 8 <> 0

One other sticky detail:

JOIN   empleados e ON e.codigo = f.vendedor

But f.vendedor can be NULL. Is it your intention to remove all rows with f.vendedor IS NULL from the result? Because that's what the join does.

And I commented the three joins to articulos, tallas and colores. The FK columns are NOT NULL, the join does nothing but cost time and you are not using any of the columns.

Table definition

A primary key constraint over 7 columns is a terrible idea. Expensive and unwieldy. Add a surrogate primary key - I suggest a serial column:

You can still enforce uniqueness on your set of 7 columns with a UNIQUE constraint - if you actually need that.
About UNIQUE and PRIMARY KEY constraints (per request in comment):

Suggested table design:

CREATE TABLE articulos_factura_venta (
    afv_id serial PRIMARY KEY  -- pick your column name
    fila integer NOT NULL,
    cantidad integer NOT NULL,
    color integer NOT NULL,
    talla integer NOT NULL,
    estado integer DEFAULT 2 NOT NULL,
    factura integer NOT NULL,
    articulo integer NOT NULL,
    precio integer NOT NULL,
    fecha date NOT NULL DEFAULT now()::date,
    origen integer,
    vendedor integer,
    anulado boolean DEFAULT false,  -- NOT NULL ?
    iva double precision DEFAULT 12.0,
    CONSTRAINT uni7  -- pick your contraint name
     UNIQUE (fila, factura, articulo, precio, talla, color, estado)
);

Then the query can be simplified to:

...
LEFT   JOIN articulos_factura_venta r ON r.afv_id = f.afv_id
                                     AND r.estado & 16 <> 0
LEFT   JOIN articulos_factura_venta v ON v.afv_id = f.afv_id
                                     AND v.estado & 8 <> 0
...

@iharob: That means that one row has two matches in one of the joined tables, which probaly should not occur ... — Erwin Brandstetter, Apr 23 '15 at 02:18
It's because some of the bits are set in both rows, `4` is almost in every row, so adding `f.estado & 8 = 0 AND f.estado & 16 = 0` did it and it actually makes sense. — Iharob Al Asimi, Apr 23 '15 at 02:21
About the primary key issue, this was a problem in the original design and I made that key from the evolution of the applicaction I am writing, although a better solution would be interesting, I will make sure to read the links you posted. I am no SQL expert, as you might have noticed. Would it be different if it was just a `UNIQUE` constraint? or the same problem? — Iharob Al Asimi, Apr 23 '15 at 02:24
A `UNIQUE` constraint enforces all the same rules and is implemented with a unique index just like a PK. But columns are allowed to be NULL. So set all involved columns NOT NULL to arrive at the same. I added links to more. — Erwin Brandstetter, Apr 23 '15 at 02:38
Adding a `SERIAL` column as a primary key, which is a technique present all over my database, would not help in ensuring that the combination of values that I need are unique, I need the combination to be unique, so a `SERIAL` column would be ok if they were all the columns in the row, or am I missing something? — Iharob Al Asimi, Apr 23 '15 at 02:47
If you need the set of 7 columns to be unique, add a `UNIQUE` constraint, just like I wrote already. The surrogate PK column adds a 4 byte integer [(or maybe even nothing)](http://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468) to your table and another index on it. But all FK constraints, queries, joins, views etc. are so much smaller, easier and faster now. In the above query, we would replace 7 expressions with 1 - two times ... So, a PK on 7 columns is fine as long as it's only to enforce uniqueness. If you do anything else with it, it sucks. — Erwin Brandstetter, Apr 23 '15 at 02:54
Final Comment: Thank you for the clarification, and indeed it's only to enforce uniqueness. — Iharob Al Asimi, Apr 23 '15 at 02:59

score 1 · Answer 2 · edited Apr 23 '15 at 02:02

1

It's an issue known as chained outer joins. Once you do some LEFT OUTER JOIN's first, it creates NULL values for the columns in the right table that don't match the left table. Then when you join those NULL values with an INNER JOIN after that, the rows disappear, as if you never did an outer join in the first place.

There are two solutions:

Once you start a LEFT JOIN all subsequent JOIN's must be LEFT or FULL
Better option is do all your INNER JOIN's first and do the tables you want to be outer last as a RIGHT JOIN

Also, when you are doing OUTER JOIN's whether LEFT or RIGHT, it is usually a good idea to move the WHERE CLAUSE conditions into the ON clause instead of the WHERE clause. It's a very tricky issue, but look up the difference between a FILTER condition and JOIN condition and when they should be placed in the WHERE vs ON clause.

edited Apr 23 '15 at 02:02

Iharob Al Asimi

52,653
6
59
97

answered Apr 23 '15 at 01:44

Joseph Gagliardo

763
5
3

Interesting, although the that could mean that I can't use `USING`. – Iharob Al Asimi Apr 23 '15 at 02:02
I'm not sure which version of SQL you are using that uses the USING clause and why it would not work by moving the INNER JOIN's to the top. But regardless you could always expand the ON clause to use a full on x.col = y.col style syntax. – Joseph Gagliardo Apr 23 '15 at 02:11
See the other answer, the poster removed the `USING` because it would not be possible to add the `r.estado & 16 <> 0` after it, and that is I think, the problem. – Iharob Al Asimi Apr 23 '15 at 02:16

JOIN table with itself, and filter rows from each table with bitwise operation

Table definition

2 Answers2

Table definition