Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9500

Bug after upgrade to 10.1.10 (and 10.1.11)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.10, 10.1.11, 10.1
    • Fix Version/s: 10.1.12
    • Component/s: Triggers
    • Labels:
      None
    • Environment:
      CentOS x86_64
    • Sprint:
      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              ampf Antonio Fernandes
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration