MariaDB Crashes get_datetime_value

[Eric H]

The following Query Crashed MariaDB 5.3.6

   SELECT
    DATE_FORMAT(
     (
      SELECT MIN(date_effective)
      FROM barn_schedule ees
      WHERE
       ees.apple_id = a.apple_id AND
       date_effective > '2012-04-26' AND
       (ees.amount_non_tomato < 0 OR ees.amount_tomato < 0)
     ),
     '%c/%e/%y'
    ) AS next_due,
    IF(
     (
      SELECT MIN(date_effective)
      FROM barn_schedule evs
      WHERE
       evs.apple_id = a.apple_id AND
       (evs.amount_tomato + evs.amount_non_tomato) <= 0
     ) IN ('2012-04-25','2012-04-26') AND
     a.is_react = 'no',
     1,
     0
    ) first_seeds,
    IF(a.apple_status_id IN (126,125,127), 1, 0) special,
    IF(
     (
      SELECT
       SUM(
        IF(
         tr.festival_register_id IS NULL,
         es.amount_tomato + es.amount_non_tomato,
         tr.amount
        )
       )
      FROM
       barn_schedule es
       LEFT JOIN festival_register tr USING (barn_schedule_id)
      WHERE
       es.date_effective <= '2012-04-26' AND
       es.date_created <= '2012-04-26 22:00:13' AND
       es.apple_id = a.apple_id AND
       (
        es.barn_status = 'scheduled' OR
        tr.festival_status IN ('complete','pending')
       )
       AND
        es.context != 'cancel'
     ) <= 0 AND a.apple_status_id NOT IN (124,19),
     1,
     0
    ) payout,
    IF(
     COUNT(IF(et.name_short IN ('cancel', 'order_cancel'), 1, NULL)) > 0,
     1,
     0
    ) cancel,
    SUM(IF(
     eat.name_short = 'watermelon',
     ea.amount,
     0
    )) tomato,
    SUM(IF(
     eat.name_short = 'peach',
     ea.amount,
     0
    )) peach,
    SUM(IF(
     eat.name_short = 'grape',
     ea.amount,
     0
    )) grape,
    SUM(IF(
     eat.name_short <> 'blackberry',
     ea.amount,
     0
    )) AS amount_due
   FROM
    barn_schedule es
    JOIN company c USING (company_id)
    JOIN barn_type et USING (barn_type_id)
    JOIN barn_amount ea USING (barn_schedule_id)
    JOIN barn_amount_type eat USING (barn_amount_type_id)
    LEFT JOIN festival_register tr USING(barn_schedule_id)
    JOIN apple a ON es.apple_id = a.apple_id
    JOIN apple_status ass USING (apple_status_id)
    JOIN fresh_type lt USING (fresh_type_id)
    LEFT JOIN (
     apple_language al
     JOIN language USING (language_id)
    ) ON (
     al.apple_id = a.apple_id
     AND al.date_created < DATE_ADD(es.date_effective, INTERVAL 1 DAY)
    )
    LEFT JOIN apple_language al_check ON (
     al_check.apple_id = a.apple_id
     AND al_check.date_created < DATE_ADD(es.date_effective, INTERVAL 1 DAY)
     AND al_check.apple_language_id > al.apple_language_id
    )
   WHERE
    (
     tr.festival_register_id = ea.festival_register_id OR
     tr.festival_register_id IS NULL
    ) AND
    es.barn_type_id IN (
     SELECT et.barn_type_id
     FROM
      barn_type et
      JOIN barn_festival USING (barn_type_id)
      JOIN festival_type USING (festival_type_id)
     WHERE
      clearing_type <> 'honey'
      AND et.name_short NOT IN ('count_internal',
          'system_count',
          'discount',
          'f_count_kiwi_failed',
          'f_count_cancell')
    ) AND
    (
     -- Date Effective equals Specific
     es.date_effective = '2012-04-26' OR
     (
     -- Date Effective was previous day, scheduled after DCR was run
      es.date_effective = DATE('2012-04-25 22:00:15') AND
      es.date_created BETWEEN '2012-04-25 22:00:15' AND '2012-04-26 22:00:13'
     )
    ) AND
    es.company_id IN ('6') AND
    es.barn_status <> 'suspended' AND
    (tr.festival_status <> 'failed' OR tr.festival_status IS NULL) AND
    lt.name_short IN ('standard','order') AND
    es.amount_tomato <= 0 AND
    es.amount_non_tomato <= 0 AND
    a.apple_status_id NOT IN (115,116,114) AND
    (et.name <> 'Cancel' OR es.amount_non_tomato < 0)

    AND al_check.apple_language_id IS NULL

