[MDEV-8911] Date comparaison to string failed with '=' and ISO DATE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Cannot Reproduce
Affects Version/s: 10.0.20, 10.0(EOL), 10.1(EOL)
Fix Version/s: N/A
Component/s: Data types, Optimizer, Temporal Types
Labels:
- wrong_result
Environment:
linux, ubuntu trusty docker, mariadb 10.0.20-MariaDB-1~trusty-wsrep-log

Epic Link:
Data type cleanups

Description

Date comparison fail on the datetime fields only on strict comparisons

Table format
+------------------+--------------+------+-----+---------+-------+
\| Field \| Type \| Null \| Key \| Default \| Extra \|
+------------------+--------------+------+-----+---------+-------+
\| processingdate \| datetime \| YES \| \| NULL \| \|
+------------------+--------------+------+-----+---------+-------+

Table content
select processingdate from rbs_order_doc_shipment ;
+---------------------+
\| processingdate \|
+---------------------+
\| NULL \|
\| 2015-10-06 15:24:47 \|
\| NULL \|
\| 2015-10-06 15:27:38 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-07 06:04:30 \|
+---------------------+

Query on error
select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
Empty set (0.00 sec)

But those works:

select processingdate from rbs_order_doc_shipment where processingdate <= '2015-10-06T19:14:52+0000' ;

+---------------------+

| processingdate      |

+---------------------+

| 2015-10-06 15:24:47 |

| 2015-10-06 15:27:38 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

+---------------------+

select processingdate from rbs_order_doc_shipment where processingdate >= '2015-10-06T19:14:52+0000'   limit 10;

+---------------------+

| processingdate      |

+---------------------+

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-06 19:14:52 |

| 2015-10-07 06:04:30 |

+---------------------+

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2015-10-12 13:06

bnprss,

Could you please provide the complete output of SHOW CREATE TABLE, and also attach your cnf file(s) (or the output of SHOW VARIABLES)?

I'm not getting the problem on default settings.

Elena Stepanova added a comment - 2015-10-12 13:06 bnprss , Could you please provide the complete output of SHOW CREATE TABLE , and also attach your cnf file(s) (or the output of SHOW VARIABLES )? I'm not getting the problem on default settings.

Benjamin Preiss (Inactive) added a comment - 2015-10-12 14:46

Here are the informations :

SHOW CREATE table rbs_order_doc_shipment

