Description:
Synopsis:
I'm getting unexpected results (read - incorrect?) when using INSTR in a SELECT statement where two tables are JOINed and results are sorted with ORDER BY. Instead of returning NULL when searching a NULL string, it appears to return the same result as the previous row.
I get different (the expected) results from INSTR if the JOIN is left out of the SELECT statement.
I get different (the expected) results from INSTR if the ORDER BY is left out of the select statement.
Locate( ) and Position( ) both return the same result as INSTR.
INSTR behaves the same (unexpected) way with a (inner) JOIN as it does with the LEFT JOIN above.
Results are repeatable with a simple reconstructed database. (see SHOW CREATE TABLE info included)
Version: MySQL 5.0.45 on SUSE Linux 10.3
Demonstration :
Here's the Buggy version, using both JOIN and ORDER BY. I've marked the unexpected results.
mysql> SELECT s.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result FROM student AS s LEFT JOIN mass_screening as
ms ON (ms.student_id = s.student_id) where mass_screening_id in (150220, 150231, 150233, 150234, 150235) ORDER BY s.student_id, mass_screening_date, mass_screening_time;
+------------+-------------------+---------------+----------------+
| student_id | mass_Screening_id | inst_referred | hearing_result |
+------------+-------------------+---------------+----------------+
| 36287 | 150220 | 0 | NULL |
| 36287 | 150231 | 1 | Refer |
| 36287 | 150233 | 1 | NULL | <-- inst_referred s/b NULL
| 36287 | 150234 | 0 | test |
| 36287 | 150235 | 0 | NULL | <-- inst_referred s/b NULL
+------------+-------------------+---------------+----------------+
Here's the same query, but with the JOIN left out.
mysql> SELECT ms.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result FROM mass_screening as ms where
mass_screening_id in (150220, 150231, 150233, 150234, 150235) ORDER BY ms.student_id, mass_screening_date, mass_screening_time;
+------------+-------------------+---------------+----------------+
| student_id | mass_Screening_id | inst_referred | hearing_result |
+------------+-------------------+---------------+----------------+
| 36287 | 150220 | NULL | NULL |
| 36287 | 150231 | 1 | Refer |
| 36287 | 150233 | NULL | NULL | <-- INSTR(...) returned NULL this time, as expected
| 36287 | 150234 | 0 | test |
| 36287 | 150235 | NULL | NULL | <-- INSTR(...) returned NULL as expected
+------------+-------------------+---------------+----------------+
Here's the same query with the ORDER BY left out:
mysql> SELECT s.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result from student AS s LEFT JOIN mass_screening as
ms ON (ms.student_id = s.student_id) where mass_screening_id in (150220, 150231, 150233, 150234, 150235);
+------------+-------------------+---------------+----------------+
| student_id | mass_Screening_id | inst_referred | hearing_result |
+------------+-------------------+---------------+----------------+
| 36287 | 150220 | NULL | NULL |
| 36287 | 150231 | 1 | Refer |
| 36287 | 150233 | NULL | NULL |
| 36287 | 150234 | 0 | test |
| 36287 | 150235 | NULL | NULL |
+------------+-------------------+---------------+----------------+
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.45 |
+-----------+
1 row in set (0.00 sec)
Table Definitions in use:
For completeness, here are the table definitions:
mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------+
| Table | Create Table
|
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------+
| student | CREATE TABLE `student` (
`student_id` bigint(20) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=36289 DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------+
mysql> show create table mass_screening;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
| mass_screening | CREATE TABLE `mass_screening` (
`mass_screening_id` bigint(20) unsigned NOT NULL auto_increment,
`student_id` bigint(20) unsigned default NULL,
`hearing_result` varchar(254) default NULL,
`hearing_referral` varchar(255) default NULL,
`mass_screening_date` date default NULL,
`mass_screening_time` time default NULL,
PRIMARY KEY (`mass_screening_id`),
KEY `si` (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=150237 DEFAULT CHARSET=latin1 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------+
How to repeat:
Use the following output from mysqldump to recreate the tables as in the 'description' section, then run the sql queries in the description.
:~> mysqldump ictest student mass_screening -u icsf -plisd_sql
-- MySQL dump 10.11
--
-- Host: localhost Database: ictest
-- ------------------------------------------------------
-- Server version 5.0.45
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`student_id` bigint(20) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=36289 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `student`
--
LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (36285,'Doe, John'),(36286,'Smith, John'),(36287,'Jones, Mary'),(36288,'Brown, Jane');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `mass_screening`
--
DROP TABLE IF EXISTS `mass_screening`;
CREATE TABLE `mass_screening` (
`mass_screening_id` bigint(20) unsigned NOT NULL auto_increment,
`student_id` bigint(20) unsigned default NULL,
`hearing_result` varchar(254) default NULL,
`hearing_referral` varchar(255) default NULL,
`mass_screening_date` date default NULL,
`mass_screening_time` time default NULL,
PRIMARY KEY (`mass_screening_id`),
KEY `si` (`student_id`)
) ENGINE=MyISAM AUTO_INCREMENT=150237 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `mass_screening`
--
LOCK TABLES `mass_screening` WRITE;
/*!40000 ALTER TABLE `mass_screening` DISABLE KEYS */;
INSERT INTO `mass_screening` VALUES (150218,36285,NULL,NULL,'2007-11-27','10:03:57'),(150219,36286,NULL,NULL,'2007-11-27','10:04:22'),(150220,36287,NULL,NULL,'2007-11-27','10:05:15'),(150221,36288,NULL,NULL,'2007-11-27','10:05:27'),(150222,36288,NULL,NULL,'2007-11-27','10:05:32'),(150223,36288,NULL,NULL,'2007-11-27','10:05:35'),(150224,36286,NULL,NULL,'2007-11-27','10:05:48'),(150225,36286,NULL,NULL,'2007-11-27','10:05:50'),(150226,36286,NULL,NULL,'2007-11-27','10:05:51'),(150227,36286,NULL,NULL,'2007-11-27','10:06:13'),(150228,36286,NULL,NULL,'2007-11-27','10:06:15'),(150229,36286,NULL,NULL,'2007-11-27','10:06:20'),(150230,36288,NULL,NULL,'2007-11-27','10:06:31'),(150231,36287,'Refer',NULL,'2007-11-27','10:07:12'),(150232,36286,NULL,NULL,'2007-11-27','10:07:32'),(150233,36287,NULL,'No Problem','2007-11-27','10:07:54'),(150234,36287,'test',NULL,'2007-11-27','10:08:23'),(150235,36287,NULL,NULL,'2007-11-27','10:08:34'),(150236,36286,NULL,NULL,'2007-11-27','10:08:42');
/*!40000 ALTER TABLE `mass_screening` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2007-11-27 18:05:29
Description: Synopsis: I'm getting unexpected results (read - incorrect?) when using INSTR in a SELECT statement where two tables are JOINed and results are sorted with ORDER BY. Instead of returning NULL when searching a NULL string, it appears to return the same result as the previous row. I get different (the expected) results from INSTR if the JOIN is left out of the SELECT statement. I get different (the expected) results from INSTR if the ORDER BY is left out of the select statement. Locate( ) and Position( ) both return the same result as INSTR. INSTR behaves the same (unexpected) way with a (inner) JOIN as it does with the LEFT JOIN above. Results are repeatable with a simple reconstructed database. (see SHOW CREATE TABLE info included) Version: MySQL 5.0.45 on SUSE Linux 10.3 Demonstration : Here's the Buggy version, using both JOIN and ORDER BY. I've marked the unexpected results. mysql> SELECT s.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result FROM student AS s LEFT JOIN mass_screening as ms ON (ms.student_id = s.student_id) where mass_screening_id in (150220, 150231, 150233, 150234, 150235) ORDER BY s.student_id, mass_screening_date, mass_screening_time; +------------+-------------------+---------------+----------------+ | student_id | mass_Screening_id | inst_referred | hearing_result | +------------+-------------------+---------------+----------------+ | 36287 | 150220 | 0 | NULL | | 36287 | 150231 | 1 | Refer | | 36287 | 150233 | 1 | NULL | <-- inst_referred s/b NULL | 36287 | 150234 | 0 | test | | 36287 | 150235 | 0 | NULL | <-- inst_referred s/b NULL +------------+-------------------+---------------+----------------+ Here's the same query, but with the JOIN left out. mysql> SELECT ms.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result FROM mass_screening as ms where mass_screening_id in (150220, 150231, 150233, 150234, 150235) ORDER BY ms.student_id, mass_screening_date, mass_screening_time; +------------+-------------------+---------------+----------------+ | student_id | mass_Screening_id | inst_referred | hearing_result | +------------+-------------------+---------------+----------------+ | 36287 | 150220 | NULL | NULL | | 36287 | 150231 | 1 | Refer | | 36287 | 150233 | NULL | NULL | <-- INSTR(...) returned NULL this time, as expected | 36287 | 150234 | 0 | test | | 36287 | 150235 | NULL | NULL | <-- INSTR(...) returned NULL as expected +------------+-------------------+---------------+----------------+ Here's the same query with the ORDER BY left out: mysql> SELECT s.student_id, ms.mass_Screening_id, INSTR(ms.hearing_result, 'Refer') as inst_referred, ms.hearing_result from student AS s LEFT JOIN mass_screening as ms ON (ms.student_id = s.student_id) where mass_screening_id in (150220, 150231, 150233, 150234, 150235); +------------+-------------------+---------------+----------------+ | student_id | mass_Screening_id | inst_referred | hearing_result | +------------+-------------------+---------------+----------------+ | 36287 | 150220 | NULL | NULL | | 36287 | 150231 | 1 | Refer | | 36287 | 150233 | NULL | NULL | | 36287 | 150234 | 0 | test | | 36287 | 150235 | NULL | NULL | +------------+-------------------+---------------+----------------+ mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.45 | +-----------+ 1 row in set (0.00 sec) Table Definitions in use: For completeness, here are the table definitions: mysql> show create table student; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------+ | student | CREATE TABLE `student` ( `student_id` bigint(20) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`student_id`) ) ENGINE=MyISAM AUTO_INCREMENT=36289 DEFAULT CHARSET=latin1 | +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------+ mysql> show create table mass_screening; +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------+ | mass_screening | CREATE TABLE `mass_screening` ( `mass_screening_id` bigint(20) unsigned NOT NULL auto_increment, `student_id` bigint(20) unsigned default NULL, `hearing_result` varchar(254) default NULL, `hearing_referral` varchar(255) default NULL, `mass_screening_date` date default NULL, `mass_screening_time` time default NULL, PRIMARY KEY (`mass_screening_id`), KEY `si` (`student_id`) ) ENGINE=MyISAM AUTO_INCREMENT=150237 DEFAULT CHARSET=latin1 | +----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------+ How to repeat: Use the following output from mysqldump to recreate the tables as in the 'description' section, then run the sql queries in the description. :~> mysqldump ictest student mass_screening -u icsf -plisd_sql -- MySQL dump 10.11 -- -- Host: localhost Database: ictest -- ------------------------------------------------------ -- Server version 5.0.45 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `student` -- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `student_id` bigint(20) NOT NULL auto_increment, `name` varchar(255) default NULL, PRIMARY KEY (`student_id`) ) ENGINE=MyISAM AUTO_INCREMENT=36289 DEFAULT CHARSET=latin1; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES (36285,'Doe, John'),(36286,'Smith, John'),(36287,'Jones, Mary'),(36288,'Brown, Jane'); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `mass_screening` -- DROP TABLE IF EXISTS `mass_screening`; CREATE TABLE `mass_screening` ( `mass_screening_id` bigint(20) unsigned NOT NULL auto_increment, `student_id` bigint(20) unsigned default NULL, `hearing_result` varchar(254) default NULL, `hearing_referral` varchar(255) default NULL, `mass_screening_date` date default NULL, `mass_screening_time` time default NULL, PRIMARY KEY (`mass_screening_id`), KEY `si` (`student_id`) ) ENGINE=MyISAM AUTO_INCREMENT=150237 DEFAULT CHARSET=latin1; -- -- Dumping data for table `mass_screening` -- LOCK TABLES `mass_screening` WRITE; /*!40000 ALTER TABLE `mass_screening` DISABLE KEYS */; INSERT INTO `mass_screening` VALUES (150218,36285,NULL,NULL,'2007-11-27','10:03:57'),(150219,36286,NULL,NULL,'2007-11-27','10:04:22'),(150220,36287,NULL,NULL,'2007-11-27','10:05:15'),(150221,36288,NULL,NULL,'2007-11-27','10:05:27'),(150222,36288,NULL,NULL,'2007-11-27','10:05:32'),(150223,36288,NULL,NULL,'2007-11-27','10:05:35'),(150224,36286,NULL,NULL,'2007-11-27','10:05:48'),(150225,36286,NULL,NULL,'2007-11-27','10:05:50'),(150226,36286,NULL,NULL,'2007-11-27','10:05:51'),(150227,36286,NULL,NULL,'2007-11-27','10:06:13'),(150228,36286,NULL,NULL,'2007-11-27','10:06:15'),(150229,36286,NULL,NULL,'2007-11-27','10:06:20'),(150230,36288,NULL,NULL,'2007-11-27','10:06:31'),(150231,36287,'Refer',NULL,'2007-11-27','10:07:12'),(150232,36286,NULL,NULL,'2007-11-27','10:07:32'),(150233,36287,NULL,'No Problem','2007-11-27','10:07:54'),(150234,36287,'test',NULL,'2007-11-27','10:08:23'),(150235,36287,NULL,NULL,'2007-11-27','10:08:34'),(150236,36286,NULL,NULL,'2007-11-27','10:08:42'); /*!40000 ALTER TABLE `mass_screening` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2007-11-27 18:05:29