How to re-create
1) Load empty schema (does not matter if schema is empty but easier to use empty schema)
2) Run above query

# Schema

--
-- Table structure for table `apple`
--

DROP TABLE IF EXISTS `apple`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apple` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `apple_id` int(10) unsigned NOT NULL DEFAULT '0',
  `customer_id` int(10) unsigned NOT NULL DEFAULT '0',
  `archive_fruitline_id` int(10) unsigned DEFAULT NULL,
  `login_id` int(10) unsigned DEFAULT NULL,
  `is_react` enum('no','yes') NOT NULL,
  `fresh_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `rule_set_id` int(10) unsigned DEFAULT NULL,
  `enterprise_site_id` int(10) unsigned NOT NULL DEFAULT '0',
  `apple_status_id` int(10) unsigned NOT NULL DEFAULT '0',
  `date_apple_status_set` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_next_contact` timestamp NULL DEFAULT NULL,
  `ip_address` varchar(40) NOT NULL DEFAULT '',
  `apple_type` enum('paperless','paper') NOT NULL DEFAULT 'paperless',
  `charity_name` varchar(100) NOT NULL DEFAULT '',
  `charity_aba` varchar(9) NOT NULL DEFAULT '',
  `charity_account` varchar(24) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `charity_account_oldkey` varchar(24) DEFAULT NULL,
  `charity_account_type` enum('checking','savings') NOT NULL DEFAULT 'checking',
  `date_crop_estimated` date DEFAULT NULL,
  `date_crop_actual` date DEFAULT NULL,
  `date_first_seeds` date DEFAULT NULL,
  `crop_requested` decimal(7,2) DEFAULT NULL,
  `crop_qualified` decimal(7,2) NOT NULL DEFAULT '0.00',
  `crop_actual` decimal(7,2) DEFAULT NULL,
  `corn_charge` decimal(7,2) DEFAULT NULL,
  `seeds_total` decimal(7,2) DEFAULT NULL,
  `bean` decimal(9,4) DEFAULT NULL,
  `water_monthly` decimal(7,2) NOT NULL DEFAULT '0.00',
  `water_source` enum('sprinkler','benefits','friends','self_sprinkler') NOT NULL DEFAULT 'sprinkler',
  `water_sprinklers` enum('no','yes') NOT NULL DEFAULT 'no',
  `water_frequency` enum('weekly','twice_monthly','bi_weekly','monthly') NOT NULL DEFAULT 'weekly',
  `water_date_soap_1` date DEFAULT NULL,
  `water_date_soap_2` date DEFAULT NULL,
  `seed_date_model` enum('dw','dwpd','dmdm','wwdw','dm','dwdm','wdw') NOT NULL DEFAULT 'dw',
  `day_of_week` enum('sun','mon','tue','wed','thu','fri','sat') DEFAULT NULL,
  `weekend_nonbusiness_day_handling_type` enum('before','after','split') NOT NULL DEFAULT 'before',
  `holiday_nonbusiness_day_handling_type` enum('before','after','split') NOT NULL DEFAULT 'before',
  `last_seed_date` date DEFAULT NULL,
  `day_of_month_1` tinyint(3) unsigned DEFAULT NULL,
  `day_of_month_2` tinyint(3) unsigned DEFAULT NULL,
  `week_1` tinyint(3) unsigned DEFAULT NULL,
  `week_2` tinyint(3) unsigned DEFAULT NULL,
  `track_id` varchar(40) DEFAULT NULL,
  `agent_id` int(10) unsigned DEFAULT NULL,
  `agent_id_callcenter` int(10) unsigned DEFAULT NULL,
  `dob` varchar(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '0000-00-00',
  `dob_oldkey` varchar(16) DEFAULT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ffn_id` int(10) unsigned DEFAULT NULL,
  `ffn` varchar(12) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `ffn_oldkey` varchar(12) DEFAULT NULL,
  `farmer_id_number` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `farmer_id_number_oldkey` varchar(40) DEFAULT NULL,
  `farmer_id_state` char(2) DEFAULT NULL,
  `farmer_id_type` enum('dl','sid','pp') DEFAULT NULL,
  `pitchfork_verified` enum('unverified','verified') NOT NULL DEFAULT 'unverified',
  `email` varchar(100) NOT NULL DEFAULT '',
  `email_verified` enum('unverified','verified') NOT NULL DEFAULT 'unverified',
  `name_title` varchar(10) DEFAULT NULL,
  `name_last` varchar(50) NOT NULL DEFAULT '',
  `name_first` varchar(50) NOT NULL DEFAULT '',
  `name_nick` varchar(50) DEFAULT NULL,
  `name_middle` varchar(50) DEFAULT NULL,
  `name_suffix` varchar(20) DEFAULT NULL,
  `street` varchar(100) NOT NULL DEFAULT '',
  `unit` varchar(10) DEFAULT NULL,
  `city` varchar(30) NOT NULL DEFAULT '',
  `state` char(2) NOT NULL DEFAULT '',
  `zip_code` varchar(9) NOT NULL DEFAULT '',
  `tenancy_type` enum('unspecified','own','rent') NOT NULL DEFAULT 'unspecified',
  `phone_home` varchar(10) NOT NULL DEFAULT '',
  `phone_cell` varchar(10) DEFAULT NULL,
  `phone_fax` varchar(10) DEFAULT NULL,
  `call_time_pref` enum('no preference','morning','afternoon','evening') NOT NULL DEFAULT 'no preference',
  `contact_method_pref` enum('no preference','home phone','work phone','cell phone','email','usps') NOT NULL DEFAULT 'no preference',
  `marketing_contact_pref` enum('no preference','phone','email','usps','no contact') NOT NULL DEFAULT 'no preference',
  `department` varchar(100) NOT NULL,
  `shift` enum('day','swing','grave','other') DEFAULT NULL,
  `date_hire` datetime DEFAULT NULL,
  `job_tenure` decimal(4,2) DEFAULT NULL,
  `phone_work` varchar(10) DEFAULT NULL,
  `phone_work_ext` varchar(8) DEFAULT NULL,
  `work_address_1` varchar(50) DEFAULT NULL,
  `work_address_2` varchar(50) DEFAULT NULL,
  `work_city` varchar(30) DEFAULT NULL,
  `work_state` char(2) DEFAULT NULL,
  `work_zip_code` varchar(9) DEFAULT NULL,
  `sprinkler_verified` enum('unverified','verified') NOT NULL DEFAULT 'unverified',
  `pwadvid` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `bat_process` varchar(255) NOT NULL DEFAULT 'email_confirmation',
  `is_watched` enum('no','yes') NOT NULL,
  `schedule_model_id` int(10) unsigned NOT NULL,
  `modifying_agent_id` int(10) unsigned NOT NULL DEFAULT '1',
  `version` smallint(6) NOT NULL DEFAULT '1',
  `pickle_key_id` int(10) unsigned DEFAULT NULL,
  `phone_contactable` bit(1) NOT NULL DEFAULT b'1',
  `email_contactable` bit(1) NOT NULL DEFAULT b'1',
  `contactable` bit(1) NOT NULL DEFAULT b'1',
  `is_friends` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`apple_id`),
  UNIQUE KEY `idx_app_archive_fruitline` (`archive_fruitline_id`,`company_id`),
  KEY `idx_app_ffn_co_app` (`ffn`,`company_id`,`apple_id`),
  KEY `idx_app_lname_co_app` (`name_last`,`company_id`,`apple_id`),
  KEY `idx_app_fname_co_app` (`name_first`,`company_id`,`apple_id`),
  KEY `idx_app_lname_fname_co_app` (`name_last`,`name_first`,`company_id`,`apple_id`),
  KEY `idx_app_login_app` (`login_id`,`company_id`,`apple_id`),
  KEY `idx_app_status_co_stsdate` (`apple_status_id`,`company_id`,`date_apple_status_set`,`apple_id`),
  KEY `idx_app_status_co_nxtdate` (`apple_status_id`,`company_id`,`date_next_contact`,`apple_id`),
  KEY `idx_app_charity_acct_aba_ffn` (`charity_account`,`charity_aba`,`ffn`,`date_created`),
  KEY `idx_app_co_date_status_set` (`company_id`,`date_apple_status_set`,`apple_id`),
  KEY `idx_app_email_co_date` (`email`,`company_id`,`date_created`),
  KEY `idx_app_ruleset_date` (`rule_set_id`,`date_created`),
  KEY `idx_date_created` (`date_created`),
  KEY `idx_phone_cell` (`phone_cell`(6)),
  KEY `idx_track_id` (`track_id`(6)),
  KEY `idx_bat_process` (`bat_process`(5)),
  KEY `idx_phone_home` (`phone_home`(6)),
  KEY `idx_farmer_id_number` (`farmer_id_number`(6)),
  KEY `idx_app_custid_app` (`customer_id`,`company_id`,`apple_id`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_app_ffnid_co_app` (`ffn_id`,`company_id`,`apple_id`),
  KEY `idx_app_charity_acct_aba_ffnid` (`charity_account`,`charity_aba`,`ffn_id`,`date_created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `apple_status`
--

DROP TABLE IF EXISTS `apple_status`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apple_status` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `apple_status_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `name_short` varchar(25) NOT NULL DEFAULT '',
  `apple_status_parent_id` int(10) unsigned DEFAULT NULL,
  `level` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`apple_status_id`),
  UNIQUE KEY `idx_appsts_name_short_parent` (`name_short`,`apple_status_parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=158 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `apple_language`
--

DROP TABLE IF EXISTS `apple_language`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `apple_language` (
  `apple_language_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `apple_id` int(10) unsigned NOT NULL,
  `language_id` int(10) unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `original` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`apple_language_id`),
  KEY `idx_apple_id` (`apple_id`)
) ENGINE=InnoDB AUTO_INCREMENT=287275 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `company`
--

DROP TABLE IF EXISTS `company`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `company` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `name_short` varchar(5) NOT NULL DEFAULT '',
  `co_entity_type` enum('pseudo','clk_company') NOT NULL DEFAULT 'pseudo',
  `ecash_process_type` enum('1','2') DEFAULT NULL,
  `property_id` int(10) unsigned NOT NULL DEFAULT '0',
  `is_db_owner` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`company_id`),
  UNIQUE KEY `idx_company_name_short` (`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `barn_schedule`
--

DROP TABLE IF EXISTS `barn_schedule`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `barn_schedule` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `apple_id` int(10) unsigned NOT NULL DEFAULT '0',
  `barn_schedule_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `barn_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `origin_id` int(10) unsigned DEFAULT NULL,
  `origin_group_id` int(11) DEFAULT NULL,
  `configuration_trace_data` varchar(255) DEFAULT NULL,
  `amount_tomato` decimal(7,2) NOT NULL DEFAULT '0.00',
  `amount_non_tomato` decimal(7,2) NOT NULL DEFAULT '0.00',
  `barn_status` enum('scheduled','registered','suspended') NOT NULL DEFAULT 'scheduled',
  `date_barn` date NOT NULL DEFAULT '0000-00-00',
  `date_effective` date NOT NULL DEFAULT '0000-00-00',
  `context` enum('arrangement','manual','generated','paydown','payout','cancel','reattempt') NOT NULL DEFAULT 'generated',
  `source_id` int(10) unsigned NOT NULL DEFAULT '4',
  `is_shifted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `extension` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`barn_schedule_id`),
  KEY `idx_barn_sched_app_date` (`apple_id`,`date_barn`,`barn_schedule_id`),
  KEY `idx_barn_sched_sts_app` (`barn_status`,`apple_id`),
  KEY `idx_barn_sched_app_eff_sts` (`apple_id`,`date_effective`,`barn_status`),
  KEY `idx_barn_sched_app_origin` (`apple_id`,`origin_id`,`date_barn`),
  KEY `idx_barn_sched_app_origin_grp` (`apple_id`,`origin_group_id`,`date_barn`),
  KEY `idx_barn_sched_app_eff` (`date_effective`,`apple_id`),
  KEY `idx_barn_schedule_comp_created` (`company_id`,`date_created`),
  KEY `idx_barn_sched_sts_etid_eff` (`barn_status`,`barn_type_id`,`date_effective`)
) ENGINE=InnoDB AUTO_INCREMENT=48427481 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `barn_type`
--

DROP TABLE IF EXISTS `barn_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `barn_type` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `barn_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_short` varchar(25) NOT NULL DEFAULT '',
  `name` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`barn_type_id`),
  UNIQUE KEY `idx_barn_type_co_name_short` (`company_id`,`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `barn_festival`
--

DROP TABLE IF EXISTS `barn_festival`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `barn_festival` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `barn_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `festival_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `distribution_percentage` decimal(7,4) DEFAULT NULL,
  `distribution_amount` decimal(7,2) DEFAULT NULL,
  `spawn_percentage` decimal(7,4) DEFAULT NULL,
  `spawn_amount` decimal(7,2) DEFAULT NULL,
  `spawn_max_num` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`barn_type_id`,`festival_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `barn_amount`
--

DROP TABLE IF EXISTS `barn_amount`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `barn_amount` (
  `barn_amount_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `barn_schedule_id` int(10) unsigned NOT NULL DEFAULT '0',
  `festival_register_id` int(10) unsigned NOT NULL DEFAULT '0',
  `barn_amount_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `amount` decimal(7,2) NOT NULL DEFAULT '0.00',
  `apple_id` int(10) unsigned NOT NULL DEFAULT '0',
  `num_reattempt` int(10) unsigned NOT NULL DEFAULT '0',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`barn_amount_id`),
  KEY `idx_barn_amt_app_esid` (`apple_id`,`barn_schedule_id`,`festival_register_id`),
  KEY `idx_barn_amt_eatid_appid_esid_trid` (`barn_amount_type_id`,`apple_id`,`barn_schedule_id`,`festival_register_id`),
  KEY `idx_barn_amt_esid_eatid` (`barn_schedule_id`,`barn_amount_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=52819583 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `barn_amount_type`
