Bug #106017 case requet
Submitted: 31 Dec 2021 13:17 Modified: 31 Jan 14:46
Reporter: nicolas garnier Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: case

[31 Dec 2021 13:17] nicolas garnier
Description:
1	2005-05-24 22:53:30	367	130	2005-05-26 22:04:30	1	2021-12-28 16:12:18	1
2	2005-05-24 22:54:33	1525	459	2005-05-28 19:40:33	1	2021-12-28 16:12:18	2
3	2005-05-24 23:03:39	1711	408	2005-06-01 22:12:39	1	2021-12-28 16:12:18	1
4	2005-05-24 23:04:41	2452	333	2005-06-03 01:43:41	2	2021-12-28 16:12:18	2
5	2005-05-24 23:05:21	2079	222	2005-06-02 04:33:21	1	2021-12-28 16:12:18	1
6	2005-05-24 23:08:07	2792	549	2005-05-27 01:32:07	1	2021-12-28 16:12:18	2
7	2005-05-24 23:11:53	3995	269	2005-05-29 20:34:53	2	2021-12-28 16:12:18	3
8	2005-05-24 23:31:46	2346	239	2005-05-27 23:33:46	2	2021-12-28 16:12:18	4
9	2005-05-25 00:00:40	2580	126	2005-05-28 00:22:40	1	2021-12-28 16:12:18	1
10	2005-05-25 00:02:21	1824	399	2005-05-31 22:44:21	2	2021-12-28 16:12:18	2

SELECT rental_id, staff_id, nombre,
CASE
    WHEN nombre = 2 THEN "egale a 2"
    WHEN nombre = 1 THEN "egale a 1"
    ELSE "autre"
END
FROM rental;

How to repeat:
je fais une requête sur la table exemple rental bbd sakila
la zone staff_id (TINYINT type)  contient des 1 et des 2

le résultat de la requête est très bizarre car la requête  revoir que les staff_id = 1    (filtre)

résultat de la requête

1	1	 egale a 1
2	1	 egale a 1
3	1	 egale a 1
5	1	 egale a 1
6	1	 egale a 1
9	1	 egale a 1
13	1	 egale a 1
14	1	 egale a 1
15	1	 egale a 1
[31 Dec 2021 14:46] MySQL Verification Team
Thank you for the bug report. Please provide the correct query:

mysql 8.0 > SELECT rental_id, staff_id, nombre,
    -> CASE
    ->     WHEN nombre = 2 THEN "egale a 2"
    ->     WHEN nombre = 1 THEN "egale a 1"
    ->     ELSE "autre"
    -> END
    -> FROM rental;
ERROR 1054 (42S22): Unknown column 'nombre' in 'field list'
mysql 8.0 > DESC rental\G
*************************** 1. row ***************************
  Field: rental_id
   Type: int
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: rental_date
   Type: datetime
   Null: NO
    Key: MUL
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: inventory_id
   Type: mediumint unsigned
   Null: NO
    Key: MUL
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: customer_id
   Type: smallint unsigned
   Null: NO
    Key: MUL
Default: NULL
  Extra:
*************************** 5. row ***************************
  Field: return_date
   Type: datetime
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: staff_id
   Type: tinyint unsigned
   Null: NO
    Key: MUL
Default: NULL
  Extra:
*************************** 7. row ***************************
  Field: last_update
   Type: timestamp
   Null: NO
    Key:
Default: CURRENT_TIMESTAMP
  Extra: DEFAULT_GENERATED on update CURRENT_TIMESTAMP
7 rows in set (0.01 sec)

mysql 8.0 >
[1 Feb 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".