[MDEV-12780] Spurious results for a CONCAT statement that contains a CASE statement inside. Created: 2017-05-11  Updated: 2022-11-24  Resolved: 2022-11-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.1.23, 10.2
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Josep Sanz Assignee: Oleksandr Byelkin
Resolution: Cannot Reproduce Votes: 0
Labels: CASE, CONCAT, JOIN
Environment:

GNU/Linux CentOS 7

Attachments: Text File expected.txt     File query.sql     Text File result.txt     File saltos.sql    

 Description   

I have detected a query that contains subqueries with joins, concats and cases statements that generates spurious results.

The problem is that the field computed by the CONCAT statement is living a bug related to the string management, overflow or something similar that causes outputs as the contained in the result.txt attached file.

I have attached 4 files:

  • saltos.sql => contains a minimum schema and sufficient data to reproduce the issue.
  • query.sql => contains a simple query able to demostrate the problem.
  • result.txt => contains the result of execute the query.sql into saltos.sql.
  • expected.txt => contains the expected result.

As a note:

If you remove the WHERE statement or replace the "d.id id_usuario" by "e.id_usuario id_usuario" in the fields zone of the SELECT, then you get the expected result.

I have tryed other versions of MariaDB. I have reproduced the issue in the follows releases: 10.1.14, 10.1.16, 10.1.21, 10.1.22 and 10.1.23

Josep.

 Comments   
Comment by Elena Stepanova [ 2017-05-13 ]

Thanks for the report and test case.
Here is the exact same test case, only in one file suitable for MTR:

DROP DATABASE IF EXISTS mdev12780;
 
CREATE DATABASE mdev12780;
 
USE mdev12780;
 
 
 