--

DROP TABLE IF EXISTS `barn_amount_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `barn_amount_type` (
  `barn_amount_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_short` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`barn_amount_type_id`),
  UNIQUE KEY `name_short` (`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='REFERENCE TABLE: The different types of dollar amount';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fresh_type`
--

DROP TABLE IF EXISTS `fresh_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fresh_type` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `fresh_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `name_short` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`fresh_type_id`),
  UNIQUE KEY `idx_fresh_type_name_short` (`company_id`,`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `language`
--

DROP TABLE IF EXISTS `language`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `language` (
  `language_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(100) NOT NULL,
  `name_short` varchar(10) NOT NULL,
  `redirect_domain` varchar(100) NOT NULL,
  PRIMARY KEY (`language_id`),
  UNIQUE KEY `idx_name_short` (`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `festival_register`
--

DROP TABLE IF EXISTS `festival_register`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `festival_register` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `apple_id` int(10) unsigned NOT NULL DEFAULT '0',
  `festival_register_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `barn_schedule_id` int(10) unsigned NOT NULL DEFAULT '0',
  `honey_id` int(10) unsigned DEFAULT NULL,
  `ecld_id` int(10) unsigned DEFAULT NULL,
  `festival_type_id` int(10) unsigned NOT NULL DEFAULT '0',
  `festival_status` enum('new','pending','complete','failed') NOT NULL DEFAULT 'new',
  `amount` decimal(7,2) NOT NULL DEFAULT '0.00',
  `date_effective` date NOT NULL DEFAULT '0000-00-00',
  `source_id` int(10) unsigned NOT NULL DEFAULT '4',
  `modifying_agent_id` int(10) unsigned NOT NULL DEFAULT '1',
  `invalid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`festival_register_id`),
  KEY `idx_trans_reg_app_date` (`apple_id`,`date_effective`,`festival_register_id`),
  KEY `idx_trans_reg_app_type_date` (`apple_id`,`festival_type_id`,`date_effective`),
  KEY `idx_trans_reg_sts_co_dt` (`festival_status`,`company_id`,`date_effective`),
  KEY `idx_trans_reg_honeyid` (`honey_id`),
  KEY `idx_trans_reg_mdate` (`date_modified`),
  KEY `idx_trans_reg_barnsched_id` (`barn_schedule_id`),
  KEY `idx_trans_reg_ecldid` (`ecld_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22672269 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `festival_type`
--

DROP TABLE IF EXISTS `festival_type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `festival_type` (
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `active_status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int(10) unsigned NOT NULL DEFAULT '0',
  `festival_type_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_short` varchar(25) NOT NULL DEFAULT '',
  `name` varchar(100) NOT NULL DEFAULT '',
  `clearing_type` enum('honey','honeycheck','honey_external','accrued charge','honey_adjustment','honey_card') NOT NULL DEFAULT 'honey',
  `affects_tomato` enum('no','yes') NOT NULL DEFAULT 'no',
  `pending_period` int(10) unsigned NOT NULL,
  `end_status` enum('complete','failed') NOT NULL,
  `period_type` enum('business','calendar') DEFAULT 'business',
  PRIMARY KEY (`festival_type_id`),
  UNIQUE KEY `idx_trans_type_co_name_short` (`company_id`,`name_short`)
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;

# From error log

120501 9:59:10 Percona XtraDB (http://www.percona.com) 1.0.17-13.0 started; log sequence number 2843475283138
120501 9:59:10 [Note] ./bin/mysqld: ready for connections.
Version: '5.3.6-MariaDB-log' socket: '/san/ecashamg/ecashamg.sock' port: 3306 (MariaDB - http://mariadb.com/)
120501 9:59:20 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see http://kb.askmonty.org/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 5.3.6-MariaDB-log
key_buffer_size=16777216
read_buffer_size=4194304
max_used_connections=1
max_threads=501
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 10283383 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0x12228f80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f629328ae98 thread_stack 0x48000
./bin/mysqld(my_print_stacktrace+0x2e) [0xa2b06e]
./bin/mysqld(handle_fatal_signal+0x3f9) [0x762339]
/lib/libpthread.so.0(+0xeff0) [0x7f656c1c1ff0]
./bin/mysqld(get_datetime_value(THD*, Item***, Item**, Item*, bool*)+0x52) [0x5b4822]
./bin/mysqld(in_datetime::set(unsigned int, Item*)+0x36) [0x5b4b76]
./bin/mysqld(Item_func_in::fix_length_and_dec()+0x3c4) [0x5bb8e4]
./bin/mysqld(Item_func::fix_fields(THD*, Item**)+0x1a0) [0x59db10]
./bin/mysqld(Item_func_in::fix_fields(THD*, Item**)+0x9) [0x5b0ca9]
./bin/mysqld(Item_cond::fix_fields(THD*, Item**)+0x17f) [0x5b2a3f]
./bin/mysqld(Item_func::fix_fields(THD*, Item**)+0x1d4) [0x59db44]
./bin/mysqld(Item_func_if::fix_fields(THD*, Item**)+0x3e) [0x5b0cee]
./bin/mysqld(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool)+0x159) [0x692989]
./bin/mysqld(JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)+0x4ed) [0x6baf6d]
./bin/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x857) [0x6d1ec7]
./bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x16f) [0x6d21df]
./bin/mysqld() [0x647a2e]
./bin/mysqld(mysql_execute_command(THD*)+0x3a58) [0x64d628]
./bin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x299) [0x650409]
./bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xa9b) [0x6512fb]
./bin/mysqld(do_command(THD*)+0x101) [0x651e51]
./bin/mysqld(handle_one_connection+0xfd) [0x64325d]
/lib/libpthread.so.0(+0x68ca) [0x7f656c1b98ca]
/lib/libc.so.6(clone+0x6d) [0x7f656b78292d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x1223b8d8): SELECT
    DATE_FORMAT(
     (
      SELECT MIN(date_effective)
      FROM barn_schedule ees
      WHERE
       ees.apple_id = a.apple_id AND
       date_effective > '2012-04-26' AND
       (ees.amount_non_tomato < 0 OR ees.amount_tomato < 0)
     ),
     '%c/%e/%y'
    ) AS next_due,
    IF(
     (
      SELECT MIN(date_effective)
      FROM barn_schedule evs
      WHERE
       evs.apple_id = a.apple_id AND
       (evs.amount_tomato + evs.amount_non_tomato) <= 0
     ) IN ('2012-04-25','2012-04-26') AND
     a.is_react = 'no',
     1,
     0
    ) first_seeds,
    IF(a.apple_status_id IN (126,125,127), 1, 0) special,
    IF(
     (
      SELECT
       SUM(
        IF(
         tr.festival_register_id IS NULL,
         es.amount_tomato + es.amount_non_tomato,
         tr.amount
        )
       )
      FROM
       barn_schedule es
       LEFT JOIN festival_register tr USING (barn_schedule_id)
      WHERE
       es.date_effective <= '2012-04-26' AND
       es.date_created <= '2012-04-26 22:00:13' AND
       es.apple_id = a.apple_id AND
       (
        es.barn_status = 'scheduled' OR
        tr.festival_status IN ('complete','pending')
       )
       AND
        es.context != 'cancel'
     ) <= 0 AND a.apple_status_id NOT IN (124,19),
     1,
     0
    ) payout,
    IF(
     COUNT(IF(et.name_short IN ('cancel', 'order_cancel'), 1, NULL)) > 0,
     1,
     0
    ) cancel,
    SUM(IF(
     eat.name_short = 'watermelon',
     ea.amount,
     0
    )) tomato,
    SUM(IF(
     eat.name_short = 'peach',
     ea.amount,
     0
    )) peach,
    SUM(IF(
     eat.name_short = 'grape',
     ea.amount,
     0
    )) grape,
    SUM(IF(
     eat.name_short <> 'blackberry',
     ea.amount,
     0
    )) AS amount_due
   FROM
    barn_schedule es
    JOIN company c USING (company_id)
    JOIN barn_type et USING (barn_type_id)
    JOIN barn_amount ea USING (barn_schedule_id)
    JOIN barn_amount_type eat USING (barn_amount_type_id)
    LEFT JOIN festival_register tr USING(barn_schedule_id)
    JOIN apple a ON es.apple_id = a.apple_id
    JOIN apple_status ass USING (apple_status_id)
    JOIN fresh_type lt USING (fresh_type_id)
    LEFT JOIN (
     apple_language al
     JOIN language USING (language_id)
    ) ON (
     al.apple_id = a.apple_id
     AND al.date_created < DATE_ADD(es.date_effective, INTERVAL 1 DAY)
    )
    LEFT JOIN apple_language al_check ON (
     al_check.apple_id = a.apple_id
     AND al_check.date_created < DATE_ADD(es.date_effective, INTERVAL 1 DAY)
     AND al_check.apple_language_id > al.apple_language_id
    )
   WHERE
    (
     tr.festival_register_id = ea.festival_register_id OR
     tr.festival_register_id IS NULL
    ) AND
    es.barn_type_id IN (
     SELECT et.barn_type_id
     FROM
      barn_type et
      JOIN barn_festival USING (barn_type_id)
      JOIN festival_type USING (festival_type_id)
     WHERE
      clearing_type <> 'honey'
      AND et.name_short NOT IN ('count_internal',
          'system_count',
          'discount',
          'f_count_kiwi_failed',
          'f_count_cancell')
    ) AND
    (

     es.date_effective = '2012-04-26' OR
     (

      es.date_effective = DATE('2012-04-25 22:00:15') AND
      es.date_created BETWEEN '2012-04-25 22:00:15' AND '2012-04-26 22:00:13'
     )
    ) AND
    es.company_id IN ('6') AND
    es.barn_status <> 'suspended' AND
    (tr.festival_status <> 'failed' OR tr.festival_status IS NULL) AND
    lt.name_short IN ('standard','order') AND
    es.amount_tomato <= 0 AND
    es.amount_non_tomato <= 0 AND
    a.apple_status_id NOT IN (115,116,114) AND
    (et.name <> 'Cancel' OR es.amount_non_tomato < 0)

    AND al_check.apple_language_id IS NULL
