Error 1241 21000 operand should contain 1 column s

Learn how to fix MySQL error operand should contain 1 column(s)

The error Operand should contain 1 column(s) is most likely caused by a subquery that’s returning more than one column.

Here’s a typical SELECT query that causes this error:

SELECT column_one, 
    (SELECT column_two, column_three FROM table_two) 
  FROM table_one;

The above subquery returns column_two and column_three, so MySQL throws the Operand should contain 1 column(s) error.

Most often, you only need to check your subquery and make sure that it returns only one column.

If you need more guidance on how to fix this MySQL error, then you may read the next section.

How to fix Operand should contain 1 column(s) error

To illustrate an example, imagine you have two tables that have related data named members and pets.

The members table contain the first_name of people who have pets as shown below:

+----+------------+----------------+
| id | first_name | country        |
+----+------------+----------------+
|  1 | Jessie     | United States  |
|  2 | Ann        | Canada         |
|  3 | Joe        | Japan          |
|  4 | Mark       | United Kingdom |
|  5 | Peter      | Canada         |
+----+------------+----------------+

While the pets table contain the owner and the species column as follows:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Peter  | dog     |    5 |
+----+--------+---------+------+

The first_name and the owner columns are related, so you may use a subquery to display data from both tables like this:

SELECT `first_name` AS `owner_name`, 
  (SELECT `species`, `age` 
    FROM pets WHERE pets.owner = members.first_name) 
  FROM members;

However, the above SQL query is wrong, and it will throw an error like this:

ERROR 1241 (21000): Operand should contain 1 column(s)

This is because MySQL expects the subquery to return only one column, but the above subquery returns two.

To fix the error, you may create two subqueries with each subquery returning only one column as in the following SELECT statement:

SELECT `first_name` AS `owner_name`, 
  (SELECT `species` 
    FROM pets WHERE pets.owner = members.first_name) AS `species`,
  (SELECT `age` 
    FROM pets WHERE pets.owner = members.first_name) AS `age`  
  FROM members;

While the above query works, it will throw another error once the subquery returns more than one row.

Let’s add another pet that’s owned by “Jessie” to the pets table as shown below:

+----+--------+---------+------+
| id | owner  | species | age  |
+----+--------+---------+------+
|  1 | Jessie | bird    |    2 |
|  2 | Ann    | duck    |    3 |
|  3 | Joe    | horse   |    4 |
|  4 | Mark   | dog     |    4 |
|  5 | Peter  | dog     |    5 |
|  6 | Jessie | cat     |    4 |
+----+--------+---------+------+

Now the subqueries will return two species and age rows for “Jessie”, causing another related error:

mysql> SELECT `first_name` AS `owner_name`, 
   ->   (SELECT `species` 
   ->     FROM pets WHERE pets.owner = members.first_name) 
   ->   FROM members;
ERROR 1242 (21000): Subquery returns more than 1 row

To properly fix the error, you need to replace the subquery with a JOIN clause:

SELECT `first_name` AS `owner_name`, `species`, `age`
  FROM members JOIN pets 
  ON members.first_name = pets.owner;

Subqueries can be used to replace JOIN clauses only when you need to SELECT data from one table, but you need to filter the result by another table column.

For example, maybe you have some owner names in the pets table that aren’t recorded in the members table. You can use a subquery in the WHERE clause to display rows in the pets table that are also recorded in the members table.

Here’s an example of using a subquery in the WHERE clause:

SELECT `owner`, `species`, `age` 
  FROM pets 
  WHERE `owner` IN (SELECT `first_name` FROM members);

Without using a subquery, you need to JOIN the table as shown below:

SELECT `owner`, `species`, `age` 
  FROM pets JOIN members 
  ON pets.owner = members.first_name;

The two queries above will produce the same result set.

And that’s how you can fix the Operand should contain 1 column(s) error in MySQL.

You need to check your subquery before anything else when you encounter this error.

Источник

Hello,

With Hibernate 3.0.5 and Mysql 5, I don’t know what is wrong with the following count() method:

Code:

public int count() {
        javax.persistence.criteria.CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
        javax.persistence.criteria.Root<T> rt = cq.from(entityClass);
        cq.select(getEntityManager().getCriteriaBuilder().count(rt));
        javax.persistence.Query q = getEntityManager().createQuery(cq);
        return ((Long) q.getSingleResult()).intValue();
    }

I got the following error when I execute the count() method:

Quote:

