2003-10-28

problem: strange sorting behavior with character-set TIS620
platform: WinXP/Win2000, Apache2, Php 4.3.3, MySQL 4.0.15 
testing tool: phpMyAdmin 2.5.2-pl1

tis620 ASC sorting looks STRANGE like this
	"test100"
 	"test 50"
 	"test 70"
 
latin1 ASC sorting looks like this
 	"test 50"
 	"test 70"
	"test100"
 	
 	
tis620 DESC sorting lookis STRANGE like this:
 	"test 70"
 	"test 50"
	"test100"
	
latin1 DESC sorting looks like this
	"test100"
 	"test 70"
 	"test 50"
	
it does not recognize any spaces within string when using default-character-set=TIS620
note:	this strange sorting behavior exists from MySQL 3.x


2003-10-29

problem: crash on select distinct query with character-set TIS620
platform: WinXP/Win2000, Apache2, Php 4.3.3, MySQL 4.0.15-max-nt 
testing tool: phpMyAdmin 2.5.2-pl1

note: only crash with default-character-set=TIS620 with latin1 not crash ????

1. create table+data in test database with "test-data.sql"

2. use "test-nocrash-query.sql" to see result 28 rows

SELECT DISTINCT 
CONCAT(IF( LOCATE( 'year=\"', dyninfo ) = 1, SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1),IF( LOCATE( '\nyear=\"', dyninfo ), SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) 
AS year 
FROM nuke_nap_entity WHERE class="carframe" HAVING year != '' ORDER BY year
	
3. use "test-crash-query.sql" now it crashed, mysql service stops then connection lost

SELECT DISTINCT 
(IF( LOCATE( 'year=\"', dyninfo ) = 1, SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), IF( LOCATE( '\nyear=\"', dyninfo ), SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) 
AS year 
FROM nuke_nap_entity WHERE class="carframe" HAVING year != '' ORDER BY year

4. my decriptions
*different between crash and nocrash is
	crash: 	(IF (....)) AS year
	nocrash: CONCAT(IF(....)," ") AS year
 but I don't known the reason.
 
*test with MySQL 3.x not crash all.
*when change to extract other dyninfo's subfield it's not crash
example dyninfo  'type':
	SELECT DISTINCT 
	(IF( LOCATE( 'type=\"', dyninfo ) = 1, SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), IF( LOCATE( '\ntype=\"', dyninfo ), SUBSTRING( dyninfo, LOCATE( '\ntype=\"', dyninfo ) + 7, LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\ntype=\"', dyninfo ) +7 )) - 1), '' ))) 
	AS type 
	FROM nuke_nap_entity WHERE class="carframe" HAVING type != '' ORDER BY type

example dyninfo 'model':
	SELECT DISTINCT 
	(IF( LOCATE( 'model=\"', dyninfo ) = 1, SUBSTRING( dyninfo, 7+1, LOCATE('\"\r',dyninfo) - 7 -1), IF( LOCATE( '\nmodel=\"', dyninfo ), SUBSTRING( dyninfo, LOCATE( '\nmodel=\"', dyninfo ) + 8, LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nmodel=\"', dyninfo ) +8 )) - 1), '' ))) 
	AS model 
	FROM nuke_nap_entity WHERE class="carframe" HAVING model != '' ORDER BY model

*when change AS year to AS dyn_year, it also crash. Wording 'year' is not the problem.
	SELECT DISTINCT 
	(IF( LOCATE( 'year=\"', dyninfo ) = 1, SUBSTRING( dyninfo, 6+1, LOCATE('\"\r',dyninfo) - 6 -1), IF( LOCATE( '\nyear=\"', dyninfo ), SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) + 7, LOCATE( '\"\r', SUBSTRING( dyninfo, LOCATE( '\nyear=\"', dyninfo ) +7 )) - 1), '' ))) 
	AS dyn_year 
	FROM nuke_nap_entity WHERE class="carframe" HAVING dyn_year != '' ORDER BY dyn_year

*when not use DISTINCT, it's not crash


sathit from thailand
jsat66@hotmail.com


