diff options
Diffstat (limited to 'mysql-test/main/natural_sort_key.result')
| -rw-r--r-- | mysql-test/main/natural_sort_key.result | 185 |
1 files changed, 127 insertions, 58 deletions
diff --git a/mysql-test/main/natural_sort_key.result b/mysql-test/main/natural_sort_key.result index 1a6cf1cc3ea..a99fa7a71b8 100644 --- a/mysql-test/main/natural_sort_key.result +++ b/mysql-test/main/natural_sort_key.result @@ -2,14 +2,6 @@ SET NAMES utf8mb4; SELECT NATURAL_SORT_KEY(NULL); NATURAL_SORT_KEY(NULL) NULL -SELECT '' c WHERE 0 UNION VALUES('a10'),('a9'),('a1000'), ('a0'),('b'),('b0') ORDER BY NATURAL_SORT_KEY(c); -c -a0 -a9 -a10 -a1000 -b -b0 SELECT NATURAL_SORT_KEY(repeat('a1',@@max_allowed_packet/2-1)); NATURAL_SORT_KEY(repeat('a1',@@max_allowed_packet/2-1)) NULL @@ -22,14 +14,10 @@ Warnings: Warning 1301 Result of natural_sort_key() was larger than max_allowed_packet (16777216) - truncated CREATE TABLE t1( c VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_bin, -k VARCHAR(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS (NATURAL_SORT_KEY(CONVERT(c USING utf8mb4))) INVISIBLE, -KEY(k,c)) ENGINE=InnoDB; +k VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS (NATURAL_SORT_KEY(CONVERT(c USING utf8mb4))) VIRTUAL INVISIBLE); INSERT INTO t1 values ('A1'),('a1'),('A100'),('a100'),('A2'),('ä2'),('a2'),('A99'), ('äb'),('B1'),('B100'),('B9'),('C'),('100'); -EXPLAIN SELECT c FROM t1 ORDER BY k,c; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL k 216 NULL 14 Using index #Natural sort order. SELECT c FROM t1 ORDER BY k,c; c @@ -69,55 +57,136 @@ SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `NATURAL_SORT_KEY(c)` varchar(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL + `NATURAL_SORT_KEY(c)` varchar(45) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2; -SELECT RPAD(val,28,' ') value , RPAD(NATURAL_SORT_KEY(val),35,' ') sortkey , LENGTH(NATURAL_SORT_KEY(val)) - LENGTH(val) encoding_overhead +CREATE TABLE t1(c VARCHAR(1), k VARCHAR(2) AS (NATURAL_SORT_KEY(c)) STORED); +ERROR HY000: Function or expression 'natural_sort_key()' cannot be used in the GENERATED ALWAYS AS clause of `k` +SELECT RPAD(val,28,' ') value , RPAD(NATURAL_SORT_KEY(val),36,' ') sortkey , LENGTH(NATURAL_SORT_KEY(val)) - LENGTH(val) encoding_overhead FROM ( -SELECT 0 val -UNION VALUES ('1'),('01'),('0001') +SELECT '0' val +UNION SELECT seq FROM seq_1_to_9 UNION SELECT CONCAT('1',repeat('0',seq)) FROM seq_1_to_27 ) AS numbers ORDER BY sortkey; value sortkey encoding_overhead -0 000 2 -1 010 2 -01 011 1 -0001 013 -1 -10 1100 2 -100 21000 2 -1000 310000 2 -10000 4100000 2 -100000 51000000 2 -1000000 610000000 2 -10000000 7100000000 2 -100000000 81000000000 2 -1000000000 9010000000000 3 -10000000000 91100000000000 3 -100000000000 921000000000000 3 -1000000000000 9310000000000000 3 -10000000000000 94100000000000000 3 -100000000000000 951000000000000000 3 -1000000000000000 9610000000000000000 3 -10000000000000000 97100000000000000000 3 -100000000000000000 981000000000000000000 3 -1000000000000000000 99010000000000000000000 4 -10000000000000000000 991100000000000000000000 4 -100000000000000000000 9921000000000000000000000 4 -1000000000000000000000 99310000000000000000000000 4 -10000000000000000000000 994100000000000000000000000 4 -100000000000000000000000 9951000000000000000000000000 4 -1000000000000000000000000 99610000000000000000000000000 4 -10000000000000000000000000 997100000000000000000000000000 4 -100000000000000000000000000 9981000000000000000000000000000 4 -1000000000000000000000000000 99901271000000000000000000000000000 8 -SELECT val -FROM -( -SELECT 0 val WHERE 0 -UNION VALUES ('1/'),('01'),('1a') -) AS strings ORDER BY NATURAL_SORT_KEY(val); -val -1/ -1a -01 +0 00 1 +1 01 1 +2 02 1 +3 03 1 +4 04 1 +5 05 1 +6 06 1 +7 07 1 +8 08 1 +9 09 1 +10 110 1 +100 2100 1 +1000 31000 1 +10000 410000 1 +100000 5100000 1 +1000000 61000000 1 +10000000 710000000 1 +100000000 8100000000 1 +1000000000 901000000000 2 +10000000000 9110000000000 2 +100000000000 92100000000000 2 +1000000000000 931000000000000 2 +10000000000000 9410000000000000 2 +100000000000000 95100000000000000 2 +1000000000000000 961000000000000000 2 +10000000000000000 9710000000000000000 2 +100000000000000000 98100000000000000000 2 +1000000000000000000 9901000000000000000000 3 +10000000000000000000 99110000000000000000000 3 +100000000000000000000 992100000000000000000000 3 +1000000000000000000000 9931000000000000000000000 3 +10000000000000000000000 99410000000000000000000000 3 +100000000000000000000000 995100000000000000000000000 3 +1000000000000000000000000 9961000000000000000000000000 3 +10000000000000000000000000 99710000000000000000000000000 3 +100000000000000000000000000 998100000000000000000000000000 3 +1000000000000000000000000000 99901271000000000000000000000000000 7 +SELECT natural_sort_key('1') = natural_sort_key('0001'); +natural_sort_key('1') = natural_sort_key('0001') +1 +SELECT natural_sort_key('1.1') = natural_sort_key('1.00001'); +natural_sort_key('1.1') = natural_sort_key('1.00001') +1 +SELECT RPAD(val,20,' ') value, NATURAL_SORT_KEY(val) FROM +(SELECT '' val WHERE 0 UNION VALUES +('fred'), +('pic2'), +('pic100a'), +('pic120'), +('pic121'), +('jane'), +('tom'), +('pic02a'), +('pic3'), +('pic4'), +('1-20'), +('pic100'), +('pic02000'), +('10-20'), +('1-02'), +('1-2'), +('pic01'), +('pic02'), +('pic 6'), +('pic 7'), +('pic 5'), +('pic05'), +('pic 5 '), +('pic 5 something'), +('pic 4 else'), +('2000-1-10'), +('1999-12-25'), +('1999-3-3'), +('2000-3-23'), +('2000-1-2'), +('100.200.300.400'), +('100.50.60.70'), +('100.8.9.0'), +('a1b1'), +('a01b2'), +('a1b2'), +('a01b3') +)AS data ORDER BY 2,1; +value NATURAL_SORT_KEY(val) +1-02 01-02 +1-2 01-02 +1-20 01-120 +10-20 110-120 +100.8.9.0 2100.08.09.00 +100.50.60.70 2100.150.160.170 +100.200.300.400 2100.2200.2300.2400 +1999-3-3 31999-03-03 +1999-12-25 31999-112-125 +2000-1-2 32000-01-02 +2000-1-10 32000-01-110 +2000-3-23 32000-03-123 +a1b1 a01b01 +a01b2 a01b02 +a1b2 a01b02 +a01b3 a01b03 +fred fred +jane jane +pic 7 pic 07 +pic 4 else pic 04 else +pic 5 pic 05 +pic 5 something pic 05 something +pic 6 pic 06 +pic01 pic01 +pic02 pic02 +pic2 pic02 +pic02a pic02a +pic3 pic03 +pic4 pic04 +pic05 pic05 +pic100 pic2100 +pic100a pic2100a +pic120 pic2120 +pic121 pic2121 +pic02000 pic32000 +tom tom |