08:01:04,245 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1)
08:01:04,245 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1)
08:01:04,245 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) CategoryImagesServiceImpl Hello count()…
08:01:04,246 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) ****calling AbstractFacade()…..**********
08:01:04,247 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) Hibernate:
08:01:04,247 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) select
08:01:04,248 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) count((categoryim0_.category_id,
08:01:04,248 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) categoryim0_.image_id)) as col_0_0_
08:01:04,248 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) from
08:01:04,248 INFO [stdout] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) houseware.category_images categoryim0_ limit ?
08:01:04,248 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) SQL Error: 1241, SQLState: 21000
08:01:04,249 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) Operand should contain 1 column(s)
08:01:04,249 INFO [org.hibernate.engine.jdbc.internal.LogicalConnectionImpl] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) HHH00106:Forcing container resource cleanup on transaction completion
08:01:04,250 ERROR [org.jboss.ws.common.invocation.InvocationHandlerJAXWS] (http-portal.ixsystems.com.au-192.168.1.20-8080-1) Method invocation failed with exception: null: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_07]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_07]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_07]
at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_07]
at org.jboss.ws.common.invocation.AbstractInvocationHandlerJSE.invoke(AbstractInvocationHandlerJSE.java:111)
at org.jboss.wsf.stack.cxf.JBossWSInvoker._invokeInternal(JBossWSInvoker.java:169)
at org.jboss.wsf.stack.cxf.JBossWSInvoker.invoke(JBossWSInvoker.java:117)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441) [:1.6.0_07]
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [:1.6.0_07]
at java.util.concurrent.FutureTask.run(FutureTask.java:138) [:1.6.0_07]
at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:106)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:263)
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:118)
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:208)
at org.jboss.wsf.stack.cxf.RequestHandlerImpl.handleHttpRequest(RequestHandlerImpl.java:91)
at org.jboss.wsf.stack.cxf.transport.ServletHelper.callRequestHandler(ServletHelper.java:167)
at org.jboss.wsf.stack.cxf.CXFServletExt.invoke(CXFServletExt.java:87)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:184)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:107)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
at org.jboss.wsf.stack.cxf.CXFServletExt.service(CXFServletExt.java:135)
at org.jboss.wsf.spi.deployment.WSFServlet.service(WSFServlet.java:140) [jbossws-spi-2.0.0.Beta10.jar:2.0.0.Beta10]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.2.Final.jar:7.1.0.Alpha2-SNAPSHOT]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.2.Final.jar:7.1.0.Alpha2-SNAPSHOT]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.2.Final.jar:7.1.0.Alpha2-SNAPSHOT]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.2.Final.jar:7.1.0.Alpha2-SNAPSHOT]
at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:49) [jboss-as-jpa-7.1.0.Alpha2-SNAPSHOT.jar:7.1.0.Alpha2-SNAPSHOT]

When I execute the above query in Mysql command windows, I can re-produce the same error:

Code:

mysql> select count((categoryim0_.category_id, categoryim0_.image_id)) as col_0_0_ from houseware.category_images categoryim0_ limit;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near » at line 1
mysql> select count((categoryim0_.category_id, categoryim0_.image_id)) as col_0_0_ from houseware.category_images categoryim0_ limit 100;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select count((categoryim0_.category_id, categoryim0_.image_id)) as col_0_0_ from houseware.category_images categoryim0_ limit 1;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select count((categoryim0_.category_id, categoryim0_.image_id)) as col_0_0_ from houseware.category_images categoryim0_;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select count((categoryim0_.category_id)) as col_0_0_ from houseware.category_images categoryim0_;
+———-+
| col_0_0_ |
+———-+
|        1 |
+———-+
1 row in set (0.00 sec)

Can anyone tell me how to change the count() method to deal with counting on multi columns?

Very appreciate for any suggestion and help.
Thanks
Sam

Источник

sql – MySQL error 1241: Operand should contain 1 column(s)

Syntax error, remove the ( ) from select.

insert into table2 (name, subject, student_id, result)
select name, subject, student_id, result
from table1;

Just remove the ( and the ) on your SELECT statement:

insert into table2 (Name, Subject, student_id, result)
select Name, Subject, student_id, result
from table1;

sql – MySQL error 1241: Operand should contain 1 column(s)

Another way to make the parser raise the same exception is the following incorrect clause.

SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id ,
                     system_user_id
                 FROM role_members m
                 WHERE r.id = m.role_id
                 AND m.system_user_id = intIdSystemUser
             )

The nested SELECT statement in the IN clause returns two columns, which the parser sees as operands, which is technically correct, since the id column matches values from but one column (role_id) in the result returned by the nested select statement, which is expected to return a list.

For sake of completeness, the correct syntax is as follows.

SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id
                 FROM role_members m
                 WHERE r.id = m.role_id
                 AND m.system_user_id = intIdSystemUser
             )

The stored procedure of which this query is a portion not only parsed, but returned the expected result.

Related posts on MySQL Error :

  • MySQL error code: 1175 during UPDATE in MySQL Workbench
  • MySQL Error Operand should contain 1 column
  • MySQL ERROR 1290 (HY000) –secure-file-priv option
  • mysql error 1364 Field doesnt have a default values
  • sql – MySQL Error 1264: out of range value for column
  • sql – MySQL error: key specification without a key length
  • mySQL Error 1040: Too Many Connection
  • php – mysql error TYPE=MyISAM
Источник

Всем привет! Выходит это ошибка при вставке в таблицу данные.
Этот же код нормально работает для триггера Before Update, а для Before Insert выдает ошибку, типа много чем одной строки как я понял.
Вот мой код:

DROP TRIGGER IF EXISTS `InsertBuildingAnalysis`;
CREATE TRIGGER `InsertBuildingAnalysis` BEFORE INSERT ON `MDepartments` FOR EACH ROW BEGIN

  DECLARE population INT;
  DECLARE normative, isnornamtive INT;
  DECLARE locality VARCHAR(1256);
  DECLARE loc_dis_id int;
  DECLARE loc_isCenter int;
  DECLARE sub_pop int;
  DECLARE sub_cpop int;
  DECLARE dis_pop int;

  SET population = (SELECT new_fnc(new.ParentId));
  SET locality = (SELECT * FROM Localities WHERE Localities.Id = new.LocalityId);
  SET loc_dis_id = (SELECT Localities.DistrictId FROM Localities WHERE Localities.Id = new.LocalityId);
  SET loc_isCenter = (SELECT Localities.IsCenter from Localities WHERE Localities.Id = new.LocalityId);
  SET sub_pop = (SELECT new_fnc2(new.SubRegionId));
  SET sub_cpop = (SELECT new_fnc3(new.SubRegionId));
  SET dis_pop = (SELECT new_fnc4(loc_dis_id));

  IF (new.TypeId = 4 ) THEN
    IF (population < 50) THEN
      SET new.Normative = 0;
      SET new.IsNormativePositive = 1;
    ELSEIF (population > 8000) THEN
      SET new.Normative = 0;
      SET new.IsNormativePositive = 0;
    ELSE
      SET new.Normative = 1;
    END IF;
  ELSEIF (new.TypeId = 3) THEN
    IF (population < 800) THEN
      SET  new.Normative = 1;
      SET  new.IsNormativePositive = 1;
    ELSEIF (population > 2000) THEN
      SET new.Normative = 0;
      SET new.IsNormativePositive = 0;
    ELSE
      SET new.Normative = 1;
    END IF ;
  ELSEIF (new.TypeId = 2) THEN
    IF (population > 800 && population <= 2000) THEN
      SET new.Normative = 2;
    ELSEIF (population > 2000 ) THEN
      SET new.Normative = 1;
    ELSE
      SET new.Normative = 0;
      SET new.IsNormativePositive = 1;
    END IF;
  ELSEIF (new.TypeId = 57) THEN
    IF(population < 10000) THEN
      SET new.Normative = 0;
      SET new.IsNormativePositive = 1;
    ELSE
      SET new.Normative = 1;
    END IF;
  ELSEIF (new.TypeId = 26)THEN
    IF (!locality) THEN
      SET new.Normative = -1;
    ELSEIF (loc_isCenter != 1) THEN
      SET new.Normative = -2;
    ELSE
      SET new.Normative = 1;
    END IF;
  ELSEIF (new.TypeId = 35) THEN
    SET  population = sub_pop + sub_cpop;
    IF(population >= 100000) THEN
      SET new.Normative = 1;
    ELSE
      SET new.Normative = 0;
      SET new.IsNormativePositive = 1;
    END IF;
  ELSEIF (new.TypeId = 27) THEN
    SET population = dis_pop;
    IF(population > 5000) THEN
    SET new.Normative = 1;
    ELSE
      SET new.Normative = 0;
      SET new.IsNormativePositive = 1;
    END IF;
  ELSEIF (new.TypeId = 25) THEN
    SET new.Normative = 1;
  END IF;
END;

Не знаю как исправить! Ваши варианты пож-а!

