Thread: column check mistake or not?

Yesterday i did a mistake executing one query, i use a column daes not
exists in the table the result was all rows of the table.
Platform Fedora 14 32 bits
Version Postgresql 9.0.2

The column id_documento not exists in t_log_modificar_deuda, this
behavior is correct or not?

query
select * from t_documento where (importe_deudor_documento >
importe_documento) or id_documento in (select id_documento from
t_log_modificar_deuda);

result all column of t_documento.

schema

CREATE TABLE t_documento (
     id_documento d_codigo NOT NULL,
     nro_documento d_codigo NOT NULL,
     importe_documento d_importe,
     id_cliente d_codigo,
     id_razon_social d_codigo,
     fecha_documento d_fecha NOT NULL,
     usuario_ingreso d_usuario_ingreso,
     fecha_ingreso d_fecha_ingreso,
     hora_ingreso d_hora_ingreso,
     id_tipo_documento d_codigo,
     fecha_vencimiento_documento d_fecha,
     id_moneda d_codigo,
     id_sucursal d_codigo,
     motivo_anulacion_documento d_descripcion,
     fecha_anulacion_documento d_fecha,
     hora_anulacion_documento d_hora,
     usuario_anulacion_documento d_nombre,
     sistema_viejo_documento d_boolean DEFAULT 'N'::bpchar NOT NULL,
     redondeo_documento d_importe DEFAULT 0 NOT NULL,
     importe_deudor_documento d_importe DEFAULT 0 NOT NULL,
     id_categoria_documento d_codigo NOT NULL,
     fecha_exportado_memory_documento d_fechahora,
     aniomes_exportado_gestoria_documento integer,
     importe_deudor_documento_ant d_importe,
     modificado integer DEFAULT 0,
     anulacion_exportado_memory_documento d_fechahora
);

ALTER TABLE ONLY t_documento
     ADD CONSTRAINT documento_primary PRIMARY KEY (id_documento);
ALTER TABLE ONLY t_documento
     ADD CONSTRAINT nro_documento_unico UNIQUE (nro_documento);

CREATE TABLE t_log_modificar_deuda (
     nro_documento d_codigo NOT NULL,
     importe_actual d_importe NOT NULL,
     importe_sugerido d_importe NOT NULL,
     usuario_ingreso d_usuario_ingreso,
     fecha_ingreso d_fecha_ingreso,
     hora_ingreso d_hora_ingreso
);

Rodriguez Fernando <rodriguez@ort.edu.uy> writes:
> Yesterday i did a mistake executing one query, i use a column daes not
> exists in the table the result was all rows of the table.
> Platform Fedora 14 32 bits
> Version Postgresql 9.0.2

> The column id_documento not exists in t_log_modificar_deuda, this
> behavior is correct or not?

> query
> select * from t_documento where (importe_deudor_documento >
> importe_documento) or id_documento in (select id_documento from
> t_log_modificar_deuda);

> result all column of t_documento.

Yes, it is.  That's an outer reference to the id_documento from
t_documento, so of course the IN will succeed (assuming id_documento
isn't null and there's at least one row in t_log_modificar_deuda).

Outer references are a SQL feature that bites novices fairly often :-(
but on the other hand sub-selects would be far less useful without them.

            regards, tom lane