CREATE TABLE `rbs_order_doc_shipment` (
`document_id` int(11) NOT NULL DEFAULT '0',
`document_model` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`orderid` int(11) NOT NULL DEFAULT '0',
`contextdata` mediumtext COLLATE utf8_unicode_ci,
`prepared` tinyint(1) NOT NULL DEFAULT '0',
`parcelcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`shippingmodecode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`trackingcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`carrierstatus` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`itemcount` int(11) DEFAULT NULL,
`data` mediumtext COLLATE utf8_unicode_ci,
`address` mediumtext COLLATE utf8_unicode_ci,
`shippingdate` datetime DEFAULT NULL,
`deliverydate` datetime DEFAULT NULL,
`creationdate` datetime DEFAULT NULL,
`modificationdate` datetime DEFAULT NULL,
`authorname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`authorid` int(11) NOT NULL DEFAULT '0',
`documentversion` int(11) DEFAULT NULL,
`productreturnid` int(11) NOT NULL DEFAULT '0',
`accepted` tinyint(1) NOT NULL DEFAULT '0',
`contentdata` mediumtext COLLATE utf8_unicode_ci,
`processingdate` datetime DEFAULT NULL,
PRIMARY KEY (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Benjamin Preiss (Inactive) added a comment - 2015-10-12 14:46 Here are the informations : SHOW CREATE table rbs_order_doc_shipment CREATE TABLE `rbs_order_doc_shipment` ( `document_id` int(11) NOT NULL DEFAULT '0', `document_model` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `orderid` int(11) NOT NULL DEFAULT '0', `contextdata` mediumtext COLLATE utf8_unicode_ci, `prepared` tinyint(1) NOT NULL DEFAULT '0', `parcelcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `shippingmodecode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `trackingcode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `carrierstatus` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `itemcount` int(11) DEFAULT NULL, `data` mediumtext COLLATE utf8_unicode_ci, `address` mediumtext COLLATE utf8_unicode_ci, `shippingdate` datetime DEFAULT NULL, `deliverydate` datetime DEFAULT NULL, `creationdate` datetime DEFAULT NULL, `modificationdate` datetime DEFAULT NULL, `authorname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `authorid` int(11) NOT NULL DEFAULT '0', `documentversion` int(11) DEFAULT NULL, `productreturnid` int(11) NOT NULL DEFAULT '0', `accepted` tinyint(1) NOT NULL DEFAULT '0', `contentdata` mediumtext COLLATE utf8_unicode_ci, `processingdate` datetime DEFAULT NULL, PRIMARY KEY (`document_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Benjamin Preiss (Inactive) added a comment - 2015-10-12 14:51

And variables:

SHOW VARIABLES
\|aria_block_size\|8192\|
\|aria_checkpoint_interval\|30\|
\|aria_checkpoint_log_activity\|1048576\|
\|aria_force_start_after_recovery_failures\|0\|
\|aria_group_commit\|none\|
\|aria_group_commit_interval\|0\|
\|aria_log_file_size\|1073741824\|
\|aria_log_purge_type\|immediate\|
\|aria_max_sort_file_size\|9223372036853727232\|
\|aria_page_checksum\|ON\|
\|aria_pagecache_age_threshold\|300\|
\|aria_pagecache_buffer_size\|134217728\|
\|aria_pagecache_division_limit\|100\|
\|aria_pagecache_file_hash_size\|512\|
\|aria_recover\|NORMAL\|
\|aria_repair_threads\|1\|
\|aria_sort_buffer_size\|268434432\|
\|aria_stats_method\|nulls_unequal\|
\|aria_sync_log_dir\|NEWFILE\|
\|aria_used_for_temp_tables\|ON\|
\|auto_increment_increment\|1\|
\|auto_increment_offset\|1\|
\|autocommit\|ON\|
\|automatic_sp_privileges\|ON\|
\|back_log\|150\|
\|basedir\|/usr\|
\|big_tables\|OFF\|
\|binlog_annotate_row_events\|OFF\|
\|binlog_cache_size\|32768\|
\|binlog_checksum\|NONE\|
\|binlog_commit_wait_count\|0\|
\|binlog_commit_wait_usec\|100000\|
\|binlog_direct_non_transactional_updates\|OFF\|
\|binlog_format\|ROW\|
\|binlog_optimize_thread_scheduling\|ON\|
\|binlog_stmt_cache_size\|32768\|
\|bulk_insert_buffer_size\|8388608\|
\|character_set_client\|utf8\|
\|character_set_connection\|utf8\|
\|character_set_database\|latin1\|
\|character_set_filesystem\|binary\|
\|character_set_results\|utf8\|
\|character_set_server\|latin1\|
\|character_set_system\|utf8\|
\|character_sets_dir\|/usr/share/mysql/charsets/\|
\|collation_connection\|utf8_general_ci\|
\|collation_database\|latin1_swedish_ci\|
\|collation_server\|latin1_swedish_ci\|
\|completion_type\|NO_CHAIN\|
\|concurrent_insert\|AUTO\|
\|connect_timeout\|10\|
\|datadir\|/var/lib/mysql/\|
\|date_format\|%Y-%m-%d\|
\|datetime_format\|%Y-%m-%d%H:%i:%s\|
\|deadlock_search_depth_long\|15\|
\|deadlock_search_depth_short\|4\|
\|deadlock_timeout_long\|50000000\|
\|deadlock_timeout_short\|10000\|
\|debug_no_thread_alarm\|OFF\|
\|default_master_connection\|\|
\|default_regex_flags\|\|
\|default_storage_engine\|InnoDB\|
\|default_week_format\|0\|
\|delay_key_write\|ON\|
\|delayed_insert_limit\|100\|
\|delayed_insert_timeout\|300\|
\|delayed_queue_size\|1000\|
\|div_precision_increment\|4\|
\|error_count\|0\|
\|event_scheduler\|OFF\|
\|expensive_subquery_limit\|100\|
\|expire_logs_days\|7\|
\|external_user\|\|
\|extra_max_connections\|1\|
\|extra_port\|0\|
\|flush\|OFF\|
\|flush_time\|0\|
\|foreign_key_checks\|ON\|
\|ft_boolean_syntax\|+-><()~*:""&\|\|
\|ft_max_word_len\|84\|
\|ft_min_word_len\|4\|
\|ft_query_expansion_limit\|20\|
\|ft_stopword_file\|(built-in)\|
\|general_log\|OFF\|
\|general_log_file\|prod-mysql.log\|
\|group_concat_max_len\|1024\|
\|gtid_binlog_pos\|0-1-49317\|
\|gtid_binlog_state\|0-1-49317\|
\|gtid_current_pos\|0-1-49317\|
\|gtid_domain_id\|0\|
\|gtid_ignore_duplicates\|OFF\|
\|gtid_seq_no\|0\|
\|gtid_slave_pos\|\|
\|gtid_strict_mode\|OFF\|
\|have_compress\|YES\|
\|have_crypt\|YES\|
\|have_dynamic_loading\|YES\|
\|have_geometry\|YES\|
\|have_openssl\|YES\|
\|have_profiling\|YES\|
\|have_query_cache\|YES\|
\|have_rtree_keys\|YES\|
\|have_ssl\|DISABLED\|
\|have_symlink\|YES\|
\|histogram_size\|0\|
\|histogram_type\|SINGLE_PREC_HB\|
\|host_cache_size\|128\|
\|hostname\|prod-mysql\|
\|identity\|0\|
\|ignore_builtin_innodb\|OFF\|
\|ignore_db_dirs\|\|
\|in_transaction\|0\|
\|init_connect\|\|
\|init_file\|\|
\|init_slave\|\|
\|innodb_adaptive_flushing\|ON\|
\|innodb_adaptive_flushing_lwm\|10.000000\|
\|innodb_adaptive_hash_index\|ON\|
\|innodb_adaptive_hash_index_partitions\|1\|
\|innodb_adaptive_max_sleep_delay\|150000\|
\|innodb_additional_mem_pool_size\|8388608\|
\|innodb_api_bk_commit_interval\|5\|
\|innodb_api_disable_rowlock\|OFF\|
\|innodb_api_enable_binlog\|OFF\|
\|innodb_api_enable_mdl\|OFF\|
\|innodb_api_trx_level\|0\|
\|innodb_autoextend_increment\|64\|
\|innodb_autoinc_lock_mode\|2\|
\|innodb_buffer_pool_dump_at_shutdown\|OFF\|
\|innodb_buffer_pool_dump_now\|OFF\|
\|innodb_buffer_pool_filename\|ib_buffer_pool\|
\|innodb_buffer_pool_instances\|8\|
\|innodb_buffer_pool_load_abort\|OFF\|
\|innodb_buffer_pool_load_at_startup\|OFF\|
\|innodb_buffer_pool_load_now\|OFF\|
\|innodb_buffer_pool_populate\|OFF\|
\|innodb_buffer_pool_size\|10737418240\|
\|innodb_change_buffer_max_size\|25\|
\|innodb_change_buffering\|all\|
\|innodb_checksum_algorithm\|innodb\|
\|innodb_checksums\|ON\|
\|innodb_cleaner_lsn_age_factor\|high_checkpoint\|
\|innodb_cmp_per_index_enabled\|OFF\|
\|innodb_commit_concurrency\|0\|
\|innodb_compression_failure_threshold_pct\|5\|
\|innodb_compression_level\|6\|
\|innodb_compression_pad_pct_max\|50\|
\|innodb_concurrency_tickets\|5000\|
\|innodb_corrupt_table_action\|assert\|
\|innodb_data_file_path\|ibdata1:12M:autoextend\|
\|innodb_data_home_dir\|\|
\|innodb_disable_sort_file_cache\|OFF\|
\|innodb_disallow_writes\|OFF\|
\|innodb_doublewrite\|ON\|
\|innodb_empty_free_list_algorithm\|backoff\|
\|innodb_fake_changes\|OFF\|
\|innodb_fast_shutdown\|1\|
\|innodb_file_format\|Antelope\|
\|innodb_file_format_check\|ON\|
\|innodb_file_format_max\|Antelope\|
\|innodb_file_per_table\|ON\|
\|innodb_flush_log_at_timeout\|1\|
\|innodb_flush_log_at_trx_commit\|0\|
\|innodb_flush_method\|O_DIRECT\|
\|innodb_flush_neighbors\|0\|
\|innodb_flushing_avg_loops\|30\|
\|innodb_force_load_corrupted\|OFF\|
\|innodb_force_recovery\|0\|
\|innodb_foreground_preflush\|exponential_backoff\|
\|innodb_ft_aux_table\|\|
\|innodb_ft_cache_size\|8000000\|
\|innodb_ft_enable_diag_print\|OFF\|
\|innodb_ft_enable_stopword\|ON\|
\|innodb_ft_max_token_size\|84\|
\|innodb_ft_min_token_size\|3\|
\|innodb_ft_num_word_optimize\|2000\|
\|innodb_ft_result_cache_limit\|2000000000\|
\|innodb_ft_server_stopword_table\|\|
\|innodb_ft_sort_pll_degree\|2\|
\|innodb_ft_total_cache_size\|640000000\|
\|innodb_ft_user_stopword_table\|\|
\|innodb_io_capacity\|200\|
\|innodb_io_capacity_max\|2000\|
\|innodb_kill_idle_transaction\|0\|
\|innodb_large_prefix\|OFF\|
\|innodb_lock_wait_timeout\|50\|
\|innodb_locking_fake_changes\|ON\|
\|innodb_locks_unsafe_for_binlog\|OFF\|
\|innodb_log_arch_dir\|./\|
\|innodb_log_arch_expire_sec\|0\|
\|innodb_log_archive\|OFF\|
\|innodb_log_block_size\|512\|
\|innodb_log_buffer_size\|4194304\|
\|innodb_log_checksum_algorithm\|innodb\|
\|innodb_log_compressed_pages\|ON\|
\|innodb_log_file_size\|268435456\|
\|innodb_log_files_in_group\|2\|
\|innodb_log_group_home_dir\|./\|
\|innodb_lru_scan_depth\|1024\|
\|innodb_max_bitmap_file_size\|104857600\|
\|innodb_max_changed_pages\|1000000\|
\|innodb_max_dirty_pages_pct\|75.000000\|
\|innodb_max_dirty_pages_pct_lwm\|0.001000\|
\|innodb_max_purge_lag\|0\|
\|innodb_max_purge_lag_delay\|0\|
\|innodb_mirrored_log_groups\|1\|
\|innodb_monitor_disable\|\|
\|innodb_monitor_enable\|\|
\|innodb_monitor_reset\|\|
\|innodb_monitor_reset_all\|\|
\|innodb_old_blocks_pct\|37\|
\|innodb_old_blocks_time\|1000\|
\|innodb_online_alter_log_max_size\|134217728\|
\|innodb_open_files\|2048\|
\|innodb_optimize_fulltext_only\|OFF\|
\|innodb_page_size\|16384\|
\|innodb_print_all_deadlocks\|OFF\|
\|innodb_purge_batch_size\|300\|
\|innodb_purge_threads\|1\|
\|innodb_random_read_ahead\|OFF\|
\|innodb_read_ahead_threshold\|56\|
\|innodb_read_io_threads\|4\|
\|innodb_read_only\|OFF\|
\|innodb_replication_delay\|0\|
\|innodb_rollback_on_timeout\|OFF\|
\|innodb_rollback_segments\|128\|
\|innodb_sched_priority_cleaner\|19\|
\|innodb_show_locks_held\|10\|
\|innodb_show_verbose_locks\|0\|
\|innodb_simulate_comp_failures\|0\|
\|innodb_sort_buffer_size\|1048576\|
\|innodb_spin_wait_delay\|6\|
\|innodb_stats_auto_recalc\|ON\|
\|innodb_stats_method\|nulls_equal\|
\|innodb_stats_modified_counter\|0\|
\|innodb_stats_on_metadata\|OFF\|
\|innodb_stats_persistent\|ON\|
\|innodb_stats_persistent_sample_pages\|20\|
\|innodb_stats_sample_pages\|8\|
\|innodb_stats_traditional\|ON\|
\|innodb_stats_transient_sample_pages\|8\|
\|innodb_status_output\|OFF\|
\|innodb_status_output_locks\|OFF\|
\|innodb_strict_mode\|OFF\|
\|innodb_support_xa\|ON\|
\|innodb_sync_array_size\|1\|
\|innodb_sync_spin_loops\|30\|
\|innodb_table_locks\|ON\|
\|innodb_thread_concurrency\|4\|
\|innodb_thread_sleep_delay\|0\|
\|innodb_track_changed_pages\|OFF\|
\|innodb_undo_directory\|.\|
\|innodb_undo_logs\|128\|
\|innodb_undo_tablespaces\|0\|
\|innodb_use_atomic_writes\|OFF\|
\|innodb_use_fallocate\|OFF\|
\|innodb_use_global_flush_log_at_trx_commit\|ON\|
\|innodb_use_native_aio\|ON\|
\|innodb_use_stacktrace\|OFF\|
\|innodb_use_sys_malloc\|ON\|
\|innodb_version\|5.6.25-73.1\|
\|innodb_write_io_threads\|4\|
\|insert_id\|0\|
\|interactive_timeout\|28800\|
\|join_buffer_size\|131072\|
\|join_buffer_space_limit\|2097152\|
\|join_cache_level\|2\|
\|keep_files_on_create\|OFF\|
\|key_buffer_size\|33554432\|
\|key_cache_age_threshold\|300\|
\|key_cache_block_size\|1024\|
\|key_cache_division_limit\|100\|
\|key_cache_file_hash_size\|512\|
\|key_cache_segments\|0\|
\|large_files_support\|ON\|
\|large_page_size\|0\|
\|large_pages\|OFF\|
\|last_gtid\|\|
\|last_insert_id\|0\|
\|lc_messages\|en_US\|
\|lc_messages_dir\|\|
\|lc_time_names\|en_US\|
\|license\|GPL\|
\|local_infile\|ON\|
\|lock_wait_timeout\|31536000\|
\|locked_in_memory\|OFF\|
\|low_priority_updates\|OFF\|
\|lower_case_file_system\|OFF\|
\|lower_case_table_names\|0\|
\|master_verify_checksum\|OFF\|
\|max_allowed_packet\|16777216\|
\|max_binlog_cache_size\|18446744073709547520\|
\|max_binlog_size\|1073741824\|
\|max_binlog_stmt_cache_size\|18446744073709547520\|
\|max_connect_errors\|1000000\|
\|max_connections\|500\|
\|max_delayed_threads\|20\|
\|max_digest_length\|1024\|
\|max_error_count\|64\|
\|max_heap_table_size\|33554432\|
\|max_insert_delayed_threads\|20\|
\|max_join_size\|18446744073709551615\|
\|max_length_for_sort_data\|1024\|
\|max_long_data_size\|16777216\|
\|max_prepared_stmt_count\|16382\|
\|max_relay_log_size\|1073741824\|
\|max_seeks_for_key\|4294967295\|
\|max_sort_length\|1024\|
\|max_sp_recursion_depth\|0\|
\|max_tmp_tables\|32\|
\|max_user_connections\|0\|
\|max_write_lock_count\|4294967295\|
\|metadata_locks_cache_size\|1024\|
\|metadata_locks_hash_instances\|8\|
\|min_examined_row_limit\|0\|
\|mrr_buffer_size\|262144\|
\|multi_range_count\|256\|
\|myisam_block_size\|1024\|
\|myisam_data_pointer_size\|6\|
\|myisam_max_sort_file_size\|9223372036853727232\|
\|myisam_mmap_size\|18446744073709551615\|
\|myisam_recover_options\|BACKUP,FORCE\|
\|myisam_repair_threads\|1\|
\|myisam_sort_buffer_size\|134216704\|
\|myisam_stats_method\|nulls_unequal\|
\|myisam_use_mmap\|OFF\|
\|net_buffer_length\|16384\|
\|net_read_timeout\|30\|
\|net_retry_count\|10\|
\|net_write_timeout\|60\|
\|old\|OFF\|
\|old_alter_table\|OFF\|
\|old_mode\|\|
\|old_passwords\|OFF\|
\|open_files_limit\|65535\|
\|optimizer_prune_level\|1\|
\|optimizer_search_depth\|62\|
\|optimizer_selectivity_sampling_limit\|100\|
\|optimizer_switch\|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,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,extended_keys=on,exists_to_in=on\|
\|optimizer_use_condition_selectivity\|4\|
\|performance_schema\|OFF\|
\|performance_schema_accounts_size\|-1\|
\|performance_schema_digests_size\|-1\|
\|performance_schema_events_stages_history_long_size\|-1\|
\|performance_schema_events_stages_history_size\|-1\|
\|performance_schema_events_statements_history_long_size\|-1\|
\|performance_schema_events_statements_history_size\|-1\|
\|performance_schema_events_waits_history_long_size\|-1\|
\|performance_schema_events_waits_history_size\|-1\|
\|performance_schema_hosts_size\|-1\|
\|performance_schema_max_cond_classes\|80\|
\|performance_schema_max_cond_instances\|-1\|
\|performance_schema_max_digest_length\|1024\|
\|performance_schema_max_file_classes\|50\|
\|performance_schema_max_file_handles\|32768\|
\|performance_schema_max_file_instances\|-1\|
\|performance_schema_max_mutex_classes\|200\|
\|performance_schema_max_mutex_instances\|-1\|
\|performance_schema_max_rwlock_classes\|40\|
\|performance_schema_max_rwlock_instances\|-1\|
\|performance_schema_max_socket_classes\|10\|
\|performance_schema_max_socket_instances\|-1\|
\|performance_schema_max_stage_classes\|150\|
\|performance_schema_max_statement_classes\|180\|
\|performance_schema_max_table_handles\|-1\|
\|performance_schema_max_table_instances\|-1\|
\|performance_schema_max_thread_classes\|50\|
\|performance_schema_max_thread_instances\|-1\|
\|performance_schema_session_connect_attrs_size\|-1\|
\|performance_schema_setup_actors_size\|100\|
\|performance_schema_setup_objects_size\|100\|
\|performance_schema_users_size\|-1\|
\|pid_file\|/var/lib/mysql/mysql.pid\|
\|plugin_dir\|/usr/lib/mysql/plugin/\|
\|plugin_maturity\|unknown\|
\|port\|3306\|
\|preload_buffer_size\|32768\|
\|profiling\|OFF\|
\|profiling_history_size\|15\|
\|progress_report_time\|5\|
\|protocol_version\|10\|
\|proxy_user\|\|
\|pseudo_slave_mode\|OFF\|
\|pseudo_thread_id\|1253921\|
\|query_alloc_block_size\|8192\|
\|query_cache_limit\|1048576\|
\|query_cache_min_res_unit\|4096\|
\|query_cache_size\|33554432\|
\|query_cache_strip_comments\|OFF\|
\|query_cache_type\|OFF\|
\|query_cache_wlock_invalidate\|OFF\|
\|query_prealloc_size\|8192\|
\|rand_seed1\|0\|
\|rand_seed2\|0\|
\|range_alloc_block_size\|4096\|
\|read_buffer_size\|131072\|
\|read_only\|OFF\|
\|read_rnd_buffer_size\|262144\|
\|relay_log\|\|
\|relay_log_index\|\|
\|relay_log_info_file\|relay-log.info\|
\|relay_log_purge\|ON\|
\|relay_log_recovery\|OFF\|
\|relay_log_space_limit\|0\|
\|replicate_annotate_row_events\|OFF\|
\|replicate_do_db\|\|
\|replicate_do_table\|\|
\|replicate_events_marked_for_skip\|replicate\|
\|replicate_ignore_db\|\|
\|replicate_ignore_table\|\|
\|replicate_wild_do_table\|\|
\|replicate_wild_ignore_table\|\|
\|report_host\|\|
\|report_password\|\|
\|report_port\|3306\|
\|report_user\|\|
\|rowid_merge_buff_size\|8388608\|
\|rpl_recovery_rank\|0\|
\|secure_auth\|OFF\|
\|secure_file_priv\|\|
\|server_id\|1\|
\|skip_external_locking\|ON\|
\|skip_name_resolve\|ON\|
\|skip_networking\|OFF\|
\|skip_replication\|OFF\|
\|skip_show_database\|OFF\|
\|slave_compressed_protocol\|OFF\|
\|slave_ddl_exec_mode\|IDEMPOTENT\|
\|slave_domain_parallel_threads\|0\|
\|slave_exec_mode\|STRICT\|
\|slave_load_tmpdir\|/tmp\|
\|slave_max_allowed_packet\|1073741824\|
\|slave_net_timeout\|3600\|
\|slave_parallel_max_queued\|131072\|
\|slave_parallel_threads\|0\|
\|slave_skip_errors\|OFF\|
\|slave_sql_verify_checksum\|ON\|
\|slave_transaction_retries\|10\|
\|slave_type_conversions\|\|
\|slow_launch_time\|2\|
\|slow_query_log\|ON\|
\|slow_query_log_file\|/var/log/mysql/mysql-slow.log\|
\|socket\|/var/run/mysqld/mysqld.sock\|
\|sort_buffer_size\|2097152\|
\|sql_auto_is_null\|OFF\|
\|sql_big_selects\|ON\|
\|sql_buffer_result\|OFF\|
\|sql_log_bin\|ON\|
\|sql_log_off\|OFF\|
\|sql_mode\|\|
\|sql_notes\|ON\|
\|sql_quote_show_create\|ON\|
\|sql_safe_updates\|OFF\|
\|sql_select_limit\|18446744073709551615\|
\|sql_slave_skip_counter\|0\|
\|sql_warnings\|OFF\|
\|storage_engine\|InnoDB\|
\|stored_program_cache\|256\|
\|sync_binlog\|1\|
\|sync_frm\|ON\|
\|sync_master_info\|0\|
\|sync_relay_log\|0\|
\|sync_relay_log_info\|0\|
\|system_time_zone\|UTC\|
\|table_definition_cache\|1024\|
\|table_open_cache\|2048\|
\|thread_cache_size\|50\|
\|thread_concurrency\|10\|
\|thread_handling\|one-thread-per-connection\|
\|thread_pool_idle_timeout\|60\|
\|thread_pool_max_threads\|500\|
\|thread_pool_oversubscribe\|3\|
\|thread_pool_size\|4\|
\|thread_pool_stall_limit\|500\|
\|thread_stack\|294912\|
\|time_format\|%H:%i:%s\|
\|time_zone\|SYSTEM\|
\|timed_mutexes\|OFF\|
\|timestamp\|1444650143.484721\|
\|tmp_table_size\|33554432\|
\|transaction_alloc_block_size\|8192\|
\|transaction_prealloc_size\|4096\|
\|tx_isolation\|REPEATABLE-READ\|
\|tx_read_only\|OFF\|
\|unique_checks\|ON\|
\|updatable_views_with_limit\|YES\|
\|use_stat_tables\|PREFERABLY\|
\|userstat\|OFF\|
\|version\|10.0.21-MariaDB-1~trusty-wsrep-log\|
\|version_comment\|mariadb.orgbinarydistribution,wsrep_25.10.r4144\|
\|version_compile_machine\|x86_64\|
\|version_compile_os\|debian-linux-gnu\|
\|version_malloc_library\|bundledjemalloc\|
\|wait_timeout\|28800\|

Benjamin Preiss (Inactive) added a comment - 2015-10-12 14:51 And variables: SHOW VARIABLES |aria_block_size|8192| |aria_checkpoint_interval|30| |aria_checkpoint_log_activity|1048576| |aria_force_start_after_recovery_failures|0| |aria_group_commit|none| |aria_group_commit_interval|0| |aria_log_file_size|1073741824| |aria_log_purge_type|immediate| |aria_max_sort_file_size|9223372036853727232| |aria_page_checksum|ON| |aria_pagecache_age_threshold|300| |aria_pagecache_buffer_size|134217728| |aria_pagecache_division_limit|100| |aria_pagecache_file_hash_size|512| |aria_recover|NORMAL| |aria_repair_threads|1| |aria_sort_buffer_size|268434432| |aria_stats_method|nulls_unequal| |aria_sync_log_dir|NEWFILE| |aria_used_for_temp_tables|ON| |auto_increment_increment|1| |auto_increment_offset|1| |autocommit|ON| |automatic_sp_privileges|ON| |back_log|150| |basedir|/usr| |big_tables|OFF| |binlog_annotate_row_events|OFF| |binlog_cache_size|32768| |binlog_checksum|NONE| |binlog_commit_wait_count|0| |binlog_commit_wait_usec|100000| |binlog_direct_non_transactional_updates|OFF| |binlog_format|ROW| |binlog_optimize_thread_scheduling|ON| |binlog_stmt_cache_size|32768| |bulk_insert_buffer_size|8388608| |character_set_client|utf8| |character_set_connection|utf8| |character_set_database|latin1| |character_set_filesystem|binary| |character_set_results|utf8| |character_set_server|latin1| |character_set_system|utf8| |character_sets_dir|/usr/share/mysql/charsets/| |collation_connection|utf8_general_ci| |collation_database|latin1_swedish_ci| |collation_server|latin1_swedish_ci| |completion_type|NO_CHAIN| |concurrent_insert|AUTO| |connect_timeout|10| |datadir|/var/lib/mysql/| |date_format|%Y-%m-%d| |datetime_format|%Y-%m-%d%H:%i:%s| |deadlock_search_depth_long|15| |deadlock_search_depth_short|4| |deadlock_timeout_long|50000000| |deadlock_timeout_short|10000| |debug_no_thread_alarm|OFF| |default_master_connection|| |default_regex_flags|| |default_storage_engine|InnoDB| |default_week_format|0| |delay_key_write|ON| |delayed_insert_limit|100| |delayed_insert_timeout|300| |delayed_queue_size|1000| |div_precision_increment|4| |error_count|0| |event_scheduler|OFF| |expensive_subquery_limit|100| |expire_logs_days|7| |external_user|| |extra_max_connections|1| |extra_port|0| |flush|OFF| |flush_time|0| |foreign_key_checks|ON| |ft_boolean_syntax|+-><()~*:""&|| |ft_max_word_len|84| |ft_min_word_len|4| |ft_query_expansion_limit|20| |ft_stopword_file|(built-in)| |general_log|OFF| |general_log_file|prod-mysql.log| |group_concat_max_len|1024| |gtid_binlog_pos|0-1-49317| |gtid_binlog_state|0-1-49317| |gtid_current_pos|0-1-49317| |gtid_domain_id|0| |gtid_ignore_duplicates|OFF| |gtid_seq_no|0| |gtid_slave_pos|| |gtid_strict_mode|OFF| |have_compress|YES| |have_crypt|YES| |have_dynamic_loading|YES| |have_geometry|YES| |have_openssl|YES| |have_profiling|YES| |have_query_cache|YES| |have_rtree_keys|YES| |have_ssl|DISABLED| |have_symlink|YES| |histogram_size|0| |histogram_type|SINGLE_PREC_HB| |host_cache_size|128| |hostname|prod-mysql| |identity|0| |ignore_builtin_innodb|OFF| |ignore_db_dirs|| |in_transaction|0| |init_connect|| |init_file|| |init_slave|| |innodb_adaptive_flushing|ON| |innodb_adaptive_flushing_lwm|10.000000| |innodb_adaptive_hash_index|ON| |innodb_adaptive_hash_index_partitions|1| |innodb_adaptive_max_sleep_delay|150000| |innodb_additional_mem_pool_size|8388608| |innodb_api_bk_commit_interval|5| |innodb_api_disable_rowlock|OFF| |innodb_api_enable_binlog|OFF| |innodb_api_enable_mdl|OFF| |innodb_api_trx_level|0| |innodb_autoextend_increment|64| |innodb_autoinc_lock_mode|2| |innodb_buffer_pool_dump_at_shutdown|OFF| |innodb_buffer_pool_dump_now|OFF| |innodb_buffer_pool_filename|ib_buffer_pool| |innodb_buffer_pool_instances|8| |innodb_buffer_pool_load_abort|OFF| |innodb_buffer_pool_load_at_startup|OFF| |innodb_buffer_pool_load_now|OFF| |innodb_buffer_pool_populate|OFF| |innodb_buffer_pool_size|10737418240| |innodb_change_buffer_max_size|25| |innodb_change_buffering|all| |innodb_checksum_algorithm|innodb| |innodb_checksums|ON| |innodb_cleaner_lsn_age_factor|high_checkpoint| |innodb_cmp_per_index_enabled|OFF| |innodb_commit_concurrency|0| |innodb_compression_failure_threshold_pct|5| |innodb_compression_level|6| |innodb_compression_pad_pct_max|50| |innodb_concurrency_tickets|5000| |innodb_corrupt_table_action|assert| |innodb_data_file_path|ibdata1:12M:autoextend| |innodb_data_home_dir|| |innodb_disable_sort_file_cache|OFF| |innodb_disallow_writes|OFF| |innodb_doublewrite|ON| |innodb_empty_free_list_algorithm|backoff| |innodb_fake_changes|OFF| |innodb_fast_shutdown|1| |innodb_file_format|Antelope| |innodb_file_format_check|ON| |innodb_file_format_max|Antelope| |innodb_file_per_table|ON| |innodb_flush_log_at_timeout|1| |innodb_flush_log_at_trx_commit|0| |innodb_flush_method|O_DIRECT| |innodb_flush_neighbors|0| |innodb_flushing_avg_loops|30| |innodb_force_load_corrupted|OFF| |innodb_force_recovery|0| |innodb_foreground_preflush|exponential_backoff| |innodb_ft_aux_table|| |innodb_ft_cache_size|8000000| |innodb_ft_enable_diag_print|OFF| |innodb_ft_enable_stopword|ON| |innodb_ft_max_token_size|84| |innodb_ft_min_token_size|3| |innodb_ft_num_word_optimize|2000| |innodb_ft_result_cache_limit|2000000000| |innodb_ft_server_stopword_table|| |innodb_ft_sort_pll_degree|2| |innodb_ft_total_cache_size|640000000| |innodb_ft_user_stopword_table|| |innodb_io_capacity|200| |innodb_io_capacity_max|2000| |innodb_kill_idle_transaction|0| |innodb_large_prefix|OFF| |innodb_lock_wait_timeout|50| |innodb_locking_fake_changes|ON| |innodb_locks_unsafe_for_binlog|OFF| |innodb_log_arch_dir|./| |innodb_log_arch_expire_sec|0| |innodb_log_archive|OFF| |innodb_log_block_size|512| |innodb_log_buffer_size|4194304| |innodb_log_checksum_algorithm|innodb| |innodb_log_compressed_pages|ON| |innodb_log_file_size|268435456| |innodb_log_files_in_group|2| |innodb_log_group_home_dir|./| |innodb_lru_scan_depth|1024| |innodb_max_bitmap_file_size|104857600| |innodb_max_changed_pages|1000000| |innodb_max_dirty_pages_pct|75.000000| |innodb_max_dirty_pages_pct_lwm|0.001000| |innodb_max_purge_lag|0| |innodb_max_purge_lag_delay|0| |innodb_mirrored_log_groups|1| |innodb_monitor_disable|| |innodb_monitor_enable|| |innodb_monitor_reset|| |innodb_monitor_reset_all|| |innodb_old_blocks_pct|37| |innodb_old_blocks_time|1000| |innodb_online_alter_log_max_size|134217728| |innodb_open_files|2048| |innodb_optimize_fulltext_only|OFF| |innodb_page_size|16384| |innodb_print_all_deadlocks|OFF| |innodb_purge_batch_size|300| |innodb_purge_threads|1| |innodb_random_read_ahead|OFF| |innodb_read_ahead_threshold|56| |innodb_read_io_threads|4| |innodb_read_only|OFF| |innodb_replication_delay|0| |innodb_rollback_on_timeout|OFF| |innodb_rollback_segments|128| |innodb_sched_priority_cleaner|19| |innodb_show_locks_held|10| |innodb_show_verbose_locks|0| |innodb_simulate_comp_failures|0| |innodb_sort_buffer_size|1048576| |innodb_spin_wait_delay|6| |innodb_stats_auto_recalc|ON| |innodb_stats_method|nulls_equal| |innodb_stats_modified_counter|0| |innodb_stats_on_metadata|OFF| |innodb_stats_persistent|ON| |innodb_stats_persistent_sample_pages|20| |innodb_stats_sample_pages|8| |innodb_stats_traditional|ON| |innodb_stats_transient_sample_pages|8| |innodb_status_output|OFF| |innodb_status_output_locks|OFF| |innodb_strict_mode|OFF| |innodb_support_xa|ON| |innodb_sync_array_size|1| |innodb_sync_spin_loops|30| |innodb_table_locks|ON| |innodb_thread_concurrency|4| |innodb_thread_sleep_delay|0| |innodb_track_changed_pages|OFF| |innodb_undo_directory|.| |innodb_undo_logs|128| |innodb_undo_tablespaces|0| |innodb_use_atomic_writes|OFF| |innodb_use_fallocate|OFF| |innodb_use_global_flush_log_at_trx_commit|ON| |innodb_use_native_aio|ON| |innodb_use_stacktrace|OFF| |innodb_use_sys_malloc|ON| |innodb_version|5.6.25-73.1| |innodb_write_io_threads|4| |insert_id|0| |interactive_timeout|28800| |join_buffer_size|131072| |join_buffer_space_limit|2097152| |join_cache_level|2| |keep_files_on_create|OFF| |key_buffer_size|33554432| |key_cache_age_threshold|300| |key_cache_block_size|1024| |key_cache_division_limit|100| |key_cache_file_hash_size|512| |key_cache_segments|0| |large_files_support|ON| |large_page_size|0| |large_pages|OFF| |last_gtid|| |last_insert_id|0| |lc_messages|en_US| |lc_messages_dir|| |lc_time_names|en_US| |license|GPL| |local_infile|ON| |lock_wait_timeout|31536000| |locked_in_memory|OFF| |low_priority_updates|OFF| |lower_case_file_system|OFF| |lower_case_table_names|0| |master_verify_checksum|OFF| |max_allowed_packet|16777216| |max_binlog_cache_size|18446744073709547520| |max_binlog_size|1073741824| |max_binlog_stmt_cache_size|18446744073709547520| |max_connect_errors|1000000| |max_connections|500| |max_delayed_threads|20| |max_digest_length|1024| |max_error_count|64| |max_heap_table_size|33554432| |max_insert_delayed_threads|20| |max_join_size|18446744073709551615| |max_length_for_sort_data|1024| |max_long_data_size|16777216| |max_prepared_stmt_count|16382| |max_relay_log_size|1073741824| |max_seeks_for_key|4294967295| |max_sort_length|1024| |max_sp_recursion_depth|0| |max_tmp_tables|32| |max_user_connections|0| |max_write_lock_count|4294967295| |metadata_locks_cache_size|1024| |metadata_locks_hash_instances|8| |min_examined_row_limit|0| |mrr_buffer_size|262144| |multi_range_count|256| |myisam_block_size|1024| |myisam_data_pointer_size|6| |myisam_max_sort_file_size|9223372036853727232| |myisam_mmap_size|18446744073709551615| |myisam_recover_options|BACKUP,FORCE| |myisam_repair_threads|1| |myisam_sort_buffer_size|134216704| |myisam_stats_method|nulls_unequal| |myisam_use_mmap|OFF| |net_buffer_length|16384| |net_read_timeout|30| |net_retry_count|10| |net_write_timeout|60| |old|OFF| |old_alter_table|OFF| |old_mode|| |old_passwords|OFF| |open_files_limit|65535| |optimizer_prune_level|1| |optimizer_search_depth|62| |optimizer_selectivity_sampling_limit|100| |optimizer_switch|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,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,extended_keys=on,exists_to_in=on| |optimizer_use_condition_selectivity|4| |performance_schema|OFF| |performance_schema_accounts_size|-1| |performance_schema_digests_size|-1| |performance_schema_events_stages_history_long_size|-1| |performance_schema_events_stages_history_size|-1| |performance_schema_events_statements_history_long_size|-1| |performance_schema_events_statements_history_size|-1| |performance_schema_events_waits_history_long_size|-1| |performance_schema_events_waits_history_size|-1| |performance_schema_hosts_size|-1| |performance_schema_max_cond_classes|80| |performance_schema_max_cond_instances|-1| |performance_schema_max_digest_length|1024| |performance_schema_max_file_classes|50| |performance_schema_max_file_handles|32768| |performance_schema_max_file_instances|-1| |performance_schema_max_mutex_classes|200| |performance_schema_max_mutex_instances|-1| |performance_schema_max_rwlock_classes|40| |performance_schema_max_rwlock_instances|-1| |performance_schema_max_socket_classes|10| |performance_schema_max_socket_instances|-1| |performance_schema_max_stage_classes|150| |performance_schema_max_statement_classes|180| |performance_schema_max_table_handles|-1| |performance_schema_max_table_instances|-1| |performance_schema_max_thread_classes|50| |performance_schema_max_thread_instances|-1| |performance_schema_session_connect_attrs_size|-1| |performance_schema_setup_actors_size|100| |performance_schema_setup_objects_size|100| |performance_schema_users_size|-1| |pid_file|/var/lib/mysql/mysql.pid| |plugin_dir|/usr/lib/mysql/plugin/| |plugin_maturity|unknown| |port|3306| |preload_buffer_size|32768| |profiling|OFF| |profiling_history_size|15| |progress_report_time|5| |protocol_version|10| |proxy_user|| |pseudo_slave_mode|OFF| |pseudo_thread_id|1253921| |query_alloc_block_size|8192| |query_cache_limit|1048576| |query_cache_min_res_unit|4096| |query_cache_size|33554432| |query_cache_strip_comments|OFF| |query_cache_type|OFF| |query_cache_wlock_invalidate|OFF| |query_prealloc_size|8192| |rand_seed1|0| |rand_seed2|0| |range_alloc_block_size|4096| |read_buffer_size|131072| |read_only|OFF| |read_rnd_buffer_size|262144| |relay_log|| |relay_log_index|| |relay_log_info_file|relay-log.info| |relay_log_purge|ON| |relay_log_recovery|OFF| |relay_log_space_limit|0| |replicate_annotate_row_events|OFF| |replicate_do_db|| |replicate_do_table|| |replicate_events_marked_for_skip|replicate| |replicate_ignore_db|| |replicate_ignore_table|| |replicate_wild_do_table|| |replicate_wild_ignore_table|| |report_host|| |report_password|| |report_port|3306| |report_user|| |rowid_merge_buff_size|8388608| |rpl_recovery_rank|0| |secure_auth|OFF| |secure_file_priv|| |server_id|1| |skip_external_locking|ON| |skip_name_resolve|ON| |skip_networking|OFF| |skip_replication|OFF| |skip_show_database|OFF| |slave_compressed_protocol|OFF| |slave_ddl_exec_mode|IDEMPOTENT| |slave_domain_parallel_threads|0| |slave_exec_mode|STRICT| |slave_load_tmpdir|/tmp| |slave_max_allowed_packet|1073741824| |slave_net_timeout|3600| |slave_parallel_max_queued|131072| |slave_parallel_threads|0| |slave_skip_errors|OFF| |slave_sql_verify_checksum|ON| |slave_transaction_retries|10| |slave_type_conversions|| |slow_launch_time|2| |slow_query_log|ON| |slow_query_log_file|/var/log/mysql/mysql-slow.log| |socket|/var/run/mysqld/mysqld.sock| |sort_buffer_size|2097152| |sql_auto_is_null|OFF| |sql_big_selects|ON| |sql_buffer_result|OFF| |sql_log_bin|ON| |sql_log_off|OFF| |sql_mode|| |sql_notes|ON| |sql_quote_show_create|ON| |sql_safe_updates|OFF| |sql_select_limit|18446744073709551615| |sql_slave_skip_counter|0| |sql_warnings|OFF| |storage_engine|InnoDB| |stored_program_cache|256| |sync_binlog|1| |sync_frm|ON| |sync_master_info|0| |sync_relay_log|0| |sync_relay_log_info|0| |system_time_zone|UTC| |table_definition_cache|1024| |table_open_cache|2048| |thread_cache_size|50| |thread_concurrency|10| |thread_handling|one-thread-per-connection| |thread_pool_idle_timeout|60| |thread_pool_max_threads|500| |thread_pool_oversubscribe|3| |thread_pool_size|4| |thread_pool_stall_limit|500| |thread_stack|294912| |time_format|%H:%i:%s| |time_zone|SYSTEM| |timed_mutexes|OFF| |timestamp|1444650143.484721| |tmp_table_size|33554432| |transaction_alloc_block_size|8192| |transaction_prealloc_size|4096| |tx_isolation|REPEATABLE-READ| |tx_read_only|OFF| |unique_checks|ON| |updatable_views_with_limit|YES| |use_stat_tables|PREFERABLY| |userstat|OFF| |version|10.0.21-MariaDB-1~trusty-wsrep-log| |version_comment|mariadb.orgbinarydistribution,wsrep_25.10.r4144| |version_compile_machine|x86_64| |version_compile_os|debian-linux-gnu| |version_malloc_library|bundledjemalloc| |wait_timeout|28800|

Elena Stepanova added a comment - 2015-10-17 22:11

bnprss, thank you for the info, it helped.

psergey,

I used the table definition as above, and the following simple INSERT to produce the data:

insert into rbs_order_doc_shipment (document_id, processingdate) values (1, null),(2,'2015-10-06 15:24:47'),(3,null),(4,'2015-10-06 15:27:38'),(5,'2015-10-06 19:14:52'),(6,'2015-10-06 19:14:52'),(7,'2015-10-06 19:14:52'),(8,'2015-10-07 06:04:30');

The problem shows up with set optimizer_use_condition_selectivity > 2.
The plan changes to 'Impossible where', and the result set is empty.

optimizer_use_condition_selectivity=2 - OK
MariaDB [test]> set optimizer_use_condition_selectivity=2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
+---------------------+
\| processingdate \|
+---------------------+
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
\| 2015-10-06 19:14:52 \|
+---------------------+
3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> explain select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
\| 1 \| SIMPLE \| rbs_order_doc_shipment \| ALL \| NULL \| NULL \| NULL \| NULL \| 8 \| Using where \|
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

optimizer_use_condition_selectivity=3 - FAIL
MariaDB [test]> set optimizer_use_condition_selectivity=3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
Empty set (0.00 sec)

MariaDB [test]> explain select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ;
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
\| 1 \| SIMPLE \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| Impossible WHERE noticed after reading const tables \|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

It does not matter whether the stat tables are populated or not.

Elena Stepanova added a comment - 2015-10-17 22:11 bnprss , thank you for the info, it helped. psergey , I used the table definition as above, and the following simple INSERT to produce the data: insert into rbs_order_doc_shipment (document_id, processingdate) values (1, null),(2,'2015-10-06 15:24:47'),(3,null),(4,'2015-10-06 15:27:38'),(5,'2015-10-06 19:14:52'),(6,'2015-10-06 19:14:52'),(7,'2015-10-06 19:14:52'),(8,'2015-10-07 06:04:30'); The problem shows up with set optimizer_use_condition_selectivity > 2. The plan changes to 'Impossible where', and the result set is empty. optimizer_use_condition_selectivity=2 - OK MariaDB [test]> set optimizer_use_condition_selectivity=2; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ; +---------------------+ | processingdate | +---------------------+ | 2015-10-06 19:14:52 | | 2015-10-06 19:14:52 | | 2015-10-06 19:14:52 | +---------------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> explain select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ; +------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | rbs_order_doc_shipment | ALL | NULL | NULL | NULL | NULL | 8 | Using where | +------+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) optimizer_use_condition_selectivity=3 - FAIL MariaDB [test]> set optimizer_use_condition_selectivity=3; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ; Empty set (0.00 sec) MariaDB [test]> explain select processingdate from rbs_order_doc_shipment where processingdate = '2015-10-06T19:14:52+0000' ; +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) It does not matter whether the stat tables are populated or not.

Ian Gilfillan added a comment - 2021-08-05 19:02

This appears to have been fixed at some point, the behaviour does not occur in 10.6.3.

Ian Gilfillan added a comment - 2021-08-05 19:02 This appears to have been fixed at some point, the behaviour does not occur in 10.6.3.

Alice Sherepa added a comment - 2022-11-30 10:37

the correct result on current 10.3 (4e9206736c403206915) -10.10

Alice Sherepa added a comment - 2022-11-30 10:37 the correct result on current 10.3 (4e9206736c403206915) -10.10

People

Assignee:: Sergei Petrunia

Reporter:: Benjamin Preiss (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2015-10-07 10:18

Updated:: 2022-12-01 10:59

Resolved:: 2022-11-30 10:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server