It looks like a generic feature request, not a bug anyhow related to CREATE OR REPLACE TABLE.
If it's a bug about CREATE OR REPLACE, I would close it as not-a-bug, because

a) indeed, CREATE OR REPLACE TABLE IF NOT EXISTS does not work, simply because it makes no logical sense,
b) CREATE OR REPLACE is not a replacement for CREATE IF NOT EXISTS, they serve different purposes, and I'd say in general trying to juggle them in the same context is more dangerous than beneficial, because your application will have to take into account that maybe the table was re-created and hence is empty and might have a different structure, or it was not touched and hence it has contents etc.; and
c) new syntax does not normally come with some means for backward compatibility.

Of course, in some specific situations (and yours might be one of those, I can't know) it can make sense.

I suggest you modify the subject to the request about reversed executable comments, having CREATE OR REPLACE vs NOT EXISTS as a mere example of what you're trying to achieve, and I'll convert the issue into a task.

I agree, so I made the changes. But I can't change a bug to feature request.

A possible syntax could be

CREATE /*!100000 OR REPLACE */ TABLE /*!!100000 IF NOT EXISTS */ t1 ( ...)

I'll start working on this ticket

Hi gabocic still wanting to work on this MDEV?

Some background around lex and this MDEV.
Lets analyze an example of version-controlled executable comments as found in KB:

SELECT 2 /*M!50101 +   1 */; # Note no delimiter `;` inside

So there is a function lex_one_token() -> sql/sql_lex.cc, which is checking the next state of the token from list of possible values found include/m_ctype.h using my_lex_states.
Next state is iterating starting from 0 MY_LEX_START.
Depending on the state it will go to MY_LEX_LONG_COMENT (there is also MY_LEX_COMMENT called from MY_LEX_MINUS_OR_COMMENT state covering -- MySQL comments).
Now there is a class{{Lex_input_stream in sql_lex.h}} used in sql/sql_yacc.yy and by THD::Parser_state in sql/sql_class.h for handling lexical analysis.
Instance of this class is used in lex_one_token() as lip variable to store the context of the yylip generated from sql/sql_yacc.cc.
Some of used API with lip:

• yyGet() - get the next token and changing the state (danger)
• yyPeek[n]([n]) - functions to look 1 or n steps ahead n and not changing the state
  --yyUnlink - changing m_ptr internal buffer of yylip used for inspecting the next token, etc.

For comments handling there are 2 members of this structure (found in sql/sql_lex.h):

/** State of the lexical analyser for comments. */

enum_comment_state in_comment;

enum_comment_state in_comment_saved;

where enum can be:

• NO_COMMENT - not parsing as a comment => command,
• PRESERVE_COMMENT- parse as comment and preserve and
• DISCARD_COMMENT- handle it as versioned comment.

The best situation we can observe on debugging an example:
Back trace of above query:

#0  lex_one_token (yylval=0x7fffef615610, thd=0x7fffd8000ce8) at /home/anel/mariadb/10.2/sql/sql_lex.cc:1798

#1  0x0000555556195152 in MYSQLlex (yylval=0x7fffef615610, thd=0x7fffd8000ce8) at /home/anel/mariadb/10.2/sql/sql_lex.cc:1264

#2  0x000055555655139d in MYSQLparse (thd=0x7fffd8000ce8) at sql/sql_yacc.cc:21438

#3  0x00005555561de382 in parse_sql (thd=0x7fffd8000ce8, parser_state=0x7fffef6165c0, creation_ctx=0x0, do_pfs_digest=true) at /home/anel

/mariadb/10.2/sql/sql_parse.cc:10032

#4  0x00005555561d4a93 in mysql_parse (thd=0x7fffd8000ce8, rawbuf=0x7fffd8013770 "SELECT 2 /*M!50101 +   1 */", length=27, parser_state=0

x7fffef6165c0, is_com_multi=false, is_next_command=false) at /home/anel/mariadb/10.2/sql/sql_parse.cc:7686

#5  0x00005555561b121e in dispatch_command (command=COM_QUERY, thd=0x7fffd8000ce8, packet=0x7fffd80082e9 "SELECT 2 /*M!50101 +   1 */", p

acket_length=27, is_com_multi=false, is_next_command=false) at /home/anel/mariadb/10.2/sql/sql_parse.cc:1826

#6  0x00005555561ae8c1 in do_command (thd=0x7fffd8000ce8) at /home/anel/mariadb/10.2/sql/sql_parse.cc:1380

#7  0x000055555641cbfc in do_handle_one_connection (connect=0x5555592f0db8) at /home/anel/mariadb/10.2/sql/sql_connect.cc:1336

#8  0x000055555641c5ec in handle_one_connection (arg=0x5555592f0db8) at /home/anel/mariadb/10.2/sql/sql_connect.cc:1241

#9  0x00007ffff683d6db in start_thread (arg=0x7fffef617700) at pthread_create.c:463

#10 0x00007ffff5bdca3f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Now in sql/sql_lex.cc there is a logic for handling versioned comments for MySQL version higher or equal than 5.7 and {{MariaDB}}specific:

 if (version <= MYSQL_VERSION_ID &&

              (version < 50700 || version > 99999 || maria_comment_syntax))

Note: MYSQL_VERSION_ID defined in include/mysql_version.h.in and represents latest version of server (in my case 10.2.35 latest Debug build atm).

So regarding the suggested syntax of reversed comments:
/*[M]![!]version*/ would mean 2 conditions? Question 1:

• still respect handling of versioned comments regarding MySQL and MariaDB
• It should be handled only if MYSQL_VERSION_ID != version}}
  + Some of the examples? Question 2
  --Working on 10.2.10, running query SELECT 2 /*!!100235*/; #should work
  --Working on 10.2.35 but I'm running SELECT 2 /*!!100235*/; #should fail
  + What are the use cases here? What are the benefits, why?Question 3

Note: after testing found that nesting of comments doesn't work and should it?

SELECT 2  /*M!50101 + 2*/; #works

SELECT 2  /*M!50101 + 2/*comment*/*/; #works

SELECT 2  /*M!50101 + 2/*!50101+2*/*/; #not working

SELECT 2  /*M!50101/*!50101+2*/*/; #not working, but should?

f_razzoli serg please let me know your thoughts regarding above questions.

Hi @Anel Husakovic

Yes, I'm still interested in working on this ticket, although I'm currently not finding the time to do it.

If you can keep this ticket with me for a few more weeks, I'll do my best to start digging into it.

Thanks!

Hi gabocic sure, take your time, probably will be free couple more weeks.