quote_char causes incorrect SQL in aliases

[djerius]

As part of an investigation in incorrect quoting happening, I modified t/01-sql_abstract_more.t to set quote_char and name_sep:

diff --git a/t/01-sql_abstract_more.t b/t/01-sql_abstract_more.t
index bdf88bc..d0945e2 100644
--- a/t/01-sql_abstract_more.t
+++ b/t/01-sql_abstract_more.t
@@ -14,7 +14,7 @@ diag( "Testing SQL::Abstract::More $SQL::Abstract::More::VERSION, "
 use constant N_DBI_MOCK_TESTS =>  2;
 
 
-my $sqla = SQL::Abstract::More->new;
+my $sqla = SQL::Abstract::More->new( quote_char => q{"}, name_sep => q{.});^M
 my ($sql, @bind, $join);

and then manually reviewed the results, with the incorrect instances shown below. Some of the emitted SQL is incorrectly quoted, usually overquoted, but sometimes underquoted. I've highlighted the incorrect parts below; hopefully it's obvious. For some I think the test itself may be incorrect (e.g. passing "COUNT(*)" as a column name instead of "COUNT(*)|count")

#   Failed test '-from => arrayref (several tables)'
#   at t/01-sql_abstract_more.t line 66.
# SQL expressions differ
#  got: SELECT "bar" FROM "Foo, Bar, Buz" WHERE ( "bar" > ? )
# want: SELECT bar FROM Foo, Bar, Buz WHERE bar > ?
# 
# mismatch around
# [ "bar" ] != [ bar ]
# left: "bar"
# right: bar
# 

SELECT "bar" FROM "Foo, Bar, Buz" WHERE ( "bar" > ? )

#   Failed test '-from with alias'
#   at t/01-sql_abstract_more.t line 91.
# SQL expressions differ
#  got: SELECT "bar" FROM """Foo"" AS ""f""" WHERE ( "f"."bar" = ? )
# want: SELECT bar FROM Foo AS f WHERE f.bar = ?
#  #/
# mismatch around
# [ "bar" ] != [ bar ]
# left: "bar"
# right: bar
# 

SELECT "bar" FROM """Foo"" AS ""f""" WHERE ( "f"."bar" = ? )

#   Failed test 'subquery in select list'
#   at t/01-sql_abstract_more.t line 144.
# SQL expressions differ
#  got: SELECT "col1", (SELECT max(bar) FROM Bar WHERE bar < ?) AS "col2", "col3" FROM "Foo" WHERE ( "foo" = ? )
# want: SELECT col1, (SELECT max(bar) FROM Bar WHERE bar < ?) AS col2, col3 FROM Foo WHERE foo = ?
# 
# mismatch around
# [ "col1" ] != [ col1 ]
# left: "col1" ( SELECT max( bar ) FROM Bar WHERE bar < ? ) AS "col2" "col3"
# right: col1 ( SELECT max( bar ) FROM Bar WHERE bar < ? ) AS col2 col3
# 

SELECT "col1", (SELECT max(bar) FROM Bar WHERE bar < ?) AS "col2", "col3" FROM "Foo" WHERE ( "foo" = ? )

#   Failed test 'subquery, example from the doc'
#   at t/01-sql_abstract_more.t line 164.
# SQL expressions differ
#  got: SELECT "col1", "col2", (SELECT "COUNT(*)" FROM "Foo" WHERE ( ( "bar_id" = "Bar"."bar_id" AND ( "height" BETWEEN ? AND ? ) ) )) AS "col3", "col4" FROM "Bar" WHERE ( "color" = ? )
# want: SELECT col1, col2,
#          (SELECT COUNT(*) FROM Foo WHERE bar_id=Bar.bar_id and height BETWEEN ? AND ?) AS col3,
#          col4
#     FROM Bar WHERE color = ?
# 
# mismatch around
# [ "col1" ] != [ col1 ]
# left: "col1" "col2" ( SELECT "COUNT( * ) " FROM "Foo" WHERE "bar_id" = "Bar"."bar_id" AND "height" BETWEEN ? AND ? ) AS "col3" "col4"
# right: col1 col2 ( SELECT COUNT( * ) FROM Foo WHERE bar_id = Bar.bar_id AND height BETWEEN ? AND ? ) AS col3 col4
# 

SELECT "col1", "col2", (SELECT "COUNT(*)" FROM "Foo" WHERE ( ( "bar_id" = "Bar"."bar_id" AND ( "height" BETWEEN ? AND ? ) ) )) AS "col3", "col4" FROM "Bar" WHERE ( "color" = ? )

#   Failed test 'subquery in -from'
#   at t/01-sql_abstract_more.t line 186.
# SQL expressions differ
#  got: SELECT "subq".*, count(*) AS "nb_a" FROM """(SELECT """"a"""", """"b"""", """"c"""" FROM """"Foo"""" WHERE ( """"foo"""" = ? ))"" AS ""subq""" WHERE ( "b" = ? ) GROUP BY "a"
# want: SELECT subq.*, count(*) AS nb_a
#    FROM (SELECT a, b, c FROM Foo WHERE foo = ?) AS subq
#    WHERE b = ?
#    GROUP BY a
# 
# mismatch around
# [ "subq".* ] != [ subq.* ]
# left: "subq".* count( * ) AS "nb_a"
# right: subq.* count( * ) AS nb_a
# 

SELECT "subq"., count() AS "nb_a" FROM """(SELECT """"a"""", """"b"""", """"c"""" FROM """"Foo"""" WHERE ( """"foo"""" = ? ))"" AS ""subq""" WHERE ( "b" = ? ) GROUP BY "a"

#   Failed test 'subqueries in column list and in source'
#   at t/01-sql_abstract_more.t line 212.
# SQL expressions differ
#  got: SELECT "x", (SELECT "MAX(amount)" FROM "Expenses" WHERE ( ( "date" > ? AND "exp_id" = "x" ) )) AS "max_amount" FROM """(SELECT """"f"""" AS """"x"""" FROM """"Foo"""" UNION SELECT """"b"""" AS """"x"""" FROM """"Bar"""" WHERE ( """"barbar"""" = ? ))"" AS ""Foo_union_Bar""" ORDER BY "x"
# want:  SELECT x, (SELECT MAX(amount) FROM Expenses WHERE ( date > ? AND exp_id = x)) AS max_amount
#     FROM (SELECT f AS x FROM Foo UNION SELECT b AS x FROM Bar WHERE barbar = ?) AS Foo_union_Bar
#     ORDER BY x
# 
# mismatch around
# [ "x" ] != [ x ]
# left: "x" ( SELECT "MAX( amount ) " FROM "Expenses" WHERE "date" > ? AND "exp_id" = "x" ) AS "max_amount"
# right: x ( SELECT MAX( amount ) FROM Expenses WHERE date > ? AND exp_id = x ) AS max_amount
# 

SELECT "x", (SELECT "MAX(amount)" FROM "Expenses" WHERE ( ( "date" > ? AND "exp_id" = "x" ) )) AS "max_amount" FROM """(SELECT """"f"""" AS """"x"""" FROM """"Foo"""" UNION SELECT """"b"""" AS """"x"""" FROM """"Bar"""" WHERE ( """"barbar"""" = ? ))"" AS ""Foo_union_Bar""" ORDER BY "x"

#   Failed test 'select from join'
#   at t/01-sql_abstract_more.t line 240.
# SQL expressions differ
#  got: SELECT * FROM Foo INNER JOIN Bar ON ( "Foo"."fk" = "Bar"."pk" )
# want: SELECT * FROM Foo INNER JOIN Bar ON Foo.fk=Bar.pk
# 
# mismatch around
# [ "Foo"."fk" ] != [ Foo.fk ]
# left: "Foo"."fk" "Bar"."pk"
# right: Foo.fk Bar.pk
# 

SELECT * FROM Foo INNER JOIN Bar ON ( "Foo"."fk" = "Bar"."pk" )

#   Failed test 'select from join with bind value'
#   at t/01-sql_abstract_more.t line 250.
# SQL expressions differ
#  got: SELECT * FROM Foo INNER JOIN Bar ON ( ( "Foo"."fk" = "Bar"."pk" AND "Foo"."other" = ? ) )
# want: SELECT * FROM Foo INNER JOIN Bar ON Foo.fk=Bar.pk and Foo.other = ?
# 
# mismatch around
# [ "Foo"."fk" ] != [ Foo.fk ]
# left: "Foo"."fk" "Bar"."pk"
# right: Foo.fk Bar.pk
# 

SELECT * FROM Foo INNER JOIN Bar ON ( ( "Foo"."fk" = "Bar"."pk" AND "Foo"."other" = ? ) )

#   Failed test 'aliased cols with '|''
#   at t/01-sql_abstract_more.t line 409.
# SQL expressions differ
#  got: SELECT "A||B", "C||D" AS "cd", (E||F||G) AS "efg", "true|false" AS "bool" FROM "Foo"
# want: SELECT A||B, C||D AS cd, (E||F||G) AS efg, true|false AS bool FROM Foo
# 
# mismatch around
# [ "A||B" ] != [ A||B ]
# left: "A||B" "C||D" AS "cd" ( E||F||G ) AS "efg" "true|false" AS "bool"
# right: A||B C||D AS cd ( E||F||G ) AS efg true|false AS bool
# 

SELECT "A||B", "C||D" AS "cd", (E||F||G) AS "efg", "true|false" AS "bool" FROM "Foo"

#   Failed test 'aliased cols with '|', single char on left-hand side'
#   at t/01-sql_abstract_more.t line 419.
# SQL expressions differ
#  got: SELECT "NULL" AS "a1", "2" AS "a2", "x" AS "a3" FROM "Foo"
# want: SELECT NULL AS a1, 2 AS a2, x AS a3 FROM Foo
# 
# mismatch around
# [ "NULL" ] != [ NULL ]
# left: "NULL" "a1"
# right: NULL a1
# 

SELECT "NULL" AS "a1", "2" AS "a2", "x" AS "a3" FROM "Foo"

#   Failed test 'join syntax'
#   at t/01-sql_abstract_more.t line 527.
# SQL expressions differ
#  got: "Foo" AS "f" LEFT OUTER JOIN Bar ON ( ( "f"."fk_A" = "Bar"."pk_A" AND "f"."fk_B" = "Bar"."pk_B" ) )
# want: Foo AS f LEFT OUTER JOIN Bar ON f.fk_A = Bar.pk_A AND f.fk_B = Bar.pk_B
# 
# mismatch around
# [ "Foo" ] != [ Foo ]
# left: "Foo" "f"
# right: Foo f
# 

"Foo" AS "f" LEFT OUTER JOIN Bar ON ( ( "f"."fk_A" = "Bar"."pk_A" AND "f"."fk_B" = "Bar"."pk_B" ) )

#   Failed test 'join syntax with OR'
#   at t/01-sql_abstract_more.t line 534.
# SQL expressions differ
#  got: Foo INNER JOIN Bar ON ( ( "Foo"."A" < "Bar"."B" OR "Foo"."C" < "Bar"."D" ) )
# want: Foo INNER JOIN Bar ON Foo.A < Bar.B OR Foo.C < Bar.D
# 
# mismatch around
# [ "Foo"."A" ] != [ Foo.A ]
# left: "Foo"."A" "Bar"."B"
# right: Foo.A Bar.B
# 

Foo INNER JOIN Bar ON ( ( "Foo"."A" < "Bar"."B" OR "Foo"."C" < "Bar"."D" ) )

#   Failed test at t/01-sql_abstract_more.t line 553.
# SQL expressions differ
#  got: "Table1" AS "t1" INNER JOIN "Table2" AS "t2" ON ( "t1"."ab" = "t2"."cd" ) INNER JOIN Table3 ON ( ( "t2"."ef" > "Table3"."gh" AND "t2"."ij" < "Table3"."kl" ) ) LEFT OUTER JOIN Table4 ON ( "t1"."mn" = "Table4"."op" )
# want: Table1 AS t1 INNER JOIN      Table2 AS t2 ON t1.ab=t2.cd
#                 INNER JOIN      Table3       ON t2.ef>Table3.gh 
#                                             AND t2.ij<Table3.kl
#                 LEFT OUTER JOIN Table4       ON t1.mn=Table4.op
# 
# mismatch around
# [ "Table1" ] != [ Table1 ]
# left: "Table1" "t1"
# right: Table1 t1
# 

"Table1" AS "t1" INNER JOIN "Table2" AS "t2" ON ( "t1"."ab" = "t2"."cd" ) INNER JOIN Table3 ON ( ( "t2"."ef" > "Table3"."gh" AND "t2"."ij" < "Table3"."kl" ) ) LEFT OUTER JOIN Table4 ON ( "t1"."mn" = "Table4"."op" )

#   Failed test 'full outer join'
#   at t/01-sql_abstract_more.t line 565.
# SQL expressions differ
#  got: Foo FULL OUTER JOIN Bar ON ( "Foo"."a" = "Bar"."b" )
# want: Foo FULL OUTER JOIN Bar ON Foo.a=Bar.b
# 
# mismatch around
# [ "Foo"."a" ] != [ Foo.a ]
# left: "Foo"."a" "Bar"."b"
# right: Foo.a Bar.b
# 

Foo FULL OUTER JOIN Bar ON ( "Foo"."a" = "Bar"."b" )

[djerius]

I've created a "quoted" version of 01_sql_abstract_more.t as 08-sql_abstract_more_quoted.t

https://github.com/djerius/SQL-Abstract-More/commits/sqla_escape_table_alias/

with the expected results from quoting (I hope; I've gone through the results a number of times, and have caught issues, so there may be some remaining).

My understanding is that in the "$name|$alias" construct $name is untouched while $alias is quoted. It might safe be quote $name if it is a valid SQL identifier but I'll admit to not thinking through all of the corner cases.

[djerius]

I've pushed some updates to that branch.

1. More fixes to the tests to catch some errors and misunderstandings on my part
2. Code changes which allow the tests to pass.

The code hasn't been commented yet, so isn't yet ready for a PR.

[djerius]

Please see #26