Mysql Error 1054 In Where Clause
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Unknown Column In Where Clause up vote 64 down vote favorite 15 I have a simple query: SELECT u_name AS user_name FROM users WHERE user_name = "john"; I get Unknown Column 'user_name' in where clause. Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'? mysql sql mysql-error-1054 share|improve this question edited Jul 25 '13 at 14:14 Btuman 5031729 asked Sep 30 '08 at 15:37 Corpo add a comment| 17 Answers 17 active oldest votes up vote 57 down vote SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name has not yet occurred. share|improve this answer edited Oct 29 '15 at 9:44 idmean 7,83762153 answered Sep 30 '08 at 15:41 dacracot 11.2k1776135 18 Rather than "backwards" I think it makes more sense to say "inside out" –Joe Philllips May 26 '10 at 3:35 2 Isn't it both inside out and right to left? –PeteGO Apr 28 '14 at 17:52 It makes more sense to say that the entire statement is parsed, transformed, and optimised as a whole in a complex, multistage process. "SQL is evaluated backwards, from right to left" is just wrong –David Aldridge Sep 28 at 19:54 add a comment| up vote 18 down vote See the following MySQL manual page: http://dev.mysql.com/doc/refman/5.0/en/select.html "A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses." share|improve this answer answered Sep 30 '08 at 15:44 Paul Dixon 185k33248302 add a comment| up vote 17 down vote What about: SELECT u_name AS user_na
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1054. Unknown column in 'where clause' up vote 0 http://stackoverflow.com/questions/153598/unknown-column-in-where-clause down vote favorite I don't know what the problem is in my stored procedure below: CREATE PROCEDURE `GetDataByColNameByTime `( IN Col_Name VARCHAR(50), IN DateStart VARCHAR(50), IN DateEnd VARCHAR(50) ) BEGIN SET @sqlx = CONCAT ('SELECT ', Col_Name , ' , SAMPLE_TIME FROM indicator_data_history I WHERE ( I.SAMPLE_TIME >= `2014-04-04 11:47:57` ) AND ( I.SAMPLE_TIME <= `2014-04-04 11:33:57` ) order by SAMPLE_TIME'); PREPARE stmtx FROM @sqlx; EXECUTE stmtx; I can http://stackoverflow.com/questions/22912987/error-code-1054-unknown-column-in-where-clause make a similar query work if written in a simple statement: SELECT RT , SAMPLE_TIME FROM indicator_data_history I WHERE ( I.SAMPLE_TIME >= '2014-04-04 11:47:57' ) AND ( I.SAMPLE_TIME <= '2014-04-04 11:33:57' ) ; after analysis the quotes seem to be the problem 2014-04-04 11:33:57 - any idea how to fix this? mysql stored-procedures share|improve this question edited Apr 7 '14 at 12:59 Paul R 149k16224367 asked Apr 7 '14 at 12:52 user3506769 12 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote Backticks (`) are used for column and table names. Single quotes (') are used for strings. For example, this is invalid: I.SAMPLE_TIME >= `2014-04-04 11:47:57` and this is valid: I.SAMPLE_TIME >= '2014-04-04 11:47:57' share|improve this answer answered Apr 7 '14 at 12:55 Damir Kasipovic 3,853615 @ D. Kasipovic, the thing is how can you add the quote within a string because I have a syntax error by doing what you suggested –user3506769 Apr 7 '14 at 13:01 is \' the way to go? –user3506769 Apr 7 '14 at 13:05 Finally \' was did the trick. –user3506769 Apr 7 '14 at 13:13 thanks D. Kasipovic for ur prompt reply u
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site http://dba.stackexchange.com/questions/103723/error-1054-42s22-unknown-column-plugin-in-mysql-user About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's mysql error how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user' up vote 5 down vote favorite I am trying to grant privileges for a user to a new database mysql> grant all on db_test.* to 'user_test'@'localhost' identified by 'usersexistingpassword'; I get the following error ERROR 1054 mysql error 1054 (42S22): Unknown column 'plugin' in 'mysql.user' I am using MySQL 5.6 mysql> select @@version; +-------------+ | @@version | +-------------+ | 5.6.24-72.2 | +-------------+ 1 row in set (0.00 sec) I did find an article on MySQL on updating Native Passwords on 5.6.x and later 6.3.9.3 Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin https://dev.mysql.com/doc/refman/5.7/en/account-upgrades.html I ran the command they recommend as root mysql> UPDATE mysql.user SET plugin = 'mysql_native_password' -> WHERE plugin = '' AND (Password = '' OR LENGTH(Password) = 41); ERROR 1054 (42S22): Unknown column 'plugin' in 'where clause' mysql mysql-5.6 errors users permissions share|improve this question edited Jun 10 '15 at 16:24 RolandoMySQLDBA 108k15139276 asked Jun 10 '15 at 16:08 brentwpeterson 128116 add a comment| 2 Answers 2 active oldest votes up vote 7 down vote accepted Your problem has to do with mysql.user and the way you upgraded to MySQL 5.6 If you look my answer to Cannot GRANT privileges as root, I show you the description of mysql.user from MySQL 4.1 to MySQL 5.6. The column plugin is column #41 in mysql.user in MySQL 5.5/5.6 mysql> SELECT column_name,ordinal_position FROM information_schema.columns -> WHERE table_schema='mysql' and table_name='user' and column_name='plugin'; +-------------+------------------+ | column_name |