Multi_insert returns more results than expected in some specific conditions

Abierto
  • ¿Algo no está bien?
#384 5 comentarios 0 reacciones 0 asignados Ver en GitHub

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
35/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Estancado
Stack tecnológico
mysql, ruby, sql
Área
databases

Línea de trabajo

Start by tracing relation.multi_insert and the relation.primary_key lookup described in the issue, then reproduce the behavior with the provided composite-key MySQL schema and create command. Done means the command returns only the inserted rows, or no rows if that behavior is chosen, rather than every row in the table.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

bug

Describe the bug

When using mysql2 as the underlying driver, dealing with a table that has a multi-column primary key, multi_insert tries to get back the primary key from the inserts. However, since this is a multi-column PK, LAST_INSERT_ID() has 0, so relation.multi_insert will give back an array of 0.
multi_insert will in turn, try to query the relation with the primary keys it got back. It uses relation.primary_key for this, which will return as far as I can understand the first field of the PK. If the PK has a varchar as the first field, this will result in a query such as SELECT * FROM relation where varchar_field in (0, 0, 0...), which in mysql will return every field in the table.

To Reproduce

Create a table which contain more than one column as primary key, such as:

CREATE TABLE `my_table` (
  `my_field1` varchar(255) NOT NULL,
  `my_field2` varchar(255) NOT NULL,
  PRIMARY KEY (`my_field1`, `my_field2`)
)

Insert random data in the created able.

Try to insert more than one value in this table using something like so:

new_values = [{my_field1: "abc", my_field2: "cde"}, {my_field1: "gde", my_field2: "zyx"}]
create_cmd = my_table.command(:create, result: :many)
d = create_cmd.call(new_values)

d will contain all rows in the table, instead of only the two inserted ones.

Expected behavior

Only the two values are returned (or optionally, no values at all)

Your environment

  • Affects my production application: No, we rolled back our change and we're going straight to the db.
  • Ruby version: 2.6
  • OS: Linux
  • Rom-sql 3.1.0
  • Rom-core 5.1.2
Lenguaje dominante
Ruby
Estrellas
220
Forks
97
Sin PR fusionados en 30 d

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de rom-rb/rom-sql

Todos los issues de rom-rb/rom-sql

Issues similares

Más issues de Ruby

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.