fork download 
  1. <?php
  2.  
  3. #####################################################
  4. # Test script for: https: //github.com/ProjectNami/projectnami/pull/416
  5. # Created by: Solomon Rutzky ( https: //SqlQuantumLeap.com/ )
  6. # Created on: 2020-11-05
  7. #
  8. #####################################################
  9.  
  10. ##### RegEx patterns:
  11. $OriginalPattern = '(IF\s*\(*((.*),(.*),(.*))\)\s*(AS\s*\w*))';
  12. $UpdatedPattern = '\bIF\s*\((.+?),(.+?),(.+?)\)\s*(AS\s*\w*)';
  13. $RevisedUpdatedPattern = '\bIF\s*\(.+?,.+?,.+?\)\s*(?:AS\s*\w*)';
  14. $ReRevisedUpdatedPattern = '\b(I)F\s*\(.+?,.+?,.+?\)\s*(?:AS\s*\w*)';
  15.  
  16. ##### Test queries:
  17. $Query366 = 'SELECT FaUserLogin.*, UserMeta.meta_value, datediff(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration FROM ';
  18.  
  19. $Query415 = 'SELECT id, user_id, item_id, secondary_item_id, component_name, component_action, date_notified, is_new, COUNT(id) as total_count FROM wp_bp_notifications n WHERE user_id IN ';
  20.  
  21. $Query366_2 = 'SELECT FaUserLogin.*, UserMeta.meta_value, dated if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, table.Oops, function(col1)as"g" FROM ';
  22.  
  23. $Query366_3 = 'SELECT FaUserLogin.*, UserMeta.meta_value, dated if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4,col3,col4)AS"test2" FROM ';
  24.  
  25. $QueryOneIF = 'SELECT col1, if(1==2,"a","bbb")AS test, col3, DATEDIFF(c4, c5, c6)AS"t2" FROM ';
  26.  
  27. $QueryTwoIFs = 'SELECT col1, if(1==2,"a","bbb")AS test, col3, IF(c7==2, c8, c9)AS"t3" FROM ';
  28.  
  29. ##### Show effect of bug:
  30. print "-----------------------------------------------\n";
  31. print "Original query from Issue #366:\n";
  32. print $Query366."\n\n";
  33.  
  34. print "Bug in original pattern results in (array elements 3 - 6 [below] are used):\n";
  35. print "dated CASE WHEN f(SECOND THEN FaUserLogin.time_login ELSE FaUserLogin.time_last_seen END as duration\n\n";
  36.  
  37. preg_match('/'.$OriginalPattern.'/is', $Query366, $Matches);
  38. print_r($Matches);
  39.  
  40. ##
  41.  
  42. print "-----------------------------------------------\n";
  43. print "Original query from Issue #415:\n";
  44. print $Query415."\n\n";
  45.  
  46. print "Bug in original pattern results in (array elements 3 - 6 [below] are used):\n";
  47. print "date_not CASE WHEN ied THEN is_new ELSE COUNT(id END as total_count\n\n";
  48.  
  49. preg_match('/'.$OriginalPattern.'/is', $Query415, $Matches);
  50. print_r($Matches);
  51.  
  52. ##
  53.  
  54. print "-----------------------------------------------\n";
  55. print "Updated query from Issue #366 with more SELECT fields including a function:\n";
  56. print $Query366_2."\n\n";
  57.  
  58. print "Bug in original pattern also captures to last \") AS\" found:\n";
  59. preg_match('/'.$OriginalPattern.'/is', $Query366_2, $Matches);
  60. print_r($Matches);
  61.  
  62.  
  63. print "-----------------------------------------------\n";
  64. print "Updated query from Issue #366 with an extra IF() function:\n";
  65. print $Query366_3."\n\n";
  66.  
  67. print "Bug in original pattern also captures to last \") AS\" found:\n";
  68. preg_match('/'.$OriginalPattern.'/is', $Query366_3, $Matches);
  69. print_r($Matches);
  70.  
  71.  
  72. ##### Show effect of updated pattern:
  73. print "-----------------------------------------------\n";
  74. print "New pattern:\n";
  75. print "a) makes the match much more specific\n";
  76. print "b) removes unused groups\n";
  77. print "\n";
  78.  
  79. print "Matches for updated pattern applied to original query for Issue #366 (should be none):\n";
  80. preg_match('/'.$UpdatedPattern.'/is', $Query366, $Matches);
  81. print_r($Matches);
  82. print "------\n";
  83.  
  84. print "Matches for updated pattern applied to query for Issue #415 (should be none):\n";
  85. preg_match('/'.$UpdatedPattern.'/is', $Query415, $Matches);
  86. print_r($Matches);
  87.  
  88. ##
  89.  
  90. print "-----------------------------------------------\n";
  91. print "Test query that should match on 1 IF function:\n";
  92. print $QueryOneIF."\n\n";
  93.  
  94. print "Updated pattern captures the following:\n";
  95. preg_match_all('/'.$UpdatedPattern.'/is', $QueryOneIF, $Matches, PREG_SET_ORDER);
  96. print_r($Matches);
  97.  
  98.  
  99. print "-----------------------------------------------\n";
  100. print "Test query that should match on 2 IF functions:\n";
  101. print $QueryTwoIFs."\n\n";
  102.  
  103. print "Updated pattern captures the following:\n";
  104. preg_match_all('/'.$UpdatedPattern.'/is', $QueryTwoIFs, $Matches, PREG_SET_ORDER);
  105. print_r($Matches);
  106.  
  107. ##### Show new IIF option:
  108. print "==========================================================\n";
  109. print "While the new pattern will work, there is an even better option:\n";
  110. print "The new IIF() function, introduced in SQL Server 2012, which is the same\n";
  111. print "as MySQL's IF() function, except it has an extra \"I\" in the name.\n\n";
  112.  
  113. print "We can simplify the updated pattern by removing the capture groups for\n";
  114. print "the three items separated by the commas and within the parens since there\n";
  115. print "is nothing to reconstruct. Now the operation is a simple concatenation:\n";
  116. print "a RegEx Replace will simply prefix an \"I\" onto any match:\n\n";
  117.  
  118. print "-----------------------------------------------\n";
  119. print "Revised updated pattern captures the following for the 1 IF query:\n";
  120. preg_match_all('/'.$RevisedUpdatedPattern.'/is', $QueryOneIF, $Matches, PREG_SET_ORDER);
  121. print_r($Matches);
  122.  
  123. print "---------\n";
  124. print "And the RegEx Replace returns:\n";
  125. print preg_replace('/'.$RevisedUpdatedPattern.'/is', 'I$0', $QueryOneIF);
  126. print "\n";
  127.  
  128. ##
  129.  
  130. print "-----------------------------------------------\n";
  131. print "Revised updated pattern captures the following for the 2 IFs query:\n";
  132. preg_match_all('/'.$RevisedUpdatedPattern.'/is', $QueryTwoIFs, $Matches, PREG_SET_ORDER);
  133. print_r($Matches);
  134.  
  135. print "---------\n";
  136. print "And the RegEx Replace returns:\n";
  137. print preg_replace('/'.$RevisedUpdatedPattern.'/is', 'I$0', $QueryTwoIFs);
  138. print "\n";
  139.  
  140. ##
  141.  
  142. print "----------------------------------------------------------\n";
  143. print "We can even get fancy and capture the \"I\" in \"IF\" in\n";
  144. print "order to match the case of the captured character.\n\n";
  145.  
  146. print "-----------------------------------------------\n";
  147. print "RE-revised updated pattern captures the following for the 1 IF query:\n";
  148. preg_match_all('/'.$ReRevisedUpdatedPattern.'/is', $QueryOneIF, $Matches, PREG_SET_ORDER);
  149. print_r($Matches);
  150.  
  151. print "---------\n";
  152. print "And the RegEx Replace returns:\n";
  153. print preg_replace('/'.$ReRevisedUpdatedPattern.'/is', '$1$0', $QueryOneIF);
  154. print "\n";
  155.  
  156. ##
  157.  
  158. print "-----------------------------------------------\n";
  159. print "RE-revised updated pattern captures the following for the 2 IFs query:\n";
  160. preg_match_all('/'.$ReRevisedUpdatedPattern.'/is', $QueryTwoIFs, $Matches, PREG_SET_ORDER);
  161. print_r($Matches);
  162.  
  163. print "---------\n";
  164. print "And the RegEx Replace returns:\n";
  165. print preg_replace('/'.$ReRevisedUpdatedPattern.'/is', '$1$0', $QueryTwoIFs);
  166. print "\n";
  167.  
  168. ?>
  169.  
