admin管理员组

文章数量:1130349

MySQL, can anybody explain what I'm doing wrong here with:

select ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > '0.5', r > '0.2', r > '0.1',
CASE
      WHEN r > '0.5' THEN '0.5'
      WHEN r > '0.2' THEN '0.2'
      WHEN r > '0.1'THEN '0.1'
      ELSE '0'
END as 'actual'
from (select id, rand() r from users) t;

results in:

1   0.3050642603124159  1   1   1   0.5
2   0.03457492965699633 0   1   0   0.5
3   0.8674438212695008  0   1   1   0.5
4   0.077966298980607   1   1   1   0.2
5   0.3282627941372458  0   0   1   0.5
6   0.2562537558900693  0   1   1   0.2
7   0.017397192323018975    0   1   0   0.2
8   0.5937670381681687  0   1   1   0.5
9   0.1845749962931266  0   1   1   0.2
10  0.8352527859017744  0   1   1   0.2
11  0.9591796351216544  1   0   1   0.2
12  0.05485615977538392 1   1   1   0.2
13  0.8754085310505783  1   1   1   0.5
14  0.7607530902705649  0   1   1   0.2
15  0.07947531070511352 0   1   0   0.2
16  0.7614915275587621  0   1   1   0.2
17  0.4879049840270588  0   1   1   0.5
18  0.5419163830037381  0   1   1   0.2
19  0.5332705336932749  0   1   1   0.5
20  0.9298071674348853  0   1   1   0.2
21  0.29434789651478444 1   1   1   0.2
22  0.10249882294097806 1   1   1   0.5
23  0.7074116633342687  1   1   1   0.5
24  0.5250272765057415  0   1   1   0.5
25  0.5142364888584581  1   1   1   0.5
26  0.9172307084474999  1   1   1   0.2

The tests return value is not what I expect at all. If I take the first row for instance, I would have expected:

1   0.3050642603124159  1   1   0   0.2

instead of:

1   0.3050642603124159  1   1   1   0.5

I'm totally lost. Thanks for any help, this is killing me.

@ErgestBasha Here it is:

CREATE TABLE `users` (  
`id` char(36) NOT NULL,  
`user_name` varchar(60) DEFAULT NULL,  
`user_hash` varchar(255) DEFAULT NULL,  
`system_generated_password` tinyint(1) DEFAULT NULL,  
`pwd_last_changed` datetime DEFAULT NULL,  
`authenticate_id` varchar(100) DEFAULT NULL,  
`sugar_login` tinyint(1) DEFAULT '1',  
`first_name` varchar(30) DEFAULT NULL,  
`last_name` varchar(30) DEFAULT NULL,  
`is_admin` tinyint(1) DEFAULT '0',  
`external_auth_only` tinyint(1) DEFAULT '0',  
`receive_notifications` tinyint(1) DEFAULT '1',  
`description` text,  `date_entered` datetime DEFAULT NULL,  
`date_modified` datetime DEFAULT NULL,  
`modified_user_id` char(36) DEFAULT NULL,  
`created_by` char(36) DEFAULT NULL,  
`title` varchar(50) DEFAULT NULL,  
`photo` varchar(255) DEFAULT NULL,  
`department` varchar(50) DEFAULT NULL,  
`phone_home` varchar(50) DEFAULT NULL,  
`phone_mobile` varchar(50) DEFAULT NULL,  
`phone_work` varchar(50) DEFAULT NULL,  
`phone_other` varchar(50) DEFAULT NULL,  
`phone_fax` varchar(50) DEFAULT NULL,  
`status` varchar(100) DEFAULT NULL,  
`address_street` varchar(150) DEFAULT NULL,  
`address_city` varchar(100) DEFAULT NULL,  
`address_state` varchar(100) DEFAULT NULL,  
`address_country` varchar(100) DEFAULT NULL,  
`address_postalcode` varchar(20) DEFAULT NULL,  
`deleted` tinyint(1) DEFAULT NULL,  
`portal_only` tinyint(1) DEFAULT '0',  
`show_on_employees` tinyint(1) DEFAULT '1',  
`employee_status` varchar(100) DEFAULT NULL,  
`messenger_id` varchar(100) DEFAULT NULL,  
`messenger_type` varchar(100) DEFAULT NULL,  
`reports_to_id` char(36) DEFAULT NULL,  
`is_group` tinyint(1) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_user_name` (`user_name`,`is_group`,`status`,`last_name`,`first_name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

MySQL, can anybody explain what I'm doing wrong here with:

select ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > '0.5', r > '0.2', r > '0.1',
CASE
      WHEN r > '0.5' THEN '0.5'
      WHEN r > '0.2' THEN '0.2'
      WHEN r > '0.1'THEN '0.1'
      ELSE '0'
END as 'actual'
from (select id, rand() r from users) t;

results in:

1   0.3050642603124159  1   1   1   0.5
2   0.03457492965699633 0   1   0   0.5
3   0.8674438212695008  0   1   1   0.5
4   0.077966298980607   1   1   1   0.2
5   0.3282627941372458  0   0   1   0.5
6   0.2562537558900693  0   1   1   0.2
7   0.017397192323018975    0   1   0   0.2
8   0.5937670381681687  0   1   1   0.5
9   0.1845749962931266  0   1   1   0.2
10  0.8352527859017744  0   1   1   0.2
11  0.9591796351216544  1   0   1   0.2
12  0.05485615977538392 1   1   1   0.2
13  0.8754085310505783  1   1   1   0.5
14  0.7607530902705649  0   1   1   0.2
15  0.07947531070511352 0   1   0   0.2
16  0.7614915275587621  0   1   1   0.2
17  0.4879049840270588  0   1   1   0.5
18  0.5419163830037381  0   1   1   0.2
19  0.5332705336932749  0   1   1   0.5
20  0.9298071674348853  0   1   1   0.2
21  0.29434789651478444 1   1   1   0.2
22  0.10249882294097806 1   1   1   0.5
23  0.7074116633342687  1   1   1   0.5
24  0.5250272765057415  0   1   1   0.5
25  0.5142364888584581  1   1   1   0.5
26  0.9172307084474999  1   1   1   0.2

The tests return value is not what I expect at all. If I take the first row for instance, I would have expected:

1   0.3050642603124159  1   1   0   0.2

instead of:

1   0.3050642603124159  1   1   1   0.5

I'm totally lost. Thanks for any help, this is killing me.

@ErgestBasha Here it is:

CREATE TABLE `users` (  
`id` char(36) NOT NULL,  
`user_name` varchar(60) DEFAULT NULL,  
`user_hash` varchar(255) DEFAULT NULL,  
`system_generated_password` tinyint(1) DEFAULT NULL,  
`pwd_last_changed` datetime DEFAULT NULL,  
`authenticate_id` varchar(100) DEFAULT NULL,  
`sugar_login` tinyint(1) DEFAULT '1',  
`first_name` varchar(30) DEFAULT NULL,  
`last_name` varchar(30) DEFAULT NULL,  
`is_admin` tinyint(1) DEFAULT '0',  
`external_auth_only` tinyint(1) DEFAULT '0',  
`receive_notifications` tinyint(1) DEFAULT '1',  
`description` text,  `date_entered` datetime DEFAULT NULL,  
`date_modified` datetime DEFAULT NULL,  
`modified_user_id` char(36) DEFAULT NULL,  
`created_by` char(36) DEFAULT NULL,  
`title` varchar(50) DEFAULT NULL,  
`photo` varchar(255) DEFAULT NULL,  
`department` varchar(50) DEFAULT NULL,  
`phone_home` varchar(50) DEFAULT NULL,  
`phone_mobile` varchar(50) DEFAULT NULL,  
`phone_work` varchar(50) DEFAULT NULL,  
`phone_other` varchar(50) DEFAULT NULL,  
`phone_fax` varchar(50) DEFAULT NULL,  
`status` varchar(100) DEFAULT NULL,  
`address_street` varchar(150) DEFAULT NULL,  
`address_city` varchar(100) DEFAULT NULL,  
`address_state` varchar(100) DEFAULT NULL,  
`address_country` varchar(100) DEFAULT NULL,  
`address_postalcode` varchar(20) DEFAULT NULL,  
`deleted` tinyint(1) DEFAULT NULL,  
`portal_only` tinyint(1) DEFAULT '0',  
`show_on_employees` tinyint(1) DEFAULT '1',  
`employee_status` varchar(100) DEFAULT NULL,  
`messenger_id` varchar(100) DEFAULT NULL,  
`messenger_type` varchar(100) DEFAULT NULL,  
`reports_to_id` char(36) DEFAULT NULL,  
`is_group` tinyint(1) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_user_name` (`user_name`,`is_group`,`status`,`last_name`,`first_name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
  • mysql
Share Improve this question edited Dec 17, 2024 at 15:48 Ergest Basha 8,9784 gold badges10 silver badges30 bronze badges asked Dec 17, 2024 at 15:08 ebardetebardet 2421 silver badge10 bronze badges 9
  • 3 why you do > comparison on strings? – Marcin Orlowski Commented Dec 17, 2024 at 15:10
  • 1 Please post the output of show create table users – Ergest Basha Commented Dec 17, 2024 at 15:18
  • @MarcinOrlowski I admit this is not clever, I started my investigation with numbers and I got the same. I ended up using strings just to check out but it actually doesn't make any difference. – ebardet Commented Dec 17, 2024 at 15:30
  • @ErgestBasha, added the outup in the question. – ebardet Commented Dec 17, 2024 at 15:35
  • 2 Tips for asking a good Structured Query Language (SQL) question, #5 and #3 – Akina Commented Dec 17, 2024 at 15:39
 |  Show 4 more comments

1 Answer 1

Reset to default 3

Apparently MySQL "optimizes" this by replacing each instance of r in the main query with RAND(), so it's comparing a different random number each time and you get inconsistent results.

The workaround is to create a temporary table to associate a specific random number with each ID:

CREATE TEMPORARY TABLE users_rand (
  id INT PRIMARY KEY,
  r DOUBLE
);
INSERT INTO users_rand
SELECT id, RAND()
FROM users;

SELECT ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > 0.5, r > 0.2, r > 0.1,
CASE
      WHEN r > 0.5 THEN 0.5
      WHEN r > 0.2 THEN 0.2
      WHEN r > 0.1 THEN 0.1
      ELSE 0
END as actual
FROM users_rand;

If you need this frequently you might make this an actual column in the users table.

MySQL, can anybody explain what I'm doing wrong here with:

select ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > '0.5', r > '0.2', r > '0.1',
CASE
      WHEN r > '0.5' THEN '0.5'
      WHEN r > '0.2' THEN '0.2'
      WHEN r > '0.1'THEN '0.1'
      ELSE '0'
END as 'actual'
from (select id, rand() r from users) t;

results in:

1   0.3050642603124159  1   1   1   0.5
2   0.03457492965699633 0   1   0   0.5
3   0.8674438212695008  0   1   1   0.5
4   0.077966298980607   1   1   1   0.2
5   0.3282627941372458  0   0   1   0.5
6   0.2562537558900693  0   1   1   0.2
7   0.017397192323018975    0   1   0   0.2
8   0.5937670381681687  0   1   1   0.5
9   0.1845749962931266  0   1   1   0.2
10  0.8352527859017744  0   1   1   0.2
11  0.9591796351216544  1   0   1   0.2
12  0.05485615977538392 1   1   1   0.2
13  0.8754085310505783  1   1   1   0.5
14  0.7607530902705649  0   1   1   0.2
15  0.07947531070511352 0   1   0   0.2
16  0.7614915275587621  0   1   1   0.2
17  0.4879049840270588  0   1   1   0.5
18  0.5419163830037381  0   1   1   0.2
19  0.5332705336932749  0   1   1   0.5
20  0.9298071674348853  0   1   1   0.2
21  0.29434789651478444 1   1   1   0.2
22  0.10249882294097806 1   1   1   0.5
23  0.7074116633342687  1   1   1   0.5
24  0.5250272765057415  0   1   1   0.5
25  0.5142364888584581  1   1   1   0.5
26  0.9172307084474999  1   1   1   0.2

The tests return value is not what I expect at all. If I take the first row for instance, I would have expected:

1   0.3050642603124159  1   1   0   0.2

instead of:

1   0.3050642603124159  1   1   1   0.5

I'm totally lost. Thanks for any help, this is killing me.

@ErgestBasha Here it is:

CREATE TABLE `users` (  
`id` char(36) NOT NULL,  
`user_name` varchar(60) DEFAULT NULL,  
`user_hash` varchar(255) DEFAULT NULL,  
`system_generated_password` tinyint(1) DEFAULT NULL,  
`pwd_last_changed` datetime DEFAULT NULL,  
`authenticate_id` varchar(100) DEFAULT NULL,  
`sugar_login` tinyint(1) DEFAULT '1',  
`first_name` varchar(30) DEFAULT NULL,  
`last_name` varchar(30) DEFAULT NULL,  
`is_admin` tinyint(1) DEFAULT '0',  
`external_auth_only` tinyint(1) DEFAULT '0',  
`receive_notifications` tinyint(1) DEFAULT '1',  
`description` text,  `date_entered` datetime DEFAULT NULL,  
`date_modified` datetime DEFAULT NULL,  
`modified_user_id` char(36) DEFAULT NULL,  
`created_by` char(36) DEFAULT NULL,  
`title` varchar(50) DEFAULT NULL,  
`photo` varchar(255) DEFAULT NULL,  
`department` varchar(50) DEFAULT NULL,  
`phone_home` varchar(50) DEFAULT NULL,  
`phone_mobile` varchar(50) DEFAULT NULL,  
`phone_work` varchar(50) DEFAULT NULL,  
`phone_other` varchar(50) DEFAULT NULL,  
`phone_fax` varchar(50) DEFAULT NULL,  
`status` varchar(100) DEFAULT NULL,  
`address_street` varchar(150) DEFAULT NULL,  
`address_city` varchar(100) DEFAULT NULL,  
`address_state` varchar(100) DEFAULT NULL,  
`address_country` varchar(100) DEFAULT NULL,  
`address_postalcode` varchar(20) DEFAULT NULL,  
`deleted` tinyint(1) DEFAULT NULL,  
`portal_only` tinyint(1) DEFAULT '0',  
`show_on_employees` tinyint(1) DEFAULT '1',  
`employee_status` varchar(100) DEFAULT NULL,  
`messenger_id` varchar(100) DEFAULT NULL,  
`messenger_type` varchar(100) DEFAULT NULL,  
`reports_to_id` char(36) DEFAULT NULL,  
`is_group` tinyint(1) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_user_name` (`user_name`,`is_group`,`status`,`last_name`,`first_name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

MySQL, can anybody explain what I'm doing wrong here with:

select ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > '0.5', r > '0.2', r > '0.1',
CASE
      WHEN r > '0.5' THEN '0.5'
      WHEN r > '0.2' THEN '0.2'
      WHEN r > '0.1'THEN '0.1'
      ELSE '0'
END as 'actual'
from (select id, rand() r from users) t;

results in:

1   0.3050642603124159  1   1   1   0.5
2   0.03457492965699633 0   1   0   0.5
3   0.8674438212695008  0   1   1   0.5
4   0.077966298980607   1   1   1   0.2
5   0.3282627941372458  0   0   1   0.5
6   0.2562537558900693  0   1   1   0.2
7   0.017397192323018975    0   1   0   0.2
8   0.5937670381681687  0   1   1   0.5
9   0.1845749962931266  0   1   1   0.2
10  0.8352527859017744  0   1   1   0.2
11  0.9591796351216544  1   0   1   0.2
12  0.05485615977538392 1   1   1   0.2
13  0.8754085310505783  1   1   1   0.5
14  0.7607530902705649  0   1   1   0.2
15  0.07947531070511352 0   1   0   0.2
16  0.7614915275587621  0   1   1   0.2
17  0.4879049840270588  0   1   1   0.5
18  0.5419163830037381  0   1   1   0.2
19  0.5332705336932749  0   1   1   0.5
20  0.9298071674348853  0   1   1   0.2
21  0.29434789651478444 1   1   1   0.2
22  0.10249882294097806 1   1   1   0.5
23  0.7074116633342687  1   1   1   0.5
24  0.5250272765057415  0   1   1   0.5
25  0.5142364888584581  1   1   1   0.5
26  0.9172307084474999  1   1   1   0.2

The tests return value is not what I expect at all. If I take the first row for instance, I would have expected:

1   0.3050642603124159  1   1   0   0.2

instead of:

1   0.3050642603124159  1   1   1   0.5

I'm totally lost. Thanks for any help, this is killing me.

@ErgestBasha Here it is:

CREATE TABLE `users` (  
`id` char(36) NOT NULL,  
`user_name` varchar(60) DEFAULT NULL,  
`user_hash` varchar(255) DEFAULT NULL,  
`system_generated_password` tinyint(1) DEFAULT NULL,  
`pwd_last_changed` datetime DEFAULT NULL,  
`authenticate_id` varchar(100) DEFAULT NULL,  
`sugar_login` tinyint(1) DEFAULT '1',  
`first_name` varchar(30) DEFAULT NULL,  
`last_name` varchar(30) DEFAULT NULL,  
`is_admin` tinyint(1) DEFAULT '0',  
`external_auth_only` tinyint(1) DEFAULT '0',  
`receive_notifications` tinyint(1) DEFAULT '1',  
`description` text,  `date_entered` datetime DEFAULT NULL,  
`date_modified` datetime DEFAULT NULL,  
`modified_user_id` char(36) DEFAULT NULL,  
`created_by` char(36) DEFAULT NULL,  
`title` varchar(50) DEFAULT NULL,  
`photo` varchar(255) DEFAULT NULL,  
`department` varchar(50) DEFAULT NULL,  
`phone_home` varchar(50) DEFAULT NULL,  
`phone_mobile` varchar(50) DEFAULT NULL,  
`phone_work` varchar(50) DEFAULT NULL,  
`phone_other` varchar(50) DEFAULT NULL,  
`phone_fax` varchar(50) DEFAULT NULL,  
`status` varchar(100) DEFAULT NULL,  
`address_street` varchar(150) DEFAULT NULL,  
`address_city` varchar(100) DEFAULT NULL,  
`address_state` varchar(100) DEFAULT NULL,  
`address_country` varchar(100) DEFAULT NULL,  
`address_postalcode` varchar(20) DEFAULT NULL,  
`deleted` tinyint(1) DEFAULT NULL,  
`portal_only` tinyint(1) DEFAULT '0',  
`show_on_employees` tinyint(1) DEFAULT '1',  
`employee_status` varchar(100) DEFAULT NULL,  
`messenger_id` varchar(100) DEFAULT NULL,  
`messenger_type` varchar(100) DEFAULT NULL,  
`reports_to_id` char(36) DEFAULT NULL,  
`is_group` tinyint(1) DEFAULT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_user_name` (`user_name`,`is_group`,`status`,`last_name`,`first_name`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
  • mysql
Share Improve this question edited Dec 17, 2024 at 15:48 Ergest Basha 8,9784 gold badges10 silver badges30 bronze badges asked Dec 17, 2024 at 15:08 ebardetebardet 2421 silver badge10 bronze badges 9
  • 3 why you do > comparison on strings? – Marcin Orlowski Commented Dec 17, 2024 at 15:10
  • 1 Please post the output of show create table users – Ergest Basha Commented Dec 17, 2024 at 15:18
  • @MarcinOrlowski I admit this is not clever, I started my investigation with numbers and I got the same. I ended up using strings just to check out but it actually doesn't make any difference. – ebardet Commented Dec 17, 2024 at 15:30
  • @ErgestBasha, added the outup in the question. – ebardet Commented Dec 17, 2024 at 15:35
  • 2 Tips for asking a good Structured Query Language (SQL) question, #5 and #3 – Akina Commented Dec 17, 2024 at 15:39
 |  Show 4 more comments

1 Answer 1

Reset to default 3

Apparently MySQL "optimizes" this by replacing each instance of r in the main query with RAND(), so it's comparing a different random number each time and you get inconsistent results.

The workaround is to create a temporary table to associate a specific random number with each ID:

CREATE TEMPORARY TABLE users_rand (
  id INT PRIMARY KEY,
  r DOUBLE
);
INSERT INTO users_rand
SELECT id, RAND()
FROM users;

SELECT ROW_NUMBER()  OVER (
    ORDER BY id
  ) row_num, r, r > 0.5, r > 0.2, r > 0.1,
CASE
      WHEN r > 0.5 THEN 0.5
      WHEN r > 0.2 THEN 0.2
      WHEN r > 0.1 THEN 0.1
      ELSE 0
END as actual
FROM users_rand;

If you need this frequently you might make this an actual column in the users table.

本文标签： Can39t understand CASEtestRAND in MySQLStack Overflow

版权声明：本文标题：Can't understand CASE, test, RAND in MySQL - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://it.en369.cn/questions/1735995622a1376348.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。