Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.10, 10.1.11, 10.1(EOL)
-
None
-
CentOS x86_64
-
10.1.12
Description
Hi Elena and all,
The bug behind MDEV-9332 isn't after all behind my query not running. It runs perfectly until 10.1.9 and after it, it throws an error.
The Query:
UPDATE
|
tbl_stock_grupo_controlo
|
SET
|
tbl_stock_grupo_controlo.MSVArm71 = (
|
SELECT |
MIN(TIMESTAMPDIFF(MONTH,det.`Data`, CURDATE())) |
FROM |
MCPC_SI.tbl_facturas fac,
|
MCPC_SI.tbl_facturas_detalhe det,
|
MCLP_SI.tbl_armazens_rel rel
|
WHERE |
fac.CodCliente <> rel.CodCliente
|
AND ( |
fac.CtaPostal = '' |
OR IFNULL(fac.CtaPostal,'') <> IFNULL(rel.CtaPostal,'') |
)
|
AND tbl_stock_grupo_controlo.Armazem = rel.ArmazemDestino |
AND fac.Armazem = rel.ArmazemOrigem |
AND fac.Factura = det.Factura |
AND tbl_stock_grupo_controlo.v_ReferenciaCompleta = det.Referencia |
AND (det.Marca = rel.Marca OR rel.Marca = '') |
AND det.Atendedor <> 'VEND.INTERNET' |
AND det.CodCliente NOT IN ('K0071','K0078','K0471','K0478') |
AND det.`Data` >= '2012-01-01' |
GROUP BY |
tbl_stock_grupo_controlo.Armazem,
|
det.Referencia
|
)
|
MariaDB 10.1.9: OK
MariaDB >= 10.1.10: SQL Error (1062): Duplicate entry '191' for key 'group_key'
10.1.9 EXPLAIN FORMAT=JSON
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"update": 1,
|
"table_name": "tbl_stock_grupo_controlo",
|
"access_type": "index",
|
"key": "PRIMARY",
|
"key_length": "",
|
"used_key_parts": ["Armazem", "Marca", "Referencia"],
|
"rows": 14371
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"filesort": {
|
"temporary_table": {
|
"function": "buffer",
|
"table": {
|
"table_name": "rel",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "FK_tbl_armazens_rel_tbl_armazens_2"],
|
"key": "FK_tbl_armazens_rel_tbl_armazens_2",
|
"key_length": "11",
|
"used_key_parts": ["ArmazemDestino"],
|
"ref": ["MCPC_SI.tbl_stock_grupo_controlo.Armazem"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "det",
|
"partitions": ["p08", "p09", "p10", "p11", "p12", "p99"],
|
"access_type": "ref",
|
"possible_keys": ["Referencia", "Factura", "CodCliente", "Data"],
|
"key": "Referencia",
|
"key_length": "153",
|
"used_key_parts": ["Referencia"],
|
"ref": ["MCPC_SI.tbl_stock_grupo_controlo.v_ReferenciaCompleta"],
|
"rows": 6,
|
"filtered": 100,
|
"attached_condition": "(((det.Marca = MCLP_SI.rel.Marca) or (MCLP_SI.rel.Marca = '')) and (det.Atendedor <> 'VEND.INTERNET') and (det.CodCliente not in ('K0071','K0078','K0471','K0478')) and (det.`Data` >= '2012-01-01') and (det.Factura is not null))"
|
},
|
"table": {
|
"table_name": "fac",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "Armazem"],
|
"key": "PRIMARY",
|
"key_length": "47",
|
"used_key_parts": ["Factura"],
|
"ref": ["MCPC_SI.det.Factura"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "((fac.Armazem = MCLP_SI.rel.ArmazemOrigem) and (fac.CodCliente <> MCLP_SI.rel.CodCliente) and ((fac.CtaPostal = '') or (ifnull(fac.CtaPostal,'') <> ifnull(MCLP_SI.rel.CtaPostal,''))))"
|
}
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
10.1.10 and 10.1.11 EXPLAIN FORMAT=JSON
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"update": 1,
|
"table_name": "tbl_stock_grupo_controlo",
|
"access_type": "index",
|
"key": "PRIMARY",
|
"key_length": "",
|
"used_key_parts": ["Armazem", "Marca", "Referencia"],
|
"rows": 14110
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"filesort": {
|
"temporary_table": {
|
"function": "buffer",
|
"table": {
|
"table_name": "rel",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "FK_tbl_armazens_rel_tbl_armazens_2"],
|
"key": "FK_tbl_armazens_rel_tbl_armazens_2",
|
"key_length": "11",
|
"used_key_parts": ["ArmazemDestino"],
|
"ref": ["MCPC_SI.tbl_stock_grupo_controlo.Armazem"],
|
"rows": 1,
|
"filtered": 100
|
},
|
"table": {
|
"table_name": "det",
|
"partitions": ["p08", "p09", "p10", "p11", "p12", "p99"],
|
"access_type": "ref",
|
"possible_keys": ["Referencia", "Factura", "CodCliente", "Data"],
|
"key": "Referencia",
|
"key_length": "153",
|
"used_key_parts": ["Referencia"],
|
"ref": ["MCPC_SI.tbl_stock_grupo_controlo.v_ReferenciaCompleta"],
|
"rows": 6,
|
"filtered": 100,
|
"attached_condition": "(((det.Marca = MCLP_SI.rel.Marca) or (MCLP_SI.rel.Marca = '')) and (det.Atendedor <> 'VEND.INTERNET') and (det.CodCliente not in ('K0071','K0078','K0471','K0478')) and (det.`Data` >= '2012-01-01') and (det.Factura is not null))"
|
},
|
"table": {
|
"table_name": "fac",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "Armazem"],
|
"key": "PRIMARY",
|
"key_length": "47",
|
"used_key_parts": ["Factura"],
|
"ref": ["MCPC_SI.det.Factura"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "((fac.Armazem = MCLP_SI.rel.ArmazemOrigem) and (fac.CodCliente <> MCLP_SI.rel.CodCliente) and ((fac.CtaPostal = '') or (ifnull(fac.CtaPostal,'') <> ifnull(MCLP_SI.rel.CtaPostal,''))))"
|
}
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
Any idea what might be?
Best regards,
Antonio Fernandes