Bug #5411 SQL Error 1030 / Error 124 during execution of sql statement
Submitted: 4 Sep 2004 18:01 Modified: 26 Nov 2004 7:25
Reporter: Helmut Leininger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.1-alpha snapshot OS:Windows (Windows XP)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[4 Sep 2004 18:01] Helmut Leininger 
Description:
I get an SQL Error 1030 with error 124 (MySQL error:  124 = Wrong index
given to function) during the execution of the following SQL statement:

select t.Datum, t.kmStand,  t.kmStand - max(h.kmStand) as km, t.Betrag,
round(t.Menge,1) as Liter,
round(t.Menge * 100 / (t.kmStand - max(h.kmStand)), 1) as Verbrauch,
round(t.Betrag * 100 / (t.kmStand - max(h.kmStand)),2) as 'ct/km',
round(t.Betrag / t.Menge, 3) as 'EUR/Liter',
t.Kommentar from Treibstoff as t, Treibstoff as h
where h.kmStand < t.kmStand
group by 2
order by t.Datum, t.kmStand;

The table is the following:

+-----------+------------------+------+-----+------------+-------+
| Field     | Type             | Null | Key | Default    | Extra |
+-----------+------------------+------+-----+------------+-------+
| Datum     | date             |      |     | 0000-00-00 |       |
| kmStand   | int(10) unsigned |      | PRI | 0          |       |
| Menge     | float            |      |     | 0          |       |
| Betrag    | decimal(9,2)     |      |     | 0.00       |       |
| Kommentar | varchar(150)     | YES  |     | NULL       |       |
+-----------+------------------+------+-----+------------+-------+

Additional Info:
If I change the comparison in the where clause, the SQL errors disappear. But, of course, the query delivers a (logically) wrong result.

Mysql is 5.0.1alpha-snapshot on Windows XP.

I do not remember this problem in 5.0.0alpha

Regards 

How to repeat:
Execute above query against the table the table.

A test table may be created by loading the following data:

2001-06-26|740|51.4|39.20|
2001-07-02|1527|52.8|42.20|
2001-07-12|2188|42.2|34.20|
2001-07-20|2943|52.3|38.50|
2001-08-04|3705|52.6|37.80|
2001-08-27|4409|54|39.20|
2001-09-17|4913|39.5|27.60|
2001-09-24|5611|44|32.00|
2001-10-08|6361|51.5|35.60|
2001-10-29|7105|52|38.20|
2001-11-10|7698|40.1|29.10|
2001-11-19|8492|50.6|35.60|
2001-12-03|9291|51.3|36.00|
2001-12-17|10030|46.3|32.70|
2001-12-26|10808|48.8|34.20|
2002-01-21|11578|52.1|36.00|
2002-02-11|12356|50.5|33.70|
2002-03-12|13051|53.1|36.50|
2002-03-20|13801|51.5|36.00|
2002-04-15|14454|49.3|35.50|
2002-05-02|15204|54.3|37.60|
2002-05-18|15861|47.8|34.00|
2002-05-21|16726|52|38.00|
2002-06-01|17257|31.9|22.60|
2002-06-10|17990|54.4|38.00|
2002-07-02|18634|50.3|35.00|
2002-07-11|19409|51.3|36.00|
2002-07-22|20220|53.4|39.80|
2002-08-11|20865|46.7|32.00|
2002-08-20|21550|43.8|30.00|
2002-08-29|22363|53.6|38.00|
2002-09-23|23000|52.3|37.00|
2002-10-10|23623|46.1|35.20|
2002-10-28|24362|51.7|36.00|
2002-11-18|25114|52.3|35.00|
2002-12-13|25869|51.8|36.00|
2003-01-20|26453|45.2|34.00|
2003-01-27|27128|44.8|33.00|
2003-02-10|27944|53.6|40.00|
2003-02-17|28737|51.6|39.00|
2003-02-25|29501|52.6|41.00|
2003-03-11|30314|52|41.00|
2003-03-29|30930|42.5|31.80|
2003-04-22|31786|52.3|36.00|
2001-06-10|3|0|0.00|
2003-05-12|32508|50.8|36.00|
2003-05-15|33276|49.35|34.50|
2003-06-14|33988|52.25|36.00|
2003-06-26|34664|47.2|33.00|
2003-07-03|35389|45.9|35.00|\N
2003-07-13|36093|50.8|36.00|\N
2003-07-31|36853|47.9|35.00|\N
2003-08-13|37517|44.4|32.60|\N
2003-08-21|38346|52.9|37.00|\N
2003-10-28|41901|43.8|30.00|\N
2003-10-21|41197|42.1|29.00|\N
2003-10-03|40573|50.8|34.00|\N
2003-09-22|39833|47.9|31.50|\N
2003-09-12|39087|51.6|38.00|\N
2003-11-10|42581|41.26|28.00|\N
2003-11-20|43399|54.24|39.00|\N
2003-12-10|44275|53.8|36.00|\N
2003-12-18|44997|48.5|33.20|\N
2004-01-06|45750|50.8|35.00|\N
2004-01-23|46464|53.2|38.00|\N
2004-02-10|47259|52.25|37.00|\N
2004-03-11|47967|53.6|39.00|\N
2004-04-02|48572|43.1|34.00|\N
[6 Sep 2004 13:15] MySQL Verification Team 
Verified on the latest BK 5.0 tree.
MySQL server crashes with the above SELECT.

