#948 (in 2.6.0) Keyword field names become capitalized

[Gabe da Silveira]

I ran into this bug while replicating tables using the
phpMyAdmin Export function from phpMyAdmin 2.3.3 or so.
I noticed you have fixed the export function so it displays
field names in backticks which presumably makes the
import work, but when you run the old query it still
capitalizes keywords (in my case first and status became
FIRST and STATUS).

However, I think it would make sense to support keyword
field names as typed into the SQL dialog box because ad hoc
table creators tend to skip the backticks except when
necessary.

[Garvin Hicking]

Logged In: YES
user_id=473563

Could you please specify this more clearly?

When phpMyAdmin 2.3.3 doesn't use backticks where necessary,
you sadly have to insert them into the file. MAybe you can
post a sample query?

And the keywords you type in the SQL dialog box do not have
to contain backticks where not neccessary, as phpMyAdmin can
detect where they are neccessary. I'm sorry, but I don't
really understand what you mean :)

Regards,
Garvin.

[Gabe da Silveira]

Logged In: YES
user_id=268048

Okay, type this into the SQL query box and submit. phpMyAdmin
capitalizes the fieldname first so you end with a table that has ids
`id` and `FIRST`. This can easily go unnoticed since MySQL is not
case-sensitive until you pull it with a mysql_fetch_array into PHP.

CREATE TABLE SG_Persons (
id int(10) unsigned NOT NULL auto_increment,
first varchar(64) NOT NULL default '',
PRIMARY KEY (`id`)
)

[Marc Delisle]

Logged In: YES
user_id=210714

I think that the problem here is that the user types

select first from mytable
and it becomes
SELECT FIRST FROM mytable

because FIRST and STATUS are marked as reserved words in
sqlparser.data, but are not present on
http://www.mysql.com/doc/en/Reserved_words.html

Do I understand correctly the problem?

[Gabe da Silveira]

Logged In: YES
user_id=268048

Precisely, although it is less annoying in that instance because the
query still works as expected. What's annoying is when you
create a table and the fields are created differently than you typed
them.

[Alexander M. Turek]

Logged In: YES
user_id=418833

Marc,

Your fix messes up the pretty-printer!
This is rather an analyzer problem.

FIRST and STATUS have been inside this reserved words array
because they are part of MySQL commands.
STATUS is used in "SHOW STATUS" and FIRST part of the
ALTER sytax.

Furthermore, this bug affects a lot more words than just
STATUS and FIRST. I do not want to know what happens if we
remove them all from the reserved words array...

Because of your change, both queries are not highlighted well
anymore.

The only way to get around this is to teach the analyzer when
it should expect keywords and when it might find identifiers
instead.

Anyway, I recommend that you revert your change as it
destroys more than it fixes.

[Marc Delisle]

Logged In: YES
user_id=210714

Reversed.

[Marc Delisle]

Logged In: YES
user_id=210714

Solving the CREATE TABLE case is easy in the parser, but
solving all the cases would take much time.

select first from mytable;
select first, first from mytable;
insert into mytable(first) values('bla');

[Ivan Baldo Bosco]

Logged In: YES
user_id=325446

PHPMyAdmin should not modify the query sent to MySQL, the
pretty printing should only be used for visualization purposes.
I too consider this behaviour as an error and a nasty thing.

[Marc Delisle]

Logged In: YES
user_id=210714

Fixed in the query sent to MySQL (pretty printer still shows
a capitalized keyword).