Connection ID (thread ID): 2
Status: NOT_KILLED
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
120501 09:59:20 mysqld_safe Number of processes running now: 0
120501 09:59:20 mysqld_safe mysqld restarted

[Elena Stepanova (elenst)]

Thank you for the report.

# Simplified test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a DATE );
SELECT * FROM t1 WHERE ( SELECT a FROM t1 ) IN ('2012-04-25','2012-04-26');

# End of test case

Reproducible on the current maria-5.3 tree (revno 3510).
Reproducible with the default optimizer_switch, as well as with all OFF values.
Could not reproduce on maria-5.1, maria-5.2, maria-5.5, mysql-5.5, mysql-trunk.

#3 <signal handler called>
#4 0x00000000006388fb in get_datetime_value (thd=0x1c2b9a8, item_arg=0x7fb02c5d60f0,
    cache_arg=0x0, warn_item=0x0, is_null=0x7fb02c5d60ef) at item_cmpfunc.cc:877
#5 0x00000000006413f2 in in_datetime::set (this=0x1cde590, pos=0, item=0x1cb16e8)
    at item_cmpfunc.cc:3455
#6 0x0000000000642fa8 in Item_func_in::fix_length_and_dec (this=0x1cb18a0)
    at item_cmpfunc.cc:3958
