incomplete result from subquery

[nardus]

Hello everyone,

 

I have the following query.

 

select a.*, b.exibue, b.almac,
--begin problematic subquery
(select CONCAT(CAST(e.dtoprd as CHAR), ',', CAST(e.dtoprt AS CHAR), ',', CAST(e.dtoprs AS CHAR)) from alfpreal e
where CAST(e.cod1 AS UNSIGNED) <= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
and CAST(e.cod2 AS UNSIGNED) >= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
) as prigen
-- end problematic subquery
FROM minimart_copy a left join minimaexi b
using (NUAR1, NUAR2, NUAR3, NUAR4, NUAR5)
left join forzona3 c on (b.almac = c.cventa)
WHERE
(CONCAT(NUAR1, NUAR2, NUAR3, NUAR4) like '$BusNum%' or
MATCH (nomart) AGAINST ('+LAVABO +BLANCO +ALOA' IN BOOLEAN MODE))
AND (almac = 29 or almac = 28 or almac = 7600)
ORDER BY a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4, a.NUAR5;

As you see, the third result parameter ('as prigen') should return three different columns from table "alfpreal" concatenated in a single string in the form of "val1, val2, val3".

 

The query works, but the subquery return only "val1, val2," and that's it. If I take out that query and execute it by itself, it wors as inteded. But whenever I run it inside the larger query, it fails again. All the other results work as intended.

 

I'm using MySQL 5.022.

 

Any clue of what's going on?

 

Thanks for any help anyone can provide.

 

Regards,

 

I.-

[nardus]

The reply from the server:

 

NUAR1:'0'

NUAR2:'0'

NUAR3:'1'

NUAR4:'007605'

NUAR5:''

NOMART:'327865000 LAVABO ALOA ENC BLANCO'

EXIBUE:0

ALMAC: 28

PRIGEN:'41,42,'

 

That last column should be "41, 42, 43".

All values are there, I tried changing the order and would get the correct but incomplete answer (e.g.: "43,42," or "41,43,").

Using the subquery by itself, I get the correct answer.

 

Thanks and regards.

 

I.-

 

[nardus]

Description of tables:

 

CREATE TABLE `alfpreal` (
  `cod1` varchar(10) NOT NULL,
  `cod2` varchar(10) NOT NULL,
  `dtoprs` tinyint(4) default NULL,
  `dtoprt` tinyint(4) default NULL,
  `dtoprd` tinyint(4) default NULL,
  PRIMARY KEY  (`cod1`,`cod2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE `minimart_copy` (
  `NUAR1` char(1) NOT NULL default '',
  `NUAR2` char(1) NOT NULL default '',
  `NUAR3` char(1) NOT NULL default '',
  `NUAR4` char(6) NOT NULL default '',
  `NUAR5` char(1) NOT NULL default '',
  `nomart` char(32) NOT NULL,
  PRIMARY KEY  (`NUAR1`,`NUAR2`,`NUAR3`,`NUAR4`,`NUAR5`),
  FULLTEXT KEY `NomArt` (`nomart`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE `forzona3` (
  `cventa` decimal(4,0) NOT NULL,
  `zona` decimal(2,0) NOT NULL,
  `posicion` decimal(2,0) NOT NULL,
  PRIMARY KEY  (`cventa`,`zona`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE `minimaexi` (
  `almac` decimal(4,0) NOT NULL,
  `NUAR1` varchar(1) NOT NULL,
  `NUAR2` varchar(1) NOT NULL,
  `NUAR3` varchar(1) NOT NULL,
  `NUAR4` varchar(6) NOT NULL,
  `NUAR5` varchar(1) NOT NULL,
  `exibue` float NOT NULL,
  PRIMARY KEY  (`almac`,`NUAR1`,`NUAR2`,`NUAR3`,`NUAR4`,`NUAR5`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

[fenway]

Not sure... CONCAT() only returns NULL as a whole occasionally, not just a single argument.  Same for CAST()... strange.