>Description: This is a simple bug with dates and 'between' operator. Full data is supported below, but for the start I show the very essence of the problem. I use the latest stable version of MySQL - 3.23.52.
SELECT weeks.ix FROM team, weeks WHERE
team.ix=6 AND
weeks.start > team.ctime AND
weeks.start < DATE_ADD(team.ctime, INTERVAL 7 DAY);
returns 1 row;
SELECT weeks.ix FROM team, weeks WHERE
team.ix=6 AND
(weeks.start BETWEEN team.ctime AND DATE_ADD(team.ctime, INTERVAL 7 DAY));
... is supposed to do the same as the first command - but it does return
no rows.
Interestingly, replacing team.ctime with NOW() removes the problem:
SELECT weeks.ix FROM weeks WHERE
(weeks.start BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY));
... it returns one row, as it is supposed to. So, the problems seems to be
related somehow to selected date columns used together with BETWEEN
operator.
>How-To-Repeat:
This is full log of operations, as they were requested in the manual:
Logging to file 'bugreport'
mysql> use sok;
Database changed
mysql> show variables;
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
| |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50
|
| |
| basedir | /usr/local/mysql/
|
| |
| binlog_cache_size | 32768
|
| |
| character_set | latin2
|
| |
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7
|usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250
|croat cp1257 latin5 |
| concurrent_insert | ON
|
| |
| connect_timeout | 5
|
| |
| datadir | /usr/local/mysql/var/
|
| |
| delay_key_write | ON
|
| |
| delayed_insert_limit | 100
|
| |
| delayed_insert_timeout | 300
|
| |
| delayed_queue_size | 1000
|
| |
| flush | OFF
|
| |
| flush_time | 0
|
| |
| have_bdb | NO
|
| |
| have_gemini | NO
|
| |
| have_innodb | NO
|
| |
| have_isam | YES
|
| |
| have_raid | NO
|
| |
| have_openssl | NO
|
| |
| init_file |
|
| |
| interactive_timeout | 28800
|
| |
| join_buffer_size | 131072
|
| |
| key_buffer_size | 12288
|
| |
| language | /usr/local/mysql/share/mysql/english/
|
| |
| large_files_support | ON
|
| |
| locked_in_memory | OFF
|
| |
| log | OFF
|
| |
| log_update | OFF
|
| |
| log_bin | OFF
|
| |
| log_slave_updates | OFF
|
| |
| log_long_queries | OFF
|
| |
| long_query_time | 10
|
| |
| low_priority_updates | OFF
|
| |
| lower_case_table_names | 0
|
| |
| max_allowed_packet | 1047552
|
| |
| max_binlog_cache_size | 4294967295
|
| |
| max_binlog_size | 1073741824
|
| |
| max_connections | 100
|
| |
| max_connect_errors | 10
|
| |
| max_delayed_threads | 20
|
| |
| max_heap_table_size | 16777216
|
| |
| max_join_size | 4294967295
|
| |
| max_sort_length | 1024
|
| |
| max_user_connections | 0
|
| |
| max_tmp_tables | 32
|
| |
| max_write_lock_count | 4294967295
|
| |
| myisam_max_extra_sort_file_size | 256
|
| |
| myisam_max_sort_file_size | 2047
|
| |
| myisam_recover_options | 0
|
| |
| myisam_sort_buffer_size | 8388608
|
| |
| net_buffer_length | 1024
|
| |
| net_read_timeout | 30
|
| |
| net_retry_count | 10
|
| |
| net_write_timeout | 60
|
| |
| open_files_limit | 0
|
| |
| pid_file | /usr/local/mysql/var/konsument.pid
|
| |
| port | 0
|
| |
| protocol_version | 10
|
| |
| record_buffer | 131072
|
| |
| record_rnd_buffer | 131072
|
| |
| query_buffer_size | 0
|
| |
| safe_show_database | OFF
|
| |
| server_id | 1
|
| |
| slave_net_timeout | 3600
|
| |
| skip_locking | ON
|
| |
| skip_networking | ON
|
| |
| skip_show_database | OFF
|
| |
| slow_launch_time | 2
|
| |
| socket | /tmp/mysql.sock
|
| |
| sort_buffer | 65528
|
| |
| sql_mode | 0
|
| |
| table_cache | 4
|
| |
| table_type | MYISAM
|
| |
| thread_cache_size | 0
|
| |
| thread_stack | 65536
|
| |
| transaction_isolation | READ-COMMITTED
|
| |
| timezone | CEST
|
| |
| tmp_table_size | 33554432
|
| |
| tmpdir | /tmp/
|
| |
| version | 3.23.52
|
| |
| wait_timeout | 28800
|
| |
+---------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
82 rows in set (0.00 sec)
mysql> show columns from team\G
*************************** 1. row ***************************
Field: ix
Type: smallint(5) unsigned
Null:
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: nazwa
Type: tinyblob
Null:
Key:
Default:
Extra:
*************************** 3. row ***************************
Field: opis
Type: blob
Null:
Key:
Default:
Extra:
*************************** 4. row ***************************
Field: szef
Type: smallint(5) unsigned
Null:
Key: UNI
Default: 0
Extra:
*************************** 5. row ***************************
Field: ctime
Type: timestamp(14)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: start_pts
Type: smallint(5) unsigned
Null:
Key:
Default: 0
Extra:
*************************** 7. row ***************************
Field: current_pts
Type: smallint(5) unsigned
Null:
Key:
Default: 0
Extra:
*************************** 8. row ***************************
Field: flags
Type: set('A','B','C','D','closed','global')
Null:
Key:
Default:
Extra:
*************************** 9. row ***************************
Field: visual
Type: enum('boss','team','all')
Null:
Key:
Default: boss
Extra:
9 rows in set (0.00 sec)
mysql> show columns from weeks\G
*************************** 1. row ***************************
Field: ix
Type: tinyint(3) unsigned
Null:
Key: PRI
Default: 0
Extra:
*************************** 2. row ***************************
Field: start
Type: datetime
Null:
Key: UNI
Default: 0000-00-00 00:00:00
Extra:
2 rows in set (0.00 sec)
mysql> select weeks.ix from team, weeks where team.ix=6 and weeks.start > team.ctime
and weeks.start < date_add(team.ctime, interval 7 day);
+----+
| ix |
+----+
| 43 |
+----+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select weeks.ix from team, weeks where team.ix=6 and weeks.start between
team.ctime and date_add(team.ctime, interval 7 day);
Empty set (0.00 sec)
mysql> show status;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 145 |
| Bytes_sent | 45 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Connections | 34 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 16 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 9 |
| Key_read_requests | 1 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 4 |
| Open_files | 7 |
| Open_streams | 0 |
| Opened_tables | 0 |
| Questions | 244 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 2 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 33 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 4554 |
+--------------------------+-------+
112 rows in set (0.00 sec)
mysql> explain select weeks.ix from team, weeks where team.ix=6 and weeks.start >
team.ctime and weeks.start < date_add(team.ctime, interval 7 day);
+-------+-------+---------------+---------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+-------+------+------------+
| team | const | PRIMARY | PRIMARY | 2 | const | 1 | |
| weeks | range | start | start | 8 | NULL | 15 | where used |
+-------+-------+---------------+---------+---------+-------+------+------------+
2 rows in set (0.00 sec)
mysql> explain select weeks.ix from team, weeks where team.ix=6 and weeks.start
between team.ctime and date_add(team.ctime, interval 7 day);
+-------+-------+---------------+---------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+-------+------+------------+
| team | const | PRIMARY | PRIMARY | 2 | const | 1 | |
| weeks | ALL | start | NULL | NULL | NULL | 15 | where used |
+-------+-------+---------------+---------+---------+-------+------+------------+
2 rows in set (0.00 sec)
mysql> notee
Logging to file 'bugreport2'
mysql> select * from team where ix=6\G
*************************** 1. row ***************************
ix: 6
nazwa: Wykłady
opis: Nie jest to prawdziwy zespół. Służy do odnototywania faktu obecności na
walnych zgromadzeniach.
szef: 1
ctime: 20021021104000
start_pts: 1500
current_pts: 0
flags: A,B,C,D
visual: boss
1 row in set (0.00 sec)
mysql> select * from weeks;
+----+---------------------+
| ix | start |
+----+---------------------+
| 40 | 2002-10-07 00:00:00 |
| 41 | 2002-10-14 00:00:00 |
| 42 | 2002-10-21 00:00:00 |
| 43 | 2002-10-28 00:00:00 |
| 44 | 2002-11-04 00:00:00 |
| 45 | 2002-11-11 00:00:00 |
| 46 | 2002-11-18 00:00:00 |
| 47 | 2002-11-25 00:00:00 |
| 48 | 2002-12-02 00:00:00 |
| 49 | 2002-12-09 00:00:00 |
| 50 | 2002-12-16 00:00:00 |
| 51 | 2002-12-23 00:00:00 |
| 52 | 2002-12-30 00:00:00 |
| 53 | 2003-01-06 00:00:00 |
| 54 | 2003-01-13 00:00:00 |
+----+---------------------+
15 rows in set (0.01 sec)
mysql> notee
>Submitter-Id: <submitter ID>
>Originator: Jakub Szewczyk
>MySQL support: none
>Synopsis: BETWEEN with dates doesn't return rows, while < and > operators do work
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.52 (Source distribution)
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux konsument 2.2.22 #4 Tue Oct 1 03:32:18 CEST 2002 i586 unknown
Architecture: i586
Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gcc
GCC: Reading specs from /usr/local/lib/gcc-lib/i586-pc-linux-gnu/3.2/specs
Configured with: ../gcc-3.2/configure --enable-languages=c,c++ --enable-shared
Thread model: posix
gcc version 3.2
Compilation info: CC='gcc' CFLAGS='-O3 -mcpu=pentium -fomit-frame-pointer' CXX='g++'
CXXFLAGS='-O3 -mcpu=pentium -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Sep 29 03:28 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Apr 28 11:57 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Apr 28 11:58 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 28 11:58 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql --without-debug
--without-readline --with-extra-charset=latin2 'CFLAGS=-O3 -mcpu=pentium
-fomit-frame-pointer' 'CXXFLAGS=-O3 -mcpu=pentium -felide-constructors -fno-exceptions
-fno-rtti'
With regards,
Jakub Szewczyk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