Success #stdin #stdout 0.02s 24692KB
comments (?)
stdin 
Standard input is empty
stdout 
-----------------------------------------------
Original query from Issue #366:
SELECT FaUserLogin.*, UserMeta.meta_value, datediff(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration FROM 

Bug in original pattern results in (array elements 3 - 6 [below] are used):
dated CASE WHEN f(SECOND THEN FaUserLogin.time_login ELSE FaUserLogin.time_last_seen END as duration

Array
(
    [0] => iff(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration
    [1] => iff(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration
    [2] => f(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen
    [3] => f(SECOND
    [4] => FaUserLogin.time_login
    [5] => FaUserLogin.time_last_seen
    [6] => as duration
)
-----------------------------------------------
Original query from Issue #415:
SELECT id, user_id, item_id, secondary_item_id, component_name, component_action, date_notified, is_new, COUNT(id) as total_count FROM wp_bp_notifications n WHERE user_id IN 

Bug in original pattern results in (array elements 3 - 6 [below] are used):
date_not CASE WHEN ied THEN is_new ELSE COUNT(id END as total_count

Array
(
    [0] => ified, is_new, COUNT(id) as total_count
    [1] => ified, is_new, COUNT(id) as total_count
    [2] => ied, is_new, COUNT(id
    [3] => ied
    [4] =>  is_new
    [5] =>  COUNT(id
    [6] => as total_count
)
-----------------------------------------------
Updated query from Issue #366 with more SELECT fields including a function:
SELECT FaUserLogin.*, UserMeta.meta_value, dated if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, table.Oops, function(col1)as"g" FROM 

Bug in original pattern also captures to last ") AS" found:
Array
(
    [0] => if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, table.Oops, function(col1)as
    [1] => if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, table.Oops, function(col1)as
    [2] => SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, table.Oops, function(col1
    [3] => SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration
    [4] =>  table.Oops
    [5] =>  function(col1
    [6] => as
)
-----------------------------------------------
Updated query from Issue #366 with an extra IF() function:
SELECT FaUserLogin.*, UserMeta.meta_value, dated if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4,col3,col4)AS"test2" FROM 

Bug in original pattern also captures to last ") AS" found:
Array
(
    [0] => if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4,col3,col4)AS
    [1] => if(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4,col3,col4)AS
    [2] => SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4,col3,col4
    [3] => SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration, if(3!=4
    [4] => col3
    [5] => col4
    [6] => AS
)
-----------------------------------------------
New pattern:
a) makes the match much more specific
b) removes unused groups

Matches for updated pattern applied to original query for Issue #366 (should be none):
Array
(
)
------
Matches for updated pattern applied to query for Issue #415 (should be none):
Array
(
)
-----------------------------------------------
Test query that should match on 1 IF function:
SELECT col1, if(1==2,"a","bbb")AS test, col3, DATEDIFF(c4, c5, c6)AS"t2" FROM 

Updated pattern captures the following:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
            [1] => 1==2
            [2] => "a"
            [3] => "bbb"
            [4] => AS test
        )

)
-----------------------------------------------
Test query that should match on 2 IF functions:
SELECT col1, if(1==2,"a","bbb")AS test, col3, IF(c7==2, c8, c9)AS"t3" FROM 

Updated pattern captures the following:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
            [1] => 1==2
            [2] => "a"
            [3] => "bbb"
            [4] => AS test
        )

    [1] => Array
        (
            [0] => IF(c7==2, c8, c9)AS
            [1] => c7==2
            [2] =>  c8
            [3] =>  c9
            [4] => AS
        )

)
==========================================================
While the new pattern will work, there is an even better option:
The new IIF() function, introduced in SQL Server 2012, which is the same
as MySQL's IF() function, except it has an extra "I" in the name.

We can simplify the updated pattern by removing the capture groups for
the three items separated by the commas and within the parens since there
is nothing to reconstruct. Now the operation is a simple concatenation:
a RegEx Replace will simply prefix an "I" onto any match:

-----------------------------------------------
Revised updated pattern captures the following for the 1 IF query:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
        )

)
---------
And the RegEx Replace returns:
SELECT col1, Iif(1==2,"a","bbb")AS test, col3, DATEDIFF(c4, c5, c6)AS"t2" FROM 
-----------------------------------------------
Revised updated pattern captures the following for the 2 IFs query:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
        )

    [1] => Array
        (
            [0] => IF(c7==2, c8, c9)AS
        )

)
---------
And the RegEx Replace returns:
SELECT col1, Iif(1==2,"a","bbb")AS test, col3, IIF(c7==2, c8, c9)AS"t3" FROM 
----------------------------------------------------------
We can even get fancy and capture the "I" in "IF" in
order to match the case of the captured character.

-----------------------------------------------
RE-revised updated pattern captures the following for the 1 IF query:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
            [1] => i
        )

)
---------
And the RegEx Replace returns:
SELECT col1, iif(1==2,"a","bbb")AS test, col3, DATEDIFF(c4, c5, c6)AS"t2" FROM 
-----------------------------------------------
RE-revised updated pattern captures the following for the 2 IFs query:
Array
(
    [0] => Array
        (
            [0] => if(1==2,"a","bbb")AS test
            [1] => i
        )

    [1] => Array
        (
            [0] => IF(c7==2, c8, c9)AS
            [1] => I
        )

)
---------
And the RegEx Replace returns:
SELECT col1, iif(1==2,"a","bbb")AS test, col3, IIF(c7==2, c8, c9)AS"t3" FROM
https://ideone.com/bhisge
Test for Project Nami RegEx bug for "IF" in SELECT statements.
language:
PHP (php 7.3.5)
created:
5 years ago
visibility:
secret

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!