Источник

  • #1

Собстнно:

Database Error

Server Address: localhost
Server Username: root

Error Diagnostic:
 Bad query.

Server Error: (1241) Operand should contain 1 column(s)

Last SQL Query:
		SELECT
			COUNT(*)
		FROM
			hlstats_Players
		WHERE
			game='cstrike'
			AND hideranking = 0
			AND kills >= 1
			AND (
					(skill > '1055') OR (
						(skill = '1055') AND (kills/IF(deaths=0,1,deaths) > 0,64285714285714)
					)
			)

Этот лог выдаёт hlstatsx последней да и старших версий, в разделе /hlstats.php?mode=playerinfo&player={num}
после поля Очки: или Points: , покопав код и гугл пришёл к выводу что мой mysql не может работать с запросами такого вида, где числа (kills/deaths=0,64285714285714) как видно с запятой.
Недавно был переход на новое ядро и смена apache2 на nginx, соот. mysql и nginx + php5-fpm тоже обновились, и на старой mysql такого небыло.

Если округлить поля kills и deaths на выходе получим целое число с которым запрос прекрасно выполняется без всяких там Operand should contain 1 column(s).

Нашёл я этот файл webincludesfunctions.php

(".$g_options['rankingtype']." = '".$playerdata[$g_options['rankingtype']]."') AND (kills/IF(deaths=0,1,deaths) > ".($playerdata['kills']/$tempdeaths).")

добавив всего round 

(".$g_options['rankingtype']." = '".$playerdata[$g_options['rankingtype']]."') AND (kills/IF(deaths=0,1,deaths) > ".round($playerdata['kills']/$tempdeaths).")

конечно всё стало работать.

Ребят мож кто сталкивался с такой проблемой?
Мож конфиг где не тот у демонов для работы с числами?

nginx 1.1.19-1
php5-fpm 5.3.10-1
mysql-server 5.5.24

Прув: http://cs.rainbows.org.ua/hlstats.php?mode=playerinfo&player=25212

  • #2

Попробуй repair table hlstats_Players

А потом запусти демон run_hlstats

  • #3

Попробуй repair table hlstats_Players

А потом запусти демон run_hlstats

Заделал: 

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
hlstats.hlstats_Players  repair  status    OK  

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
hlstats.hlstats_Players  check   status    OK   

Table                    Op       Msg_type  Msg_text                   
-----------------------  -------  --------  ---------------------------
hlstats.hlstats_Players  analyze  status    Table is already up to date

убрал round получил тож самы лог :(

tiger_by, а при краше таблицы разве не этот лог? http://hlmod.ru/forum/showpost.php?p=68380&postcount=1

Да и при round е он при краше бы написал что таблице хана наверно.

  • #4

  • Неверное число столбцов в подзапросе:
    ERROR 1241 (ER_OPERAND_COLUMNF)
    SQLSTATE = 21000
    Message = «Operand should contain 1 column(s)»
    Эта ошибка возникает в случаях наподобие следующего:
    SELECT (SELECT columnl, column2 FROM t2) FROM tl;
    Допустимо применять подзапросы, которые возвращают несколько столбцов с целью сравнения. См. раздел 6.1.8.7. Но в других контекстах подзапрос должен быть скалярным операндом.

google
https://www.google.ru/#q=Server+Err…pw.r_qf.&fp=4264de5f97fa16b9&biw=1221&bih=832

Последнее редактирование: 1 Окт 2012

  • #5

  • Неверное число столбцов в подзапросе:
    ERROR 1241 (ER_OPERAND_COLUMNF)
    SQLSTATE = 21000
    Message = «Operand should contain 1 column(s)»
    Эта ошибка возникает в случаях наподобие следующего:
    SELECT (SELECT columnl, column2 FROM t2) FROM tl;
    Допустимо применять подзапросы, которые возвращают несколько столбцов с целью сравнения. См. раздел 6.1.8.7. Но в других контекстах подзапрос должен быть скалярным операндом.

google
https://www.google.ru/#q=Server+Err…pw.r_qf.&fp=4264de5f97fa16b9&biw=1221&bih=832

Ужас = Главное я исходный то не менял, посмотрев гугл увидел куча таких серверов с такими же логами :)

Источник

Понравилась статья? Поделить с друзьями:
  • Error 1227 mysql
  • Error 1227 42000
  • Error 1221 hy000 incorrect usage of db grant and global privileges
  • Error 1146 42s02 at line 1 table zabbix images doesn t exist
  • Error 1140 sql