INTERSECT ALL returns wrong number of records from RHS
- Lenguaje dominante
- Rust
- Estrellas
- 9.3k
- Forks
- 2.4k
- Merge medio
- 3 d 11 h
- PR fusionados (30 d)
- 360
Descripción
### Describe the bug
According to the [SQL spec](https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt), when returning duplicate records from INTERSECT ALL the minimum number of copies from either input should be returned. Specifically:
```
b) If a set operator is specified, then the result of applying
the set operator is a table containing the following rows:
i) Let R be a row that is a duplicate of some row in T1 or of
some row in T2 or both. Let m be the number of duplicates
of R in T1 and let n be the number of duplicates of R in
T2, where m � 0 and n � 0.
...
iii) If ALL is specified, then
Case:
1) If UNION is specified, then the number of duplicates of
R that T contains is (m + n).
2) If EXCEPT is specified, then the number of duplicates of
R that T contains is the maximum of (m - n) and 0.
3) If INTERSECT is specified, then the number of duplicates
of R that T contains is the minimum of m and n.
```
DataFusion currently returns ALL copies of duplicated records from the RHS.
### To Reproduce
The following query
```sql
➜ ~ datafusion-cli
DataFusion CLI v42.0.0
> SELECT * FROM VALUES ('a'), ('b'), ('b'), ('c'), ('c'), ('c')
INTERSECT ALL
SELECT * FROM VALUES ('b'), ('b'), ('b'), ('c'), ('c');
+---------+
| column1 |
+---------+
| b |
| b |
| c |
| c |
| c |
+---------+
```
returns 3 copies of the record `('c')` which does not match the expected behaviour based on the spec.
Note that only 2 copies of `('b')` are returned, so this only appears to affect the RHS.
### Expected behavior
The above query should return 2 copies of the record `('c')`
### Additional context
See DB Fiddle for Postgres which showcases the expected behaviour:
https://www.db-fiddle.com/f/ja4BG5CfyEvak5ScoBwCZr/0
Guía de contribución
Línea de trabajo
Comienza reproduciendo la consulta INTERSECT ALL reportada en DataFusion y sigue la ruta de ejecución que gestiona las filas duplicadas de la entrada derecha. Se considera terminado cuando se devuelve el recuento mínimo de duplicados de ambas entradas para cada fila, con una prueba de regresión que cubra el ejemplo de b y c.
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- rust, sql
- Área
- databases
- Tipo de issue
- Error
- Dificultad
- 3/5
- Tiempo estimado
- 1-2 días
- Estado de actividad
- Estancado
- Claridad
- Bien especificado
- Aptitud para principiantes
- 45/100