#7 0x000000000061cc1d in Item_func::fix_fields (this=0x1cb18a0, thd=0x1c2b9a8,
    ref=0x1cb1e60) at item_func.cc:207
#8 0x00000000006424cb in Item_func_in::fix_fields (this=0x1cb18a0, thd=0x1c2b9a8,
    ref=0x1cb1e60) at item_cmpfunc.cc:3776
#9 0x000000000075b1c1 in setup_conds (thd=0x1c2b9a8, tables=0x1cb0398, leaves=...,
    conds=0x1cb1e60) at sql_base.cc:8502
#10 0x00000000007ad527 in setup_without_group (thd=0x1c2b9a8,
    ref_pointer_array=0x1cb20a0, tables=0x1cb0398, leaves=..., fields=...,
    all_fields=..., conds=0x1cb1e60, order=0x0, group=0x0,
    hidden_group_fields=0x1cb1d57) at sql_select.cc:519
#11 0x0000000000771447 in JOIN::prepare (this=0x1cb1a88, rref_pointer_array=0x1c2e5e8,
    tables_init=0x1cb0398, wild_num=1, conds_init=0x1cb18a0, og_num=0, order_init=0x0,
    group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x1c2e398,
    unit_arg=0x1c2de90) at sql_select.cc:667
#12 0x0000000000779b83 in mysql_select (thd=0x1c2b9a8, rref_pointer_array=0x1c2e5e8,
    tables=0x1cb0398, wild_num=1, fields=..., conds=0x1cb18a0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x1cb1a68,
    unit=0x1c2de90, select_lex=0x1c2e398) at sql_select.cc:2932
