summaryrefslogtreecommitdiff
path: root/mysql-test/main/natural_sort_key.result
diff options
context:
space:
mode:
authorVladislav Vaintroub <wlad@mariadb.com>2021-09-01 17:44:24 +0200
committerVladislav Vaintroub <wlad@mariadb.com>2021-10-14 12:13:05 +0200
commit6c5c1fd55ab2b771c78fb241c1cf749860a0d5cf (patch)
tree05961fd2d17109b5b1c576fd8c379865801112f5 /mysql-test/main/natural_sort_key.result
parent167d2509249f9e501090ac4a5e985fe014c40932 (diff)
downloadmariadb-git-6c5c1fd55ab2b771c78fb241c1cf749860a0d5cf.tar.gz
MDEV-4742 - remove leading zero handling, and cleanups.
Leading zeros added a single byte overhead per numeric string, even when they were. Sorting leading zeros offers only for little value (except determinism in sort). I decided to drop it for now, we can be like ICU, which drops leading zeros, in numeric sorting, even with IDENTICAL collation strength. Also, disabled virtual stored columns (thus also indexes), on Serg's request Hopefully it is temporarily, and will be reenabled soon, when everyone is as happy with key generation algorithm as I am.
Diffstat (limited to 'mysql-test/main/natural_sort_key.result')
-rw-r--r--mysql-test/main/natural_sort_key.result185
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
View Lorry Controller Status