/var/MYSQL/vita-mysql-5.0/libexec/mysqld: ready for connections.
Version: '5.0.2-alpha-debug-log'  socket: '/var/MYSQL/vita-mysql-5.0/var/mysql.sock'  port: 3503
[New Thread 163851 (LWP 7343)]
mysqld: ha_myisam.cc:1113: int ha_myisam::index_first(byte *): Assertion `inited==INDEX' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 163851 (LWP 7343)]
0x40254a21 in kill () from /lib/libc.so.6
(gdb) Quit
(gdb) bt
#0  0x40254a21 in kill () from /lib/libc.so.6
#1  0x40166051 in pthread_kill () from /lib/libpthread.so.0
#2  0x4016642b in raise () from /lib/libpthread.so.0
#3  0x402547b4 in raise () from /lib/libc.so.6
#4  0x40255c7d in abort () from /lib/libc.so.6
#5  0x4024ddef in __assert_fail () from /lib/libc.so.6
#6  0x081ee2f0 in ha_myisam::index_first (this=0x87a18d8, buf=0x87a1998 "?\202?\017?\005") at ha_myisam.cc:1113
#7  0x081ea47a in handler::read_range_first (this=0x87a18d8, start_key=0x0, end_key=0xbe5feff4,
    eq_range_arg=false, sorted=108) at handler.cc:1408
#8  0x081e0d66 in QUICK_RANGE_SELECT::get_next (this=0x879fb90) at opt_range.cc:5653
#9  0x081e4c9d in rr_quick (info=0x87a3f64) at records.cc:156
#10 0x081a4687 in join_init_read_record (tab=0x87a3f2c) at sql_select.cc:8231
#11 0x081a45a0 in join_init_quick_read_record (tab=0x87a3f2c) at sql_select.cc:8210
#12 0x081a368c in sub_select (join=0x87a2ad8, join_tab=0x87a3f2c, end_of_records=false) at sql_select.cc:7689
#13 0x081a37b2 in sub_select (join=0x87a2ad8, join_tab=0x87a3dd8, end_of_records=false) at sql_select.cc:7771
#14 0x081a3230 in do_select (join=0x87a2ad8, fields=0x0, table=0x879e6e8, procedure=0x0) at sql_select.cc:7469
#15 0x08195875 in JOIN::exec (this=0x87a2ad8) at sql_select.cc:1182
#16 0x08197604 in mysql_select (thd=0x878b4f8, rref_pointer_array=0x878b7e8, tables=0x879b6e0, wild_num=0,
    fields=@0x878b734, conds=0x879ba58, og_num=3, order=0x879bc08, group=0x879bb58, having=0x0,
    proc_param=0x0, select_options=8669696, result=0x879bcf0, unit=0x878b534, select_lex=0x878b6c8)
    at sql_select.cc:1968
#17 0x081932f4 in handle_select (thd=0x878b4f8, lex=0x878b528, result=0x879bcf0) at sql_select.cc:220
#18 0x0816b5aa in mysql_execute_command (thd=0x878b4f8) at sql_parse.cc:2040
#19 0x08171149 in mysql_parse (thd=0x878b4f8,
    inBuf=0x879a3b8 "select t.Datum, t.kmStand,  t.kmStand - max(h.kmStand) as km, t.Betrag,\nround(t.Menge,1) as Liter,\nround(t.Menge * 100 / (t.kmStand - max(h.kmStand)), 1) as Verbrauch,\nround(t.Betrag * 100 / (t.kmStan"..., length=396) at sql_parse.cc:4475
#20 0x0816a362 in dispatch_command (command=COM_QUERY, thd=0x878b4f8,
    packet=0x8796359 "select t.Datum, t.kmStand,  t.kmStand - max(h.kmStand) as km, t.Betrag,\nround(t.Menge,1) as Liter,\nround(t.Menge * 100 / (t.kmStand - max(h.kmStand)), 1) as Verbrauch,\nround(t.Betrag * 100 / (t.kmStan"..., packet_length=397) at sql_parse.cc:1515
#21 0x08169c6c in do_command (thd=0x878b4f8) at sql_parse.cc:1297
#22 0x081691dc in handle_one_connection (arg=0x878b4f8) at sql_parse.cc:1033
#23 0x4016314b in pthread_start_thread () from /lib/libpthread.so.0
#24 0x401631df in pthread_start_thread_event () from /lib/libpthread.so.0
#25 0x402df50a in clone () from /lib/libc.so.6
[26 Nov 2004 7:25] Ramil Kalimullin 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Hi Helmut!

Just tried to reproduce the bug using the latest 5.0 tree.
No luck. Probably it was fixed somehow.
Thanks for the bug report.