archived-sql-formatter/tests/sql.sql

SELECT customer_id, customer_name, COUNT(order_id) as total
FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customer_id, customer_name
HAVING COUNT(order_id) > 5
ORDER BY COUNT(order_id) DESC;
---

---
UPDATE customers
        SET totalorders = ordersummary.total
        FROM (SELECT customer_id, count(order_id) As total
FROM orders GROUP BY customer_id) As ordersummary
        WHERE customers.customer_id = ordersummary.customer_id
---
SELECT * FROM sometable
UNION ALL
SELECT * FROM someothertable;
---
SET NAMES 'utf8';
---
CREATE TABLE `PREFIX_address` (
  `id_address` int(10) unsigned NOT NULL auto_increment,
  `id_country` int(10) unsigned NOT NULL,
  `id_state` int(10) unsigned default NULL,
  `id_customer` int(10) unsigned NOT NULL default '0',
  `id_manufacturer` int(10) unsigned NOT NULL default '0',
  `id_supplier` int(10) unsigned NOT NULL default '0',
  `id_warehouse` int(10) unsigned NOT NULL default '0',
  `alias` varchar(32) NOT NULL,
  `company` varchar(64) default NULL,
  `lastname` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `address1` varchar(128) NOT NULL,
  `address2` varchar(128) default NULL,
  `postcode` varchar(12) default NULL,
  `city` varchar(64) NOT NULL,
  `other` text,
  `phone` varchar(16) default NULL,
  `phone_mobile` varchar(16) default NULL,
  `vat_number` varchar(32) default NULL,
  `dni` varchar(16) DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `active` tinyint(1) unsigned NOT NULL default '1',
  `deleted` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY (`id_address`),
  KEY `address_customer` (`id_customer`),
  KEY `id_country` (`id_country`),
  KEY `id_state` (`id_state`),
  KEY `id_manufacturer` (`id_manufacturer`),
  KEY `id_supplier` (`id_supplier`),
  KEY `id_warehouse` (`id_warehouse`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
CREATE TABLE `PREFIX_alias` (
  `id_alias` int(10) unsigned NOT NULL auto_increment,
  `alias` varchar(255) NOT NULL,
  `search` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY (`id_alias`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8
---
CREATE TABLE `PREFIX_carrier` (
  `id_carrier` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_reference` int(10) unsigned NOT NULL,
  `id_tax_rules_group` int(10) unsigned DEFAULT '0',
  `name` varchar(64) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipping_handling` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `range_behavior` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_module` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_free` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipping_external` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `need_range` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `external_module_name` varchar(64) DEFAULT NULL,
  `shipping_method` int(2) NOT NULL DEFAULT '0',
  `position` int(10) unsigned NOT NULL default '0',
  `max_width` int(10) DEFAULT 0,
  `max_height` int(10)  DEFAULT 0,
  `max_depth` int(10)  DEFAULT 0,
  `max_weight` int(10)  DEFAULT 0,
  `grade` int(10)  DEFAULT 0,
  PRIMARY KEY (`id_carrier`),
  KEY `deleted` (`deleted`,`active`),
  KEY `id_tax_rules_group` (`id_tax_rules_group`)
) ENGINE=ENGINE_TYPE  DEFAULT CHARSET=utf8
---
CREATE TABLE IF NOT EXISTS `PREFIX_specific_price_rule` (
	`id_specific_price_rule` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`id_shop` int(11) unsigned NOT NULL DEFAULT '1',
	`id_currency` int(10) unsigned NOT NULL,
	`id_country` int(10) unsigned NOT NULL,
	`id_group` int(10) unsigned NOT NULL,
	`from_quantity` mediumint(8) unsigned NOT NULL,
	`price` DECIMAL(20,6),
	`reduction` decimal(20,6) NOT NULL,
	`reduction_type` enum('amount','percentage') NOT NULL,
	`from` datetime NOT NULL,
	`to` datetime NOT NULL,
	PRIMARY KEY (`id_specific_price_rule`),
	KEY `id_product` (`id_shop`,`id_currency`,`id_country`,`id_group`,`from_quantity`,`from`,`to`)
) ENGINE=ENGINE_TYPE  DEFAULT CHARSET=utf8
---
UPDATE `PREFIX_configuration` SET value = '6' WHERE name = 'PS_SEARCH_WEIGHT_PNAME'
---
UPDATE `PREFIX_hook_module` SET position = 1
WHERE
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayPayment') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'cheque')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayPaymentReturn') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'cheque')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayHome') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'homeslider')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionAuthentication') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsdata')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionShopDataDuplication') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'homeslider')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayTop') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blocklanguages')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCustomerAccountAdd') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsdata')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayCustomerAccount') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'favoriteproducts')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsModules') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statsvisits')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsGraphEngine') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'graphvisifire')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayAdminStatsGridEngine') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'gridhtml')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayLeftColumnProduct') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blocksharefb')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionSearch') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'statssearch')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryAdd') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryUpdate') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionCategoryDelete') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'actionAdminMetaSave') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockcategories')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayMyAccountBlock') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'favoriteproducts')
	OR
	id_hook = (SELECT id_hook FROM `PREFIX_hook` WHERE name = 'displayFooter') AND id_module = (SELECT id_module FROM `PREFIX_module` WHERE name = 'blockreinsurance')
---
ALTER TABLE `PREFIX_employee` ADD `bo_color` varchar(32) default NULL AFTER `stats_date_to`
---
INSERT INTO `PREFIX_cms_category_lang` VALUES(1, 3, 'Inicio', '', 'home', NULL, NULL, NULL)
---
INSERT INTO `PREFIX_cms_category` VALUES(1, 0, 0, 1, NOW(), NOW(),0)
---
UPDATE `PREFIX_cms_category` SET `position` = 0
---
ALTER TABLE `PREFIX_customer` ADD `note` text AFTER `secure_key`
---
ALTER TABLE `PREFIX_contact` ADD `customer_service` tinyint(1) NOT NULL DEFAULT 0 AFTER `email`
---
INSERT INTO `PREFIX_specific_price` (`id_product`, `id_shop`, `id_currency`, `id_country`, `id_group`, `priority`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`)
	(	SELECT dq.`id_product`, 1, 1, 0, 1, 0, 0.00, dq.`quantity`, IF(dq.`id_discount_type` = 2, dq.`value`, dq.`value` / 100), IF (dq.`id_discount_type` = 2, 'amount', 'percentage'), '0000-00-00 00:00:00', '0000-00-00 00:00:00'
		FROM `PREFIX_discount_quantity` dq
		INNER JOIN `PREFIX_product` p ON (p.`id_product` = dq.`id_product`)
	)
---
DROP TABLE `PREFIX_discount_quantity`
---
INSERT INTO `PREFIX_specific_price` (`id_product`, `id_shop`, `id_currency`, `id_country`, `id_group`, `priority`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`) (
	SELECT
		p.`id_product`,
		1,
		0,
		0,
		0,
		0,
		0.00,
		1,
		IF(p.`reduction_price` > 0, p.`reduction_price`, p.`reduction_percent` / 100),
		IF(p.`reduction_price` > 0, 'amount', 'percentage'),
		IF (p.`reduction_from` = p.`reduction_to`, '0000-00-00 00:00:00', p.`reduction_from`),
		IF (p.`reduction_from` = p.`reduction_to`, '0000-00-00 00:00:00', p.`reduction_to`)
	FROM `PREFIX_product` p
	WHERE p.`reduction_price` OR p.`reduction_percent`
)
---
ALTER TABLE `PREFIX_product`
	DROP `reduction_price`,
	DROP `reduction_percent`,
	DROP `reduction_from`,
	DROP `reduction_to`
---
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES
('PS_SPECIFIC_PRICE_PRIORITIES', 'id_shop;id_currency;id_country;id_group', NOW(), NOW()),
('PS_TAX_DISPLAY', 0, NOW(), NOW()),
('PS_SMARTY_FORCE_COMPILE', 1, NOW(), NOW()),
('PS_DISTANCE_UNIT', 'km', NOW(), NOW()),
('PS_STORES_DISPLAY_CMS', 0, NOW(), NOW()),
('PS_STORES_DISPLAY_FOOTER', 0, NOW(), NOW()),
('PS_STORES_SIMPLIFIED', 0, NOW(), NOW()),
('PS_STATSDATA_CUSTOMER_PAGESVIEWS', 1, NOW(), NOW()),
('PS_STATSDATA_PAGESVIEWS', 1, NOW(), NOW()),
('PS_STATSDATA_PLUGINS', 1, NOW(), NOW())
---
INSERT INTO `PREFIX_configuration` (`name`, `value`, `date_add`, `date_upd`) VALUES ('PS_CONDITIONS_CMS_ID', IFNULL((SELECT `id_cms` FROM `PREFIX_cms` WHERE `id_cms` = 3), 0), NOW(), NOW())
---
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` (
	`value` text
)
---
SET @defaultOOS = (SELECT value FROM `PREFIX_configuration` WHERE name = 'PS_ORDER_OUT_OF_STOCK')
---
UPDATE `PREFIX_product` p SET `cache_default_attribute` =  0 WHERE `id_product` NOT IN (SELECT `id_product` FROM `PREFIX_product_attribute`)
---
INSERT INTO `PREFIX_hook` (`name`, `title`, `description`, `position`) VALUES ('processCarrier', 'Carrier Process', NULL, 0)
---
INSERT INTO `PREFIX_stock_mvt_reason_lang` (`id_stock_mvt_reason`, `id_lang`, `name`) VALUES
(1, 1, 'Order'),
(1, 2, 'Commande'),
(2, 1, 'Missing Stock Movement'),
(2, 2, 'Mouvement de stock manquant'),
(3, 1, 'Restocking'),
(3, 2, 'Réassort')
---
INSERT INTO `PREFIX_meta_lang` (`id_lang`, `id_meta`, `title`, `url_rewrite`) VALUES
(1, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Authentication', 'authentication'),
(2, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Authentification', 'authentification'),
(3, (SELECT `id_meta` FROM `PREFIX_meta` WHERE `page` = 'authentication'), 'Autenticación', 'autenticacion')
---
LOCK TABLES `admin_assert` WRITE
---
UNLOCK TABLES
---
DROP TABLE IF EXISTS `admin_role`
---
SELECT * FROM
-- This is another comment
MyTable # One final comment

/* This is a block comment
*/ WHERE 1 = 2;
---
SELECT -- This is a test
---
SELECT Test FROM Test WHERE
(
 MyColumn = 1 )) AND ((( SomeOtherColumn = 2);
---
ALTER TABLE `test_modify` MODIFY `id` INT(11) UNSIGNED NOT NULL;
---
ALTER TABLE `test_change` CHANGE `id` `_id` BIGINT(20) UNSIGNED NULL;
---
SELECT * LIMIT 1; SELECT a,b,c,d FROM e LIMIT 1, 2; SELECT 1,2,3 WHERE a in (1,2,3,4,5) and b=5;
---
SELECT count - 50
WHERE a-50 = b
WHERE 1 and - 50
WHERE -50 = a
WHERE a = -50
WHERE 1 /*test*/ - 50
WHERE 1 and -50;
---
SELECT @ and b;
---
SELECT @"weird variable name";
---
SELECT :pdoParam;
---
SELECT "no closing quote
---
SELECT [sqlserver] FROM [escap[e]]d style];
---
SELECT a FROM b LEFT
OUTER
JOIN c on (d=f);
---
WITH cte AS (SELECT a, b FROM table),
RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
  FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
---
WITH cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
---
SELECT a,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS) AS no_others,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS current_row,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) AS grp,
    GROUP_CONCAT(b, '.') OVER (ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS tie,
    GROUP_CONCAT(b, '.') FILTER (WHERE c != 'two') OVER (ORDER BY a) AS filtered,
    CONVERT(VARCHAR(20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
    AVG(starting_salary) OVER w2 AVG,
    MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
    MAX(starting_salary) OVER (w1 ORDER BY hire_date),
    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_ROWS,
    LISTAGG(arg, ',') OVER (PARTITION BY part ORDER BY ord RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LISTAGG_RANGE,
    MIN(Revenue) OVER (PARTITION BY DepartmentID ORDER BY RevenueYear ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinRevenueBeyond
FROM t1
WINDOW w1 AS (PARTITION BY department, division), w2 AS (w1 ORDER BY hire_date);
---
SELECT 1::text;
---
-- semicolon must decrease special indentation level
MY_NON_TOP_LEVEL_KEYWORD_FX_1();
MY_NON_TOP_LEVEL_KEYWORD_FX_2();
SELECT x
FROM
  (SELECT 1 as x);
MY_NON_TOP_LEVEL_KEYWORD_FX_3();
BEGIN
    MY_NON_TOP_LEVEL_KEYWORD_FX_4();
    MY_NON_TOP_LEVEL_KEYWORD_FX_5();
END;
BEGIN
  SELECT x
  FROM
    (SELECT 1 as x);
  MY_NON_TOP_LEVEL_KEYWORD_FX_6();
END;
---
SELECT
  case when name = 1 then
    10
  when name = 2 then
    20
  when name = 3 then
    case when age > 10 then
      30
    else
      31
    end
  else
    40
  end AS case1,
  (SELECT
    case name
      when 1 then
        10
      when 2 then
        20
      when 3 then
        case age
          when 10 then
            30
          else
            31
        end
      else
        40
  end) case2,
  name
FROM
  user
---
SELECT
  (
    SELECT * FROM T LIMIT 5 OFFSET 10
  ) PostgreSQL_offset_10_limit_5,
  (
    SELECT * FROM T LIMIT 5 OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
  ) MSSQL_offset_10_limit_5,
  (
    SELECT * FROM T FETCH FIRST ROW ONLY
  ) oracle_limit_1,
  (
    SELECT * FROM T OFFSET 10 ROWS
  ) oracle_offset_10,
  (
    SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
  ) oracle_offset_10_limit_5,
FROM dual;
---
begin try
  insert into [t] ([name], [int], [float], [null])
  values (N'Ewa', 1, 1.0, null);
end try
begin catch
  if ERROR_NUMBER() = 544
    begin
      set IDENTITY_INSERT [t] on;
      begin try
        insert into [t] ([name], [int], [float], [null])
          values (N'Ewa', 1, 1.0, null);
        set IDENTITY_INSERT [t] off;
      end try
      begin catch
        set IDENTITY_INSERT [t] off;
        throw;
      end catch
    end
  else
    begin
      throw;
    end
end catch
---
BEGIN
  FOR i IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
---
SELECT a FROM test STRAIGHT_JOIN test2 ON test.id = test2.id
---
SELECT
  t.id,
  t.start,
  t.end,
  t.end AS e2,
  t.limit,
  t.begin,
  t.case,
  t.when,
  t.then,
  t.else
FROM t
WHERE t.start = t.end
---
CREATE TABLE t (c VARCHAR(20)) DEFAULT CHARACTER SET utf8mb4 ENGINE = InnoDB
---
SELECT '{}'::json #> '{}'
---
SELECT vector1 <#> vector2
---
SELECT text ~* '\w+'