#13 0x0000000000770801 in handle_select (thd=0x1c2b9a8, lex=0x1c2ddf0,
    result=0x1cb1a68, setup_tables_done_option=0) at sql_select.cc:285
#14 0x00000000006feecc in execute_sqlcom_select (thd=0x1c2b9a8, all_tables=0x1cb0398)
    at sql_parse.cc:5151
#15 0x00000000006f5f89 in mysql_execute_command (thd=0x1c2b9a8) at sql_parse.cc:2284
#16 0x00000000007018a7 in mysql_parse (thd=0x1c2b9a8,
    rawbuf=0x1cb0140 "SELECT * FROM t1 WHERE ( SELECT a FROM t1 ) IN ('2012-04-25','2012-04-26')", length=74, found_semicolon=0x7fb02c5d7c98) at sql_parse.cc:6152
#17 0x00000000006f3738 in dispatch_command (command=COM_QUERY, thd=0x1c2b9a8,
    packet=0x1ca6c89 "SELECT * FROM t1 WHERE ( SELECT a FROM t1 ) IN ('2012-04-25','2012-04-26')", packet_length=74) at sql_parse.cc:1228
#18 0x00000000006f2a6c in do_command (thd=0x1c2b9a8) at sql_parse.cc:923
#19 0x00000000006ef8ba in handle_one_connection (arg=0x1c2b9a8) at sql_connect.cc:1218
#20 0x00007fb035d75a4f in start_thread () from /lib64/libpthread.so.0
#21 0x00007fb03515f82d in clone () from /lib64/libc.so.6

[Elena Stepanova (elenst)]

Also filed in JIRA as https://mariadb.atlassian.net/browse/MDEV-241

[Elena Stepanova (elenst)]

Fix released in 5.3.7 and 5.5.24