CREATE TABLE `tbl_clientes` (
 
  `id` int(11) NOT NULL,
 
  `id_campanya` int(11) NOT NULL DEFAULT '0',
 
  `id_tipo` int(11) NOT NULL DEFAULT '0',
 
  `nombre` varchar(255) NOT NULL DEFAULT '',
 
  `nombre1` varchar(255) NOT NULL DEFAULT '',
 
  `nombre2` varchar(255) NOT NULL DEFAULT '',
 
  `cif` varchar(255) NOT NULL DEFAULT '',
 
  `comentarios` text NOT NULL,
 
  `corriente` varchar(255) NOT NULL DEFAULT '',
 
  `contable` varchar(255) NOT NULL DEFAULT '',
 
  `diapago` int(11) NOT NULL DEFAULT '0',
 
  `direccion` varchar(255) NOT NULL DEFAULT '',
 
  `id_pais` int(11) NOT NULL DEFAULT '0',
 
  `id_provincia` int(11) NOT NULL DEFAULT '0',
 
  `id_poblacion` int(11) NOT NULL DEFAULT '0',
 
  `id_codpostal` int(11) NOT NULL DEFAULT '0',
 
  `nombre_pais` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_provincia` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_poblacion` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_codpostal` varchar(255) NOT NULL DEFAULT '',
 
  `email` varchar(255) NOT NULL DEFAULT '',
 
  `web` varchar(255) NOT NULL DEFAULT '',
 
  `tel_fijo` varchar(255) NOT NULL DEFAULT '',
 
  `tel_movil` varchar(255) NOT NULL DEFAULT '',
 
  `fax` varchar(255) NOT NULL DEFAULT ''
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `tbl_empleados` (
 
  `id` int(11) NOT NULL,
 
  `nombre` varchar(255) NOT NULL DEFAULT '',
 
  `nombre1` varchar(255) NOT NULL DEFAULT '',
 
  `nombre2` varchar(255) NOT NULL DEFAULT '',
 
  `cargo` varchar(255) NOT NULL DEFAULT '',
 
  `comentarios` text NOT NULL,
 
  `direccion` varchar(255) NOT NULL DEFAULT '',
 
  `id_pais` int(11) NOT NULL DEFAULT '0',
 
  `id_provincia` int(11) NOT NULL DEFAULT '0',
 
  `id_poblacion` int(11) NOT NULL DEFAULT '0',
 
  `id_codpostal` int(11) NOT NULL DEFAULT '0',
 
  `nombre_pais` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_provincia` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_poblacion` varchar(255) NOT NULL DEFAULT '',
 
  `nombre_codpostal` varchar(255) NOT NULL DEFAULT '',
 
  `email` varchar(255) NOT NULL DEFAULT '',
 
  `web` varchar(255) NOT NULL DEFAULT '',
 
  `tel_fijo` varchar(255) NOT NULL DEFAULT '',
 
  `tel_movil` varchar(255) NOT NULL DEFAULT '',
 
  `fax` varchar(255) NOT NULL DEFAULT ''
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tbl_empleados` (`id`, `nombre`, `nombre1`, `nombre2`, `cargo`, `comentarios`, `direccion`, `id_pais`, `id_provincia`, `id_poblacion`, `id_codpostal`, `nombre_pais`, `nombre_provincia`, `nombre_poblacion`, `nombre_codpostal`, `email`, `web`, `tel_fijo`, `tel_movil`, `fax`) VALUES
 
(1, 'Josep Sanz', '', '', '', '', '', 0, 0, 0, 0, '', '', '', '', '', '', '', '', '');
 
 
 
CREATE TABLE `tbl_ficheros` (
 
  `id` int(11) NOT NULL,
 
  `id_aplicacion` int(11) NOT NULL DEFAULT '0',
 
  `id_registro` int(11) NOT NULL DEFAULT '0',
 
  `id_usuario` int(11) NOT NULL DEFAULT '0',
 
  `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `fichero` varchar(255) NOT NULL DEFAULT '',
 
  `fichero_file` varchar(255) NOT NULL DEFAULT '',
 
  `fichero_size` int(11) NOT NULL DEFAULT '0',
 
  `fichero_type` varchar(255) NOT NULL DEFAULT '',
 
  `search` mediumtext NOT NULL,
 
  `indexed` int(11) NOT NULL DEFAULT '0',
 
  `retries` int(11) NOT NULL DEFAULT '0'
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tbl_ficheros` (`id`, `id_aplicacion`, `id_registro`, `id_usuario`, `datetime`, `fichero`, `fichero_file`, `fichero_size`, `fichero_type`, `search`, `indexed`, `retries`) VALUES
 
(1, 10, 1, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(2, 10, 2, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(3, 10, 3, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(4, 10, 4, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(5, 10, 5, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(6, 10, 6, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(7, 10, 7, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(8, 10, 8, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(9, 10, 9, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0),
 
(10, 10, 10, 0, '0000-00-00 00:00:00', '', '', 0, '', '', 0, 0);
 
 
 
CREATE TABLE `tbl_registros_i` (
 
  `id` int(11) NOT NULL,
 
  `id_aplicacion` int(11) NOT NULL DEFAULT '0',
 
  `id_registro` int(11) NOT NULL DEFAULT '0',
 
  `id_usuario` int(11) NOT NULL DEFAULT '0',
 
  `datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tbl_registros_i` (`id`, `id_aplicacion`, `id_registro`, `id_usuario`, `datetime`) VALUES
 
(1, 10, 1, 38, '0000-00-00 00:00:00'),
 
(2, 10, 2, 38, '0000-00-00 00:00:00'),
 
(3, 10, 3, 38, '0000-00-00 00:00:00'),
 
(4, 10, 4, 38, '0000-00-00 00:00:00'),
 
(5, 10, 5, 38, '0000-00-00 00:00:00'),
 
(6, 10, 6, 38, '0000-00-00 00:00:00'),
 
(7, 10, 7, 38, '0000-00-00 00:00:00'),
 
(8, 10, 8, 38, '0000-00-00 00:00:00'),
 
(9, 10, 9, 38, '0000-00-00 00:00:00'),
 
(10, 10, 10, 38, '0000-00-00 00:00:00');
 
 
 
CREATE TABLE `tbl_usuarios` (
 
  `id` int(11) NOT NULL,
 
  `id_grupo` int(11) NOT NULL DEFAULT '0',
 
  `id_aplicacion` int(11) NOT NULL DEFAULT '0',
 
  `id_registro` int(11) NOT NULL DEFAULT '0',
 
  `activo` int(11) NOT NULL DEFAULT '0',
 
  `login` varchar(255) NOT NULL DEFAULT '',
 
  `password` varchar(255) NOT NULL DEFAULT '',
 
  `hora_ini` time NOT NULL DEFAULT '00:00:00',
 
  `hora_fin` time NOT NULL DEFAULT '00:00:00',
 
  `dias_sem` varchar(255) NOT NULL DEFAULT ''
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tbl_usuarios` (`id`, `id_grupo`, `id_aplicacion`, `id_registro`, `activo`, `login`, `password`, `hora_ini`, `hora_fin`, `dias_sem`) VALUES
 
(38, 0, 31, 1, 0, 'sanz', '', '00:00:00', '00:00:00', '');
 
 
 
ALTER TABLE `tbl_clientes`
 
  ADD PRIMARY KEY (`id`),
 
  ADD KEY `id_campanya` (`id_campanya`),
 
  ADD KEY `id_pais` (`id_pais`),
 
  ADD KEY `id_provincia` (`id_provincia`),
 
  ADD KEY `id_poblacion` (`id_poblacion`),
 
  ADD KEY `id_codpostal` (`id_codpostal`);
 
 
 
ALTER TABLE `tbl_empleados`
 
  ADD PRIMARY KEY (`id`),
 
  ADD KEY `id_pais` (`id_pais`),
 
  ADD KEY `id_provincia` (`id_provincia`),
 
  ADD KEY `id_poblacion` (`id_poblacion`),
 
  ADD KEY `id_codpostal` (`id_codpostal`);
 
 
 
ALTER TABLE `tbl_ficheros`
 
  ADD PRIMARY KEY (`id`),
 
  ADD KEY `ficheros` (`id_aplicacion`,`id_registro`),
 
  ADD KEY `ficheros2` (`indexed`,`retries`);
 
 
 
ALTER TABLE `tbl_registros_i`
 
  ADD PRIMARY KEY (`id`),
 
  ADD KEY `id_usuario` (`id_usuario`),
 
  ADD KEY `registros` (`id_aplicacion`,`id_registro`),
 
  ADD KEY `registros3` (`id_aplicacion`,`id_registro`,`id_usuario`);
 
 
 
ALTER TABLE `tbl_usuarios`
 
  ADD PRIMARY KEY (`id`),
 
  ADD KEY `id_grupo` (`id_grupo`),
 
  ADD KEY `usuarios` (`id_aplicacion`,`id_registro`);
 
 
 
ALTER TABLE `tbl_clientes`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
 
 
 
ALTER TABLE `tbl_empleados`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
 
 
 
ALTER TABLE `tbl_ficheros`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
 
 
 
ALTER TABLE `tbl_registros_i`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
 
 
 
ALTER TABLE `tbl_usuarios`
 
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=139;COMMIT;
 
 
 
SELECT usuario FROM (
 
	SELECT a.id id,
 
	a.id_registro id_registro,
 
	a.id_aplicacion id_aplicacion,
 
	d.id id_usuario,
 
	d.id_grupo id_grupo,
 
	e.datetime datetime,
 
	CONCAT(
 
		CASE d.id_aplicacion
 
			WHEN '1' THEN (SELECT nombre FROM tbl_clientes WHERE id=d.id_registro)
 
			WHEN '31' THEN (SELECT nombre FROM tbl_empleados WHERE id=d.id_registro)
 
		END,
 
		' (',
 
		d.login,
 
		')'
 
	) usuario
 
	FROM tbl_ficheros a
 
	LEFT JOIN tbl_registros_i e ON e.id_aplicacion=a.id_aplicacion AND e.id_registro=a.id_registro
 
	LEFT JOIN tbl_usuarios d ON e.id_usuario=d.id
 
) z
 
WHERE id_usuario='38';
 
 
 
DROP DATABASE mdev12780;

Actual result

 
usuario
 
Josep Sanz (sanz)
 
Josep Sanz (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz)
 
Josep Sanz (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz) (sanz)

Expected result

 
usuario
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)
 
Josep Sanz (sanz)

Reproducible with derived_merge=on or when the subquery `z` is wrapped into a merge view.

sanz, thus, for now a workaround could be to set derived_merge=off.

Comment by Josep Sanz [ 2017-05-13 ]

I have added the derived_merge=off to the optimizer_switch, but too, I have experimented an important performance loss!!!.

Comment by Elena Stepanova [ 2017-05-13 ]

Unfortunately yes, derived_merge is an optimization, so turning it off expectedly causes inferior performance.
It's just a workaround though, hopefully the problem will be fixed soon enough.

Please note that technically you can turn it off only for a given session, or even just for the given query (I don't know if it's possible or easy in your application/workflow).

Comment by Josep Sanz [ 2017-05-16 ]

Thanks by the idea, but this queries are used by all lists on our application. I will expect the fix to this issue. Thanks by your suggestion.

Comment by Josep Sanz [ 2017-10-16 ]

Hi Elena.

Don't misunderstand the question, just is only for my information: do you have some prediction about when you could fix the reported bug?

Thanks in advance.

Josep.

Comment by Elena Stepanova [ 2017-10-16 ]

sanz, no, I don't have any prediction. Maybe sanja (the assignee) does.

Comment by Josep Sanz [ 2017-10-16 ]

OK

Thanks by your quick response.

Josep.

Comment by Josep Sanz [ 2018-05-30 ]

Hi.

I updated today my MariaDB from 10.1.14 to 10.3.7 and after I execute the test case of this bug, I got the expected result.

I think that the problem was resolved.

Josep.

Comment by Alice Sherepa [ 2022-11-24 ]

works correctly on the current 10.3-10.10 (10.3 f4a1298f245f678badc8a5b5)

Generated at Thu Feb 08 08:00:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.