phpmyadmin Question posted in
The official documentation can be found here.

Getting error "A non-numeric value encountered" when using parentheses with LIMIT in SELECT statements – Phpmyadmin

PLEASE SEE THE LAST EDIT ON THE BOTTOM

I have the following query using MySQL in phpMyAdmin:

(SELECT 
  id, 
  name, 
  rank1, 
  rank2 
FROM rank_list 
LEFT JOIN names ON names.id = rank_list.name_id 
WHERE type = "typeA" 
ORDER BY rank1
LIMIT 50) 

UNION ALL

(SELECT 
  id, 
  name, 
  rank1, 
  rank2 
FROM rank_list 
LEFT JOIN names ON names.id = rank_list.name_id 
WHERE type = "typeB" 
ORDER BY rank2 
LIMIT 50)
  • What it does is to get the top 50 ranks for rank1 of typeA and merge it with top 50 ranks for rank2 of typeB.
  • I need to use parentheses around each subSELECT because I apply a
    LIMIT and ORDER BY statement to both.
  • Both individually are working fine.

PROBLEM

I am getting the following error when applying the UNION to both queries:

A non-numeric value encountered

I really don’t get it here… One thing that might cause this is that I fill rank1 column -1 if type = "typeB" (same for rank2 and type = "typeA".

Or is it something else here?

EDIT:

It does work when I don’t apply a LIMIT to it

EDIT 2

I figured out that when doing the following (using parentheses) I also get the same error:

(SELECT id FROM rank_list LIMIT 1)
Tags:

2

Answers


  1. 0

    try enfor the data type using a cast 

      (SELECT 
    id, 
    name, 
    cast(rank1 AS SIGNED ), 
    cast(rank2  AS SIGNED )
  FROM rank_list 
  LEFT JOIN names ON names.id = rank_list.name_id 
  WHERE type = "typeA" 
  ORDER BY rank1
  LIMIT 50) 

  UNION ALL

  (SELECT 
    id, 
    name, 
    cast(rank1 AS SIGNED ), 
    cast(rank2  AS SIGNED )
  FROM rank_list 
  LEFT JOIN names ON names.id = rank_list.name_id 
  WHERE type = "typeB" 
  ORDER BY rank2 
  LIMIT 50)
    Login or Signup to reply.
  2. 0

    I cannot reproduce your issue

    drop table if exists rank_list,names;
create table rank_list
(id int,name_id int,rank1 int,rank2 int,type varchar(10));
create table names
(id int, name varchar(3));

insert into rank_list values
(1,1,1,1,'typea'),(2,2,3,1,'typea'),
(2,2,1,1,'typeb');

insert into names values
(1,'aaa'),(2,'bbb');

(SELECT 
  rank_list.id, 
  name, 
  rank1, 
  rank2 
FROM rank_list 
LEFT JOIN names ON names.id = rank_list.name_id 
WHERE type = "typeA" 
ORDER BY rank1 desc
LIMIT 50) 

UNION ALL

(SELECT 
  rank_list.id, 
  name, 
  rank1, 
  rank2 
FROM rank_list 
LEFT JOIN names ON names.id = rank_list.name_id 
WHERE type = "typeB" 
ORDER BY rank2 desc
LIMIT 50);

+------+------+-------+-------+
| id   | name | rank1 | rank2 |
+------+------+-------+-------+
|    2 | bbb  |     3 |     1 |
|    1 | aaa  |     1 |     1 |
|    2 | bbb  |     1 |     1 |
+------+------+-------+-------+
3 rows in set (0.00 sec)

    Note I have fully qualified id, If your model is different then without more input from you any answer or comment will be guesswork.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search